QAN: Slow Log Configuration + Bug in Logrotation

Stan_Marsh · January 2, 2021, 2:13pm

Hello,

How to change the default value of “Max slow log size”?

Seems it is not possible to do through QAN web-interface (see schreenshot)

And one more. When mysql-slow-queries.log file grows up to 1GB, PMM renames it to mysql-slow-queries.log-1479XXXXXX, but doesn’t create a new mysql-slow-queries.log file.

PMM v1.0.5
Database v5.1.73 (Percona Server (GPL), Release 14.12, Revision 624)
OS CentOS 6.7 x86_64

Thanks in advance for feedback.

weber · December 30, 2020, 4:33pm

There is no option to override max slow query size for now, we will make it possible in the next versions.

Slow log rotation has been fixed in 1.0.6, we recommend you to update to the latest 1.0.7 version.

Stan_Marsh · December 30, 2020, 4:33pm

Thank you.

Stan_Marsh · December 30, 2020, 4:33pm

Hello,

At the moment the latest available version of pmm-server is 1.0.6 (https://hub.docker.com/r/percona/pmm-server/tags/)

I have upgraded both the pmm-server and the pmm-client to the version 1.0.6, following documentation (https://www.percona.com/doc/percona-monitoring-and-management/install.html)

[root&#64;pmm-server ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
0ed9c0e2f5c5 percona/pmm-server:1.0.6 "/opt/entrypoint.sh" 18 hours ago Up 17 hours 0.0.0.0:80->80/tcp, 443/tcp pmm-server

[root&#64;pmm-client ~]# pmm-admin --version
1.0.6

Nevertheless this didn’t solve the problem with slow query log rotation.

This night, when the mysql-slow-queries.log file grew up to 1GB, it was renamed by pmm but the new mysql-slow-queries.log wasn’t created:

[root&#64;pmm-client ~]# ll /var/log/mysql-slow-queries*
-rw-rw---- 1 mysql mysql 1077406966 Nov 29 02:17 /var/log/mysql-slow-queries.log-1480382280

[root&#64;pmm-client ~]# grep ERROR /var/log/pmm-mysql-queries-42001.log -A 4
2016/11/29 02:18:00.042963 ERROR qan-analyzer-1df25dd2-worker Error 29: File '/var/log/mysql-slow-queries.log' not found (Errcode: 13)
2016/11/29 02:18:00.043097 ERROR qan-analyzer-1df25dd2 open /var/log/mysql-slow-queries.log: no such file or directory
2016/11/29 02:19:00.001465 WARNING qan-interval stat /var/log/mysql-slow-queries.log: no such file or directory
2016/11/29 02:20:00.001156 WARNING qan-interval stat /var/log/mysql-slow-queries.log: no such file or directory
2016/11/29 02:21:00.001494 WARNING qan-interval stat /var/log/mysql-slow-queries.log: no such file or directory
2016/11/29 02:22:00.001221 WARNING qan-interval stat /var/log/mysql-slow-queries.log: no such file or directory

weber · December 30, 2020, 4:33pm

Looks like the user that is used in connection DSN for mysql:queries service (check it via pmm-admin list) does not have RELOAD grant to run FLUSH SLOW LOGS.
https://www.percona.com/doc/percona-monitoring-and-management/faq.html#what-privileges-are-required-to-monitor-a-mysql-instance

It should be able to execute FLUSH SLOW LOGS so MySQL creates a new slow log file.

Stan_Marsh · December 30, 2020, 4:33pm

The pmm user has the RELOAD grant:

[root&#64;pmm-client ~]# mysql -u root -pXXXXXXX -e "SELECT user,host FROM mysql.user WHERE user = 'pmm'\G"
*************************** 1. row ***************************
user: pmm
host: localhost

[root&#64;pmm-client ~]# mysql -u root -pXXXXXXX -e "SHOW GRANTS FOR 'pmm'&#64;'localhost'\G"
*************************** 1. row ***************************
Grants for pmm&#64;localhost: GRANT SELECT, RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'pmm'&#64;'localhost' IDENTIFIED BY PASSWORD '*XXXXXXX' WITH MAX_USER_CONNECTIONS 10

But can’t execute FLUSH SLOW LOGS in spite of the general FLUSH LOGS:

[root&#64;pmm-client ~]# mysql -u pmm -pXXXXXXX -e "FLUSH SLOW LOGS"
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOGS' at line 1

[root&#64;pmm-client ~]# mysql -u pmm -pXXXXXXX -e "FLUSH LOGS"
[root&#64;pmm-client ~]# echo $?
0

Because we use the lower version of MySQL then 5.5 where “FLUSH SLOW LOGS” was added:

[root&#64;pmm-client ~]# mysql -u root -pXXXXXXX -e "SHOW VARIABLES LIKE 'version%'"
+-------------------------+---------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------+
| version | 5.1.73-14.12-log |
| version_comment | Percona Server (GPL), Release 14.12, Revision 624 |
| version_compile_machine | x86_64 |
| version_compile_os | unknown-linux-gnu |
+-------------------------+---------------------------------------------------+

Is it possible to find some workaround for MySQL lower then 5.5?

weber · December 30, 2020, 4:33pm

As a workaround I suggest to setup slow log rotation on your own per https://www.percona.com/blog/2013/04/18/rotating-mysql-slow-logs-safely/
Just set the size threshold a bit lower than 1G. This will make rotation working by logrotate prior it is executed by qan-agent itself.
qan-agent should correctly detect whether slow log is rotated externally.

We will look for a fix in the next version for MySQL 5.1.

Stan_Marsh · December 30, 2020, 4:33pm

Our slow query log file size can grow larger than 1G during the day so the system-wide logrotate daemon, that runs once a day, won’t be able to rotate it before the PMM.

As a temporary workaround i have created a custom “/etc/logrotate.d/mysql-slow-queries” config with the following settings:

/var/log/mysql-slow-queries.log {
create 660 mysql mysql
size 500M
rotate 10
missingok
nocompress
nodateext
sharedscripts
postrotate
env HOME=/root/ /usr/bin/mysqladmin flush-logs
endscript
}

And will run it each 10 minutes from the crontab:

*/10 * * * * /usr/sbin/logrotate /etc/logrotate.d/mysql-slow-queries > /dev/null 2>&1

Thanks for the feedback.

Looking forward for the next version of PMM:)