postgresql96 mysql57 组提交基准测试对比记录二

之前做了在完全不丢失数据的情况下pg，mysql的性能测试对比，看到pg是优于mysql的，pg中有异步提交，mysql中没有对应的配置，因此本次先单独测试下pg的异步提交就，然后在测试下同步设置下与mysql的组提交的对比。另外在http://blog.163.com/[email protected]/blog/static/1638770402011102214142132/中提到pg中的组提交是不会丢失数据的，这个不知道是怎么实现的，暂且不去了解。
下面是50用户下的异步提交的记录
Initializing worker threads…

Threads started!

SQL statistics:
queries performed:
read: 6962816
write: 1989311
other: 994725
total: 9946852
transactions: 497330 (828.17 per sec.)
queries: 9946852 (16563.78 per sec.)
ignored errors: 14 (0.02 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 600.5181s
total number of events: 497330

Latency (ms):
min: 3.71
avg: 60.34
max: 1603.13
95th percentile: 97.55
sum: 30009066.24

Threads fairness:
events (avg/stddev): 9946.6000/58.38
execution time (avg/stddev): 600.1813/0.16

在本次的压测时候会话是active的情况下，没有看到walwrite lock的等待时间。很多情况下的会话是在idle in transaction中。

本次的压测记录与同步的情况下的差别不大，但是iowait要高很多，是磁盘达到了限制。

avg-cpu: %user %nice %system %iowait %steal %idle
57.25 0.00 15.99 8.22 3.74 14.80

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
vda 0.00 0.00 1.00 0.00 52.00 0.00 104.00 0.00 0.50 0.50 0.00 0.50 0.05
vdb 3.50 8.00 3720.00 897.00 92422.00 18216.00 47.93 16.06 3.47 3.01 5.41 0.15 67.65
dm-0 0.00 0.00 3728.00 891.50 92972.00 18212.00 48.14 16.29 3.51 3.02 5.55 0.15 67.65

avg-cpu: %user %nice %system %iowait %steal %idle
58.24 0.00 16.18 7.86 3.54 14.18

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
vda 0.00 1.00 22.00 6.00 422.00 31.50 32.39 0.02 0.75 0.64 1.17 0.21 0.60
vdb 0.50 0.00 3866.50 1281.00 103460.00 13408.25 45.41 17.00 3.31 2.49 5.78 0.13 66.10
dm-0 0.00 0.00 3848.00 1269.50 102740.00 13408.25 45.39 17.86 3.49 2.70 5.89 0.13 66.05

avg-cpu: %user %nice %system %iowait %steal %idle
55.35 0.00 16.59 11.92 5.43 10.71

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
vda 0.00 0.00 0.50 0.00 2.00 0.00 8.00 0.01 24.00 24.00 0.00 24.00 1.20
vdb 3.50 112.00 4476.50 3206.00 125666.00 41466.50 43.51 35.81 4.66 4.26 5.23 0.10 79.20
dm-0 0.00 0.00 4481.00 3307.50 125674.00 41466.50 42.92 39.44 5.06 4.51 5.81 0.10 79.20

avg-cpu: %user %nice %system %iowait %steal %idle
56.65 0.00 17.67 7.40 3.78 14.50

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
vda 0.00 0.00 7.50 0.00 364.00 0.00 97.07 0.01 1.00 1.00 0.00 0.07 0.05
vdb 0.00 264.00 4174.50 546.50 109206.00 8237.00 49.75 12.53 2.66 2.65 2.66 0.15 68.50
dm-0 0.00 0.00 4173.50 800.50 109198.00 8237.00 47.22 14.79 2.96 2.65 4.58 0.14 68.70

下面测试下10用户下的对比情况
异步提交的情况
Initializing worker threads…

Threads started!

SQL statistics:
queries performed:
read: 5873700
write: 1678188
other: 839104
total: 8390992
transactions: 419546 (699.09 per sec.)
queries: 8390992 (13981.95 per sec.)
ignored errors: 4 (0.01 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 600.1303s
total number of events: 419546

Latency (ms):
min: 3.66
avg: 14.30
max: 565.14
95th percentile: 21.89
sum: 5998739.92

Threads fairness:
events (avg/stddev): 41954.6000/246.06
execution time (avg/stddev): 599.8740/0.05

下面的是同步提交的情况
Initializing worker threads…

Threads started!

SQL statistics:
queries performed:
read: 5093270
write: 1455192
other: 727622
total: 7276084
transactions: 363797 (606.22 per sec.)
queries: 7276084 (12124.59 per sec.)
ignored errors: 8 (0.01 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 600.1096s
total number of events: 363797

Latency (ms):
min: 4.32
avg: 16.49
max: 2161.06
95th percentile: 24.38
sum: 5999018.89

Threads fairness:
events (avg/stddev): 36379.7000/108.94
execution time (avg/stddev): 599.9019/0.05

对比看到在异步提交的情况下，tps是要略高，同步配置下的io消耗更大，差距还是很大的。
下面对比下10并发下的组提交

pg组提交的数据，配置
commit_delay = 10
commit_siblings = 16

Number of threads: 10
Initializing random number generator from current time

Initializing worker threads…

Threads started!

SQL statistics:
queries performed:
read: 5350296
write: 1528632
other: 764340
total: 7643268
transactions: 382158 (636.82 per sec.)
queries: 7643268 (12736.56 per sec.)
ignored errors: 6 (0.01 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 600.1045s
total number of events: 382158

Latency (ms):
min: 4.26
avg: 15.70
max: 2276.09
95th percentile: 23.95
sum: 5998970.55

Threads fairness:
events (avg/stddev): 38215.8000/198.68
execution time (avg/stddev): 599.8971/0.04
看到在10并发用户的情况下，性能提升的不是很明显。
在低并发的情况下设置组提交并不能明显的提升mysql表现，在配置了异步后虽然tps，qps没有明显的上升，但是io下降的幅度还是很明显的。
另外在设置检查点执行频率checkpoint_completion_target后，指标曲线变的平稳了些。
下面进行下50并发用户的组提交pg，mysql对比。

Initializing worker threads…

Threads started!

SQL statistics:
queries performed:
read: 6576682
write: 1878825
other: 939623
total: 9395130
transactions: 469698 (782.68 per sec.)
queries: 9395130 (15655.49 per sec.)
ignored errors: 65 (0.11 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 600.1172s
total number of events: 469698

Latency (ms):
min: 8.25
avg: 63.87
max: 2541.90
95th percentile: 92.42
sum: 30001937.99

Threads fairness:
events (avg/stddev): 9393.9600/39.15
execution time (avg/stddev): 600.0388/0.01

基本上是由于磁盘的io限制了测试的结果，50并发用户下的tps不高，已经达到了资源限制。看到tps曲线不如10并发用户时候的平稳，另外还是出现了walwriterlock的等待时间。

下面测试下mysql的组提交下的表现。
sync_binlog | 1000 |
| innodb_flush_log_at_trx_commit | 0

Number of threads: 50
Initializing random number generator from current time

Initializing worker threads…

Threads started!

SQL statistics:
queries performed:
read: 5905928
write: 1687408
other: 843704
total: 8437040
transactions: 421852 (702.93 per sec.)
queries: 8437040 (14058.57 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 600.1350s
total number of events: 421852

Latency (ms):
min: 3.85
avg: 71.12
max: 444.83
95th percentile: 125.52
sum: 30002036.59

Threads fairness:
events (avg/stddev): 8437.0400/889.72
execution time (avg/stddev): 600.0407/0.04

看到性能表现在这台机器上差不多。
总结：
其实本次的基准测试是有个主要的问题。就是服务器的资源不够充足，io限制了数据库的表现。
但是也能得到些结论：
1在完全保证数据安全的配置下pg表现比mysql好。
2pg的异步提交主要效果是节省io资源，对于提升吞吐效果不明显。
3低并发下组提交的效果不明显
4在配置组提交的情况下，mysql与pg的表现在这台io资源受限的机器上是差不多一致的。