MySQL 8.0 Reference Manual（读书笔记72节--InnoDB Buffer Pool Configura
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
来源:https://www.cnblogs.com/xuliuzai/p/18093143
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]