0%

你的MySQL5.5还好么?

组提交性能问题

在MySQL5.6之前只支持redo日志组提交,如果在环境中开启了binlog,那binlog只能只能串行写入,无法进行组提交,导致数据库整体性能下降非常严重。

开启binglog

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[ 10s ] thds: 100 tps: 1391.16 qps: 1391.16 (r/w/o: 0.00/1391.16/0.00) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 1302.81 qps: 1302.81 (r/w/o: 0.00/1302.81/0.00) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 1305.70 qps: 1305.70 (r/w/o: 0.00/1305.70/0.00) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 1354.09 qps: 1354.09 (r/w/o: 0.00/1354.09/0.00) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 1354.20 qps: 1354.20 (r/w/o: 0.00/1354.20/0.00) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 1188.90 qps: 1188.90 (r/w/o: 0.00/1188.90/0.00) lat (ms,95%): 196.89 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 1060.10 qps: 1060.10 (r/w/o: 0.00/1060.10/0.00) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 1163.70 qps: 1163.70 (r/w/o: 0.00/1163.70/0.00) lat (ms,95%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 1216.50 qps: 1216.50 (r/w/o: 0.00/1216.50/0.00) lat (ms,95%): 189.93 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 1250.09 qps: 1250.09 (r/w/o: 0.00/1250.09/0.00) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 125975
other: 0
total: 125975
transactions: 125975 (1257.66 per sec.)
queries: 125975 (1257.66 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 100.1647s
total number of events: 125975

Latency (ms):
min: 1.17
avg: 79.43
max: 788.50
95th percentile: 179.94
sum: 10006517.26

Threads fairness:
events (avg/stddev): 1259.7500/20.46
execution time (avg/stddev): 100.0652/0.04

未开启binlog

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[ 10s ] thds: 100 tps: 11771.70 qps: 11771.70 (r/w/o: 0.00/11771.70/0.00) lat (ms,95%): 24.83 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 10423.24 qps: 10423.34 (r/w/o: 0.00/10423.34/0.00) lat (ms,95%): 32.53 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 9948.20 qps: 9948.10 (r/w/o: 0.00/9948.10/0.00) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 9280.40 qps: 9280.40 (r/w/o: 0.00/9280.40/0.00) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 10392.88 qps: 10392.88 (r/w/o: 0.00/10392.88/0.00) lat (ms,95%): 34.95 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 9799.44 qps: 9799.44 (r/w/o: 0.00/9799.44/0.00) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 9832.77 qps: 9832.77 (r/w/o: 0.00/9832.77/0.00) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 10348.78 qps: 10348.78 (r/w/o: 0.00/10348.78/0.00) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 10284.11 qps: 10284.11 (r/w/o: 0.00/10284.11/0.00) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 9858.21 qps: 9858.21 (r/w/o: 0.00/9858.21/0.00) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 1019515
other: 0
total: 1019515
transactions: 1019515 (10187.78 per sec.)
queries: 1019515 (10187.78 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 100.0708s
total number of events: 1019515

Latency (ms):
min: 0.22
avg: 9.81
max: 1577.13
95th percentile: 36.24
sum: 9998817.18

Threads fairness:
events (avg/stddev): 10195.1500/131.28
execution time (avg/stddev): 99.9882/0.01

可以从sysbench的结果看出开启binlog和不开启binlog写入性能相差快10倍了,这是因为在MySQL5.6之前binlog不支持组提交,在开启binlog后会通过prepare_commit_mutex和sync_binlog保证二进制日志和存储引擎顺序一致,只能串行化写入。当sync_binlog=1时,prepare_commit_mutex的锁机制造成高并发提交事务的性能急剧下降,因此建议尽快升级到5.6及以上的版本。

复制同步问题

在MySQL5.6之前Slave不支持Crash Safe,经常会出现重复主键等同步问题,导致同步中断,只能设置参数跳过指定错误

IO-Thread

IO-Thread负责接收master传输过来的二进制日志内容并生成relay log了,IO-Thread接收的信息都保存在master.info文件中,当然这些信息并不是实时写入到磁盘上的,默认sync_master_info为10000,当Slave异常重启后这部分信息就会丢失,启动后会重复拉取相同操作记录。当然也可以将参数设置为1,每次都直接写入到文件中,但是频繁的fsync必然导致性能下降。MySQL5.6中通过设置master-info-repository=table将信息存到表中,这样就提供Crash Safe的功能。

SQL-Thread

SQL-Thread负责操作回放,SQL thread的执行状态信息保存在relay-log.info文件,也存在上述文件与实际不一致的情况。在MySQL5.6中通过参数relay-log-info-repository=table将信息保存到数据表中,利用事务的原子性来保证其操作一致性

从上面的改变解决了SQL thread记录执行状态可能导致不一致的风险,但是对于IO thread依然存在问题 。IO thread 从master上拷贝binlog写入relay log中,每个二进制日志由多个log event组成,所以每接收到一个log event就需要更新master-info.log而且该是写入操作系统缓存。从IO thread的工作原理来看,它没有办法通过数据表来保证两个文件的原子性,因此IO thread还是会存在一些影响,这里我们可以设置relay-log-recover=1,当slave发生crash重启后,slave不根据master info的信息进行重连,而是根据relay-info中SQL-Thread执行到master的位置信息重新开始拉master上的日志数据。