Using show Processlist and Mysqladmin debug Output in conjunction with show INNODB STATUS

When InnoDB appears hung, I know the natural reaction are to check SHOW ENGINE InnoDB STATUS.

In fact, it's the first thing I check when InnoDB tables is involved.

However, I just want to iterate what valuable SHOW full processlist and/or mysqladmin debug outputs can be even when it see Ms Mysqld is hung on InnoDB table.

II Recent cases I ' ve encountered illustrate why.

Case #1:

MySQL appeared hung on the following simple, Single-row INSERT:

---TRANSACTION 0 2035648699, ACTIVE 76629 sec, process no 9047,os thread ID 3069426592, thread declared inside InnoDB 500m Ysql tables in use 1, locked 1...INSERT to test (ID, parent, text) VALUES (180370, 70122, ' Test table ')

At least this ' s what it seemed per the INNODB STATUS, but unfortunately, there wasn ' t any further information to go on.

The next time it occurred, SHOW full processlist is captured at the time.

Turns out, there is a *very* long SELECT running, but not from the same table, and no foreign keys (FKs) either. Turned out it is some crazy, auto-generated query that self-joined itself 548 times. So there were no locks, per se. This is a query itself held up everything, and thus also the INSERT.

Case #2:

This is a table that is also hanging on a certain and simple UPDATE. The UPDATE is based on Te PK, so only one row is to is updated.

Yet, it hung, and it hung, longer than wait_timeout, interactive_timeout, and innodb_lock_wait_time out. And there were no other transactions running in the INNODB STATUS.

Turned out, another client had issued a LOCK TABLE command on the table. Since lock TABLE is handled outside of the InnoDB storage engine, the lock doesn ' t appear in SHOW InnoDB STATUS output.

Using mysqladmin Debug output, coupled with the SHOW Processlist helped catch this offender.

At no rate, hope this helps, and happy troubleshooting.

Reference:

http://www.chriscalender.com/using-show-processlist-and-mysqladmin-debug-output-in-conjunction-with-show-innodb-status/

Using show Processlist and Mysqladmin debug Output in conjunction with show INNODB STATUS