MySQL redo log structure vs. SQL Server log structure

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.