PosgreSQL快速参数调优和sysbench压测

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
性能测试 PTS,5000VUM额度
简介: 关于PostgreSQL的性能调优可以参考《PostgreSQL 9.0 High Performance》,以及朱贤文在2014 PostgreSQL中国用户大会上分享的《高性能Postgres 最佳实践》。

关于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
相关实践学习
通过性能测试PTS对云服务器ECS进行规格选择与性能压测
本文为您介绍如何利用性能测试PTS对云服务器ECS进行规格选择与性能压测。
相关文章
|
5月前
|
JavaScript 前端开发 算法
性能测试与调优
性能测试与调优
76 0
|
负载均衡 测试技术 应用服务中间件
性能测试常见瓶颈分析及调优方法总结
性能测试常见瓶颈分析及调优方法总结
330 0
|
28天前
|
缓存 Java 测试技术
谷粒商城笔记+踩坑(11)——性能压测和调优,JMeter压力测试+jvisualvm监控性能+资源动静分离+修改堆内存
使用JMeter对项目各个接口进行压力测试,并对前端进行动静分离优化,优化三级分类查询接口的性能
谷粒商城笔记+踩坑(11)——性能压测和调优,JMeter压力测试+jvisualvm监控性能+资源动静分离+修改堆内存
|
2月前
|
监控 Java 测试技术
实战派必看!Python性能测试中,JMeter与Locust如何助力性能调优
【8月更文挑战第6天】性能优化是软件开发的关键。本文介绍JMeter与Locust两款流行性能测试工具,演示如何用于Python应用的性能调优。JMeter可模拟大量用户并发访问,支持多种协议;Locust用Python编写,易于定制用户行为并模拟高并发。根据场景选择合适工具,确保应用在高负载下的稳定运行。
102 4
|
2月前
|
关系型数据库 MySQL 测试技术
压测工具sysbench的使用
压测工具sysbench的使用
|
4月前
|
监控 Java 测试技术
Java性能测试与调优工具使用指南
Java性能测试与调优工具使用指南
|
4月前
|
缓存 Java 测试技术
Spring Boot中的性能测试与调优
Spring Boot中的性能测试与调优
|
5月前
|
关系型数据库 MySQL 测试技术
sysbench 对MySQL压测100分钟的命令
使用 `sysbench` 对 MySQL 数据库进行性能测试(压测)时,首先确保 `sysbench` 和 MySQL 数据库已经安装,并且你有一个测试数据库可以使用。下面是一个针对 MySQL 数据库进行压测的示例命令,测试时长为 100 分钟(6000 秒)。 在运行此命令之前,请确保以下内容: - 使用适当的数据库连接参数(主机、端口、用户名、密码、数据库名)。 - 根据你的需求调整测试参数(如并发数、线程数、事务数等)。 以下是一个示例命令,使用 `sysbench` 对 MySQL 数据库进行压测 100 分钟: ```shell sysbench --db-driver=m
110 0
|
存储 监控 安全
深聊性能测试,从入门到放弃之:如何对IO进行性能调优
深聊性能测试,从入门到放弃之:如何对IO进行性能调优
252 0
|
JSON 测试技术 数据格式
性能测试(16)——参数关联
当请求之间有依赖关系,比如一个请求的入参是另一个请求返回的数据,这时候就需要用到关联处理。 JMeter可以通过“后置处 理器”中的一些组件来处理关联。 常用的关联方法: 正则表达式提取器 XPath提取器 JSON提取器
136 0
性能测试(16)——参数关联