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

[Rick James]

Are you chasing a "load" problem? Or a "deadlock" problem? Or something else.

Load -- use the slowlog to identify the worst queries; then optimize them.

Deadlock -- SHOW ENGINE INNODB STATUS and PROCESSLIST (as Aftab says) will provide some info -- if you can run the SHOW immediately before/after the problem.

Deadlock -- Some deadlocks cannot be prevent, but can only be dealt with. Usually this is by restarting the transaction at the BEGIN.

Deadlocks happen less often after optimizing slow queries.

Non-deadlock hangs -- These are trickier to address. Again INNODB STATUS and PROCESSLIST may provide clues.

The term "deadlock" has a precise meaning, and a casual meaning. I can't tell which definition you are using. INNODB STATUS may clarify.