I always have difficulties with complex analysis schemes, so fall back to something that is somewhat easier. Or much easier. Here I will explain the super-powerful method of database write workload analysis.
Doing any analysis on master servers is already too complicated, as instead of analyzing write costs one can be too obsessed with locking and there’s sometimes uncontrollable amount of workload hitting the server beside writes. Fortunately, slaves are much better targets, not only because writes there are single-threaded, thus exposing every costly I/O as time component, but also one can drain traffic from slaves, or send more in order to cause more natural workload.
Also, there can be multiple states of slave load:
- Healthy, always at 0-1s lag, write statements are always immediate
- Spiky, usually at 0s lag, but has jumps due to sometimes occuring slow statements
- Lagging, because of …