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.
MySQL with too many tables can lead to PMM Server overload due to
because of streaming too much time series data. It can also lead to too many
mysqld_exporter and extra load on MySQL. Therefore PMM
Server disables most consuming
mysqld_exporter collectors automatically
if there are more than 1000 tables.
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.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 |abbr.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. It’s better to create a user with only the necessary privileges for collecting data.
For example can 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 --username root --password root
Of course this user should have necessary privileges for collecting data. If
pmm user already exists, you can grant the required privileges as
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';