MySQL Bugs: #22349: Status of Innodb

Bug #22349	Status of Innodb_checkpoint wanted
Submitted:	14 Sep 2006 14:18	Modified:	3 Jun 2010 7:40
Reporter:	Oli Sennhauser	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	n.a.	OS:	Any (n.a.)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
In documentation (http://dev.mysql.com/doc/refman/5.0/en/innodb-checkpoints.html) is written, that checkpoints can cause IO problems.

Unfortunatly no SHOW STATUS LIKE 'Innodb_checkpoint' exists to show how many checkpoints happened.

SHOW ENGINE INNODB STATUS shows only the inconvenient:
"Last checkpoint at  0 4049209668"

How to repeat:
n.a.

Suggested fix:
Implement status Innodb_checkpoint

or better a memorytable where every checkpoint is listened with timestamp and duration.

Thank you for a reasonable feature request. Information about checkpoints can also be reported to the error log.

Oli,

please note that in InnoDB, a checkpoint is 'fuzzy'. A 'checkpoint' in InnoDB just means writing to ib_logfile1 the current lowest log sequence number (lsn) which has modifications in the buffer pool. We must advance this lsn when there is a danger that we would circularly write over that lsn in ib_logfiles.

Inside InnoDB, this advancing is done by writing dirty (modified) pages in the 'flush list' of the buffer pool. We could add statistics that would show how many pages are written in 'flush list' writes. InnoDB also writes dirty buffer pool pages in 'LRU writes'. That is done simply to get more unmodified pages to the buffer pool so that we can read in new pages.

If there are lots of 'flush list' writes, then ib_logfiles are maybe configured too small. But this information is not that valuable, since if the user follows the manual and sets the combined size of ib_logfiles = 1/2 * buffer pool size, then the log files hardly can be too small.

Thus, this is a low priority feature request unless someone explains why the statistics is needed for something else.

Regards,

Heikki

In InnoDB Information Schema Metrics Table, we will have counter maps to MONITOR_NUM_CHECKPOINT, documenting the number of checkpoints if you turn on the monitor.