Not the answer you need?
Register and ask your own question!

QAN: Slow Log Configuration + Bug in Logrotation

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.

Comments

  • weberweber Advisor Inactive User Role Beginner
    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 MarshStan Marsh Entrant Inactive User Role Beginner
    Thank you.
  • Stan MarshStan Marsh Entrant Inactive User Role Beginner
    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@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
    
  • weberweber Advisor Inactive User Role Beginner
    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 MarshStan Marsh Entrant Inactive User Role Beginner
    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?
  • weberweber Advisor Inactive User Role Beginner
    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 MarshStan Marsh Entrant Inactive User Role Beginner
    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:)
This discussion has been closed.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.