synchronous_commit 参数是 PostgreSQL WAL 日志文件类的重要参数，当设置成 on 时，表示事务提交需等待 WAL 刷到磁盘后才返回成功信息，这是最大限度数据保护模式；当设置成 off 时，事务提交不需等待 WAL 刷到磁盘就返回成功信息。而这个理解来看，设置成 on 会带来性能开销，那么这个开销大概有多少呢？ 一直想做这个测试，今天有空，测试下。
环境信息
硬件：华为服务器 RH5885 V3
PostgreSQL版本：9.5alpha2
操作系统： SUSE Linux
测试模型
创建测试表并插入一千万条数据，如下：
1
2
3
4
5
|
drop table if exists test_sync;
create table test_sync(id int8,name text,creat_time timestamp(0) without time zone default clock_timestamp());
insert into test_sync(id,name) select n,n||'_test' from generate_series(1,10000000) n;
alter table test_sync add primary key(id);
vacuum analyze test_sync;
|
测试场景 Synchronous_commit = off
主键查询： select_1.sql
1
2
3
4
|
[pg93@db2 load_test]$ cat select_1.sql
setrandom v_id 1 10000000
select name from test_sync where id=:v_id;
|
pgbench
1
2
|
pgbench -c 64 -T 120 -n -N -M prepared -d francs -U francs -f select_1.sql > select_1.out 2>&1 &
pgbench -c 64 -T 120 -n -N -M prepared -d francs -U francs -f select_1.sql > select_2.out 2>&1 &
|
pgbench 结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
pg95@db1:~/script/load_test> tail -f select_1.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 1891643
latency average: 4.060 ms
tps = 15741.393851 (including connections establishing)
tps = 15761.220964 (excluding connections establishing)
pg95@db1:~/script/load_test> tail -f select_2.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 1867651
latency average: 4.112 ms
tps = 15546.474928 (including connections establishing)
tps = 15565.536436 (excluding connections establishing)
备注：合计 TPS 为： 15741+15546=31287， 开了两个 pgbench 进程，机器性能依然没压到极限。
|
主键更新： updaet_1.sql
1
2
3
4
|
[pg93@db2 load_test]$ cat update_1.sql
setrandom v_id 1 10000000
update test_sync set name='off' where id=:v_id;
|
pgbench
1
2
|
pgbench -c 64 -T 120 -n -N -M prepared -d francs -U francs -f update_1.sql > update_1.out 2>&1 &
pgbench -c 64 -T 120 -n -N -M prepared -d francs -U francs -f update_1.sql > update_2.out 2>&1 &
|
pgbench 结果:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
pg95@db1:~/script/load_test> tail -f update_1.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 533015
latency average: 14.409 ms
tps = 4436.334568 (including connections establishing)
tps = 4441.801624 (excluding connections establishing)
pg95@db1:~/script/load_test> tail -f update_2.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 538226
latency average: 14.269 ms
tps = 4480.139660 (including connections establishing)
tps = 4485.694991 (excluding connections establishing)
|
备注：合计 TPS 为： 4436+4480=8916
测试场景: Synchronous_commit = on
为了测试准确，重新刷一次脚本
1
2
3
4
5
|
drop table if exists test_sync;
create table test_sync(id int8,name text,creat_time timestamp(0) without time zone default clock_timestamp());
insert into test_sync(id,name) select n,n||'_test' from generate_series(1,10000000) n;
alter table test_sync add primary key(id);
vacuum analyze test_sync;
|
主键查询： select_1.sql
1
2
3
4
|
[pg93@db2 load_test]$ cat select_1.sql
setrandom v_id 1 10000000
select name from test_sync where id=:v_id;
|
pgbench
1
2
|
pgbench -c 64 -T 120 -n -N -M prepared -d francs -U francs -f select_1.sql > select_1.out 2>&1 &
pgbench -c 64 -T 120 -n -N -M prepared -d francs -U francs -f select_1.sql > select_2.out 2>&1 &
|
pgbench 结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
pg95@db1:~/script/load_test> tail -f select_1.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 1907432
latency average: 4.026 ms
tps = 15872.562421 (including connections establishing)
tps = 15892.146509 (excluding connections establishing)
pg95@db1:~/script/load_test> tail -f select_2.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 1913928
latency average: 4.013 ms
tps = 15926.439118 (including connections establishing)
tps = 15946.082816 (excluding connections establishing)
|
备注：合计 TPS 为： 15872+15926=31798
主键更新： updaet_1.sql
1
2
3
4
|
[pg93@db2 load_test]$ cat update_1.sql
setrandom v_id 1 10000000
update test_sync set name='off' where id=:v_id;
|
pgbench
1
2
|
pgbench -c 64 -T 120 -n -N -M prepared -d francs -U francs -f update_1.sql > update_1.out 2>&1 &
pgbench -c 64 -T 120 -n -N -M prepared -d francs -U francs -f update_1.sql > update_2.out 2>&1 &
|
pgbench 结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
pg95@db1:~/script/load_test> tail -f update_1.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 337821
latency average: 22.734 ms
tps = 2803.250510 (including connections establishing)
tps = 2806.689839 (excluding connections establishing)
pg95@db1:~/script/load_test> tail -f update_2.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 340240
latency average: 22.572 ms
tps = 2822.794480 (including connections establishing)
tps = 2826.240282 (excluding connections establishing)
|
备注：合计 TPS 为： 2822+2826=5648
测试总结
| 测试场景 |
主键查询 |
主键更新 |
| synchronous_commit = off |
31287 |
8916 |
| synchronous_commit = on |
31798 |
5648 |
备注：从结果看出，SELECT 几乎不受 synchronous_commit 参数影响，而 UPDATE 在 synchronous_commit= on 的场景下要比 synchronous_commit = off 场景下的 TPS 低 36.7% 左右。
参考
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/tech/database/240094.html
