MySQL redo log structure vs. SQL Server log structure

MySQL redo log structure vs. SQL Server log structure

InnoDB storage Engine MySQL Technology insider

F

1

F

2

F

3

F

4

F

5

F

6

F

7

F

Sql server

Http://www.cnblogs.com/CareySon/p/3308926.html

Transaction-to-database changes are broken down into multiple atomic-level entries that are recorded in persistent storage, which are known as Log records, which we can view through Fn_dblog. As shown in 2.

Figure 2. Fn_dblog

Each log record is given a unique sequential number, which is 10 bytes and consists of three parts, namely:

• VLF Sequence Number (4 bytes)
• Log Block Sequence Number (4 bytes)
• Sequence number within the Log block (2 bytes)

Therefore, because VLF is constantly increasing (the same VLF is reused to cause number changes), the LSN sequence number is also increasing. Therefore, it is not difficult to see through the above LSN structure, if the smaller granularity than VLF is not directly corresponding to the log RECORD, but log Block. Log block is the smallest unit of logs written to persistent storage, and the log block varies in size from 512 bytes to 60K, depending on the size of the transaction, which is known as the in-flight log block, which is not yet written to the persisted storage. The following two factors determine the size of a log block:

• Transaction Commit or Rollback
• Log block full 60K forces flush to persistent storage to ensure that the Wal

So when a transaction is large (such as a large update), every 60K becomes a log block written to the persisted storage. For many small transactions, commit or rollback is called a block write persistence store, so the size of the LOG block will vary depending on the size of the transaction. It is doubtful that the size of the allocation unit on disk is 2 N, so the closest to log block should be 64K, and why SQL Server does not set the log block to 64K. This allows you to optimize IO more.

The relationship between VLF and log block and log record is shown in 3.

Figure 3: Relationship between the three

After learning the logs from a relatively high level, we carefully understand the key information that should be stored in the log, and each log record contains the following key information:

• Lsn
• The context of the Log record
• The transaction ID that the Log record belongs to (all user transactions will have a transaction ID)
• The bytes of the Log record
• LSN of the previous log record in the same transaction (for undo)
• The log space reserved for undo

Of course, these are just a small part of the log. By logging the log record, you can accurately record changes made to the database.

Log for Undo

Before we understand the role of the log in order to undo, we can first understand why a transaction exists to be rolled back:

• Because transactions can fail, or deadlock, and so on, if you want the transaction to not violate atomicity and cause the database to be inconsistent, you need to roll back the partially executed transaction by rolling back.
• The data is rolled back, depending on the business requirements, if some of the associated services fail.

MySQL redo log structure vs. SQL Server log structure