Problem

• Users want to ensure that their database instances do not experience unbounded log file growth. There are two methods:

  1. Percona Server for MySQL / Percona XtraDB Cluster provide max_slowlog_size, max_slowlog_files settings to ensure individual log size limit and maximum number of log files to maintain.

     • When a log file is incremented, the name is also published to slow_query_log_file

  2. pmm-agent provides --size-slow-logs to also rotate slow logs

• pmm-agent needs to know the value of slow_query_log_file in order to read the slow log

• If max_slowlog_size < --size-slow-logs then you will make pmm-agent unable to read the slow query log, and thus Query Analytics will fail because the value of slow_query_log_file has been updated
   

  EXPECTED BEHAVIOUR

• pmm-agent does not miss any log events from the slow log when pmm-agent --size-slow-logs is configured

• pmm-agent will rotate slow logs when size threshold is reached - default 1GB or auto-detect max_slowlog_size and reduce it somewhat

• if for any reason pmm-agent is not running, mysqld is configured for max_slowlog_size > 1GB, the slow log will be rotated, avoiding a disk full condition

Implementation Details

1. Check if max_slowlog_files > 0, if so, get value of max_slowlog_size

2. Set --size-slow-logs = ( max_slowlog_size x 80% )

This way pmm-agent does the rotation before max_slowlog_size is reached. Therefore also value of slowlog_file_name does not change.

And should pmm-agent fail, then max_slowlog_size will begin to trigger new values for slowlog_file_name.
pmm-agent can again be safely re-started and take over rotation

https://docs.percona.com/percona-server/8.0/slowlog-rotation.html

https://github.com/percona/pmm-infra/commit/3f53ceafcef97ee021000a04cc7b095cd679b122
https://github.com/percona/pmm-infra/commit/b0d6ffd796d20c3aeec8ff6fc383da238c50b592

STR

• Enable settings in MySQL:

  max_slowlog_size=1G max_slowlog_files=3

  Process:

  [root@percona-xtradb-cluster-1 ~]# mysql -e "show global variables like 'slow_query_log_file'" +---------------------+-----------------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------------+ | slow_query_log_file | /data/percona-xtradb-cluster-1-slow.log | +---------------------+-----------------------------------------+ [root@percona-xtradb-cluster-1 ~]# ls -ltr /data/*slow* -rw-r----- 1 mysql mysql 35558789120 nov 11 20:35 /data/percona-xtradb-cluster-1-slow.log [root@percona-xtradb-cluster-1 ~]# mysql -e 'set global max_slowlog_files=3; set global max_slowlog_size=10000' [root@percona-xtradb-cluster-1 ~]# mysql -e "show global variables like 'slow_query_log_file'" +---------------------+------------------------------------------------+ | Variable_name | Value | +---------------------+------------------------------------------------+ | slow_query_log_file | /data/percona-xtradb-cluster-1-slow.log.000001 | +---------------------+------------------------------------------------+ [root@percona-xtradb-cluster-1 ~]# ls -ltr /data/*slow* -rw-r----- 1 mysql mysql 35558789120 nov 11 20:35 /data/percona-xtradb-cluster-1-slow.log -rw-r----- 1 mysql mysql 0 nov 11 20:36 /data/percona-xtradb-cluster-1-slow.log.000001

• Configure pmm2-client for query-source=slowlog

• Review pmm-agent logs

  Nov 26 21:38:15 percona-xtradb-cluster-1.pmmdemodev.local pmm-agent[2105]: time="2023-11-26T21:38:15.465+00:00" level=warning msg="Failed to stat new file: stat /data/percona-xtradb-cluster-1-slow.log: no such file or directory." agentID=/agent_id/d66cc52e-ef56-4b85-8484-42caad8feb20 component=slowlog/reader file=/data/percona-xtradb-cluster-1-slow.log type=qan_mysql_slowlog_agent Nov 26 21:38:16 percona-xtradb-cluster-1.pmmdemodev.local pmm-agent[2105]: time="2023-11-26T21:38:16.466+00:00" level=error msg="Failed to start reader for file /data/percona-xtradb-cluster-1-slow.log.000722: open /data/percona-xtradb-cluster-1-slow.log.000722: no such file or directory." agentID=/agent_i d/d66cc52e-ef56-4b85-8484-42caad8feb20 component=agent-builtin type=qan_mysql_slowlog_agent