topic Parsing MySQL slow log in All Apps and Add-ons

Parsing MySQL slow log

paulwilliamhill — Sat, 19 Nov 2011 04:08:15 GMT

Does anyone know how to parse the MySQL slow queries log?
I've tried a number of configurations in the .conf files but it's a pretty goofy format and I've had little luck getting the linemerge to consistently end with the query.

Re: Parsing MySQL slow log

_d_ — Sat, 19 Nov 2011 04:34:21 GMT

Paste a few sample lines and i'll give it a shot.

Re: Parsing MySQL slow log

paulwilliamhill — Sat, 19 Nov 2011 05:04:31 GMT

I can't fit it in a comment

Re: Parsing MySQL slow log

paulwilliamhill — Sat, 19 Nov 2011 05:06:15 GMT

# Time: 111118 18:55:48
# User@Host: x @ localhost []
# Query_time: 0.000250  Lock_time: 0.000079 Rows_sent: 1  Rows_examined: 6
SET timestamp=1321671348;
SELECT count(*) FROM mysql.user WHERE user='root' and password='';
# User@Host: x @ localhost []
# Query_time: 0.031790  Lock_time: 0.000080 Rows_sent: 30  Rows_examined: 88
SET timestamp=1321671348;
select concat('select count(*) into @discard from `',
                    TABLE_SCHEMA, '`.`', TABLE_NAME, '`') 
      from information_schema.TABLES where ENGINE='MyISAM';

Re: Parsing MySQL slow log

paulwilliamhill — Sat, 19 Nov 2011 05:09:24 GMT

# Time: 111118 19:51:35
# User@Host: ach.test[ach.test] @ test1.mercury.local [192.168.0.61]
# Query_time: 0.000361  Lock_time: 0.000043 Rows_sent: 1  Rows_examined: 635
use test1;
SET timestamp=1321674695;
SELECT COUNT(*) AS count
    FROM ...;
# User@Host: ach.test[ach.test] @ test1.mercury.local [192.168.0.61]
# Query_time: 0.000931  Lock_time: 0.000098 Rows_sent: 10  Rows_examined: 675
SET timestamp=1321674695;
SELECT STRAIGHT_JOIN
      ...
    FROM ...
    JOIN ... ON ...
    JOIN ... f ON ...
    JOIN ... u ON ...
    ORDER BY ... DESC
    LIMIT 0, 10;

Re: Parsing MySQL slow log

paulwilliamhill — Sat, 19 Nov 2011 05:12:40 GMT

by deafult splunk seems to use the SET timestamp= line to break but that is not really the event break. The end of the query is.
Also it throws in a # Time: line every now an then. but it's not a bounding element of the log line. It's just extra logged info. I think it may turn up for new connections and because we have a pool it's only every so often.

Re: Parsing MySQL slow log

_d_ — Sat, 19 Nov 2011 05:32:38 GMT

OK, so what are the boundaries of an event? In other words, where would you like Splunk to break, and where in the event should it look for a timestamp?

Re: Parsing MySQL slow log

paulwilliamhill — Sat, 19 Nov 2011 05:48:47 GMT

We this guy seems to have gotten pretty close http://splunk-base.splunk.com/answers/13109/mysql-slow-query-log-parsing but my logs don't consistently output the Time: log line.