Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
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 '12 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 '12 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 '12 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 '12 at 9:55
add comment

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.

share|improve this answer
1  
Take care when using MySQL Proxy. It's terribly slow. –  tex Aug 11 '12 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 '12 at 10:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.