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

    • Type: New Feature
    • Status: On Hold
    • Priority: Medium
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: PMM Client
    • Labels:
      None

      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: