MySQL Bugs: #19673: Bursts in checkpointing with a main memory database

Bug #19673	Bursts in checkpointing with a main memory database
Submitted:	10 May 2006 11:57	Modified:	13 May 2010 16:04
Reporter:	Heikki Tuuri	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	All	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

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.

Peter Z's May 10th, 2006 entry discusses the fix:

http://www.mysqlperformanceblog.com/

Reclassifying as a feature request.

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).