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

This article mainly explains "how to set up mysql log files undo log and redo log". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn how to set up the mysql log files undo log and redo log.

What is 1 undo1.1 undo?

Undo log is used to store the value before data modification. If you modify the row data of id=2 in tba table and change Name='B' to Name=' B2', then undo log will be used to store Name='B' records. If there is an exception in this modification, you can use undo log to implement rollback operation to ensure transaction consistency.

The operation of changing data mainly comes from INSERT UPDATE DELETE, while there are two types of UNDO LOG, one is INSERT_UNDO (INSERT operation), the unique key value of record insertion, the other is UPDATE_UNDO (including UPDATE and DELETE operation), the only key value of record modification and old column record.

IdName1A2B3C4

D

1.2 undo parameters

The MySQL parameter settings related to undo are as follows:

Mysql > show global variables like'% undo%' +-- +-+ | Variable_name | Value | +-+-+ | innodb_max_undo_log_size | 1073741824 | | innodb_undo_directory |. / | | Innodb_undo_log_truncate | OFF | | innodb_undo_logs | 128 | | innodb_undo_tablespaces | 3 | +-+-+ mysql > show global variables like'% truncate%' +-- +-+ | Variable_name | Value | +-- +-+ | innodb_purge_rseg_truncate_ Frequency | 128 | | innodb_undo_log_truncate | OFF | +-- +-+

Innodb_max_undo_log_size

Controls the size of the maximum undo tablespace file. When innodb_undo_log_truncate is started, truncate is tried when undo tablespace exceeds the innodb_max_undo_log_size threshold. The default size of this value is 1G, and the default size after truncate is 10m.

Innodb_undo_tablespaces

Set the number of independent undo tablespaces, ranging from 0 to 128. the default value of 0 means that independent undo tablespaces are not enabled and undo logs are stored in ibdata files. This parameter can only be specified when initializing the MySQL instance. If the instance has been created, this parameter cannot be changed. If the number of innodb_undo_tablespaces specified in the database configuration document .cnf is greater than the number specified when the instance is created, the startup will fail, indicating that the parameter is set incorrectly.

If this parameter is set to n (n > 0), then n undo files (undo001,undo002. Exe) will be created in the undo directory. Undo n), the default size of each file is 10m.

When do I need to set this parameter?

When the DB writing pressure is high, you can set up an independent UNDO tablespace to separate the UNDO LOG from the ibdata file and specify the innodb_undo_directory directory to store it, which can be set to a high-speed disk to speed up the read and write performance of UNDO LOG.

Innodb_undo_log_truncate

InnoDB's purge thread reclaims space and reinitializes undo file according to the innodb_undo_log_truncate setting on or off, the parameter value of innodb_max_undo_log_size, and the frequency of truncate.

This parameter takes effect only if independent tablespaces are set and the number of independent tablespaces is greater than or equal to 2.

During the truncate undo log file process, the purge thread needs to check whether there are any active transactions on the file. If not, the undo log file needs to be marked as unallocable. At this time, the undo log will be recorded on other files, so at least 2 independent tablespace files are needed to perform the truncate operation. After marking the non-distributable, a separate file undo__trunc.log is created, and the record is now truncate a undo log file. Then initialize the undo log file to 10m. After the operation, delete the undo__trunc.log file that represents the truncate action. This file ensures that even if a failure occurs during the truncate process and the database service is restarted, the service will continue to complete the truncate operation when the file is found. After the file is deleted, the undo log file can be assigned.

Innodb_purge_rseg_truncate_frequency

Used to control the frequency of the purge rollback segment, which defaults to 128. If it is set to n, it means that when the coordinating thread of the Innodb Purge operation purge transactions 128times, History purge will be triggered to check whether the current undo log tablespace state will trigger truncate.

1.3 undo space management

If you need to set up independent tablespaces, you need to specify the number of independent tablespaces when initializing the database instance.

The UNDO is composed of multiple rollback segments, namely Rollback segment, which is stored in the ibdata system table space, from resg slot0-resg slot127, each resg slot, that is, each rollback segment, is composed of 1024 undo segment.

The rollback segment (rollback segment) is assigned as follows:

Slot 0, reserved for system tablespace

Slot 1-32, reserved for temporary tablespaces, which are rebuilt every time the database is restarted

Slot33-127. if there is an independent table space, it is reserved for the UNDO independent table space; if not, it is reserved for the system table space

Except for 32 temporary table transactions in the rollback segment, the remaining 128-32mm 96 rollback segments can perform 969,1024 concurrent transaction operations, each taking up one undo segment slot. Note that if there are temporary table transactions in the transaction, another undo segment slot will be occupied in the undo segment slot in the temporary table space, that is, 2 undo segment slot. If there is: Cannot find a free slot for an undo log in the error log. It means that there are too many concurrent transactions, so we need to consider whether to divert the business.

The rollback segment (rollback segment) is allocated by polling and scheduling. If the independent tablespace is set, the undo segment in the system tablespace rollback segment will not be used, but the independent tablespace will be used. At the same time, if the retrospective segment is in Truncate operation, it will not be allocated.

What is 2 redo2.1 redo?

When the database makes changes to the data, the data page needs to be read from disk to buffer pool and then modified in buffer pool. Then the data page in buffer pool is inconsistent with the data page on disk. The data page in buffer pool is called dirty page dirty data. If abnormal DB service restart occurs at this time, the data is not in memory yet and is not synchronized to the disk file (note Synchronizing to the disk file is a random IO), that is, data loss will occur. If you can record the corresponding modification record to this file after the data page change in buffer pool is completed (note that the log is sequential IO), then when crash occurs in the DB service and the DB is restored, it can also be reapplied to the disk file according to the recorded contents of this file. The data are consistent.

This file is called redo log, which is used to record the modified data and record it sequentially. It can bring these benefits:

When the dirty page in buffer pool has not been flushed to disk, crash occurs. After starting the service, you can find the records that need to be refreshed to disk files through redo log.

The data in buffer pool is directly flush to disk file, which is a random IO and inefficient, while recording the data in buffer pool to redo log is a sequential IO, which can improve the speed of transaction commit.

Suppose you modify the row data of id=2 in the tba table and change Name='B' to Name='B2', then the redo log will be used to store the records of Name='B2'. If this modification is abnormal when flush is transferred to a disk file, you can use redo log to implement the redo operation to ensure the persistence of the transaction.

IdName1A2B3C4

D

Note the difference between redo log and binary log. Redo log is generated by the storage engine layer, while binary log is generated by the database layer. Suppose a large transaction inserts 100000 rows of records to tba. In the process, it keeps recording to redo log sequentially, but binary log will not record it. Until the transaction is committed, it will be written to the binary log file at once. There are three record formats of binary log: row,statement and mixed, which are different from each other.

2.2 redo parameters

Innodb_log_files_in_group

The number of redo log files, such as ib_logfile0,iblogfile1... Iblogfilen . The default is 2 and the maximum is 100.

Innodb_log_file_size

The file size is set, with a default value of 48m and a maximum value of 512G. Note that the maximum value refers to the sum of the entire redo log series files, that is, (innodb_log_files_in_group * innodb_log_file_size) cannot be greater than the maximum value of 512G.

Innodb_log_group_home_dir

File storage path

Innodb_log_buffer_size

Redo Log cache area, default 8m, can be set 1-8m. Delay writing the transaction log to disk, put redo log into the buffer, and then flush the log from buffer to disk according to the setting of the innodb_flush_log_at_trx_commit parameter.

Innodb_flush_log_at_trx_commit

Innodb_flush_log_at_trx_commit=1, every time commit writes redo log from redo log buffer to system, and fsync flushes to disk file.

Innodb_flush_log_at_trx_commit=2, each time a transaction commits, MySQL writes logs from redo log buffer to system, but only to file system buffer, and fsync to disk files from within the system. If the database instance is crash, the redo log will not be lost, but if the server is crash, this part of the data will be lost because it is too late for file system buffer to fsync to the disk file.

Innodb_flush_log_at_trx_commit=0, the transaction occurs, the log is always activated in redo log buffer, like other settings, but when the transaction commits, it does not generate redo write operations, but MySQL internal operations once a second, from redo log buffer, to write data to the system. If crash occurs, the transaction modification operation within 1 second is lost.

Note: due to the problem of process scheduling policy, this "flush operation per second" does not guarantee 100% "per second".

2.3 redo space management

The Redo log file is named after the ib_ log file, and Redo log writes the file sequentially, and when it is full, it goes back to the first file to overwrite. However, when you do redo checkpoint, the header checkpoint tag of the first log file is also updated, so it is not strictly written sequentially.

In fact, redo log consists of two parts: redo log buffer and redo log file. Record the data modification in buffer pool to redo log buffer, and then brush down the redo log to redo log file if the following occurs:

Insufficient Redo log buffer space

Transaction commit (dependent on innodb_flush_log_at_trx_commit parameter setting)

Background thread

Do checkpoint

Instance shutdown

Binlog handoff

3 how undo and redo record transactions 3.1 simplified process of Undo + Redo transactions

Suppose you have two data An and B, with values of 1 and B respectively, and start a transaction, and the operation content of the transaction is: change 1 to 3, 2 to 4, then the actual record is as follows (simplified):

a. The business begins.

b. Record Agg1 to undo log.

c. Modify Atom 3.

d. Record Aban 3 to redo log.

e. Record bread2 to undo log.

f. Modify Bamboo 4.

g. Record bread4 to redo log.

h. Write redo log to disk.

i. Transaction commit

3.2 IO impact

The main consideration of the design of Undo + Redo is to improve IO performance and increase database throughput. As you can see, B D E G H is a new operation, but B D E G is buffered to buffer area, and only G adds IO operation. In order to ensure that Redo Log can have better IO performance, the design of InnoDB Redo Log has the following features:

a. Try to keep the Redo Log stored in a continuous space. Therefore, the log file space is fully allocated when the system starts for the first time. Redo Log is recorded as sequential appends, and performance is improved by sequential IO.

b. Write to the log in bulk. The log is not written directly to the file, but first to redo log buffer. When you need to flush logs to disk (such as transaction commit), write many logs to disk together.

c. Concurrent transactions share the storage space of Redo Log, and their Redo Log are recorded alternately according to the execution order of statements, in order to reduce the space occupied by logs. For example, a record in Redo Log might look like this:

Record 1:

Record 2:

Record 3:

Record 4:

Record 5:

d. Because of C, when a transaction writes Redo Log to disk, the logs of other uncommitted transactions are also written to disk.

Only sequential appends are performed on E. Redo Log, and when a transaction needs to be rolled back, its Redo Log record is not deleted from the Redo Log.

3.3 recovery

As mentioned earlier, uncommitted and rolled-back transactions also record Redo Log, so these transactions require special processing during recovery. There are two different recovery strategies:

a. When resuming, only the committed transactions are redone.

b. When recovering, redo all transactions, including uncommitted transactions and rolled-back transactions. And then roll back those through Undo Log

Uncommitted transactions.

The MySQL database InnoDB storage engine uses the B strategy, and the recovery mechanism in the InnoDB storage engine has several characteristics:

a. Transactionality is not a concern when redoing Redo Log. When recovering, there is no BEGIN, and there is no COMMIT,ROLLBACK behavior. I don't care which transaction each log belongs to. Although transaction-related content such as transaction ID is recorded in Redo Log, it is treated as part of the data to be manipulated.

b. Using strategy B requires that the Undo Log be persisted and the corresponding Undo Log must be written to disk before writing the Redo Log. This association between Undo and Redo Log complicates persistence. To reduce complexity, InnoDB treats Undo Log as data, so the operation of recording Undo Log is also logged to redo log. This allows undo log to be cached like data without having to write to disk before redo log.

The Redo Log that contains the Undo Log operation looks like this:

Record 1:

Record 2:

Record 3:

Record 4:

Record 5:

Record 6:

c. At this point, there is still one question that has not been clarified. Since Redo is not transactional, won't the rolled-back transaction be reexecuted?

Yes, indeed. At the same time, Innodb also records the operation when the transaction is rolled back to redo log. The rollback operation is essentially a modification of the data, so the operation on the data during the rollback is also recorded in the Redo Log.

The Redo Log of a rolled-back transaction looks like this:

Record 1:

Record 2:

Record 3:

Record 4:

Record 5:

Record 6:

Record 7:

Record 8:

Record 9:

The recovery operation of a rolled-back transaction is to redo and then undo, so it does not break the consistency of the data.

At this point, I believe you have a deeper understanding of "how to set up mysql log files undo log and redo log". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.