I have installed Grafana Loki(Using the docker method : Installation | Grafana Loki documentation) and Grafana agent for dashboard. My goal is to get the unique slow running queries in our MySQL database.
I did integrate the MySQL slow query log with the Grafana Loki and I am able to see the logs in the Label filters : filename = /var/log/slow.log. However, I need help in getting the log displayed and formatted correctly.
I have attached the screenshots of the Logs that are getting displayed currently. The problem is I am not able to see the log completely and I see only the line that I have filtered the condition. I have gone through the documentation that there is a way we can use that will help us in getting the complete data i.e. I need the Logs to show the query details like the below example :
How do you currently integration slow query log with Loki? Do you use some plugin, or custom code?
I don’t have this use case myself personally, but if you are coding your own parser, I’d recommend doing the following.
Given your example:
# User@Host: adsro[adsro] @ [xx.xx.xxx.xxx] Id: 180859012
# Schema: schema_name Last_errno: 0 Killed: 0
# Query_time: 0.000089 Lock_time: 0.000034 Rows_sent: 5 Rows_examined: 5 Rows_affected: 0
# Bytes_sent: 574 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: 79FB71618B
# QC_Hit: No Full_scan: Yes 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: 1
SET timestamp=1682185843;
SELECT ExchangeID, ProxyIP, BypassAllCampaignFilters, Trace, FixedBid FROM ProxyIpConfig;
You would naturally parse the slow query log by section, and within each section, you’d parse the lines starting with # for metrics that you care about. For the actual query, my opinion is that it does not make sense for the query to be in the log line in plain text format, and I would probably base64 encode the log string instead. That way you keep your log lines shorter and smaller, while retain the ability to decode the query string when needed.
So for the slow query log integration, I have copied the slow log from another server to the /var/log/ directory and I am able to see them in the Label filters section. You would naturally parse the slow query log by section, and within each section, you’d parse the lines starting with # for metrics that you care about.For the actual query, my opinion is that it does not make sense for the query to be in the log line in plain text format, and I would probably base64 encode the log string instead. - How do I do this ?
