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

Reported by Fernando Ipar on 2012-06-30
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
Medium
Daniel Nichter

Bug Description

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.

tags: added: pt-stalk
Changed in percona-toolkit:
status: New → Triaged
Changed in percona-toolkit:
milestone: none → 2.2.1
importance: Undecided → Medium
Changed in percona-toolkit:
assignee: nobody → Daniel Nichter (daniel-nichter)
Daniel Nichter (daniel-nichter) wrote :

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?

Changed in percona-toolkit:
status: Triaged → In Progress
Fernando Ipar (fipar) wrote :

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) wrote :

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) wrote :

Daniel,

I think the attached patch should do the trick.

Regards,
Fernando.

Fernando Ipar (fipar) wrote :

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.

summary: - pt-stalk does not get the full innodb output if there are too many
- transactions
+ pt-stalk truncates InnoDB status if there are too many transactions
Changed in percona-toolkit:
status: In Progress → Fix Committed
Changed in percona-toolkit:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers