| Bug #59429 | display checkpoint age in SHOW STATUS and SHOW ENGINE INNODB STATUS | ||
|---|---|---|---|
| Submitted: | 11 Jan 2011 19:28 | Modified: | 15 Oct 2012 14:58 |
| Reporter: | Mark Callaghan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB Plugin storage engine | Severity: | S4 (Feature request) |
| Version: | 5.5 | OS: | Any |
| Assigned to: | Geir Høydalsvik | CPU Architecture: | Any |
| Tags: | innodb, Monitoring | ||
[11 Jan 2011 19:28]
Mark Callaghan
[12 Jan 2011 4:24]
Valeriy Kravchuk
Thank you for the feature request.
[14 Jan 2011 16:39]
Mark Callaghan
Whether or not the code in the Facebook patch or XtraDB is faster than official InnoDB, many apps will run much faster on XtraDB/Facebook because they have much better monitoring and tuning is easier. We need more data in SHOW STATUS and I don't want to file a feature request per counter. I have more requests based on the effort from http://bugs.mysql.com/bug.php?id=59291. InnoDB is much harder to diagnose in official InnoDB than in the Facebook patch or XtraDB. My high level request is that someone look at values added to SHOW ENGINE INNODB STATUS and SHOW STATUS between Facebook/Percona and official InnoDB to understand what is missing. It isn't enough to put something into SHOW ENGINE INNODB STATUS. We need to scrape values into external monitoring utilities to display rates. Even official InnoDB has several variables in SHOW ENGINE INNODB STATUS that are not in SHOW STATUS. Things that are missing include: * rate at which purge processing is done -- undo log pages per second * rate at which reads are done for purge and insert buffer merges * time spent in various background processing tasks See http://www.facebook.com/notes/mysql-at-facebook/several-ways-to-be-faster/488085270932 for details on counters that are missing from SHOW STATUS for InnoDB
[14 Jan 2011 17:01]
Mark Callaghan
Other data that is in the Facebook patch for SHOW ENGINE INNODB STATUS Time spent do different tasks by srv_master_thread ----------------- BACKGROUND THREAD ----------------- ... Seconds in background IO: 0.00 insert buffer, 76.40 buffer pool, 172.31 purge, 0.02 free margin fg, 0.00 free margin bg, 0.70 checkpoint, 811 .21 sleep Checkpoint IO seconds: 78.99 background, 112.82 foreground More details on lock waits and timeouts, commits and rollback ------------ TRANSACTIONS ------------ ... Lock stats: 0 deadlocks, 0 lock wait timeouts Commits: 49112713 all, 24556522 with undo Rollback: 0 total, 0 partial Stats on the adaptive hash index: ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- ... adaptive hash: 4352719 fail, 913301 pages added, 38074003 rows added, 448294 pages removed, 1163685 rows removed, 62138136 rows updated More details on checkpoint LOG --- ... Foreground flush margins: sync 3023360986 async 2821803587 Space to flush margin: sync 10387295 async 0 Current_LSN - Min_LSN 3012973691 Checkpoint age 3012973691 Max checkpoint age 3224918385 More details on page flushing BUFFER POOL AND MEMORY ---------------------- ... Percent pages dirty: 92.45 ... Total writes: 0 LRU, 1948074 flush list, 0 single page Write sources: fg free margin 0, bg free margin 0, bg dirty 9686, preflush 1346803, adaptive 439382, other 1033, bg checkpoint 419651, fg checkpoint 927152 Neighbor pages flushed: 1917317 from list, 0 from LRU
[13 Aug 2012 18:30]
Baron Schwartz
I'm a bit sad that the Innodb_checkpoint_age status counter looks like it won't be in MySQL 5.6. It's helpful and important for monitoring server health. I have written far too many SHOW ENGINE INNODB STATUS parsers in the last several years.
[15 Oct 2012 14:58]
Erlend Dahl
This was fixed in 5.6.2. Closing.
