MySQL Background thread cleanup work

background cleanup work : Dirty page Brush disc, Undo Recycle

1.Page Cleaner thread: Refresh Dirty page

2,purge thread: Empty the Undo page, clean the "deleted" page

First, Innodb_page_cleaners

Page cleaner The number of threads that the thread has brushed dirty pages from in buffer pool.

1, 5.7 new features

Prior to version 1 and 5.6, the cleanup of dirty pages was handed to master thread;

2. Page Cleaner thread is a new thread introduced by 5.6.2 (single threaded), removing the buffer pool brush from the master thread the work of the Dirty page is independent of the thread (default is to start a thread);

3,5.7 began to support multi-threaded brush dirty page ;

2. Initiating a write request for dirty pages

Cleanup because it triggers dirty pages that require dirty page recycling (dirty for a long time, cold dirty pages ...) ）

3. How to adjust this parameter

Mysql>Show variables like 'i%cleaners';+----------------------+-------+|Variable_name|Value|+----------------------+-------+|Innodb_page_cleaners| 1     |+----------------------+-------+1Rowinch Set(0.05Sec

1. Add innodb_page_cleaners=num value in configuration file my.cnf

2, the default is 1, the maximum can be 64, that is, there will be 64 page cleaner threads concurrently working to clean up dirty pages

4, how to determine whether to modify the increase innodb_page_cleaners

Mysql>Show Global Status like '%wait_free';+------------------------------+-------+|Variable_name|Value|+------------------------------+-------+|Innodb_buffer_pool_wait_free| 0     |+------------------------------+-------+1Rowinch Set(0.01Sec

Innodb_buffer_pool_wait_free: Indicates that the dirty page has not become a system performance bottleneck, if the value is large, you need to increase the innodb_page_cleaners value, and increase the write thread.

1, usually, for buffer pool write occurs in the background, when InnoDB need to read or create a data page, but there is no clean available pages, InnoDB will be able to wait for the operation to complete the first to brush some dirty pages into the disk.

2,Innodb_buffer_pool_wait_free is the number of instances of this wait operation . If the size of the innodb_buffer_pool_size is set appropriately, the value will be small, even 0.

Second, innodb_purge_threads

Purge thread, background thread, dedicated to InnoDB cleanup, resource recycling operations.

1. Cleaning operation

1. Cleanup Undo Page

Undo records the data before the modification is used for rollback, and the undo information is cleaned up when it is committed and no longer rolled back.

2. clean the data line with "deleted" tag inside the page

1, when we delete the data row, is the data page to delete the data rows to mark "deleted", transaction commit (FAST);

2. The background thread purge the thread to actually delete the data row with the "deleted" label in the data page.

2. Adjustment basis

1, the system has a large number of delete, the update of the primary key

Mysql>Show Global Status like '%rows%d%ted';+---------------------+-------+|Variable_name|Value|+---------------------+-------+|innodb_rows_deleted| 0     ||innodb_rows_updated| 5     |+---------------------+-------+2Rowsinch Set(0.01Sec

2, mysql> show engine InnoDB status \g

 trx id  counter 1159171   #事务计数  Purge  done  for  trx " s n : o < 1157813  #事务清空位置    #   1159171- 1157813 represents the amount of transactions to be emptied  undo N:o  < 0  # Current cleanup transaction undo location  State:running but idle  #启动但是闲置  History list Length  Span style= "COLOR: #800080" >1029   #当前undo数据页的总量1029 *16k   

3, adjust:innodb_purge_threads Default value is 1,OLTP system will generally be modified to 4

Mysql>Show variables like '%purge_t%';+----------------------+-------+|Variable_name|Value|+----------------------+-------+|Innodb_purge_threads| 4     |+----------------------+-------+1Rowinch Set(0.01Sec

MySQL Background thread cleanup work