Reducing MTS checkpointing causes high IO load
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
|||
Percona Server moved to https://jira.percona.com/projects/PS |
Triaged
|
Medium
|
Unassigned | ||
5.5 |
Invalid
|
Undecided
|
Unassigned | ||
5.6 |
Won't Fix
|
Medium
|
Unassigned | ||
5.7 |
Won't Fix
|
Medium
|
Unassigned |
Bug Description
Originally reported at https:/
Description:
The implementation of slave_worker_
That column can go up to 65K - 1 bit per 8 transaction - and is written out with every gtid processed (in theory every commit but that's another issue). Setting it to max (512K) sent the writes of a test server from 12-20MB/s up to 100-150MB/s, and from table_io_
Granted, that's the max value, but it shows how the slave's internal information storage can be almost 10x the IO load of the actual application writes to the database. It's expected based on the logic of that column, but as it makes reduced checkpointing impossible, it's a blocker for a better mts (well, binlog file rollover too).
How to repeat:
This easy test used 5.7.17.
Set up two servers, master A and slave B. On B, set slave_parallel_
Suggested fix:
Find a better way to do this which doesn't require a whole max-size bitmap write with every commit. I'll review the code at some point, and if I think of anything easy, I'll gladly suggest it - but this is a very high cost operation that, while valuable, is way out of proportion.
tags: | added: performance |
Percona now uses JIRA for bug reports so this bug report is migrated to: https:/ /jira.percona. com/browse/ PS-1789