MySQL Bugs: #111835: Contribution by Tencent: Replica buffer pool fast warmup

Bug #111835	Contribution by Tencent: Replica buffer pool fast warmup
Submitted:	21 Jul 2023 8:14	Modified:	21 Jul 2023 11:20
Reporter:	songwei chen (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	8.1.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In the MySQL master-replica architecture, the service load will be switched to the replica instance when the master instance fails to serve due to various reasons. However, since the buffer pool in the replica instance is cold (with a low cache hit rate), the service will be affected.

Although the buffer pool dump/load function can reduce the warmup period after restarting the server, it cannot be used in the scenario of fast warmup of the replica because the data distribution is different between a master and its replica.

Therefore, I propose a method to quickly warm up the buffer pool of the replica by synchronizing the contents of the buffer pool between the master and replicas.The proposed method consists of three steps:

1. Snapshot: Obtain the buffer pool information from the master and save it to a local file.
2. Transmit: Transfer the information file to the replicas.
3. Recover: Apply the buffer pool information from the file to the buffer pool of the replicas.

How to repeat:
N/A

Suggested fix:
I'll attach a patch later.

Change severity to "Feature request"

commit log:

[feature] Replica buffer pool fast warmup

When MySQL has a master-replica switch due to various reasons, since the
content in the buffer pool of the replica is different from that of the master,
the hit rate of the buffer pool will be low for a long time, and the service
will also be affected.

This commit introduces a method to quickly warm up the buffer pool of the
replica by synchronizing the contents of the buffer pool between the master
and replicas. The specific method consists of three steps:

1. Snapshot: Retrieve the buffer pool information from the master and save it
to a local file.
2. Transmit: Transfer the information file to the replicas.
3. Recover: Apply the buffer pool information from the file to the buffer pool
of the replicas.

The detailed usage is as follows:

1. snapshot
To take a snapshot of the buffer pool, execute the following statement on the
master side:
* SET GLOBAL innodb_buffer_pool_sync_snapshot_now=ON;

Two triggers are introduced to snapshot bufferpool:
* SET GLOBAL innodb_buffer_pool_sync_snapshot_interval=120;
* SET GLOBAL innodb_buffer_pool_sync_snapshot_threashold=10;
Set the above variables to 0 to disable them.

Use the innodb_buffer_pool_sync_snapshot_pct to select the hottest N% of
buffer pools to be snapshotted:
* SET GLOBAL innodb_buffer_pool_sync_snapshot_pct=60;

Use the innodb_buffer_pool_sync_snapshot_status to observe the behavior
of the snapshot:
* SHOW status LIKE innodb_buffer_pool_sync_snapshot_status;

2. transmit
The replica can initiate the copying of the ib_bp_info file from the master
node by executing the following statement to configure it:
* SET GLOBAL innodb_buffer_pool_sync_transmit_enabled=ON;
* SET GLOBAL innodb_buffer_pool_sync_transmit_interval=120;

Use the innodb_buffer_pool_sync_transmit_status to observe the behavior
of the transmit:
* SHOW status LIKE innodb_buffer_pool_sync_transmit_status;

3. recover
To recover a buffer pool, execute the following statement on the replica side:
* SET GLOBAL innodb_buffer_pool_sync_recover_now=ON;

One trigger is introduced to recover the buffer pool. If the following variable
is set, buffer pool recovery will be executed after each transmission:
* SET GLOBAL innodb_buffer_pool_sync_recover_after_transmit=ON;

commit log:

[feature] Replica buffer pool fast warmup

When MySQL has a master-replica switch due to various reasons, since the
content in the buffer pool of the replica is different from that of the master,
the hit rate of the buffer pool will be low for a long time, and the service
will also be affected.

This commit introduces a method to quickly warm up the buffer pool of the
replica by synchronizing the contents of the buffer pool between the master
and replicas. The specific method consists of three steps:

1. Snapshot: Retrieve the buffer pool information from the master and save it
to a local file.
2. Transmit: Transfer the information file to the replicas.
3. Recover: Apply the buffer pool information from the file to the buffer pool
of the replicas.

The detailed usage is as follows:

1. snapshot
To take a snapshot of the buffer pool, execute the following statement on the
master side:
* SET GLOBAL innodb_buffer_pool_sync_snapshot_now=ON;

Two triggers are introduced to snapshot bufferpool:
* SET GLOBAL innodb_buffer_pool_sync_snapshot_interval=120;
* SET GLOBAL innodb_buffer_pool_sync_snapshot_threashold=10;
Set the above variables to 0 to disable them.

Use the innodb_buffer_pool_sync_snapshot_pct to select the hottest N% of
buffer pools to be snapshotted:
* SET GLOBAL innodb_buffer_pool_sync_snapshot_pct=60;

Use the innodb_buffer_pool_sync_snapshot_status to observe the behavior
of the snapshot:
* SHOW status LIKE innodb_buffer_pool_sync_snapshot_status;

2. transmit
The replica can initiate the copying of the ib_bp_info file from the master
node by executing the following statement to configure it:
* SET GLOBAL innodb_buffer_pool_sync_transmit_enabled=ON;
* SET GLOBAL innodb_buffer_pool_sync_transmit_interval=120;

Use the innodb_buffer_pool_sync_transmit_status to observe the behavior
of the transmit:
* SHOW status LIKE innodb_buffer_pool_sync_transmit_status;

3. recover
To recover a buffer pool, execute the following statement on the replica side:
* SET GLOBAL innodb_buffer_pool_sync_recover_now=ON;

One trigger is introduced to recover the buffer pool. If the following variable
is set, buffer pool recovery will be executed after each transmission:
* SET GLOBAL innodb_buffer_pool_sync_recover_after_transmit=ON;

Hello songwei chen,

Thank you for the feature request.

regards,
Umesh

The patch of "Replica buffer pool fast warmup" (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-feature-Replica-buffer-pool-fast-warmup.patch (application/octet-stream, text), 679.16 KiB.