Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the use of MySQL database buffer pool". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

If your database runs slowly or fails to respond to queries for some reason, every component in the technology stack that relies on the database will suffer performance problems. In order to ensure the smooth operation of the database, you can actively monitor the following indicators related to performance and resource utilization: buffer pool usage.

Buffer pool usage

MySQL's default storage engine, InnoDB, uses an area of memory called a buffer pool to cache data for data tables and indexes. Buffer pool indicators belong to resource indicators, not working indicators.

By default, the size of the buffer pool is usually relatively small, 128MiB. However, MySQL recommends that it be expanded to 80% of the physical memory of the dedicated database server. However, MySQL also points out some caveats: the memory overhead of InnoDB may increase its memory footprint by more than 10% of the buffer pool size. Moreover, if you run out of physical memory, the system will turn to paging, resulting in serious damage to database performance.

The buffer pool sizing operation is done in blocks, and the size of the buffer pool must be the size of the block multiplied by the number of instances and then multiplied by a multiple.

Innodb_buffer_pool_size = N * innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

The default size of the block is 128 MiB, but it can be configured by itself starting with MySQL 5.7.5. The values of both parameters can be checked as follows:

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size"; SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances"

Monitoring metrics:

The metrics Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads are critical to understanding buffer pool utilization. Innodb_buffer_pool_read_requests tracks the number of reasonable read requests, while the Innodb_buffer_pool_reads tracking buffer pool cannot satisfy the number of requests that can only be read from disk. We know that reading from memory is usually several orders of magnitude faster than reading from disk, so if the value of Innodb_buffer_pool_reads starts to increase, it means that there is a big problem with database performance.

Buffer pool utilization is an important indicator that should be checked before considering expanding the buffer pool. Utilization metrics cannot be read directly, but can be simply calculated in the following ways:

(Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free) / Innodb_buffer_pool_pages_total

If your database does a lot of reading from disk and the buffer pool has a lot of free space, this may be because the cache has only recently been cleaned and is still in the warm-up stage. If your buffer pool is not full but can effectively handle read requests, your data working set is well suited to the current memory configuration.

Convert buffer pool metrics to bytes:

Most buffer pool metrics are recorded in memory pages, but these metrics can also be converted to bytes, making it easier to associate with the actual size of the buffer pool. For example, you can use the server status variable that tracks the total number of memory pages in the buffer pool to find out the total size of the buffer pool in bytes:

Innodb_buffer_pool_pages_total * innodb_page_size

The InnoDB page size is adjustable, but the default setting is 16 KiB, or 16384 bytes. You can use the SHOW VARIABLES query to find out its current value:

SHOW VARIABLES LIKE "innodb_page_size"

This is the end of "MySQL database buffer pool usage". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.