Re: Problematic Query identification at the time of load on server

[Aftab Khan]

You can always get inforamtion about the queries involved in the (last) deadlock from SHOW ENGINE INNODB STATUS output under following section:

SHOW ENGINE INNODB STATUS\G
...
------------------------
LATEST DETECTED DEADLOCK
------------------------
....

However you can capture periodic output of InnoDB Monitor into mysql error log- to enable innodb_monitor, create the innodb_monitor table in any database:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;


Similarly, you can collect more information then just innodb monitor output, you can capture MYSQL processlist for a periodic ouptut. For example:

mysqladmin ext proc stat -i 30 -hlocalhost -uroot -pxxx > stats.txt

The above will stay connected to the server, so it may catch more information after a hangup.

If you know the symptoms when the problem occurs you can collect even more useful diagnotics using pt-stalk tool:


http://mysqlopt.blogspot.co.uk/2012/06/mysql-capturing-diagnostic-data.html