Innodb buffer parameters and viewing methods (to be continued), innodbbuffer

Innodb buffer parameters and viewing methods (to be continued), innodbbuffer

Buffer parameters:

Innodb_buffer_pool_size innodb_buffer size

Innodb_buffer_pool_instances innodb_buffer instance count

Innodb_old_blocks_pct LRU end mid Point Location

Innodb_old_blocks_time LRU retention time on the Old end

Innodb_io_capacity

Innodb_max_dirty_pages_pct: maximum percentage of dirty pages in buffer

Innodb_purge_batch_size full purge reclaim the number of undo pages

Innodb_adaptive_flushing adaptive refresh, which affects the number of dirty pages refreshed per second (if the size of dirty pages is greater than innodb_max_dirty_pages_pct, the most appropriate number of dirty pages refreshed is determined based on the redolog generation speed)

Innodb_change_buffering DML cache (inserts, deletes, purges, changes, all, none)

Innodb_change_buffer_max_size DML cache proportion to innodb Cache

-- View the free_buffer and total number of pages in the Buffer.

SELECT POOL_ID, POOL_SIZE, FREE_BUFFERS, DATABASE_PAGES

FROM INNODB_BUFFER_POOL_STATS;


-- View page made young and not young
-- (Page made young indicates the number of data pages that are moved from the OLD end to the New end for LRU; not young indicates the number of data pages that are not moved from the Old end to the New end for innodb_old_blocks_time parameter)

SELECT POOL_ID, HIT_RATE, PAGES_MADE_YOUNG, PAGES_NOT_MADE_YOUNG

FROM INNODB_BUFFER_POOL_STATS;

-- View the unzip_LRU list page (unzip_LRU manages non-16 K pages)

SELECT TABLE_NAME, SPACE, PAGE_NUMBER, COMPRESSED_SIZE

FROM INNODB_BUFFER_PAGE_LRU

WHERE COMPRESSED_SIZE <> 0;


-- View the dirty data page (if table_name is NULL, it indicates the system tablespace)

SELECT table_name, space, page_number, page_type

FROM INNODB_BUFFER_PAGE_LRU

WHERE OLDEST_MODIFICATION> 0;