Bug #56922 SHOW ENGINE INNODB STATUS truncation limit is too strict and not instrumented
Submitted: 22 Sep 2010 10:53 Modified: 1 Dec 2010 19:51
Reporter: Mark Leith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.5.6 OS:Any
Assigned to: Mark Leith CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[22 Sep 2010 10:53] Mark Leith
Description:
Currently SHOW ENGINE INNODB STATUS truncates it's entire output at 64 kilobytes. With modern systems, often pushing past 1024 connections and active transactions, this often leads to the output of SHOW ENGINE INNODB STATUS being truncated during the TRANSACTIONS section.

This then has the side effect that monitoring tools which rely on statistics that come at the end of the output start to lose data. Further, when this starts to happen, it is hard to debug because there is nothing instrumented in the server to tell you when this truncation is happening. 

How to repeat:
o Connect 1500 connections and have them all start a transaction
o Execute SHOW ENGINE INNODB STATUS\G

Suggested fix:
We can safely increase MAX_STATUS_SIZE to 1 megabyte, there is no longer any need to limit to 64 kilobytes. 

We should also add a status variable that can be monitored which tracks the number of truncations that happen with SHOW ENGINE INNODB STATUS.
[23 Sep 2010 8:50] Mark Leith
Patch pushed to the mysql-5.5-innodb tree (email hooks failed, so no mail went out):

revno: 3221
committer: Mark Leith <mark.leith@oracle.com>
branch nick: mysql-5.5-innodb
timestamp: Thu 2010-09-23 09:12:09 +0100
message:
  Bug#56922 SHOW ENGINE INNODB STATUS truncation limit is too strict and not instrumented
  
  rb://459 approved by Jimmuy / Inaam
[26 Oct 2010 0:41] John Russell
Added to change log:

The output from the SHOW ENGINE INNODB STATUS command can now be up
to 1 MB. Formerly, it was truncated at 64 KB. Monitoring applications
that parse can check if output exceeds this new, larger limit by
testing the Innodb_truncated_status_writes status variable.

Also added info about the new status variable in the relevant reference section of the doc. I'm open to suggestions if it should be mentioned elsewhere in the monitoring etc. discussions.
[9 Nov 2010 19:43] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:vasil.dimov@oracle.com-20100928081234-22qbm6cwht521484) (merge vers: 5.1.51) (pib:21)
[13 Nov 2010 16:17] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100928081234-22qbm6cwht521484) (merge vers: 5.1.51) (pib:21)
[13 Nov 2010 16:30] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100928081234-22qbm6cwht521484) (pib:21)
[1 Dec 2010 19:51] John Russell
Also corrected one place under the 'Monitors' section that mentioned the old 64KB figure.