Shulou(Shulou.com)05/31 Report--

The main content of this article is to explain "what is the role of mysql checkpoint". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the role of mysql checkpoint?"

Checkpoint checkpoint is mainly the work of refreshing dirty pages to disk to make the database achieve data state consistency. Because the commit of the transaction only refreshes the operation log to disk, dirty data is flushed to disk asynchronously, which depends on checkpoint.

1. Mainly solve the following problems:

1. Reduce the roll forward operation during recovery and reduce the recovery time

2. Recycle dirty pages and recycle cache pool

3. Redo log is not available. Refresh dirty pages.

2. Types and trigger conditions of checkpoint

Sharp checkpoint (similar to full oracle)

When innodb_fast_shutdown=1 and 0, all dirty pages are flushed to disk when the database is shut down

Fuzzy checkpiont (similar to oracle increment)

1. The Master Thread process asynchronously flushes a certain proportion of dirty pages to disk every few seconds or every ten seconds.

2. FlUSH_LUR_LIST checkpoint is because before the innodbmysql5.6 version, you need to ensure that there are about 100 free pages in the LRU list. Since the mysql5.6 version, this check is carried out in a separate page cleaner thread, and the user can control the number of available pages in the LRU list through the parameter innodb_lru_scan_depth. The default is 1024.

3. When the Async/Sync Flush checkpoint value redo log file is not available

In cases where the redo log file is not available, some need to be flushed back to disk, and the operation occurs on the Flush list. If you record the LSN that has been written to the redo log as redo_lsn and the latest LSN that has been flushed back to disk as checkpoint_lsn, you can define: checkpoint_age = redo_lsn-checkpoint_lsn defines the variables async_water_mark=75%*total_redo_log_file_size and sync_water_mark=90%*total_redo_file_size. If the size of each redo log is 1G and two redo logs are defined for a total of 2G. So, async_water_mark=1.5G,sync_water_mark=1.8G.

When checkpoint_age

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.