innodb statistics not full in case when there are a lot of transactions

[GoogleCodeExporter]

According to MySQL documentation
(http://dev.mysql.com/doc/refman/4.1/en/innodb-monitors.html for example)
is output of "SHOW /*!50000 ENGINE*/ INNODB STATUS" limited:

InnoDB Monitor output is limited to 64,000 bytes when produced via the SHOW
ENGINE INNODB STATUS statement. This limit does not apply to output written
to the server's error output.

In case when there are a lot of transactions the output of "SHOW /*!50000
ENGINE*/ INNODB STATUS" ends somewhere in a list of running transactions
thus truncating the rest of output. As a result there are empty graphs for
all INNODB statistics which follow the list of transactions.

Environment:
Mysql server 4.0.27, 64 bit
Linux <SKIP> 2.6.18-92.1.22.el5 #1 SMP Tue Dec 16 11:57:43 EST 2008 x86_64
x86_64 x86_64 GNU/Linux

How to reproduce:
In out case only around 343 transactions are listed, the rest of output is
truncated. So, obviously creating a number of transaction around this value
will reproduce an issue.

It looks of course like MySQL issue and not Cacti template/script issue,
but may be template could be redesigned to use output to files which is not
limited:

Beginning with MySQL 4.0.19, InnoDB sends diagnostic output to stderr or
files instead of stdout or fixed-size memory buffers, to avoid potential
buffer overflow errors. As a side effect, the output of SHOW INNODB STATUS
is written to a status file in the MySQL data directory every fifteen
seconds. The name of the file is innodb_status.pid, where pid is the server
process ID.

Original issue reported on code.google.com by rust...@gmail.com on 16 Mar 2010 at 4:06

[GoogleCodeExporter]

Sorry, but there's nothing that can be done about that in the general case.  The
workaround is not useful for systems that are monitoring the server remotely.

Original comment by baron.schwartz on 17 Mar 2010 at 2:42

• Changed state: WontFix

[GoogleCodeExporter]

Thanks a lot for looking into this issue.

But as far as I have understood you do perform some remote script call in case 
of
Apache monitoring. And what I meant with redesign is to get content of a file
"innodb_status.pid" with SSH. This requires of course some template 
modifications
(may be a lot), but I think that this is a way to get full info about innodb 
status
without limitations and to get correct monitoring in all cases.

Best regards,

R

Original comment by rust...@gmail.com on 17 Mar 2010 at 7:25