InnoDB maintains a cache of data and index information to the memory of the storage area called buffer pool, which caches the most recently accessed data. By configuring the parameters of each buffer pool, we can significantly improve the performance of MySQL. InnoDB buffer pool is based on the LRU algorithm, we can easily understand the following LRU algorithm: Lru:least recently used, InnoDB management buffer poll is the buffer pool as a list management, Based on the LRU algorithm management. When a new page message is read into buffer pool, buffer pool expels the least recently used page information from the buffer pool and joins the new page to the middle of the list, which is called the midpoint insertion strategy. In general, the list head is stored in hot data, the so-called Young page (the most recently accessed data), the tail of the list is the old page (the data is not accessed recently). This algorithm guarantees that the most recently used page information will be saved in the recently accessed sublist, instead of being frequently accessed, it will be saved in the old sublist. The page information in the old sublist will be evicted when the new data is written. The LRU algorithm has the following standard algorithms:
(1) The list information for 3/8 is the old list, and this information is the object being evicted.
(2) The midpoint of the list is the connection point of our so-called old list head and the new list tail, which is equivalent to a boundary
(3) The reading of the new data is first inserted into the old list's head,
(4) If the data of the old list is accessed, the page information becomes the new list and becomes the young page, and the data page information is moved to the head of new sublist.
(5) in the buffer pool of the database, whether the data of new sublist or old sublist will not be accessed, the end will be moved to the tail of the list as the victimIn general, page information is moved to new sublist by query statements that are immediately queried, which means they remain in buffer pool for a long time. A table scan, which includes operations such as mysqldump or a select without a where condition, will flash a large amount of data into the buffer pool, and will also brush more information from the buffer pool, even if the operation may only be used once. Similarly, if the read-ahead background process reads large amounts of data, it will also cause the buffer pool to refresh a large number of high-frequency data pages, but these operations are controllable, the following 3, 4 will be said. Read-ahead Operation simply said is a MySQL background pre-reading process, to ensure that MySQL pre-read data into the buffer pool. Let's take a look at some parameter information of InnoDB buffer pool, which will explain in detail later: Innodb_buffer_pool_size This value is the total size of the set InnoDB buffer pool Innodb_buffer_pool_ Chunk_size is the size of the execution unit chunk size of the InnoDB buffer pool. There's a relationship here. To determine the following, it is best to follow this setting innodb_buffer_pool_size=innodb_buffer_pool_chunk_size * INNODB_BUFFER_POOL_INSTANCES*N (N >=1) Innodb_buffer_pool_instances is the number of instances that set the InnoDB buffer pool, each of which has its own standalone list management buffer pool. Innodb_old_blocks_pct is the default InnoDB buffer pool midpoint location, the default value is 37, the maximum 100, that is, our so-called 3/8 location, you can set their own innodb_old_blocks_ Time to save the data stored in buffer pool when the list position is not changed at insert time innodb_read_ahead_threshold parameter controls when MySQL is pre-read, It is also possible to control the data sensitivity of MySQL read-ahead data, and InnoDB initiates an asynchronous read-ahead operation if the data page stored in the buffer pool has a frequent value greater than Innodb_read_ahead_threshold value innodb_ Random_read_aheadThe disabled is the parameter that controls the read-ahead mode, and when turned on, it uses a random read-ahead innodb_adaptive_flushing to specify whether or not to dynamically adapt the dirty page to the disk, which is determined by the load itself. However, try not to set INNODB_ADAPTIVE_FLUSHING_LWM close adaptive_flushing words will be useful to mark the percentage of redo log. Because it's very seldom used, not too much to see innodb_flush_neighbors controls whether to refresh the buffer pool or refresh the dirty pages in the buffer pool to a single page. Innodb_flushing_avg_loops parameter for InnoDB save InnoDB buffer pool Several flushing snapshots Innodb_lru_scan_depth control the LRU algorithm for a parameter to control the buffer The location of the pool background process Page_cleaner refreshes the dirty page innodb_max_dirty_pages_pct parameter causes the InnoDB buffer pool to refresh the data without allowing the percentage of dirty data to exceed this value. Innodb_max_dirty_pages_pct_lwminnodb automatically maintains background jobs automatically cleans up dirty data from buffer pool, when the dirty page in buffer pool occupies a ratio of innodb_max_dirty_pages_ When the PCT_LWM is set, the dirty page is automatically cleared out of buffer poolinnodb_buffer_pool_filename the specified file name Innodb_buffer_pool_dump_at_ Shutdown configured InnoDB How to preserve the state of the current buffer pool to avoid a lengthy warm-up time after the server restarts. INNODB_BUFFER_POOL_LOAD_AT_STARTUP Specifies that this parameter is started, the database restarts after the automatic warm-up, read into the buffer pool before restarting the information saved Innodb_buffer_pool_dump_now and innodb_ Buffer_pool_load_now when the database has been raised, we forget the previous designation, or you can specify to restore innodb_buffer_pool_dump_pct set the following settings settings to restore how much data in the buffer pool Innodb_ Buffer_pool_load_abort terminates the buffer pool load and can specify the load to run &nbsP; The following is a detailed interpretation of the InnoDB buffer pool. 1: Ideally, the buffer pool size should be set as large as possible so that the extra paging of the process can be reduced, and when the buffer pool size is set large enough, the entire database is stored in the memory, when the data is read to buffer pool After a size, subsequent reads do not have to be read on disk. Let's take a look at how buffer pool size is set: (1): When the database has been started, we can modify the size of the buffer pool by online adjustment. By the following statement: SET GLOBAL innodb_buffer_pool_size=402653184; When the statement is executed, it does not take effect immediately, but waits for all things to succeed before it takes effect. New connections and things must wait for other things to complete successfully after the buffer pool size setting takes effect, the connection succeeds, otherwise it will remain in the waiting state. During the buffer pool size to complete the defragmentation, remove the cache page, and so on. The operation to increase or decrease the buffer pool size is performed as an execution block, and the size of the innodb_buffer_pool_chunk_size defines the size of an execution block, which, by default, is 128M. The size of the buffer pool size is preferably set to an integer multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, and is greater than or equal to 1. If the size of your machine configuration is not an integer multiple, the size of the buffer pool size is modified to be an integer multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. will be slightly smaller than the size of the buffer pool size you configured. For example, take 8G for example: Mysqld--innodb_buffer_pool_size=8g--innodb_buffer_pool_instances=16, then innodb_buffer_pool_instances= The size of 16 is just set to 16. Is an integer multiple of the relationship. And the size of the innodb_buffer_pool_chunk_size can be specified in the MY.CNF. There is another situation where the INNODB_BUFFER_POOL_CHUNK_Size * Innodb_buffer_pool_instances is larger than buffer pool size, innodb_buffer_pool_chunk_size is also adaptive to buffer pool Size/innodb _buffer_pool_instances, it can be seen that the management of MySQL is very only. Show status where Variable_name= ' Innodb_buffer_pool_resize_status can help us see the status if we want to see the state of the buffer pool. We can take a look at the process of adding buffer pool and look at the reduced log: In fact, it is well understood that we can see that every time we increase or decrease the buffer pool, we do innodb_buffer_pool_. Chunk increase or release, increase or release the execution block according to the size of the innodb_buffer_pool_chunk_size setpoint. Added process: Increase the execution block, specify the new address, and add the newly added execution block to the free list (which is understood as a listing of the control execution block). Reduced process: Rearrange the buffer pool and free pages, remove data from the block, and specify a new address. 2: In the case of a 64-bit operating system, you can split the buffer pool into multiple parts, so that the maximum possible reduction in contention can occur in high concurrency. Let's take a look at how to configure the buffer pool instances configuring multiple buffer pool instances can greatly improve the performance of MySQL in high concurrency situations, and optimize the contention of different connection read buffer pages. We can set the buffer pool Instances by setting innodb_buffer_pool_instances. When the InnoDB buffer pool is large enough, you can have a good performance when you read from memory, but it is also possible to encounter multiple threads simultaneously requesting a buffer pool bottleneck. Setting multiple buffer Pool instances at this time can minimize contention for connections. This ensures that each page read from memory corresponds to a buffer Pool Instances, and that this correspondence is a random relationship. It is not that the thermal data is stored in a buffer Pool instances, and the internal hash algorithm is used to achieve this random number. Each buffer Pool instances has its own free LISTS,LRU and some other buffer pool data structures, each buffer pool instances is relatively independent. Innodb_buffer_pool_instances settings must be greater than one is more than a configuration, but this function is the premise that the innodb_buffer_pool_size size must be greater than 1G, ideally innodb_buffer_ Every instance of Pool_instances is guaranteed to be above 1G. 3: You can frequently read data in buffer pool when backup or report, there is no need to worry too much. InnoDB uses a method that is not as strict as LRU to ensure that the most recently accessed data is written to the buffer pool, and the maximum possible reduction in the amount of data is reduced. The statement is a full-table scan or the data will not be accessed again, but the buffered data will still be written to buffer pool. The newly written data is inserted in the middle of the LRU list and is inserted by default into the position of 3/8 from the end of the list, and when the written data is first accessed in the buffer pool, the position in the list moves forward. In fact, the list is kept in two positions, the old location will not be immediately cleared, until the old LRU list is marked as the same time, the next time the data is inserted as the victim will be erased. We can specify where to insert the LRU list, and we can also set the same optimization method when the index scan or full table scan is used. innodb_old_blocks_pct This parameter is set to the location of the insertion, the default value is 37, we can set the value is between 5-95, the remainder is not used to save hot data. But another serious problem is that when a full-table scan or an index scan is often accessed, large data is stored in the buffer pool, which we all know is a dangerous thing to do. So MySQL gives us the following parameters to set the save time for the data that remains in buffer pool when the list position is not changed at insertion time innodb_old_blocks_time, in milliseconds, the default value of this value is 1000. If you increase this value, you will make a lot of page information in the buffer pool become older faster, this is good to understand, because the data will not be quickly erased in memory, it would become hot data and squeeze out the original cached data. The above two parameters can be set dynamically, of course, can also be set in the MY.CNF. Of course set these before the machine must beConfiguration, table information, load conditions are fully understood in order to set up, production library as far as possible not to modify. If there are a large number of large queries in the OLTP system, setting up the innodb_old_blocks_time can provide a large system stability. If a large query is large enough to be stored in the buffer pool, we can specify a smaller value for the innodb_old_blocks_pct to ensure that the data will only be read once, for example when set to 5. It restricts the read data to be read to only 5% of the buffer pool. Of course, some small table and is often accessed by the data, you can set a large value, such as 50. Of course setting these two values must be based on your full understanding of your data load on the basis of, or do not change. 4: We can control when MySQL reads data into buffer pool in what way. Read-Ahead is the asynchronous reading of multiple pages of data read into the buffer pool of a process, and these pages are considered to be read quickly, when the need to read this data will be required to put the page in a zone, InnoDB is to improve the performance of Io read and write by two read-ahead. Linear pre-reading: a technique that predicts that data will soon be read because the page data in the buffer pool is accessed sequentially. We can control when MySQL is pre-read by setting the Innodb_read_ahead_threshold parameter, or it can control the data sensitivity when MySQL read-ahead data, if the data page stored in buffer pool has more frequent values than innodb_ The value of Read_ahead_threshold, InnoDB initiates an asynchronous read-ahead operation, and the Innodb_read_ahead_threshold value can be set to any value of 0-64. The default value is 56, and the higher the value setting, the more restrictive the search will be. When set to 8, only drizzle 8 pages of data are read into the buffer pool before being retrieved. Random pre-read: Random pre-reading allows the page information that will be read in memory to be quickly organized into a zone, and the reading order of these pages is not sequenced, and InnoDB can quickly dispatch information about many pages similar to the buffer pool and make requests. These pages may not be contiguous, and to use this feature you will need to open innodb_random_read_ahead this parameter is on. The following output information from show ENGINE Innodb can help us navigate to whether to open this parameter: Innodb_buffer_pool_read_aheadinnodb_buffer_pool_read_ahead_evictedinnodb_buffer_pool_read_ahead_rnd5: We can control when the MySQL daemon process is refreshed or dynamically adjusted to the MySQL load. InnoDB automatically cleans the dirty data from the buffer pool automatically, and automatically clears the dirty page out of buffer when the dirty page in buffer pool takes up the INNODB_MAX_DIRTY_PAGES_PCT_LWM value. Pool, which is to ensure the share of dirty pages in the buffer pool, but also to prevent the dirty page share more than innodb_max_dirty_pages_pct set value, when the share of dirty pages reached Innodb_max_dirty_pages_ When the PCT is set, InnoDB forces the buffer pool pages to be refreshed. InnoDB uses an algorithm based on the latest generation of redo log and the most recent refresh rate to determine the flushing speed. Such an algorithm can ensure that the database flushing does not affect the performance of the database, but also to ensure that the data in the database buffer pool dirty data consumption ratio. This automatic adjustment also prevents the sudden concurrency beneficence from getting larger, but the flush will not be able to perform normal IO read and write operations. We do not use the log until InnoDB is recycled, and before reusing the previous log file, InnoDB will flush all the data in the buffer pool associated with this log record to disk, the so-called sharp checkpoint, Very much like the checkpoint of SQL Server. When an INSERT statement generates a large amount of redo information, the logs that need to be logged are currently redo log files that are not fully stored and are also written to the current redo file. When all the space used in the redo log is exhausted, it triggers sharp checkpoint, so this time, even if the dirty data share does not reach innodb_max_dirty_pages_pct, it will be refreshed. This algorithm is the test, so do not arbitrarily set, preferably the default value. However, we will not be able to redo log to record the redo information of two things. Because there are so many benefits, the value of Innodb_adaptive_flushing is true by default. 6: Configurable InnoDB How to preserve the state of the current buffer pool to avoid a lengthy warm-up time after the server restarts. by Innodb_buffer_poOl_dump_at_shutdown to set, when these parameters are set, MySQL will quickly restore the previous in-memory data after the machine restarts, of course, the data from the disk from the new read into the buffer pool, the individual think this value still need to be configured, Of course it will take some time for the new DML operation to not be able to operate when the data is newly read into memory. How is this data recovered? In fact, the INNODB_BUFFER_PAGE_LRU table (INFORMATION_SCHEMA) records the cached table ID and PAGE ID, which can be recovered by this. Before the load data enters the buffer pool, you can set the following settings to restore how much data in the buffer pool. Of course the default value is definitely 100, and not setting the default is all recovery. Set GLOBAL innodb_buffer_pool_dump_pct=40; Resets the load data into buffer pool when the server restarts, by default it is off, It can also be specified at the start time or specified in my.cnf: Set GLOBAL innodb_buffer_pool_dump_at_shutdown=on; When the database has been raised, we forget the previous designation, or we can specify the immediate recovery: set Global Innodb_buffer_pool_dump_now=on; Save information to specify set GLOBAL innodb_buffer_pool_load_now=on before restarting; Load information if you want to terminate the buffer pool load, you can specify the load to run: SET GLOBAL innodb_buffer_pool_load_abort=on, or you can view the status with the following command: Show status like ' Innodb_buffer_pool_dump_status '; SHOW STATUS like ' innodb_buffer_pool_load_status '; and we can monitor the buffer pool through the INNODB performance schema. Load status (1) Open or close Stage/innodb/buffer pool load update performance_schema.setup_instruments SET ENABLED = ' YES ' WHERE NAME like ' Stage/innodb/buffEr% '; (2) Open the following parameters to get the nearest buffer pool status: SET GLOBAL innodb_buffer_pool_dump_now=on; SET GLOBAL Innodb_buffer_pool_load_now=on; SHOW STATUS like ' innodb_buffer_pool_dump_status ' \gselect event_name, work_completed, work_estimated from Performance_ Schema.events_stages_current; SELECT Event_Name, work_completed, work_estimated from performance_schema.events_stages_history; one thing to keep in mind is that if you compress a table, The compressed format is still maintained when reading to buffer pool. The decompression program is not invoked until it is read.
InnoDB buffer Pool pee
