Memory in MySQL InnoDB architecture, mysqlinnodb

Source: Internet
Author: User

Memory in MySQL InnoDB architecture, mysqlinnodb

First

1. Buffer Pool
The InnoDB Storage engine is based on disk storage and manages records on pages. The buffer pool is usually used to improve the overall performance of the database. The buffer pool is simply a piece of memory. The memory speed can compensate for the impact of slow disk speeds on database performance. When performing read operations in the database, first store the pages read from the disk in the buffer pool. The next time you read the same page, first determine whether the same page exists in the buffer pool, if yes, it is directly read by hit. If no, it is read from the disk. During database modification, the pages in the buffer pool are first refreshed to the disk at a certain frequency. The refresh mechanism is not triggered when every page is changed. Instead, a checkpoint mechanism is used to refresh the disk. You can set the buffer pool size through the innodb_buffer_pool_size (unit) parameter. Database types in the buffer pool include: index page, data page, undo page, insert buffer page, adaptive hash index, and lock info stored in innodb), data dictionary information (data dictionary ). The innodb_buffer_pool_instances parameter allows multiple buffer pool instances. Each page is evenly allocated to different instances based on the hash value to reduce competition for internal database resources and increase the concurrent processing capability of the database.
2. LRU List, Free List, and Flush List
The buffer pool of the innoDB Storage engine is managed through the LRU algorithm, that is, the most frequently used pages are in the front of the LRU list (managing pages that have been read, the least used page is at the end of the LRU list. The size of each page in the buffer pool is 16 kb. However, it optimizes the LRU algorithm and adds the midpoint location. Add the newly acquired data to the midpoint instead of the List header. Generally, the midpoint is located at 5/8 of the LRU list. You can set it through the innodb_old_blocks_pct parameter. The list after the midpoint is called the old list, and the previous list is called the new list. Why is the innoDB Storage Engine Using midpoint? This is because, if you directly put the read data into the beginning of LRU, some SQL operations will cause the pages in the buffer pool to be flushed out, thus affecting the efficiency of the buffer pool. You can use the innodb_old_blocks_time parameter to set how long it will take to add the page to the LRU new list after it is read to the midpoint location.

When the database is started, the LRU list is empty, and all the pages are in the Free List. To split pages from the buffer pool, first check whether there are idle pages in the Free List. If yes, delete the pages from the Free List and add them to the LRU list. Otherwise, the page at the end of the LRU list is eliminated Based on the LRU algorithm and the memory space is allocated to the new page. When a page is added from the old part of LRU to the new part, page made young, because of the setting of the innodb_old_blocks_time parameter, the operation that does not add a page from old to new is called page not made young. The InnoDB Storage engine 1.0.X supports page compression. The original 16 K pages are compressed to 1 K, 2 K, 4 K, and 8 K. The LRU list page changes accordingly due to the size of the page, for non-16 K pages, use the unzip_LRU list for maintenance. After a page in the LRU list is modified, it is called a dirty page. In this case, the database uses the checkpoint mechanism to fl the dirty pages back to the disk. The pages in the Flush list are dirty pages. Dirty pages exist in the LRU list page and in the Flush list. The LUR list is used to manage the availability of pages in the buffer pool, and the Flush list is used to manage and Flush back the disk. You can run the show engine innodb status Command to view the usage and running status of the LRU and Free lists.

The figure shows that:

Buffer pool size has a total of 8191 pages (8191*16 k );

Free buffers indicates the number of pages in the current Free list;

Datebase pages indicates the number of pages in the LRU list. The sum of Free buffers and Database pages may not be equal to the Buffer pool size. This is because the buffer pool may also be allocated to pages such as adaptive hash indexes, lock information, and insert buffer. This part does not require LRU algorithm maintenance, so it is not in the LRU list.

Page made young indicates the number of times the page was moved to the previous section in the LRU list.

Buffer pool hit rate indicates the Buffer pool hit rate, which cannot be less than 95%.

LRU len indicates the number of pages in the LRU list.

Unzip_LRU len indicates the number of compressed pages. LRU len contains unzip_LRU len. Modified db pages indicates the number of dirty pages.

You can also use the information_schema.INNODB_BUFFER_PAGE_LRU table to observe the specific information of each page in the LRU list.

3. redo log buffer)
The innoDB Storage engine first puts the redo log information into this buffer zone, and then fl it into the redo log file at a certain frequency. Redo the log buffer usually does not need to be much, as long as the transaction volume generated per second is within the buffer size. You can use the innodb_log_buffer_size parameter to set the size.

4. Additional memory pool
In the innoDB Storage engine, memory management is performed in a way called memory heap. When allocating the memory of some data structures, you need to apply from the extra memory pool. When the memory in this region is insufficient, you need to apply from the buffer pool. For example, L: buffer Pool is allocated, but the frame buffer in each buffer pool also has the corresponding buffer control object (buffer control block ), these records some information such as LRU, lock, and wait, and the memory of this object needs to be applied from the extra memory pool. Therefore, you must add this value when applying for a large buffer pool.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.