All Apps and Add-ons

Parsing MySQL slow log

paulwilliamhill
New Member

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.

Tags (1)
0 Karma

_d_
Splunk Employee
Splunk Employee

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?

0 Karma

paulwilliamhill
New Member

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.

0 Karma

_d_
Splunk Employee
Splunk Employee

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

0 Karma

paulwilliamhill
New Member

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.

0 Karma

paulwilliamhill
New Member
# 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;
0 Karma

paulwilliamhill
New Member
# 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';
0 Karma

paulwilliamhill
New Member

I can't fit it in a comment

0 Karma