pt-stalk truncates InnoDB status if there are too many transactions

[Fernando Ipar]

If there is a very high number of open transactions, the output from SHOW ENGINE INNODB STATUS will be truncated.

I think pt-stalk could create the innodb_monitor table in addition to running the SHOW ENGINE command, so that if the output is truncated, the full content will be sent to the error log.

Since the error log is already saved, I believe this can be achieved by simply adding the following lines to the start and end of a capture:

$CMD_MYSQL $EXT_ARGV mysql -e 'CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB'

$CMD_MYSQL $EXT_ARGV mysql -e 'DROP TABLE innodb_monitor'

Feel free to change this into a feature request. In my personal experience I prefer to consider it a bug as we're missing a very important part of the picture while trying to diagnose an intermittent db outage.

[Daniel Nichter (daniel-nichter)]

Fernando, can you clarify how/why this works? So you're saying that if SHOW ENGINE INNODB STATUS is truncated due to too many open transactions, that creating a table--any table--will fail and that failure will be logged along with the entire output of SHOW ENGINE INNODB STATUS?

[Fernando Ipar (fipar)]

Daniel:

Not just any table, the innodb_monitor table is special and documented here: http://dev.mysql.com/doc/refman/5.5/en/innodb-monitors.html

I actually think it would be best if the collect script would:
- attempt to collect by 'SHOW ENGINE INNODB STATUS"
- detect if said output is truncated
- iff that happens, use the innodb_monitor approach instead.

Regards,
Fernando.

[Fernando Ipar (fipar)]

Daniel:

Here's an alternative approach, maybe it's easier to just find this fd instead of using the magic table: http://www.mysqlperformanceblog.com/2008/10/31/full-innodb-status/

Regards,
Fernando.

[Fernando Ipar (fipar)]

Daniel,

I think the attached patch should do the trick.

Regards,
Fernando.

[Fernando Ipar (fipar)]

Daniel:

I've linked a branch, my original patch only worked in some cases, as it depended on the output to ls.
This one should be better.

I've tested on raspbian and ubuntu.

Regards,
Fernando.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-549