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

Allow pmm-client to advise and possibly set optimal settings for QAN

    XMLWordPrintable

    Details

      Description

      When adding nodes to PMM, one of the most common issues seems to be "no data in QAN".
      This is almost always a result of the MySQL configuration.

      I created a query to help quickly identify settings to change when collecting via the slowlog, as this is the most commonly used, although P_S could have an equivalent.

      Check for slow query settings
      SELECT @@hostname,
        variable_name, 
        CASE LOWER(variable_name)
         WHEN 'slow_query_log' THEN IF(variable_value = 'ON', 'OK', CONCAT('SET GLOBAL ', LOWER(variable_name), ' = ON; /* Current: ', variable_value, '*/'))
         WHEN 'long_query_time' THEN IF(variable_value = 0, 'OK', CONCAT('SET GLOBAL ', LOWER(variable_name), ' = 0; /* Current: ', variable_value, '*/'))
         WHEN 'log_slow_rate_limit' THEN IF(variable_value = 100, 'OK', CONCAT('SET GLOBAL ', LOWER(variable_name), ' = 100; /* Current: ', variable_value, '*/'))
         WHEN 'log_slow_rate_type' THEN IF(variable_value = 'query', 'OK', CONCAT('SET GLOBAL ', LOWER(variable_name), ' = "query"; /* Current: ', variable_value, '*/'))
         WHEN 'log_slow_verbosity' THEN IF(variable_value = 'microtime,query_plan,innodb', 'OK', CONCAT('SET GLOBAL ', LOWER(variable_name), ' = "full"; /* Current: ', variable_value, '*/'))
         WHEN 'log_slow_admin_statements' THEN IF(variable_value = 'ON', 'OK', CONCAT('SET GLOBAL ', LOWER(variable_name), ' = ON; /* Current: ', variable_value, '*/'))
         WHEN 'log_slow_slave_statements' THEN IF(variable_value = 'ON', 'OK', CONCAT('SET GLOBAL ', LOWER(variable_name), ' = ON; /* Current: ', variable_value, '*/'))
         WHEN 'slow_query_log_always_write_time' THEN IF(variable_value = 1, 'OK', CONCAT('SET GLOBAL ', LOWER(variable_name), ' = 1; /* Current: ', variable_value, '*/'))
         WHEN 'slow_query_log_use_global_control' THEN IF(variable_value = 'log_slow_filter,log_slow_rate_limit,log_slow_verbosity,long_query_time,min_examined_row_limit', 'OK', CONCAT('SET GLOBAL ', LOWER(variable_name), ' = "all"; /* Current: ', variable_value, '*/'))
         WHEN 'innodb_monitor_enable' THEN IF(variable_value = 'all', 'OK', CONCAT('SET GLOBAL ', LOWER(variable_name), ' = "all"; /* Current: ', variable_value, '*/'))
         WHEN 'userstat' THEN IF(variable_value = 'ON', 'OK', CONCAT('SET GLOBAL ', LOWER(variable_name), ' = ON; /* Current: ', variable_value, '*/'))
         ELSE CONCAT('Unexpected variable: ', LOWER(variable_name))
        END  AS variable_value
      FROM global_variables
      WHERE LOWER(variable_name) IN(
       'long_query_time',
       'log_slow_rate_limit',
       'log_slow_rate_type',
       'log_slow_verbosity',
       'log_slow_admin_statements',
       'log_slow_slave_statements',
       'slow_query_log',
       'slow_query_log_always_write_time',
       'slow_query_log_use_global_control',
       'innodb_monitor_enable',
       'userstat'
      ) 
      

      This can then be used with HAVING variable_value <> 'OK' or HAVING variable_value = 'OK'.

      I think that it would be rather useful if pmm-admin had a command to advise on settings that should be adjusted to get the most from PMM, with the option to apply them or output them in a mysql-client friendly manner to use with a pipe.

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  ceri.williams Ceri Williams
                • Votes:
                  2 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated: