QAN: Slow Log Configuration + Bug in Logrotation

Percona Monitoring and Management (PMM) PMM 1.x
1

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.

photoid=46494

2

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.

3

Thank you.

4

Hello,

At the moment the latest available version of pmm-server is 1.0.6 ([url]Docker Hub)

I have upgraded both the pmm-server and the pmm-client to the version 1.0.6, following documentation ([url]Percona Monitoring and Management)

[root@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@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@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@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
5

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.
[url]Percona Monitoring and Management

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

6

The pmm user has the RELOAD grant:

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

[root@pmm-client ~]# mysql -u root -pXXXXXXX -e "SHOW GRANTS FOR 'pmm'@'localhost'\G"
*************************** 1. row ***************************
Grants for pmm@localhost: GRANT SELECT, RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'pmm'@'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@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@pmm-client ~]# mysql -u pmm -pXXXXXXX -e "FLUSH LOGS"
[root@pmm-client ~]# echo $?
0

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

[root@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?

7

As a workaround I suggest to setup slow log rotation on your own per [url]https://www.percona.com/blog/2013/04/18/rotating-mysql-slow-logs-safely/[/url]
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.

8

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:)