MySQL Bugs: #56922: SHOW ENGINE INNODB STATUS truncation limit is too strict and not instrumented

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

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.

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

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.

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)

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)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100928081234-22qbm6cwht521484) (pib:21)

Also corrected one place under the 'Monitors' section that mentioned the old 64KB figure.