Configuring MySQL for Best Results¶

Configuring the slow query log in Percona Server¶

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 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.

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.

Configuring Performance Schema¶

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 ON:

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:

[mysql]
performance_schema=ON

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)

$ mysqld --performance-schema-instrument='%=on'

If the instance is already running, configure the QAN agent to collect data from Performance Schema:

1. Open the Query Analytics dashboard.
2. Click the Settings button.
3. Open the Settings section.
4. Select Performance Schema in the Collect from drop-down list.
5. Click Apply to save changes.

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 pmm-admin add mysql --help.

Configuring MySQL 8.0 for PMM¶

MySQL 8 (in version 8.0.4) changes the way clients are authenticated by default. The 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 caching_sha2_password.

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:

1. Alter the MySQL user that you plan to use with PMM
2. Change the global MySQL configuration

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 --user parameter.

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 default_authentication_plugin to the value mysql_native_password before adding it as a monitoring service. Then, restart your MySQL Server to apply this change.

[mysqld]
default_authentication_plugin=mysql_native_password

Settings for Dashboards¶

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.

mysql> SET GLOBAL innodb_monitor_enable=all