I am trying to parse the mysql slow log with logstash so it's important that the format is consistent. I have noticed that if statements execute at the same time, the line # Time: yymmdd
is only logged once (see example below). I looked at the setting mysqld_log-long-format however this setting is deprecated and supposedily the default. I have not enabled the log-short-format option.
Is there any setting that can enforce the Time
field to either always show, or always not show? I don't actually need the information since there is also a timestamp
, I just need the output to be consistent.
# Time: 150419 6:00:43
# User@Host: web[web] @ localhost []
# Query_time: 7.730519 Lock_time: 0.000070 Rows_sent: 167620 Rows_examined: 167620
SET timestamp=1429416043;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`;
# Time: 150419 6:00:45
# User@Host: web[web] @ localhost []
# Query_time: 3.480173 Lock_time: 0.000101 Rows_sent: 0 Rows_examined: 0
SET timestamp=1429416045;
INSERT INTO user (lastupdated, fk_id, user_id) ..
# User@Host: web[web] @ localhost []
# Query_time: 3.388204 Lock_time: 0.000133 Rows_sent: 0 Rows_examined: 0
SET timestamp=1429416045;
INSERT INTO user (lastupdated, fk_id, user_id) ..
The logstash config I'm using looks like this – see logstash multiline codec
input {
file {
path => "mysql-slow.log"
codec => multiline {
pattern => "^# Time"
negate => true
what => previous
}
}
}
Best Answer
A lot of things have changed in the slowlog over the versions. Discover the inconsistencies and code to them!
# Time
(and maybe some other things) are specified only when they change.In 5.7.2 Time will include microseconds. This is one of many syntax changes that have happened over the years -- be careful. Percona and MariaDB also have different things that can show in the SlowLog.
Query time used to be just an integer.