MySQL File understanding

Mysql_innodb's Undo_log and Redo_logoriginal October 28, 2014 11:26:34

As is known to all, MySQL supports a variety of storage engines and is now commonly used by MyISAM and InnoDB. MyISAM is relatively simple, but does not support transactions, and InnoDB is transaction-safe. And the transaction of InnoDB is inseparable from Undo_log and Redo_log.

MySQL InnoDB storage engine

Here's a quick introduction to InnoDB's storage engine.

Memory Buffer Pool

If MySQL does not use a memory buffer pool, every time you read and write data, you need to access the disk, which will definitely increase the I/O request, resulting in inefficiency. So when the InnoDB engine reads and writes data, the corresponding data and indexes are loaded into the buffer pool in memory, which improves the speed of data reading and writing.

Buffer pool: The largest chunk of memory, the cache used to hold various data includes index pages, data pages, undo pages, insert buffers, adaptive hash indexes, lock information for INNODB storage, data dictionary information, and so on. How you work always reads the database file by page (16k per page) to the buffer pool, and then retains the cached data in the buffer pool by using the least recently used (LRU) algorithm. If the database file needs to be modified, always first modify the pages in the cache pool (the Dirty pages dirty page after the modification), and then flush the dirty pages of the buffer pool to the file at a certain frequency.

Table Space

The tablespace can be seen as the highest level of the logical structure of the INNODB storage engine. Tablespace file: InnoDB The default tablespace file is ibdata1.

• Segment: The table space consists of individual segments, the common segments are data segments, index segments, rollback segments (undo log segments), and so on.

• Zone: consists of 64 contiguous pages, each with a size of 16kb, which is 1MB per area.

• Page: 16KB per page, and cannot be changed. Common page types are: Data pages, undo pages, System pages, transactional data pages, insert buffer bitmap pages, insert buffered free list pages, uncompressed binary large object pages, compressed binary large object pages.

  Redo log and undo log

  In order to satisfy the persistence of the transaction and prevent the buffer pool data from being lost, InnoDB introduced the redo log. To satisfy the atomicity of the transaction, InnoDB introduced the undo log.

Redo Log

Redo log is to save the executed SQL statement to a specified log file, and when MySQL performs the data recovery, the SQL operation of the redo log record is re-executed. The introduction of buffer pool causes the updated data not to be persisted to the disk in real time, and when the system crashes, although the data in the buffer pool is lost and the data is not persisted, the system can restore all data to the latest state based on the contents of the Redo log. Redo log exists as a standalone file on disk. By default, there are two files, named Ib_logfile0 and Ib_logfile1, respectively.

The parameter innodb_log_file_size specifies the size of the redo log; innodb_log_file_in_group Specifies the number of redo logs, which defaults to 2; INNODB_LOG_GROUP_HOME_DIR specifies the path where the redo log is located.

2. innodb_additional_mem_pool_size = 100M
4. innodb_buffer_pool_size = 128M
6. innodb_data_home_dir = /home/mysql/local/mysql/var
8. innodb_data_file_path = ibdata1:1G：autoextend
10. innodb_file_io_threads = 4
12. innodb_thread_concurrency = 16
14. innodb_flush_log_at_trx_commit = 1
18. innodb_log_buffer_size = 8M
20. innodb_log_file_size = 128M
22. innodb_log_file_in_group = 2
24. innodb_log_group_home_dir = /home/mysql/local/mysql/var

Undo Log

To satisfy the atomicity of a transaction, before any data is manipulated, the data is first backed up to the undo Log, and then the data is modified. If an error occurs or the user executes the ROLLBACK statement, the system can use the backup in the Undo log to restore the data to the state before the transaction began. Unlike redo log, a separate undo log file does not exist on the disk, and it is stored in a special segment (segment) inside the database, which is called the Undo segment (undo segment), and the Undo field is in the shared table space.

InnoDB implements three hidden fields for each row of records:

• 6-byte transaction ID (DB_TRX_ID)
• 7-byte rollback pointer (DB_ROLL_PTR)
• The Hidden ID

Log contents of redo Log

The Undo log and the redo log itself are separate. InnoDB's undo log is recorded in the data file (IBD), and InnoDB the contents of the undo log as data, so the operation of the undo log itself (such as inserting an undo record into the undo log, etc.) will log redo log. Undo log does not have to be persisted to disk immediately. Even if it is lost, it can be restored by redo log. So when you insert a record:

1. Insert an undo log record into the undo log.
2. Insert a redo log record that inserts an undo log record into the redo log.
3. Insert data.
4. Insert an "Insert" redo log record into the redo log.

IO Performance of redo log

In order to ensure Redo log can have better IO performance, InnoDB's Redo log design has the following features:

1. Try to keep the redo log stored in a contiguous space. Therefore, the space of the log file is fully allocated when the system is first started. Records the redo log in sequential append mode.
2. Bulk write logs. Instead of writing directly to the file, the log writes redo log buffer and writes the data in buffer to disk every second
3. Concurrent transaction sharing redo log storage space, their redo log in the order of execution of the statement, sequentially alternating records together, to reduce the space occupied by the log.
4. Only sequential append operations are performed on Redo log, and when a transaction needs to be rolled back, its Redo log record is not removed from the Redo log.

Redo & Undo Log function

• Data persistence

  A linked list, called Flush_list, is maintained in the buffer pool in order of the dirty page modification. The data is brushed to persistent storage according to the order of the pages in Flush_list. Arranged in the order in which the pages were first modified. Under normal circumstances, when does the dirty page flush to disk?

  1. When the redo space fills up, a portion of the dirty page is flushed to disk, and some redo log is released.
  2. When you need to assign a page to Buffer pool, but it is full, you must flush dirty pages to disk. In general, you can control this situation by starting the parameter innodb_max_dirty_pages_pct, and when the dirty page in buffer pool reaches this scale, flush the dirty page to disk.
  3. When the system is idle, flush is detected.
• Data recovery

  With the accumulation of time, Redo log will become very large. If you start the recovery from the first record every time, the recovery process is slow and cannot be tolerated. In order to reduce the time of recovery, the checkpoint mechanism is introduced. Assume that at some point in time, all dirty pages are flushed to disk. All redo logs before this point in time do not need to be re-made. The system logs the end of the redo log at this point in time as checkpoint. At the time of recovery, start with this checkpoint position. The log before the checkpoint point is no longer needed and can be deleted.

• Transaction Rollback

  F1~f6 is the name of a row, 1~6 is its corresponding data. The following three hidden fields correspond to the transaction number and the rollback pointer for that row, respectively. If this data is just insert, you can think of ID 1, the other two fields are empty.

  Examples of data row updates and the process of rolling back:

  Transaction 1: Changing the value of a row of data

  When transaction 1 changes the value of the row, the following actions are performed:

  1. Lock the row with an exclusive lock
  2. Copy the value of the row before the change to undo log, which is the line below
  3. Modify the value of the current row, fill in the transaction number, and point the rollback pointer to the pre-modified line in undo log
  4. Log Redo Log

  Transaction 2: Change the value of the row data again

  As with transaction 1, there are two rows of records in the undo log and are linked together by a rollback pointer. Therefore, if undo log is never deleted, the rollback pointer to the current record is traced back to the initial content when the row was created. The purge thread exists in InnoDB, which queries the undo log, which is older than the oldest active transaction, and removes them, ensuring that the undo log file does not grow indefinitely.

  Rollback process

  Finds the version before the transaction is modified from the undo log based on the current rollback pointer and resumes. If the transaction affects a very high number of rows, the rollback may become inefficient. When the number of transactions is between 1000~10000, the InnoDB efficiency is still very high.

  InnoDB also logs the operation when the transaction is rolled back to the redo log. The rollback operation is essentially a modification of the data, so the operation of the data during rollback is also recorded in the Redo log.

  The redo log of a rollback process looks like this:

  • Record 1: <trx1, Undo log Insert >
  • Record 2: <trx1, insert a...>
  • Record 3: <trx1, Undo log Insert >
  • Record 4: <trx1, update b...>
  • Record 5: <trx1, Undo log Insert >
  • Record 6: <trx1, delete c...>
  • Record 7: <trx1, insert c>
  • Record 8: <trx1, update B to old value>
  • Record 9: <trx1, delete a>

MySQL File understanding