How Logs Work On MySQL With InnoDB Tables

这篇帖子个人感觉写得非常棒，不转不行

In this article I will describe how logs work with MySQL and InnoDB. Very little of this material is in the manual. I gleaned it from the source code while researching for our latest book (SQL Performance Tuning by Peter Gulutzan and Trudy Pelzer).
If you've ever worked with MySQL and InnoDB tables then this article should give you some "insider" knowledge that you wont find anywhere else, so sit back and get ready to read!


When you change data with UPDATE, INSERT or DELETE queries you're changing the data in two places: the log buffer and the data buffers. Buffers are fixed-length, typically a multiple of 512 bytes. And they are in memory – InnoDB won't write them to disk... yet.


LOG BUFFER DATA BUFFER
================= ===============
= Log Record #1 = = Page Header =
= Log Record #2 = = Data Row =
= Log Record #3 = = Data Row =
= Log Record #4 = = Data Row =
================= ===============


For example, after "INSERT INTO Jobs VALUES (1,2,3)" the log buffer will have one new log record -- call it Log Record #5 -- containing a row identifier and the new contents. Meanwhile, the data buffer will have one new row, but it will also have a mark in the page header saying "the latest log record for this page is Log Record #5". In this example "#5" is the Log Sequence Number (LSN), and it's vital for scheduling later operations.


Some details about the data-change:


(a) An INSERT log record contains only new data, which is enough so that the procedure can be repeated on the same page if necessary. This is called a "redo" entry.


(b) The LSN isn't a field in the log record, instead it's an absolute address for a file and byte offset.[1]


After InnoDB has changed the log buffer and the data buffers, it's all over but the disk writing. But that's where things get complex. There are several threads monitoring buffer activity, and three situations -- overflow, checkpoint, and commit -- that result in disk writes.


What Happens With Overflows?
Overflow is rare because InnoDB takes pro-active measures to prevent buffers from filling up (see "what happens with checkpoints" below). Still, let's discuss the two possible cases.


One: if the log buffer gets full, InnoDB writes the buffer at the "end" of the log. I've put the word "end" inside quotes because a log file, or more precisely a group of log files, looks like a snake swallowing its tail. If there's only room for four log records and we're writing #5, then it will have to go at the start of the file.


LOG FILE(S) BEFORE WRITING LOG RECORD #5
=================
= Log Record #1 =
= Log Record #2 =
= Log Record #3 =
= Log Record #4 =
=================


LOG FILE(S) AFTER WRITING LOG RECORD #5
=================
= Log Record #5 =
= Log Record #2 =
= Log Record #3 =
= Log Record #4 =
=================


There's no such thing as a log that grows forever. Even though InnoDB uses some compression tricks, the log files would get too big to fit on any disk drive. So InnoDB writes "in a circle" and that means it must overwrite old log records. This circular logging policy has implications which we'll come back to later.


Two: if the data buffers get full, InnoDB writes the least recently used buffer to the database -- but not too quickly! This is where the page header's LSN becomes useful. First, InnoDB checks whether it's greater than the LSN of the last log record in the log file. If it's greater, then InnoDB must write out log records first, until the log catches up with the data, and only then can it write the data. In other words data page writing cannot occur until writing of the corresponding log record has occurred. That is the "Write-Ahead Logging" principle which is common to all important DBMSs except InterBase.


What Happens With Checkpoints?
I said that InnoDB takes some pro-active measures against overflows, and the most important of these measures is checkpointing. There is a separate thread, or a combination of threads that are separate from the thread that changes the buffers. At fixed intervals the checkpointer will wake, look for buffer changes, and ensure that writes happen.


By my count, most DBMS's would write everything at this time so there are no buffers left which are changed but unwritten. To use the usual jargon, the DBMS would flush all "dirty" buffers with a "Sharp Checkpoint". But InnoDB only ensures that (a) log and data buffers aren't getting fuller than a fixed threshold point, (b) log writing stays ahead of data page writing, (c) there is no data buffer whose page-header LSN corresponds to a log record that's about to be overwritten. In the jargon, this means InnoDB is a "Fuzzy Checkpoint" aficionado.


At checkpoint time it's possible to write another log record which says, in effect: at this moment a checkpoint occurred, so it's certain that the database is up to date except for a few dirty pages, and here is a list of the dirty pages. This information can be useful during a recovery, so I'll mention it again later.



At COMMIT time, InnoDB will NOT write all dirty data pages to disk. I emphasize that because it's easy to think that committing changes means writing everything on a persistent medium. Well, the InnoDB folks are smarter than that. They realize that only the log records need writing. The writing of dirty data pages can happen at overflow or checkpoint time, because their contents are redundant. If the log survives a crash, remaking of data pages is possible using the information in the log records.[2]


So InnoDB should only write the log. Or to be exact, InnoDB should write log records, until it has written all log records which apply to the transaction that's committing. Since all log writing is serial, that means InnoDB must write log records for other transactions too, but that's okay.[3]


Here I must get critical, because that's not what InnoDB does, necessarily. If MySQL's my.cnf's innodb_flush_log_at_trx_commit switch is zero, then InnoDB will avoid log writing at commit time. This means that a successful COMMIT won't "guarantee" that all data changes have become persistent, which is what the ANSI/ISO standard demands. The persistence guarantee applies only for checkpoints.


Anyway, you can set innodb_flush_log_at_trx_commit to one. In that case all's well, InnoDB will write the log. Also InnoDB will flush.


I'd better explain what flushing is, eh? Usually it's enough to merely write, but all modern operating systems will cache writes for efficiency reasons. To get the "guarantee" InnoDB must insist to the operating system "I really mean write, I want that disk write head on a platter, don't return till the physical operation is complete." This means that on a Windows system InnoDB calls the Windows-API function FlushFilBuffers, a call meaning "flush the cache." Here InnoDB parts company with Microsoft: SQL Server 2000 would use a "write through" option during the write, rather than flushing after the write.


Recovery
We come now to the point that makes all the logging trouble worthwhile: if a crash happens, you can recover your data.


For a crash that didn't melt the disk drive, recovery is automatic. InnoDB reads the last checkpoint log record, sees if the "dirty pages" were written before the crash, and (if they weren't) reads the log records which affected the page and applies them. This is called "rolling forward" and it's easy for two reasons: (1) because the LSN is there so InnoDB just has to compare numbers to get into synch, (2) because I left out a few details.


Fine. Now, what about a crash that did melt the disk drive? Then the recovery scenario depends on your preparation.


Scenario one: the log is gone. Well, you should have prepared by having a log copy on a separate drive. InnoDB has no explicit option but there are operating-system-specific ways.


Scenario two: the database is gone and the log is overwritten. Well, you should have anticipated that with circular logging, log record #5 will overwrite log record #1. Remember? Therefore if you didn't take a backup after the writing of log record #1, you've lost data.


Scenario three: the database is gone and the log is okay. In that case, congratulations. You just have to restore your last backup copy, and roll the whole log forward. There would be complications if you had to back up the log itself several times since the last full backup ("archive logging"), but I've assumed that option is currently inoperative. By the way, I am not discussing the role of MySQL's binlog. It's essential to the process but isn't part of InnoDB, so out of scope.


With an understanding of InnoDB logging, you know some things need watching. In no particular order:
use large log files so overwriting won't happen since backups
• keep log files and data files on separate disk drives

• make sure innodb_flush_log_at_trx_commit is set right

FROM: http://www.devarticles.com/c/a/MySQL/How-Logs-Work-On-MySQL-With-InnoDB-Tables/