Description:
When I did sysbench test, I found the performance down a few seconds periodically. It happens at the time binlog rotation.
Binlog rotation calls ha_flush_logs() which triggers gtid flush thread to
write gtids into gtid_executed table and then compress the table from many rows
to a few rows. It has two problems here:
- compress is very slow it can take a few seconds sometimes.
- when waiting gtid flush thread to finish the work, it uses sleep mechanism but not a wakeable mechanism. Thus it can waste some time on sleep.
[ 93s ] thds: 512 tps: 58831.57 qps: 353133.44 (r/w/o: 0.00/235457.29/117676.14) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 94s ] thds: 512 tps: 57709.89 qps: 346125.38 (r/w/o: 0.00/230713.59/115411.79) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 95s ] thds: 512 tps: 59671.77 qps: 357982.64 (r/w/o: 0.00/238636.10/119346.55) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 96s ] thds: 512 tps: 58838.63 qps: 353093.76 (r/w/o: 0.00/235410.51/117683.25) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 97s ] thds: 512 tps: 12195.12 qps: 73328.75 (r/w/o: 0.00/48939.50/24389.24) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 98s ] thds: 512 tps: 48753.84 qps: 292432.03 (r/w/o: 0.00/194927.36/97504.67) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
[ 99s ] thds: 512 tps: 58670.88 qps: 351968.28 (r/w/o: 0.00/234627.52/117340.76) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 512 tps: 57110.54 qps: 342613.27 (r/w/o: 0.00/228392.18/114221.09) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
How to repeat:
sysbench oltp_write_only with 512 threads.
Suggested fix:
Update gtid_executed table in ha_flush_logs() is not really necessary. It can be removed.
