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.
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?
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.
Paste a few sample lines and i'll give it a shot.
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.
# 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;
# 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';
I can't fit it in a comment