Shulou(Shulou.com)06/01 Report--

InnoDB change buffer is an important data change log.

The main purpose of Change buffer is to cache the data operations on the secondary index, so as to reduce the random IO of the secondary index and achieve the effect of merging operations.

In previous versions of MySQL5.5, it was originally called insert buffer because it only supported caching insert operations, but it was changed to change buffer because more operation type caching was supported in later versions.

When the page corresponding to the data of the updated / inserted nonclustered index is not in memory (the update operation for the nonclustered index usually results in a random IO), it is put into an insert buffer, and when the page is subsequently read into memory, the records of these changes are merge into the page. When the server is idle, the background thread also does the merge operation

However, change buffer takes up buffer pool, and when there are few nonclustered indexes, it is not always necessary, but reduces the ability of buffer pool to do data cache.

The INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table provides metadata about each page in the buffer pool, including change buffer index and change buffer bitmap pages. Change buffer pages are identified by PAGE_TYPE. IBUF_INDEX is the page type for change buffer index pages, and IBUF_BITMAP is the page type for change buffer bitmap pages.

For example, you can query the INNODB_BUFFER_PAGE table to determine the approximate number of IBUF_INDEX and IBUF_BITMAP pages as a percentage of total buffer pool pages.

SELECT

(SELECT COUNT (*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE

WHERE PAGE_TYPE LIKE 'IBUF%'

) AS change_buffer_pages

(

SELECT COUNT (*)

FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE

) AS total_pages

(

SELECT ((change_buffer_pages/total_pages) * 100)

) AS change_buffer_page_percentage

+-+

| | change_buffer_pages | total_pages | change_buffer_page_percentage | |

+-+

| | 25 | 8192 | 0.3052 | |

+-+

There is no comparison, I do not know whether the performance of this result is good or bad.

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.