4

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?

2 Answers 2

2

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.

4
  • Hm, "pt-query-digest parses and analyzes MySQL log files. With no FILE, or when FILE is -, it read standard input." I do NOT have a log file, so this seems not to work.
    – Wrikken
    Aug 13, 2012 at 10:08
  • 1
    A quote from pt-query-digest manual:Watch SHOW FULL PROCESSLIST and output a log in slow query log format: pt-query-digest --processlist h=host1 --print --no-report
    – Alex
    Aug 13, 2012 at 11:08
  • I just tried pt-query-digest --processlist on one of my production hosts, it does not need a log file to work, it watches the live server and records queries from SHOW FULL PROCESSLIST instead. It prints the queries to standard output so if you redirect it to a file you will get a kind of slow log.
    – Alex
    Aug 13, 2012 at 11:11
  • Hm, you are indeed right, should have read further on. Killed queries also seem to appear quite well. I like it more then mysql proxy performance wise.
    – Wrikken
    Aug 14, 2012 at 9:55
1

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.

2
  • 1
    Take care when using MySQL Proxy. It's terribly slow.
    – tex
    Aug 11, 2012 at 19:00
  • Hm, seems interesting indeed, but performance is key here. I'll do some testing and see how it does.
    – Wrikken
    Aug 13, 2012 at 10:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .