I have a mysql database and I’m trying to analyze the log files with pt-query-digest.
I can do a simple:
pt-query-digest --type genlog --report-all --limit=100% mysql.log > results.txt
Which captures a lot of data, but I’d like to have the results also include the IP of the client which executed the query.
I found I can do this:
pt-query-digest --type genlog --report-all --limit=100% --group-by host mysql.log > results2.txt
Which returns a much smaller list including the IP of the client, but then I don’t get the query itself which was executed. My goal is to get a list of all the queries executed and include the client IP from which the query was executed.
Can this be done?
1 Like
Hi @joseph85750. Is there a particular reason on why you’re using general log for this?
If the result you’re looking for is have all the queries executed and the IP, you could enable the slow query log, at least with the following parameters:
- slow_query_log=1
- long_query_time=0 (So every query get recorded).
- slow_query_log_file=/your/desired/path.log
You could modify the following as well, based on your requirements:
- log_slow_admin_statements
- log_slow_extra
- log_slow_replica_statements
And then you could use pt-query-digest with the slow query log as the source.
Best,
Mauricio.
2 Likes
I’m using the general log because I want all queries, and not just the slow queries. It was my understanding that the slow query log doesn’t log all queries. Maybe I misunderstood?
1 Like
The slow query log will log every query running longer than the threshold long_query_time. This means you can set long_query_time=0 and capture all queries.
Note the difference is that General Log writes the query when it begins (so successful and failed queries are logged), whereas slow log writes after the query has completed. This has the advantage of including valuable information such as pages read from disk, lock wait times, and query run time.
3 Likes