Uploaded image for project: 'Percona Monitoring and Management'
  1. Percona Monitoring and Management
  2. PMM-2242

`pmm-admin add mysql:queries` silently changes global server variables when slow log is FILE or TABLE

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Medium
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: 2.0.0
    • Component/s: PMM Client, QAN Agent
    • Labels:

      Description

      Testing with pmm-client 1.8.1, we got the following results:

      (adding some extra \n after each command so that output is clearer)

      [root@pmm-1 ~]# mysql -e 'show global variables' > before.out
      
      [root@pmm-1 ~]# pmm-admin add mysql:queries
      OK, now monitoring MySQL queries from slowlog using DSN root:***@tcp(localhost:3306)
      
      [root@pmm-1 ~]# mysql -e 'show global variables' > after.out
      
      [root@pmm-1 ~]# diff before.out after.out 
      273c273
      < log_output    TABLE
      ---
      > log_output    FILE
      440c440
      < slow_query_log    OFF
      ---
      > slow_query_log    ON

      As we can see there, log_output used to be TABLE and slow_query_log used to be OFF, but after running the pmm-admin add ... command, they are changed. We don't see any messages in this regard neither in the command output, nor in the logs. Following go full contents of /var/log/pmm-mysql-queries-0.log:

      # Version: percona-qan-agent 1.8.1
      # Basedir: /usr/local/percona/qan-agent
      # PID: 9122
      # API: 127.0.0.1/qan-api
      # UUID: 8701f3f605a942095f56c31dfbdfbaeb
      2018/03/16 01:20:08.041114 main.go:153: Starting agent...
      2018/03/16 01:20:08.041995 main.go:321: Agent is ready
      2018/03/16 01:20:08.050742 main.go:194: API is ready

       

      I understand this is due to the fact that --query-source is SLOWLOG by default, and that we most likely need it to be stored in a file (hence the change in log_output), and that is enabled (obviously ). However, I don't think that silently changing them is a good approach here.

       

      On the other hand, if we use --query-source set to PERFSCHEMA, we don't make analogous changes:

      (again, adding some extra \n after each command so that output is clearer)

      [root@pmm-1 ~]# mysql -e 'select @@global.log_output, @@global.slow_query_log, @@global.performance_schema;'
      +---------------------+-------------------------+-----------------------------+
      | @@global.log_output | @@global.slow_query_log | @@global.performance_schema |
      +---------------------+-------------------------+-----------------------------+
      | TABLE | 0 | 0 |
      +---------------------+-------------------------+-----------------------------+
      
      [root@pmm-1 ~]# mysql -e 'show global variables' > before.out
      
      [root@pmm-1 ~]# pmm-admin add mysql:queries --query-source perfschema
      OK, now monitoring MySQL queries from perfschema using DSN root:***@tcp(localhost:3306)
      
      [root@pmm-1 ~]# mysql -e 'show global variables' > after.out
      
      [root@pmm-1 ~]# diff before.out after.out
      [root@pmm-1 ~]#

      In this case, though, it may be useful to issue a warning, letting the user know that the Performance Schema is disabled, and that they will not be collecting any queries.

       

      Let me know your thoughts on this, or if I missed anything.

       

       

      Thanks!

      Agustín.

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  michael.coburn@percona.com Michael Coburn
                  Reporter:
                  agustin.gallego Agustín Gallego
                • Votes:
                  1 Vote for this issue
                  Watchers:
                  8 Start watching this issue

                  Dates

                  • Created:
                    Updated: