这篇帖子个人感觉写得非常棒，不转不行
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!
Page 1
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.
Page2
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.
Page4
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
