PostgreSQL的序列的性能验证

浏览数：25 / 时间：2015年06月12日

前段时间有同事咨询PostgreSQL相关的问题，发现他们用了一个自动生成的32字节的字符串作为唯一键，而这张表的数据量相当大，建议他们改用序列，可减少存储空间。但用序列有一点不一样，就是序列必须顺序产生，那么高并发访问时会不会成为性能瓶颈呢？下面做个测试验证一下。

1.测试环境

个人PC上的VMware虚拟机
PC
CPU:Intel Core i5-3470 3.2G(4核)
MEM:6GB
SSD:OCZ-VERTEX4 128GB(VMware虚拟机所在磁盘，非系统盘)
OS:Win7

VMware虚拟机
CPU:4核
MEM:1GB
OS:CentOS 6.5
PG:PostgreSQL 9.3.4(shared_buffers = 128MB,其他是默认值)

2.测试方法

创建表和序列

postgres=# create sequence seq1;
CREATE SEQUENCE
postgres=# create sequence cached_seq cache 100;
CREATE SEQUENCE
postgres=# create table tb1(c1 bigint);
CREATE TABLE

分别将下面的6个测试SQL写到到不同文件中

select 1
select nextval(‘seq1‘)
select nextval(‘cached_seq‘)
insert into tb1 values(1)
insert into tb1 values(nextval(‘seq1‘))
insert into tb1 values(nextval(‘cached_seq‘))

以“select 1”为例，分别测试1，10和100并发时的tps
1个并发

-bash-4.1$ pgbench -n -c 1 -j 1 -T 2 -f s1.sql
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 2 s
number of transactions actually processed: 53902
tps = 26938.365906 (including connections establishing)
tps = 26991.946783 (excluding connections establishing)

10个并发

-bash-4.1$ pgbench -n -c 10 -j 10 -T 2 -f s1.sql
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
duration: 2 s
number of transactions actually processed: 194019
tps = 96983.799293 (including connections establishing)
tps = 97749.039911 (excluding connections establishing)

100个并发

-bash-4.1$ pgbench -n -c 100 -j 100 -T 2 -f s1.sql
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 100
duration: 2 s
number of transactions actually processed: 178286
tps = 88122.453862 (including connections establishing)
tps = 97019.521088 (excluding connections establishing)

3.测试结果

\ 并发数 SQL \	1	10	100
select 1	26991	97749	97019
select nextval(‘seq1‘)	17336	61615	69211
select nextval(‘cached_seq‘)	19379	69693	76410
insert into tb1 values(1)	4042	19792	30982
insert into tb1 values(nextval(‘seq1‘))	4083	18822	27365
insert into tb1 values(nextval(‘cached_seq‘))	3953	18145	28701

4. 结论

1，序列创建很快
单纯的nextval()在普通PC上都可以达到7万的tps，相比其他操作，创建序列本身要快的多，所以不大可能成为系统性能的瓶颈
2，序列的cache优化效果不大
因为序列创建不是性能瓶颈所以也看不出cache的优化效果。序列cache后可能会导致序列的不连续，所以除非真的需要，否则不必cache。

5. 补充

序列既然提供了cache，想必对性能还是有用处的。参考网友对Oracle的测试，使用cache 50和不使用cache，处理时间居然差了100多倍。
http://blog.itpub.net/751051/viewspace-731760/

但在PostgreSQL上进行类似的带序列的批量插入的测试，cache的性能提高仍然不明显。是不是PG对序列的优化做的太好了，都不需要那种牺牲序列连续性的序列cache上场了？

postgres=# insert into tb1 select 1 from (select generate_series(1,100000))tbx;
INSERT 0 100000
Time: 145.642 ms
postgres=# insert into tb1 select 1 from (select generate_series(1,100000))tbx;
INSERT 0 100000
Time: 156.057 ms
postgres=# insert into tb1 select 1 from (select generate_series(1,100000))tbx;
INSERT 0 100000
Time: 172.874 ms
postgres=# insert into tb1 select nextval(‘seq1‘) from (select generate_series(1,100000))tbx;
INSERT 0 100000
Time: 184.046 ms
postgres=# insert into tb1 select nextval(‘seq1‘) from (select generate_series(1,100000))tbx;
INSERT 0 100000
Time: 183.670 ms
postgres=# insert into tb1 select nextval(‘seq1‘) from (select generate_series(1,100000))tbx;
INSERT 0 100000
Time: 183.410 ms
postgres=# insert into tb1 select nextval(‘cached_seq‘) from (select generate_series(1,100000))tbx;
INSERT 0 100000
Time: 181.197 ms
postgres=# insert into tb1 select nextval(‘cached_seq‘) from (select generate_series(1,100000))tbx;
INSERT 0 100000
Time: 144.633 ms
postgres=# insert into tb1 select nextval(‘cached_seq‘) from (select generate_series(1,100000))tbx;
INSERT 0 100000
Time: 198.545 ms