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 help 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. The slow query log provides maximum details, but can impact performance on heavily loaded systems. On Percona Server the query sampling feature may reduce the performance impact.
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
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 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 Configuring Performance Schema.
If you are running MySQL 5.5 or MariaDB 5.5, configure logging only slow queries to avoid high performance overhead.
This may affect the quality of monitoring data gathered by QAN.
log_output=file slow_query_log=ON long_query_time=0 log_slow_admin_statements=ON log_slow_slave_statements=ON
When adding a MySQL instance to monitoring, you can specify the MySQL server
superuser account credentials. However, monitoring with the superuser account
is not secure. If you also specify the
--create-user option, it will create
a user with only the necessary privileges for collecting data.
You can also set up the
pmm user manually with necessary privileges and pass
its credentials when adding the instance.
To enable complete MySQL instance monitoring, a command similar to the following is recommended:
sudo pmm-admin add mysql --user root --password root --create-user
The superuser credentials are required only to set up the
pmm user with
necessary privileges for collecting data. If you want to create this user
yourself, the following privileges are required:
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@' localhost' IDENTIFIED BY 'pass' WITH MAX_USER_CONNECTIONS 10; GRANT SELECT, UPDATE, DELETE, DROP ON performance_schema.* TO 'pmm'@'localhost';
pmm user already exists,
simply pass its credential when you add the instance:
sudo pmm-admin add mysql --user pmm --password pass
For more information, run as root
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.
These are the queries the execution time of which is above a certain
threshold. The threshold is defined by the
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.
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
capture every 100th query for the slow query log.
When using query sampling, set
so that it applies to queries, rather than sessions.
It is also a good idea to set
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
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
The default source of query data for PMM is the slow query log. It is available in MySQL 5.1 and later versions. Starting from MySQL 5.6 (including Percona Server 5.6 and later), you can choose to parse query data from the Performance Schema instead of slow query log. Starting from MySQL 5.6.6, Performance Schema is enabled by default.
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 a better alternative.
To use Performance Schema, set the
performance_schema variable to
mysql> SHOW VARIABLES LIKE 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | ON | +--------------------+-------+
If this variable is not set to ON, add the the following lines to the
MySQL configuration file
my.cnf and restart MySQL:
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)
Performance Schema instrumentation is enabled by default in MySQL 5.6.6 and later versions. It is not available at all in MySQL versions prior to 5.6.
If certain instruments are not enabled, you will not see the corresponding
graphs in the MySQL Performance Schema Dashboard dashboard. To enable
full instrumentation, set the option
'%=on' when starting the MySQL server.
$ mysqld --performance-schema-instrument='%=on'
This option can cause additional overhead and should be used with care.
If the instance is already running, configure the QAN agent to collect data from Performance Schema:
Performance Schemain the Collect from drop-down list.
If you are adding a new monitoring instance with the pmm-admin tool, use the
--query-source perfschema option:
Run this command as root or by using the sudo command
pmm-admin add mysql --user root --password root --create-user --query-source perfschema
For more information, run
MySQL 8 (in version 8.0.4) changes the way clients are authenticated by
default_authentication_plugin parameter is set to
caching_sha2_password. This change of the default value implies that MySQL
drivers must support the SHA-256 authentication. Also, the communication channel
with MySQL 8 must be encrypted when using
The MySQL driver used with PMM does not yet support the SHA-256 authentication.
With currently supported versions of MySQL, PMM requires that a dedicated MySQL
user be set up. This MySQL user should be authenticated using the
mysql_native_password plugin. Although MySQL is configured to support SSL
clients, connections to MySQL Server are not encrypted.
There are two workarounds to be able to add MySQL Server version 8.0.4 or higher as a monitoring service to PMM:
Altering the MySQL User
Provided you have already created the MySQL user that you plan to use with PMM, alter this user as follows:
mysql> ALTER USER pmm@'localhost' IDENTIFIED WITH mysql_native_password BY '$eCR8Tp@s$w*rD';
Then, pass this user to
pmm-admin add as the value of the
This is a preferred approach as it only weakens the security of one user.
Changing the global MySQL Configuration
A less secure approach is to set
to the value mysql_native_password before adding it as a
monitoring service. Then, restart your MySQL Server to apply this
Not all dashboards in Metrics Monitor are available by default for all MySQL variants and configurations: Oracle‘s MySQL, Percona Server. or MariaDB. 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
they all are enabled all the time. To enable all InnoDB metrics, set the
mysql> SET GLOBAL innodb_monitor_enable=all
User statistics is a feature of 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
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.
This feature causes very high overhead, especially on systems processing more than 10000 queries per second. Use it only temporarily when troubleshooting problems.
To enable collection of query response time:
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';
Set the global varible
mysql> SET GLOBAL query_response_time_stats=ON;
For general inquiries about our open source software and database management tools, please send us your question and someone will contact you.