关于PostgreSQL的性能调优可以参考《PostgreSQL 9.0 High Performance》，以及朱贤文在2014 PostgreSQL中国用户大会上分享的《高性能Postgres 最佳实践》。当然，首先还是应该看看PostgreSQL手册的相关章节。我们在调优时不必每个细节都做到最优，抓住主要矛盾即可。因为有些东西不在你的控制之下，或者那样优化之后维护起来麻烦。下面尝试在虚机下进行快速的PostgreSQL参数调优。

1. 虚机环境

CPU: 4 core
Mem: 8G
OS: CentOS 6.3(64 Bit)

PostgreSQL:9.4.5

sysbench:0.4.12

文件系统:ext4

2.参数设置

基本的性能参数设置可以利用下面这个在线小工具评估。

http://pgtune.leopard.in.ua/

填入系统信息，并固定最大连接数为300后，选择不同DB Type，这个工具会给出不同的参数。

Web applications

max_connections = 300
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 6990kB
maintenance_work_mem = 512MB
checkpoint_segments = 32
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100

Online transaction processing systems

max_connections = 300
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 6990kB
maintenance_work_mem = 512MB
checkpoint_segments = 64
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100

Data warehouses

max_connections = 300
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 3495kB
maintenance_work_mem = 1GB
checkpoint_segments = 128
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500

上面3种DB Type，越往后写越重，checkpoint的频率也调得越低。由于后面要做OLTP的性能评估，所以选用Online transaction processing systems的设置。

综合考虑log等需求，初步在postgresql.conf中设置参数如下

listen_addresses = '*'
port = 5432
max_connections = 300
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 6990kB
maintenance_work_mem = 512MB
checkpoint_segments = 64
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1440
log_rotation_size = 100000
log_line_prefix='%m %p %x'
wal_level = hot_standby

流复制时需要设置wal_level = hot_standby，单机场景下可以设置其它值以输出更少的WAL日志。

下面这几个参数有些需要在性能和持久性之间平衡，先全部采用默认值。

wal_sync_method = fsync
commit_delay = 0
synchronous_commit = on
full_page_writes = on
fsync = on

3.测试方法

用sysbench 做oltp的性能测试，不管使用simple还是complex测试模式，sysbench prepare时创建单个相同的测试表（那就不可能测到join了）。

建表语句如下

 
           CREATE TABLE sbtest (id SERIAL  NOT NULL , k integer  DEFAULT '0' NOT NULL, c char(120) DEFAULT '' NOT NULL, pad char(60) DEFAULT '' NOT NULL, PRIMARY KEY  (id) ) 
         
           CREATE INDEX k on sbtest(k)

然后插入数据，插入数据量由oltp-table-size决定。

 
           INSERT INTO sbtest(k, c, pad) VALUES (0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')

当插入数据量很大时，由于每个插入语句都是一次提交，因此速度很慢。我们可以用下面的方法手动准备数据，而不是使用sysbench的prepare。

 
           DROP TABLE sbtest; 
         
           CREATE TABLE sbtest (id SERIAL  NOT NULL , k integer  DEFAULT '0' NOT NULL, c char(120) DEFAULT '' NOT NULL, pad char(60) DEFAULT '' NOT NULL, PRIMARY KEY  (id) ); 
         
           CREATE INDEX k on sbtest(k); 
         
           INSERT INTO sbtest(k, c, pad) select 0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' from generate_series(1,5000000);

插入500万记录后，数据表大小大约为1GB。

 
           postgres=# \d+ 
         
                                        List of relations 
         
            Schema |       Name       |   Type   |  Owner   |    Size    | Description 
         
           --------+------------------+----------+----------+------------+------------- 
         
            public | sbtest           | table    | postgres | 1056 MB    | 
         
            public | sbtest_id_seq    | sequence | postgres | 8192 bytes | 
         
           (2 rows)

simple模式的测试语句如下

           SELECT c from sbtest where id=$1 
         

$1是个取值在oltp-table-size范围内的随机数，随机数的生成算法由oltp-dist-type决定，包括uniform,gaussian,special三种，默认是special，生成的随机数有75%(由oltp-dist-pct控制)集中在一个1%(由oltp-dist-pct控制)的热点区域。

complex模式且oltp-read-only=on时的测试语句如下

 
           BEGIN 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id between $1 and $2 
         
           SELECT SUM(K) from sbtest where id between $1 and $2 
         
           SELECT c from sbtest where id between $1 and $2 order by c 
         
           SELECT DISTINCT c from sbtest where id between $1 and $2 order by c 
         
           COMMIT

上面的between范围查询，范围大小为100(由oltp-range-size控制)。

complex模式且oltp-read-only=off时的测试语句如下

 
           BEGIN 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id=$1 
         
           SELECT c from sbtest where id between $1 and $2 
         
           SELECT SUM(K) from sbtest where id between $1 and $2 
         
           SELECT c from sbtest where id between $1 and $2 order by c 
         
           SELECT DISTINCT c from sbtest where id between $1 and $2 order by c 
         
           UPDATE sbtest set k=k+1 where id=$1 
         
           UPDATE sbtest set c=$1 where id=$2 
         
           UPDATE sbtest set k=k+1 where id=$1 
         
           DELETE from sbtest where id=$1 
         
           INSERT INTO sbtest values($1,0,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy') 
         
           COMMIT

但是在并发数很高的情况下，会报下面的错误

ERROR:  duplicate key value violates unique constraint "sbtest_pkey"

为了回避这个问题，参考德哥的方法临时修改sysbench的代码（这个问题和PostgreSQL的MVCC实现机制有关，详见http://blog.chinaunix.net/uid-20726500-id-5289907.html）。

修改方法如下：

修改sysbench-0.4.12/sysbench/tests/oltp/sb_oltp.c

找到

 
           /* Prepare the insert statement */ 
         
             snprintf(query, MAX_QUERY_LEN, "INSERT INTO %s values(?,0,' '," 
         
                      "'aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')", 
         
                      args.table_name);

改成

 
           /* Prepare the insert statement */ 
         
             if (args.auto_inc) 
         
               snprintf(query, MAX_QUERY_LEN, "INSERT INTO %s(k,c,pad) values(0,' '," 
         
                      "'aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')", 
         
                      args.table_name); 
         
             else 
         
               snprintf(query, MAX_QUERY_LEN, "INSERT INTO %s values(?,0,' '," 
         
                      "'aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')", 
         
                      args.table_name);

参考：http://blog.163.com/digoal@126/blog/static/1638770402013414549515/

修改后，最后一条INSERT句就变成了下面这样了

 
           INSERT INTO sbtest(k,c,pad) values(0,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')

注：其实更加正确的做法应该是使用sysbench 0.5而不是0.4，sysbench 0.5没有这个问题，而且0.5支持lua脚本，支持的测试方式更灵活。

4. 测试

simple模式

执行下面的命令，100并发，TPS为29200.43，测试时CPU被占满，达到CPU极限。

[postgres@node1  ~]$ sysbench --test=oltp --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=postgres --pgsql-db=postgres --oltp-table-size=5000000 --num-threads=100 --max-requests=0 --max-time=60 --oltp-test-mode=simple --oltp-read-only=on run
sysbench 0.4.12:  multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 100
Doing OLTP test.
Running simple OLTP test
Doing read-only test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 99 times)
Done.
OLTP test statistics:
    queries performed:
        read:                            1752165
        write:                           0
        other:                           0
        total:                           1752165
    transactions:                        1752165 (29200.43 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1752165 (29200.43 per sec.)
    other operations:                    0      (0.00 per sec.)
Test execution summary:
    total time:                          60.0048s
    total number of events:              1752165
    total time taken by event execution: 5993.5646
    per-request statistics:
         min:                                  0.04ms
         avg:                                  3.42ms
         max:                                968.42ms
         approx.  95 percentile:               0.34ms
Threads fairness:
    events (avg/stddev):           17521.6500/2549.74
    execution time (avg/stddev):   59.9356/0.01

complex模式且oltp-read-only=on

执行下面的命令，100并发，TPS为1505.38，测试时CPU被占满，达到CPU极限，其中sysbench进程的CPU率大约80%。

[postgres@node1  ~]$ sysbench --test=oltp --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=postgres --pgsql-db=postgres --oltp-table-size=5000000 --num-threads=100 --max-requests=0 --max-time=60 --oltp-test-mode=complex --oltp-read-only=on run
sysbench 0.4.12:  multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 100
Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 99 times)
Done.
OLTP test statistics:
    queries performed:
        read:                            1265264
        write:                           0
        other:                           180752
        total:                           1446016
    transactions:                        90376  (1505.38 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1265264 (21075.27 per sec.)
    other operations:                    180752 (3010.75 per sec.)
Test execution summary:
    total time:                          60.0355s
    total number of events:              90376
    total time taken by event execution: 5998.5193
    per-request statistics:
         min:                                  1.98ms
         avg:                                 66.37ms
         max:                               4032.00ms
         approx.  95 percentile:             161.49ms
Threads fairness:
    events (avg/stddev):           903.7600/74.26
    execution time (avg/stddev):   59.9852/0.04

complex模式且oltp-read-only=off

执行下面的命令，100并发，TPS为1232.77，测试时CPU被占满，其中sysbench进程的CPU率大约80%，磁盘Busy大约10+%，磁盘写入也是10+MB/s。

[postgres@node1  ~]$ sysbench --test=oltp --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=postgres --pgsql-db=postgres --oltp-table-size=5000000 --num-threads=100 --max-requests=0 --max-time=60 --oltp-test-mode=complex --oltp-read-only=off run
sysbench 0.4.12:  multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 100
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 99 times)
Done.
OLTP test statistics:
    queries performed:
        read:                            1035986
        write:                           369995
        other:                           147998
        total:                           1553979
    transactions:                        73999  (1232.77 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1405981 (23422.71 per sec.)
    other operations:                    147998 (2465.55 per sec.)
Test execution summary:
    total time:                          60.0264s
    total number of events:              73999
    total time taken by event execution: 5998.7493
    per-request statistics:
         min:                                  2.69ms
         avg:                                 81.07ms
         max:                                547.21ms
         approx.  95 percentile:             199.32ms
Threads fairness:
    events (avg/stddev):           739.9900/23.37
    execution time (avg/stddev):   59.9875/0.04

5. 参数的优化

由于上面测试场景的性能瓶颈都在CPU，基本没什么可优化的，（实际试了下，确实也没有优化效果）。如果瓶颈在刷盘可以通过调节下面几个参数优化。

No	参数	说明	风险
1	wal_sync_method = fdatasync	WAL刷盘的系统调用，根据之前的测试fdatasync比fsync性能好。但是Linux下的默认值就是fdatasync所以也不同修改。	无风险
2	commit_delay = 100	提交延迟，单位是微妙（不是毫秒），以进行组提交。	设得太大会影响事务的响应时间。注）实际压测发现效果不稳定。因并发连接数，热数据分布，commit_delay等的不同，有时性能提高有时降低，所以优化这个值要根据实际的应用环境。
3	synchronous_commit = off	异步提交，WAL刷盘交给OS	OS crash时，可能丢失最近的提交。
4	full_page_writes = off	在WAL中checkpoint后的第一次修改page时不写全page数据	对不支持原子写的文件系统或存储设备上，OS crash时，数据文件会损坏。
5	fsync = off	不刷盘，完全交给OS刷	OS crash时，数据文件可能会损坏。

Icon

上面的full_page_writes和fsync非常危险，修改时务必慎重！

6. 补充：page大小的调整

还有一种经常被提到的优化方法是修改page大小，PostgreSQL默认的page大小是8K，编译时可以指定。

./configure --with-blocksize=16

下面是16K和8K的性能对比，从下面可以看出16K反而性能变差，所以16K的优化方法一定要看机器环境和场景的（可能更适合OLAP吧）。而且改page大小要重新编译源码，8K的数据目录和16K的又不兼容，不能互相复制。所以修改page大小要慎重。

100线程，500W数据的sysbench测试出的tps值:

test mode	8K	16K
simple	29200.43	28795.46
complex模式且oltp-read-only=on	1505.38	1462.40
complex模式且oltp-read-only=off	1232.77	1058.48

PosgreSQL快速参数调优和sysbench压测

1. 虚机环境

2.参数设置

3.测试方法

4. 测试

5. 参数的优化

6. 补充：page大小的调整

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

PosgreSQL快速参数调优和sysbench压测

1. 虚机环境

2.参数设置

3.测试方法

4. 测试

5. 参数的优化

6. 补充：page大小的调整

热门文章

最新文章

相关课程

相关电子书

相关实验场景