PMM supports all commonly used variants of MySQL, including Percona Server, MariaDB, and Amazon RDS. To prevent data loss and performance issues, PMM does not automatically change MySQL configuration. However, there are certain recommended settings that will maximize monitoring efficiency. These recommendations depend on the variant and version of MySQL you are using, and mostly apply to very high loads.
PMM can collect query data either from the slow query log or from Performance Schema. Using the slow query log to capture all queries provides maximum details, but can impact performance on heavily loaded systems unless it is used with the query sampling feature available only in Percona Server. Performance Schema is generally better for recent versions of other MySQL variants. For older MySQL variants, which have neither sampling, nor Performance Schema, configure logging only slow queries.
You can add configuration examples provided in this guide
to my.cnf and restart the server
or change variables dynamically using the following syntax:
SET GLOBAL <var_name>=<var_value>
The following sample configurations can be used depending on the variant and version of MySQL:
If you are running Percona Server (or Percona XtraDB Cluster), configure the slow query log to capture all queries and enable sampling. This will provide the most amount of information with the lowest overhead.
log_output=file
slow_query_log=ON
long_query_time=0
log_slow_rate_limit=100
log_slow_rate_type=query
log_slow_verbosity=full
log_slow_admin_statements=ON
log_slow_slave_statements=ON
slow_query_log_always_write_time=1
slow_query_log_use_global_control=all
innodb_monitor_enable=all
userstat=1
If you are running MySQL 5.6+ or MariaDB 10.0+, configure Performance Schema.
innodb_monitor_enable=all
performance_schema=ON
If you are running MySQL 5.5 or MariaDB 5.5, configure logging only slow queries to avoid high performance overhead.
Note
This may affect the quality of monitoring data gathered by Query Analytics.
log_output=file
slow_query_log=ON
long_query_time=0.01
log_slow_admin_statements=ON
log_slow_slave_statements=ON
If you are running Percona Server, a properly configured slow query log will provide the most amount of information with the lowest overhead. In other cases, use Performance Schema if it is supported.
By definition, the slow query log is supposed to capture only slow queries.
That is, queries with execution time above a certain threshold,
which is defined by the long_query_time variable.
In heavily loaded applications, frequent fast queries can actually have
a much bigger impact on performance than rare slow queries.
To ensure comprehensive analysis of your query traffic,
set the long_query_time to 0 so that all queries are captured.
However, capturing all queries can consume I/O bandwidth and cause the slow query log file to quickly grow very large. To limit the amount of queries captured by the slow query log, use the query sampling feature available in Percona Server.
The log_slow_rate_limit variable defines the fraction of queries
captured by the slow query log.
A good rule of thumb is to have approximately 100 queries logged per second.
For example,
if your Percona Server instance processes 10 000 queries per second,
you should set log_slow_rate_limit to 100
and capture every 100th query for the slow query log.
Note
When using query sampling, set log_slow_rate_type to query
so that it applies to queries, rather than sessions.
It is also a good idea to set log_slow_verbosity to full
so that maximum amount of information about each captured query
is stored in the slow query log.
A possible problem with query sampling is that rare slow queries
might not get captured at all.
To avoid this, use the slow_query_log_always_write_time variable
to specify which queries should ignore sampling.
That is, queries with longer execution time
will always be captured by the slow query log.
By default, slow query log settings apply only to new sessions.
If you want to configure the slow query log during runtime
and apply these settings to existing connections,
set the slow_query_log_use_global_control variable to all.
Performance Schema is not as data-rich as the slow query log, but it has all the critical data and is generally faster to parse. If you are not running Percona Server (which supports sampling for the slow query log), then Performance Schema is the better alternative.
As of MySQL 5.6 (including MariaDB 10.0+ and Percona Server 5.6+), Performance Schema is enabled by default with no additional configuration required.
If you are running a custom Performance Schema configuration,
make sure that the statements_digest consumer is enabled:
mysql> select * from setup_consumers;
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | NO |
| events_transactions_current | NO |
| events_transactions_history | NO |
| events_transactions_history_long | NO |
| events_waits_current | NO |
| events_waits_history | NO |
| events_waits_history_long | NO |
| global_instrumentation | YES |
| thread_instrumentation | YES |
| statements_digest | YES |
+----------------------------------+---------+
15 rows in set (0.00 sec)
For more information about using Performance Schema in PMM, see Performance Schema.
Not all dashboards in Metrics Monitor are available by default for all MySQL variants and configurations. Some graphs require Percona Server, specialized plugins, or additional configuration.
Collecting metrics and statistics for graphs increases overhead. You can keep collecting and graphing low-overhead metrics all the time, and enable high-overhead metrics only when troubleshooting problems.
InnoDB metrics provide detailed insight about InnoDB operation.
Although you can select to capture only specific counters,
their overhead is low even when all them are enabled all the time.
To enable all InnoDB metrics,
set the global innodb_monitor_enable variable to all:
mysql> SET GLOBAL innodb_monitor_enable=all
User statistics is a feature available in Percona Server and MariaDB. It provides information about user activity, individual table and index access. In some cases, collecting user statistics can lead to high overhead, so use this feature sparingly.
To enable user statistics, set the userstat variable to 1.
With MySQL version 5.6 or later,
Performance Schema instrumentation is enabled by default.
If certain instruments are not enabled,
you will not see the corresponding graphs
in the Performance Schema dashboard.
To enable full instrumentation,
set the --performance_schema_instrument option to '%=on' at startup:
mysqld --performance-schema-instrument='%=on'
Note
This option can cause additional overhead and should be used with care.
Query response time distribution is a feature available in Percona Server. It provides information about changes in query response time for different groups of queries, often allowing to spot performance problems before they lead to serious issues.
Note
This feature causes very high overhead, especially on systems processing more than 10 000 queries per second. Use it only temporarily when troubleshooting problems.
To enable collection of query response time:
Install the QUERY_RESPONSE_TIME plugins:
mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME_AUDIT SONAME 'query_response_time.so';
mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME SONAME 'query_response_time.so';
mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME_READ SONAME 'query_response_time.so';
mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME_WRITE SONAME 'query_response_time.so';
For more information, see this guide
Set the global query_response_time_stats varible to ON:
mysql> SET GLOBAL query_response_time_stats=ON;
For general inquiries, please send us your question and someone will contact you.
