MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:April 30 2002 7:04am
Subject:Re: InnoDB's inner workings + checkpoints
View as plain text  

----- Original Message -----
From: "Balteo" <balteo@stripped>
To: <heikki.tuuri@stripped>; <mysql@stripped>
Sent: Monday, April 29, 2002 8:58 AM
Subject: InnoDB's inner workings + checkpoints

> Hello Heikki,
> I have read up on checkpoint activity and innodb and there are still a
> few points I don't understand:
> -what is the chronological sequence of events for a write into the
> innodb tablespace?
> -Is it:
> 1. An insert sql statement is issued from a mysql client.
> 2. The record is stored in the log buffer. (no disk access here)

Not at this stage.

> 3. The record is stored in the innodb log and in the buffer pool
> simultaneously. (1st disk accesss) (checkpoint)

Writing to the buffer pool and to the log buffer is essentially simultaneous
if UNIQUE key checks to secondary indexes do not require a read from disk.
But nothing is written to the disk at this stage.

> 4. The record finally makes it to the innodb tablespace and is saved to
> disk. (2nd disk access)

No. The log segment in the log buffer is written to the disk at the
transaction commit if you have not set innodb_flush_log_at_trx_commit=0, in
which case the log buffer is flushed once per second.

The data from the buffer pool is only flushed to the disk when the oldest
log record for which the modification is not yet on disk becomes too old,
that is, the log record is in danger to be overwritten in a log write.
InnoDB writes to the iblog files in a circular order.

A checkpoint in InnoDB is fuzzy: only the oldest modified pages in the
buffer pool are written to the disk, not all modified pages. After that
InnoDB stores to the first log file the log sequence number of the oldest
modification which is in the buffer pool, but not yet on disk.

The book by Jim Gray and Andreas Reuter, Transaction processing, from about
year 1992, is the best reference. Much of InnoDB has been written according
to the instructions in that book.

> Can you please correct the above if it is wrong.
> Thanks in advance,
> Balteo.

Best regards,

Heikki Tuuri
Innobase Oy
InnoDB - transactions, hot backup, and foreign key support for MySQL
See, download MySQL-Max from

InnoDB's inner workings + checkpointsBalteo29 Apr
  • Re: InnoDB's inner workings + checkpointsHeikki Tuuri30 Apr