Slow Log Settings
If you are running Percona Server for MySQL, a properly configured slow query log will provide the most amount of information with the lowest overhead. In other cases, use Performance Schema if it is supported.
The first and obvious variable to enable is
slow_query_log which controls the global Slow Query on/off status.
Secondly, verify that the log is sent to a FILE instead of a TABLE. This is controlled with the
By definition, the slow query log is supposed to capture only slow queries. These are the queries the execution time of which is above a certain threshold. The threshold is defined by the
In heavily-loaded applications, frequent fast queries can actually have a much bigger impact on performance than rare slow queries. To ensure comprehensive analysis of your query traffic, set the
long_query_time to 0 so that all queries are captured.
Depending on the amount of traffic, logging could become aggresive and resource consuming. However, Percona Server for MySQL provides a way to throttle the level of intensity of the data capture without compromising information. The most important variable is
log_slow_rate_limit, which controls the query sampling in Percona Server for MySQL. Details on that variable can be found here.
A possible problem with query sampling is that rare slow queries might not get captured at all. To avoid this, use the
slow_query_log_always_write_time variable to specify which queries should ignore sampling. That is, queries with longer execution time will always be captured by the slow query log.
PMM will take care of rotating and removing old slow log files, only if you set the
--size-slow-logs variable via pmm-admin as described in pmm-admin - PMM Administration Tool.
When the limit is reached, PMM will remove the previous old slow log file, rename the current file with the sufix
.old, and execute the MySQL command
FLUSH LOGS. It will only keep one old file. Older files will be deleted on the next iteration.