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

Through this article, you can learn about the following questions: the flow of the update of the 1:MySQL innodb engine; question 2: and the order in which you write the redo,undo,binlog, and then what is the order in which you swipe the disk? Question 3: and the process of refreshing redo and dirty data; summing up the above three questions is actually about the flow of MySQL innodb transactions. Then, I will summarize the next one in detail: the transaction flow of MySQL innodb: 1. Next, I will take update as an example to explain the transaction flow of MySQL5.6 's innodb. To sum up, it is: town to update he set name='liuwenhe' where id=5; 1) the beginning of the transaction 2) put an exclusive lock on the data of id=5, and add gap locks to the adjacent ranges on both sides of 5 to prevent other transactions from insert new data; 3) PIN the data pages that need to be modified to innodb_buffer_cache; 4) record the data of id=5 to undo log. 5) record the information of modifying id=5 to redo log. 6) modify the name='liuwenhe'. of id=5 7) refresh dirty data in innodb_buffer_cache to the underlying disk, this process has nothing to do with commit; 8) commit, triggers the page cleaner thread to flush redo from redo buffer cache to the underlying disk, and refreshing dirty data in innodb_buffer_cache to the underlying disk also triggers a refresh of redo; 9) record binlog (record to binlog_buffer_cache) 10) transaction ends; 2. About the four characteristics of transactions ACID transaction atomicity (Atomicity): all operations in the transaction are either completed, or no operations are done, and only part of the operations can be done. If an error occurs during execution, Rollback to the state before the start of the transaction as if the transaction had never been executed. Transaction Durability: once the transaction is completed, all changes made by the transaction to the database are persisted to the database. In order to ensure persistence, the database system will completely record the modified data to persistent storage. Transaction isolation: when multiple transactions are accessed concurrently, transactions are isolated, and one transaction should not affect the consistency of other transactions: consistency means that before the transaction starts and after the transaction ends, the integrity constraints of the database are not broken. This means that database transactions cannot break the integrity of relational data and the consistency of business logic. Second: redo and undo guarantee the atomicity and persistence of MySQL innodb transactions: in general, it can be considered that undo is used to save data before data changes. Ensure atomicity redo is used to save data after changes (note physical modification information), ensure persistence 1) first of all, the main purpose of Undo Log Undo Log is to achieve the atomicity of transactions. In the MySQL database InnoDB storage engine, Undo Log is also used to implement multi-version concurrency control (MVCC for short). Later articles will introduce mvcc. The principle of Undo Log is very simple, in order to satisfy the atomicity of the transaction, before operating any data, first backup the data to a place that is Undo Log, and then modify the data. If an error occurs or the user executes the ROLLBACK statement, the system can use the backup in Undo Log to restore the data to the state it was before the transaction began. It should be noted that before MySQL5.6, undo log was placed in the shared tablespace ibdata1, and MySQL5.6 began to support the separation of undo log into separate tablespaces and placed in separate file directories; with independent undo tablespaces, you no longer have to worry about undo making ibdata1 files bigger. Undo log is used for rollback, specifically, the database contents (rows) before copy transactions are transferred to undo buffer (or undo page) in innodb_buffer_pool, and the contents of undo buffer are flushed to disk at the appropriate time. Undo buffer, like redo buffer, is a circular buffer, but when the buffer is full, the contents of the undo buffer are flushed to disk; and the innodb_purge_threads background thread empties the undo page and cleans up the "deleted" page,InnoDB to treat the Undo Log as data, so the operation of recording the Undo Log is also recorded in the redo log. So that undo log can be cached like data 2) then introduce Redo Log, pay attention to write redo first, and then modify the page in buffer cache, because the modification is in page units, so write redo first to ensure that a large transaction commit, redo has been refreshed almost. On the other hand, if you first change the page in buffer cache, and then write redo, there may be a lot of redo to write, because a page may have many data rows; and many data rows may generate more redo, then commit may have a lot of redo to write; on the contrary, Redo Log records a backup of new data. You just need to persist the Redo Log before the transaction is committed, and there is no need to persist the data. When the system crashes, the data is not persisted, but the Redo Log is persisted. The system can restore all data to the latest state according to the content of Redo Log. It should be noted that during the transaction, the redo is first written into the redo log buffer, and then the MySQL background process page cleaner thread refreshes the redo appropriately to the low-level disk for permanent storage; because the redo (from redo log buffer to disk) must be refreshed before the dirty data of the buffer pool is refreshed, the condition that triggers the refresh of the dirty data of the buffer pool also triggers the refresh of the redo. It should also be noted that before version 5.6 of MySQL, master thread was used to complete the task of brushing dirty data (including dirty data in buffer pool and redo in redo log buffer). In version 5.6 of MySQL, the refresh operation was placed in a separate Page Cleaner Thread. Checkpoint (checkpoint) technology aims to solve the following problems: 1. Shorten the recovery time of the database; 2. Flush dirty pages to disk when the buffer pool is insufficient 3. Refresh the dirty page when the redo log is not available. Within the InnoDB storage engine, there are two kinds of Checkpoint: Sharp Checkpoint and Fuzzy Checkpoint Sharp Checkpoint refresh all dirty pages back to disk when the database is shut down, which is the default working mode, that is, the parameter innodb_fast_shutdown=1. However, if the database also uses Sharp Checkpoint at run time, the availability of the database will be greatly affected. Therefore, Fuzzy Checkpoint is used to refresh pages inside the InnoDB storage engine, that is, only part of the dirty pages are refreshed, rather than all the dirty pages are refreshed back to disk. Fuzzy Checkpoint: 1, Master Thread Checkpoint; 2, FLUSH_LRU_LIST Checkpoint; 3, Async/Sync Flush Checkpoint; 4, Dirty Page too much Checkpoint 1, Master Thread Checkpoint refresh a certain percentage of pages back to disk from the list of dirty pages in the buffer pool at the speed of every second or every ten seconds. This process is asynchronous, at which time the InnoDB storage engine can do other operations and the user query thread will not block. 2. FLUSH_LRU_LIST Checkpoint because the InnoDB storage engine needs to ensure that there are about 100 free pages available in the LRU list. Before the InnoDB1.1.x version, you need to check whether there is enough free space in the LRU list to occur in the user query thread, which obviously blocks the user's query operation. If there are no 100 free pages available, the InnoDB storage engine removes the page at the end of the LRU list. If there are dirty pages in these pages, you need to do Checkpoint, and these pages are from the LRU list, so they are called FLUSH_LRU_LIST Checkpoint. Starting from MySQL version 5.6, that is, the InnoDB1.2.x version, this check is done in a separate Page Cleaner thread, and the user can control the number of pages available in the LRU list through the parameter innodb_lru_scan_depth, which defaults to 1024, such as mysql > SHOW GLOBAL VARIABLES LIKE 'innodb_lru_scan_depth'. +-+ | Variable_name | Value | +-+-+ | innodb_lru_scan_depth | 1024 | +-+-+ 3. Async/Sync Flush Checkpoint refers to situations where the redo log file is not available At this point, you need to force some pages to be flushed back to disk, and the dirty pages are selected from the list of dirty pages. If you record the LSN that has been written to the redo log as redo_lsn and the LSN that has been flushed back to the latest page on disk as checkpoint_lsn, you can define: checkpoint_age (dirty pages can be understood) Or dirty pages to be refreshed) = redo_lsn-checkpoint_lsn then define the following variables: async_water_mark = 75 * total_redo_log_file_size sync_water_mark = 90 * total_redo_log_file_size if the size of each redo log file is 1GB and two redo log files are defined, the total size of the redo log file is 2GB. So, async_water_mark=1.5GB,sync_water_mark=1.8GB. Then: 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.