Description:
I have a highly loaded server.
The server handles transaction loads remarkably well.
As soon as the table cleanup thread starts, the replica starts lagging significantly.
It doesn't depend on the binlog format.
The buffer pool size is huge.
Delete queries are sent to the server based on the primary key.
> DELETE FROM ttt WHERE id IN (.... 10k ids ......);
Replica lags occur precisely during such transactions.
Experimentation has shown that rows to be deleted are not present in the replica's buffer pool.
To speed up replication, I made a "warmer" that reads the records that will be deleted on the replica 30 seconds before use.
The problem is that a table ttt can have many indexes.
The more indexes, the less effective the warmer.
Feature request:
In addition to replication application threads, data pre-warmup threads are required.
DELETE and UPDATE queries must be selected from relay, and the warmup threads must place all table indexes into the buffer pool.
It is clear that this may create some additional load on the server, but it will significantly speed up the replication process.
How to repeat:
Normal behavior (not in buffer pool)
> DELETE FROM ttt WHERE id IN (.... 10k ids ......);
1.7 s
Warmer (30-150 sec before DELETE)
> SELECT COUNT(*) INTO @nothing FROM ttt WHERE id IN (.... 10k ids ......);
Quick transaction application (in buffer pool)
> DELETE FROM ttt WHERE id IN (.... 10k ids ......);
0.8 s
