Parse and filter MySQL Slow Query Logs Using Grafana

We have MySQL Slow Query logs writing to Grafana 9.3.6.

Given a MYSQL slow log like the following, I'm trying to filter the log output to logs that are slower than, say, one second.

# User@Host: kermit[muppets] @  [99.99.99.99]  Id: 54908918
# Schema: frogs  Last_errno: 0  Killed: 0
# Query_time: 0.000218  Lock_time: 0.000081  Rows_sent: 1  Rows_examined: 1  Rows_affected: 0  Bytes_sent: 665
# Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: 0
# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 9
# Log_slow_rate_type: query  Log_slow_rate_limit: 1000
SET timestamp=1676569875;
select id FROM characters WHERE name='monster';

I've made it this far,

{service="db::muppets"} |~ `Query_time: (\d*\.\d*)`

which correctly highlights the field in the log messages, but now I'd like to use that (\d*\.\d*) capture group to reduce the logs to the queries that are more than one second.

It seems like I need something like this, but this returns no results.

{service="db::muppets"} |~ `Query_time: (?P<query_time>\d*\.\d*)` | query_time > 1

I assume there needs to be some type of text-to-number conversion of the query_time label, but I can't figure that part out.

Grafana is totally new to me.

Solution

Here is how I solved this problem.

    scrape_configs:
    - job_name: mysql/slowlogs
      static_configs:
        - labels:
            instance: $INSTANCE
            job: mysql/slowlogs
            __path__: /var/log/mysql/slow_query/*.log
      pipeline_stages:
        - multiline:
              firstline: '#\sTime:.*'
        - regex:
              expression: '#\s*Time:\s*(?P<timestamp>.*)\n#\s*User@Host:\s*(?P<user>[^\[]+).*@\s*(?P<host>.*]).*Id:\s*(?P<id>\d+)\n#\s*Query_time:\s*(?P<query_time>\d+\.\d+)\s*Lock_time:\s*(?P<lock_time>\d+\.\d+)\s*Rows_sent:\s*(?P<rows_sent>\d+)\s*Rows_examined:\s*(?P<rows_examined>\d+)\n(?P<query>(?s:.*))$'
        - labels:
            timestamp:
            user:
            host:
            id:
            query_time:
            lock_time:
            rows_sent:
            rows_examined:
            query:
        - drop:
            expression: "^ *$"
            drop_counter_reason: "drop empty lines"

Parsing Regex

view on regex101

Grafana Docs

A few things I also did for my setup.

sudo mkdir /var/log/mysql/slow_query
sudo chown mysql:adm /var/log/mysql/slow_query
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow_query/slow.log';
sudo chown mysql:adm /var/log/mysql/slow_query/LogRx-slow.log

This will pre-process the logs before getting to grafana, group all of the lines in the slow log file as one Grafana entry, and parse out all of the parts so that in Grafana you get something like this (redacted my data):