Saving and loading of MYSQL innodb buffer State data

This article refers to the official manual.

Please indicate the source of the original reprint

Version: MYSQL 5.7

When the database is restarted, the data in buffer needs to be rewarded. The so-called warm-up is to wait for commonly used data to be loaded from disk to memory by calling SQL statements.

This process is called physical read in ORACLE. For larger platforms, this warm-up process often requires a high cost. Typically, a select is run for the first time.

The statement is slow and then executed again very quickly.

In this case, by default, innodb saves and loads state data at both shutdown and startup, as determined by the parameters

Innodb_buffer_pool_load_at_startup

And

Innodb_buffer_pool_dump_at_shutdown

Control. Default is on.

Its preservation ratio is controlled by parameters.

Innodb_buffer_pool_dump_pct

The default is 25%

And its file is made up of parameters.

The innodb_buffer_pool_filename control defaults to:

Mysql > show variables like'% innodb_buffer_pool_filename%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_buffer_pool_filename | ib_buffer_pool |

+-+ +

This file is located in the innodb data file directory, in which the space_no,page_no form is saved as follows:

545213

545212

545211

545210

545209

545208

545207

.

The following statements can be used online:

SET GLOBAL innodb_buffer_pool_dump_now=ON

SET GLOBAL innodb_buffer_pool_load_now=ON

For dump and load, and can be accessed through the

SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'

And

SHOW STATUS LIKE 'Innodb_buffer_pool_load_status'

Check dump and load

Author Wechat: