Bug #19673 Bursts in checkpointing with a main memory database
Submitted: 10 May 2006 13:57
Reporter: Heikki Tuuri
Status: Open
Category:Server: InnoDB Severity:S4 (Feature request)
Version:All OS:Any
Assigned to: Heikki Tuuri Target Version:
Triage: D5 (Feature request)

[10 May 2006 13:57] Heikki Tuuri 
Description:
It can happen if all buffer pool pages suddenly become too old so that log writing
threatens to write over their latest flushed lsn. If you do random updates to pages, you
will end up with this situation.

I did not realize this danger before. I thought the modification lsn of different pages
would be more smoothly distributed.

How to repeat:
See above.

Suggested fix:
We should modify the srv_master_thread() loop in srv0srv.c, so that it would dedicate
more and more resources to flushing when the checkpoint age grows close to the total
capacity of the log group.
[11 May 2006 15:31] Heikki Tuuri 
Peter Z's May 10th, 2006 entry discusses the fix:

http://www.mysqlperformanceblog.com/
[16 Apr 2007 13:44] Heikki Tuuri 
Reclassifying as a feature request.
[16 Jan 20:09] Mike Dierken 
I believe we may be seeing this issue, but I do not know of a way to monitor when a
checkpoint occurs to correlate our 'freeze-up' issue with checkpoints.

The table that I suspect may be involved has 100M rows, with a primary key on two columns
- a varchar(128) column and an int column (and only a few other int columns for additional
data). This table uses utf-8 character set. This table is replicated from a master to
multiple slaves and it is on the slaves that we are seeing InnoDB 'freeze' for 5min to
10min.

The varchar(128) attribute of the primary key is fairly randomized with nearly all the
values of the form "M06V-FNS1QDVS-10.250.18.167" (different random text of course).
There are about 1M-1.5M inserts into this table per day (almost no updates, never any
deletes).