Home / server / Questions / 416380
Accepted
Wrikken
Asked: 2012-08-11 10:22:28 +0800 CST

log slow queries killed before completion

  • 772

Apparently, one of my colleagues killed a webserver which was making MySQL hang (locking other queries), rather then killing the specific query, and special great care was taken by him to forget any details about the query which was blocking the rest. I wish I could say this was the first time this happened, but it's about every 2 to 4 months (irregular intervals sadly, no info there), and I'd very much like an ability to log queries taking a very long time, whether they complete or not.

Now, for successful queries we have the perfectly workable slow query log, but it only ends up there on completion, not if killed during the query. Is there a built-in solution to log queries while they are still running? For now, I'm resorting to running a daemon/script querying this every 10 seconds:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE TIME > 30 
AND USER != 'replication'
AND USER != 'system user'
AND USER != 'DELAYED'
AND COMMAND != 'Sleep'
AND ID != CONNECTION_ID();

... but it feels kinda hackish, and I wonder if there's a built-in possibility I have overlooked?

mysql

2 Answers

  1. Best Answer

    Try pt-query-digest --processlist h=host1 --print --no-report as described here. I have never tried to make a digest on a live server and only used it to process an existing slow log but this seems to be the tool you need. You will need to install Percona Toolkit.

    • 2

  2. Although this involves using a second software (from the same vendor though), you should consider to put a MySQL Proxy in front of your server, and using the very extensive capabilities of it to log beginning and end times of the queries, even if they are cancelled by the server.

    • 1