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

Summary

1. The redo transaction log is ib_logfile, the two files at the beginning of ib_logfile, they are redo logfile in log group, and they are exactly the same size and equal to the value defined by the variable innodb_log_file_size.

2. The redo transaction log is used for crash recovery,crash recovery. It is an automatic behavior when the database is restarted. No extra work is required for DBA.

3. MySQL writes the redo log file in a circular manner. If the last ib_logfile is full and all the transactions recorded in the first ib_logfile have been persisted to disk, it will be emptied and reused.

4. The concept of redo transaction log is similar to oracle's online redo log, which contains commit and uncommit data.

5. There are several ways to write the redo transaction log, every second or every transaction, so it can contain data without committing uncommit.

6. Show engine innodb status can see the information of redo log

Log sequence number: indicates the latest LSN of the current redo log.

Log flushed up to: indicates the LSN that is currently flushed to redo log on disk.

Last checkpoint at: the update of the redo log record has been flushed to the checkpoint LSN on disk, and all the updates recorded on the previous redo log of that LSN have been flushed to disk and can be overwritten and reused.

7. How to view the content in ib_logfile

[root@mydb ~] # strings / var/lib/mysql/ib_logfile0

Related parameters

Innodb_log_file_size: per redo log file size

Innodb_log_files_in_group: number of members of the redo log log group

Innodb_log_group_home_dir: redo log storage directory

Innodb_page_size: page size of InnoDB tablespace. Default is 16K.

Innodb_flush_log_at_timeout: log refresh frequency (in second)

Write and flush the logs every N seconds. Innodb_flush_log_at_timeout allows the timeout period between flushes to be increased in order to reduce flushing and avoid impacting performance of binary log group commit. The default setting for innodb_flush_log_at_timeout is once per second.

The log is written and refreshed every N seconds. Innodb_flush_log_at_timeout allows you to increase the timeout between refreshes to reduce refreshes and avoid affecting the performance of binary log group submissions. The default setting for innodb_flush_log_at_timeout is once per second.

Innodb_flush_log_at_trx_commit: controls whether the commit action flushes log buffer to disk

Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches.

The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit

With a setting of 0, logs are written and flushed to disk once per second

With a setting of 2, logs are written after each transaction commit and flushed to disk once per second

Controls the balance between strict ACID compliance for commit operations and higher performance that may be achieved when rescheduling and batch completion of commit-related I / O operations.

The default setting is 1. Each time a transaction commits, the log is written and flushed to disk. In this way, no data is lost even if the system crashes, but because every commit is written to disk, IO has poor performance.

When set to 0, the log is written and flushed to disk once per second. In other words, when set to 0, the data written to disk is refreshed every second, and when the system crashes, 1 second of data is lost.

When set to 2, the log is written after each transaction is committed, and then the disk is flushed again every second. Each commit is only written to os buffer, and then fsync () is called every second to write the logs in os buffer to log file on disk.

The log refresh rate is controlled by innodb_flush_log_at_timeout and allows you to set the log refresh rate to N seconds (where N is 1... 2700 and the default is 1). However, any mysqld process crash can eliminate transactions of up to N seconds.

Many people in innodb_flush_log_at_timeout mistakenly think that they are controlling the 1-second frequency when the innodb_flush_log_at_trx_ threshold is 0 and 2, but in fact this is not the case.

There are four ways to flush the logs of the innodb log buffer to disk

1. Refresh the Innodb_log_buffer to the redo log file once per second. Even if a transaction has not been committed, the Innodb storage engine flushes the redo log cache to the redo log file every second.

2. The redo log is flushed to the redo log file when each transaction is committed.

3. When the free space of the redo log cache is less than half, the redo log cache is flushed to the redo log file

4. When there is checkpoint, checkpoint to some extent represents the LSN location where the log is located when it is flushed to disk.

Https://dev.mysql.com/doc/refman/5.7/en/innodb-redo-log.html

The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions.

Redo logs are disk-based data structures used to correct data written by incomplete transactions during crash recovery.

By default, the redo log is physically represented on disk as a set of files, named ib_logfile0 and ib_logfile1. MySQL writes to the redo log files in a circular fashion.

By default, the redo log is physically represented on disk as a set of files named ib_logfile0 and ib_logfile1. MySQL writes to the redo log file in a circular manner.

Note: innodb_log_files_in_group determines the number of ib_logfile files, naming starts from ib_logfile0. If the last ib_logfile is full and all transactions recorded in the first ib_logfile have persisted to disk, it will be emptied and reused.

Https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_redo_log

Redo

The data, in units of records, recorded in the redo log when DML statements make changes to InnoDB tables. It is used during crash recovery to correct data written by incomplete transactions. The ever-increasing LSN value represents the cumulative amount of redo data that has passed through the redo log.

When the DML statement makes changes to the InnoDB table, the data in record units is recorded in the redo log. It is used to correct data written by outstanding transactions during crash recovery. The increasing LSN value represents the cumulative amount of redo data through the redo log.

Redo log

A disk-based data structure used during crash recovery, to correct data written by incomplete transactions. During normal operation, it encodes requests to change InnoDB table data, which result from SQL statements or low-level API calls through NoSQL interfaces. Modifications that did not finish updating the data files before an unexpected shutdown are replayed automatically.

The redo log is physically represented as a set of files, typically named ib_logfile0 and ib_logfile1. The data in the redo log is encoded in terms of records affected; this data is collectively referred to as redo. The passage of data through the redo logs is represented by the ever-increasing LSN value. The original 4GB limit on maximum size for the redo log is raised to 512GB in MySQL 5.6.3.

A disk-based data structure used during crash recovery to correct data written by outstanding transactions. During normal operation, it encodes requests to change InnoDB table data from SQL statements or low-level API calls through the NoSQL interface. Changes to the updated data file that are not completed before the unexpected shutdown are automatically replayed.

The redo log is physically represented as a set of files, usually named ib_logfile0 and ib_logfile1. The data in the redo log is encoded according to the affected records; this data is collectively referred to as redo. The transfer of data through the redo log is represented by increasing LSN values. In MySQL 5.6.3, the original 4GB limit for the maximum size of redo logs was raised to 512GB.

Crash

MySQL uses the term "crash" to refer generally to any unexpected shutdown operation where the server cannot do its normal cleanup. For example, a crash could happen due to a hardware fault on the database server machine or storage device; a power failure; a potential data mismatch that causes the MySQL server to halt; a fast shutdown initiated by the DBA; or many other reasons. The robust, automatic crash recovery for InnoDB tables ensures that data is made consistent when the server is restarted, without any extra work for the DBA.

MySQL uses the term "crash" to refer to any unexpected shutdown that the server cannot clean up properly. For example, a crash may occur due to a hardware failure on a database server computer or storage device, a power outage, a potential data mismatch that causes the MySQL server to stop, a quick shutdown initiated by DBA, or many other reasons. The powerful automatic crash recovery feature of the InnoDB table ensures that the data is consistent when the server is restarted without any additional work for DBA.

Crash recovery

The cleanup activities that occur when MySQL is started again after a crash. For InnoDB tables, changes from incomplete transactions are replayed using data from the redo log. Changes that were committed before the crash, but not yet written into the data files, are reconstructed from the doublewrite buffer. When the database is shutdown normally, this type of activity is performed during shutdown by the purge operation.

During normal operation, committed data can be stored in the change buffer for a period of time before being written to the data files. There is always a tradeoff between keeping the data files up-to-date, which introduces performance overhead during normal operation, and buffering the data, which can make shutdown and crash recovery take longer.

Cleanup activity that occurs when MySQL is started again after a crash. For the InnoDB table, use the data in the redo log to replay the changes to the outstanding transaction. Changes committed before the crash but not yet written to the data file are rebuilt from the doublewrite buffer. When the database shuts down normally, such activities are performed during the cleanup operation.

During normal operation, committed data can be stored in the change buffer for a period of time before being written to the data file. There is always a tradeoff between keeping data files up to date, which introduces performance overhead and buffers data during normal operations, which takes longer to shut down and crash recovery.

Note: CrashSafe means that after the MySQL server is down and restarted, the data of all committed transactions still exist, and the data of all uncommitted transactions are rolled back automatically. Innodb can guarantee these two points through Redo Log and Undo Log.

Log buffer

The memory area that holds data to be written to the log files that make up the redo log. It is controlled by the innodb_log_buffer_size configuration option.

Saves the area of memory to write to the log file that makes up the redo log. It is controlled by the innodb_log_buffer_size configuration option.

Log file

One of the ib_logfileN files that make up the redo log. Data is written to these files from the log buffer memory area.

One of the ib_logfileN files that make up the redo log. Data is written to these files from the log buffer store.

Log group

The set of files that make up the redo log, typically named ib_logfile0 and ib_logfile1. For that reason, sometimes referred to collectively as ib_logfile.

The filesets that make up the redo log, usually named ib_logfile0 and ib_logfile1. Therefore, it is sometimes collectively referred to as ib_logfile. )

LSN

Acronym for "log sequence number". This arbitrary, ever-increasing value represents a point in time corresponding to operations recorded in the redo log. (This point in time is regardless of transaction boundaries; it can fall in the middle of one or more transactions.) It is used internally by InnoDB during crash recovery and for managing the buffer pool.

Prior to MySQL 5.6.3, the LSN was a 4-byte unsigned integer. The LSN became an 8-byte unsigned integer in MySQL 5.6.3 when the redo log file size limit increased from 4GB to 512GB, as additional bytes were required to store extra size information. Applications built on MySQL 5.6.3 or later that use LSN values should use 64-bit rather than 32-bit variables to store and compare LSN values.

In the MySQL Enterprise Backup product, you can specify an LSN to represent the point in time from which to take an incremental backup. The relevant LSN is displayed by the output of the mysqlbackup command. Once you have the LSN corresponding to the time of a full backup, you can specify that value to take a subsequent incremental backup, whose output contains another LSN for the next incremental backup.

Abbreviation for "log serial number". This arbitrary, ever-increasing value represents the point in time corresponding to the action recorded in the redo log. (this point in time is independent of the transaction boundary; it can fall in the middle of one or more transactions. It is used internally by InnoDB during crash recovery to manage buffer pools

Prior to MySQL 5.6.3, LSN was a 4-byte unsigned integer. When the redo log file size limit is increased from 4GB to 512GB, LSN becomes an 8-byte unsigned integer in MySQL 5.6.3 because additional bytes are needed to store additional size information. Applications built on MySQL 5.6.3 or later that use LSN values should use 64-bit variables instead of 32-bit variables to store and compare LSN values.

In MySQL Enterprise Backup products, you can specify LSN to represent the point in time for incremental backups. The relevant LSN is displayed by the output of the mysqlbackup command. Once you have a LSN corresponding to the full backup time, you can specify this value for subsequent incremental backups, whose output contains another LSN for the next incremental backup.

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.