1.Saving and Restoring the Buffer Pool State
主要內容為【熱啟動】，就是把之前常用的記憶體資料，按照劃定的比例快速重新載入到記憶體中。
To reduce the warmup period after restarting the server, InnoDB saves a percentage of the most recently used pages for each buffer pool at server shutdown and restores these pages at server startup. The percentage of recently used pages that is stored is defined by the innodb_buffer_pool_dump_pct configuration option.
After restarting a busy server, there is typically a warmup period with steadily increasing throughput, as disk pages that were in the buffer pool are brought back into memory (as the same data is queried, updated, and so on). The ability to restore the buffer pool at startup shortens the warmup period by reloading disk pages that were in the buffer pool before the restart rather than waiting for DML operations to access corresponding rows. Also, I/O requests can be performed in large batches, making the overall I/ O faster. Page loading happens in the background, and does not delay database startup【並不會延緩啟動程序】.
In addition to saving the buffer pool state at shutdown and restoring it at startup, you can save and restore the buffer pool state at any time, while the server is running. For example, you can save the state of the buffer pool after reaching a stable【steɪbl 穩定的、穩固的】 throughput【ˈθruːpʊt 吞吐量、生產力】 under a steady workload. You could also restore the previous buffer pool state after running reports or maintenance jobs that bring data pages into the buffer pool that are only requited for those operations, or after running some other non-typical workload.
Even though a buffer pool can be many gigabytes in size, the buffer pool data that InnoDB saves to disk is tiny【ˈtaɪni 極小的;微小的;微量的】 by comparison. Only tablespace IDs and page IDs necessary to locate the appropriate【əˈproʊprieɪt 適當的;合適的;恰當的】 pages are saved to disk. This information is derived from the INNODB_BUFFER_PAGE_LRU INFORMATION_SCHEMA table. By default, tablespace ID and page ID data is saved in a file named ib_buffer_pool, which is saved to the InnoDB data directory. The file name and location can be modified using the innodb_buffer_pool_filename configuration parameter.----實際上，儲存的數量並不大，可以理解為某種後設資料。
Because data is cached in and aged out of the buffer pool as it is with regular database operations, there is no problem if the disk pages are recently updated, or if a DML operation involves data that has not yet been loaded. The loading mechanism skips requested pages that no longer exist.
The underlying mechanism involves a background thread that is dispatched to perform the dump and load operations.
Disk pages from compressed tables are loaded into the buffer pool in their compressed form. Pages are uncompressed as usual when page contents are accessed during DML operations. Because uncompressing pages is a CPU-intensive【ɪnˈtensɪv 密集的;集約的;徹底的;十分細緻的;短時間內集中緊張進行的】 process, it is more efficient for concurrency to perform the operation in a connection thread rather than in the single thread that performs the buffer pool restore operation.
1.1 Configuring the Dump Percentage for Buffer Pool Pages
主要引數是innodb_buffer_pool_dump_pct，可以啟動時指定；也支援執行時，動態修改。
Before dumping pages from the buffer pool, you can configure the percentage of most-recently-used buffer pool pages that you want to dump by setting the innodb_buffer_pool_dump_pct option. If you plan to dump buffer pool pages while the server is running, you can configure the option dynamically
If you plan to dump buffer pool pages at server shutdown, set innodb_buffer_pool_dump_pct in your configuration file.
預設值是25%
The innodb_buffer_pool_dump_pct default value is 25 (dump 25% of most-recently-used pages).
1.2 Saving the Buffer Pool State at Shutdown and Restoring it at Startup
To save the state of the buffer pool at server shutdown, issue the following statement prior to shutting down the server:
SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;
innodb_buffer_pool_dump_at_shutdown is enabled by default. --此引數預設是開啟的。
1.3 Saving and Restoring the Buffer Pool State Online
To save the state of the buffer pool while MySQL server is running, issue the following statement:
SET GLOBAL innodb_buffer_pool_dump_now=ON;
To restore the buffer pool state while MySQL is running, issue the following statement:
SET GLOBAL innodb_buffer_pool_load_now=ON;
1.4 可用透過狀態值 和 系統表 檢視 這個過程
To display progress when saving the buffer pool state to disk, issue the following statement:
SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
To display progress when loading the buffer pool, issue the following statement:
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
系統表
You can monitor buffer pool load progress using Performance Schema.
--performance_schema.setup_instruments
--performance_schema.events_stages_current
--performance_schema.events_stages_history
The events_stages_current table returns an empty set if the buffer pool load operation has completed. In this case, you can check the events_stages_history table to view data for the completed event.
2. Excluding Buffer Pool Pages from Core Files
A core file records the status and memory image of a running process. Because the buffer pool resides in main memory, and the memory image of a running process is dumped to the core file, systems with large buffer pools can produce large core files when the mysqld process dies.
Large core files can be problematic for a number of reasons including the time it takes to write them, the amount of disk space they consume, and the challenges associated with transferring large files.---檔案太多不好
To reduce core file size, you can disable the innodb_buffer_pool_in_core_file variable to omit buffer pool pages from core dumps. The innodb_buffer_pool_in_core_file variable was introduced in MySQL 8.0.14 and is enabled by default.--可以透過引數變數來限制
Disabling innodb_buffer_pool_in_core_file takes effect only if the core_file variable is enabled and the operating system supports the MADV_DONTDUMP non-POSIX extension to the madvise() system call, which is supported in Linux 3.4 and later. The MADV_DONTDUMP extension causes pages in a specified range to be excluded from core dumps.
The core_file variable is read only and disabled by default. It is enabled by specifying the --corefile option at startup. The innodb_buffer_pool_in_core_file variable is dynamic. It can be specified at startup or configured at runtime using a SET statement.
If the innodb_buffer_pool_in_core_file variable is disabled but MADV_DONTDUMP is not supported by the operating system, or an madvise() failure occurs, a warning is written to the MySQL server error log and the core_file variable is disabled to prevent writing core files that unintentionally include buffer pool pages. If the read-only core_file variable becomes disabled, the server must be restarted to enable it again.
The reduction in core file size achieved by disabling the innodb_buffer_pool_in_core_file variable depends on the size of the buffer pool, but it is also affected by the InnoDB page size. A smaller page size means more pages are required for the same amount of data, and more pages means more page metadata.
《17.8.3.6 Saving and Restoring the Buffer Pool State》
《17.8.3.7 Excluding Buffer Pool Pages from Core Files》
https://dev.mysql.com/doc/refman/8.0/en/innodb-preload-buffer-pool.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-in-core-file.html