关于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
Online transaction processing systems
Data warehouses
上面3种DB Type，越往后写越重，checkpoint的频率也调得越低。由于后面要做OLTP的性能评估，所以选用Online transaction processing systems的设置。
综合考虑log等需求，初步在postgresql.conf中设置参数如下
流复制时需要设置wal_level = hot_standby，单机场景下可以设置其它值以输出更少的WAL日志。
下面这几个参数有些需要在性能和持久性之间平衡，先全部采用默认值。
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
|
但是在并发数很高的情况下，会报下面的错误
为了回避这个问题，参考德哥的方法临时修改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极限。
complex模式且oltp-read-only=on
执行下面的命令，100并发，TPS为1505.38，测试时CPU被占满，达到CPU极限，其中sysbench进程的CPU率大约80%。
complex模式且oltp-read-only=off
执行下面的命令，100并发，TPS为1232.77，测试时CPU被占满，其中sysbench进程的CPU率大约80%，磁盘Busy大约10+%，磁盘写入也是10+MB/s。
5. 参数的优化
由于上面测试场景的性能瓶颈都在CPU，基本没什么可优化的，（实际试了下，确实也没有优化效果）。如果瓶颈在刷盘可以通过调节下面几个参数优化。
|
|
|
|
|
|
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时，数据文件可能会损坏。
|
6. 补充：page大小的调整
还有一种经常被提到的优化方法是修改page大小，PostgreSQL默认的page大小是8K，编译时可以指定。
下面是16K和8K的性能对比，从下面可以看出16K反而性能变差，所以16K的优化方法一定要看机器环境和场景的（可能更适合OLAP吧）。而且改page大小要重新编译源码，8K的数据目录和16K的又不兼容，不能互相复制。所以修改page大小要慎重。
100线程，500W数据的sysbench测试出的tps值:
|
|
|
|
|
simple
|
29200.43
|
28795.46
|
|
complex模式且oltp-read-only=on
|
1505.38
|
1462.40
|
|
complex模式且oltp-read-only=off
|
1232.77
|
1058.48
|
