MySQL and variants¶

PMM Client collects metrics from MySQL, Percona Server for MySQL, Percona XtraDB Cluster, and MariaDB. (Amazon RDS is also supported and explained in a separate section.)

This page shows you how to set up PMM to monitor a MySQL or MySQL-based database instance. (You should read it completely before starting work.)

Here is an overview of the steps involved.

Before you start¶

• PMM Server is installed and running with a known IP address accessible from the client node.
• PMM Client is installed and the node registered with PMM Server.
• You have superuser (root) access on the client host.
• You have superuser access to any database servers that you want to monitor.

Create a database account for PMM¶

It is good practice to use a non-superuser account to connect PMM Client to the monitored database instance. This example creates a database user with name pmm, password pass, and the necessary permissions.

CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'pass' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@'localhost';

Choose and configure a source¶

Decide which source of metrics to use, and configure your database server for it. The choices are Slow query log and Performance Schema.

While you can use both at the same time we recommend using only one–there is some overlap in the data reported, and each incurs a small performance penalty. The choice depends on the version and variant of your MySQL instance, and how much detail you want to see.

Here are the benefits and drawbacks of Slow query log and Performance Schema metrics sources.

Data source recommendations

Slow query log¶

This section covers how to configure a MySQL-based database server to use the slow query log as a source of metrics.

Applicable versions

The slow query log records the details of queries that take more than a certain amount of time to complete. With the database server configured to write this information to a file rather than a table, PMM Client parses the file and sends aggregated data to PMM Server via the Query Analytics part of PMM Agent.

Settings

Examples

Configuration file

slow_query_log=ON
log_output=FILE
long_query_time=0
log_slow_admin_statements=ON
log_slow_slave_statements=ON

Session

SET GLOBAL slow_query_log = 1;
SET GLOBAL log_output = 'FILE';
SET GLOBAL long_query_time = 0;
SET GLOBAL log_slow_admin_statements = 1;
SET GLOBAL log_slow_slave_statements = 1;

Slow query log – extended¶

Some MySQL-based database servers support extended slow query log variables.

Applicable versions

Settings

Examples

Configuration file (Percona Server for MySQL, Percona XtraDB Cluster)

log_slow_rate_limit=100
log_slow_rate_type='query'
slow_query_log_always_write_time=1
log_slow_verbosity='full'
slow_query_log_use_global_control='all'

Configuration file (MariaDB)

log_slow_rate_limit=100

Session (Percona Server for MySQL, Percona XtraDB Cluster)

SET GLOBAL log_slow_rate_limit = 100;
SET GLOBAL log_slow_rate_type = 'query';
SET GLOBAL slow_query_log_always_write_time = 1;
SET GLOBAL log_slow_verbosity = 'full';
SET GLOBAL slow_query_log_use_global_control = 'all';

Slow query log rotation¶

Slow query log files can grow quickly and must be managed.

When adding a service with the command line use the pmm-admin option --size-slow-logs to set at what size the slow query log file is rotated. (The size is specified as a number with a suffix. See pmm-admin add mysql.)

When the limit is reached, PMM Client will:

• remove the previous .old slow log file,
• rename the current file by adding the suffix .old,
• execute the MySQL command FLUSH LOGS.

Only one .old file is kept. Older ones are deleted.

You can manage log rotation yourself, for example, with logrotate. If you do, you can disable PMM Client’s log rotation with the --slow-log-rotation=false option when adding a service with pmm-admin add.

Performance Schema¶

This section covers how to configure a MySQL-based database server to use Performance Schema as a source of metrics.

Applicable versions

PMM’s MySQL Performance Schema Details dashboard charts the various performance_schema metrics.

To use Performance Schema, set these variables.

Examples

Configuration file

performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-statements-digest=ON
innodb_monitor_enable=all

Session

(performance_schema cannot be set in a session and must be set at server start-up.)

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES' WHERE NAME LIKE '%statements%';
SET GLOBAL innodb_monitor_enable = all;

MariaDB 10.5.7 or lower

There is no Explain or Example data shown by default in Query Analytics when monitoring MariaDB instances version 10.5.7 or lower. A workaround is to set this variable.

Session

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE 'statement/%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%statements%';

Query response time¶

Query time distribution is a chart in the Details tab of Query Analytics showing the proportion of query time spent on various activities. It is enabled with the query_response_time_stats variable and associated plugins.

Applicable versions

Set this variable to see query time distribution charts.

Configuration file

query_response_time_stats=ON

You must also install the plugins.

Session

1. Check that /usr/lib/mysql/plugin/query_response_time.so exists.

2. Install the plugins and activate.

   For MariaDB 10.3:

   INSTALL PLUGIN QUERY_RESPONSE_TIME_AUDIT SONAME 'query_response_time.so';
   INSTALL PLUGIN QUERY_RESPONSE_TIME SONAME 'query_response_time.so';
   SET GLOBAL query_response_time_stats = ON;
   

   For Percona Server for MySQL 5.7:

   INSTALL PLUGIN QUERY_RESPONSE_TIME_AUDIT SONAME 'query_response_time.so';
   INSTALL PLUGIN QUERY_RESPONSE_TIME SONAME 'query_response_time.so';
   INSTALL PLUGIN QUERY_RESPONSE_TIME_READ SONAME 'query_response_time.so';
   INSTALL PLUGIN QUERY_RESPONSE_TIME_WRITE SONAME 'query_response_time.so';
   SET GLOBAL query_response_time_stats = ON;
   

Tablestats¶

Some table metrics are automatically disabled when the number of tables exceeds a default limit of 1000 tables. This prevents PMM Client from affecting the performance of your database server.

The limit can be changed when adding a service on the command line with the two pmm-admin options:

User statistics¶

Applicable versions

User activity, individual table and index access details are shown on the MySQL User Details dashboard when the userstat variable is set.

Examples

Configuration file

userstat=ON

Session

SET GLOBAL userstat = ON;

Add a service¶

When you have configured your database server, you can add a MySQL service with the user interface or on the command line.

When adding a service with the command line, you must use the pmm-admin --query-source=SOURCE option to match the source you’ve chosen and configured the database server for.

With the PMM user interface, you select Use performance schema, or deselect it to use slow query log.

With the user interface¶

1. Select PMM → PMM Add Instance.
2. Select MySQL – Add a remote instance.

3. Enter values for these fields.

   Section	Field	Required	Description	Default	pmm-admin parameter
   Main details
   	Hostname	☑️	Hostname or IP address of the service		--address
   	Service name		Service name		--name
   	Port		Port for accessing the service	3306	port in --address=address[:port]
   	Username		MySQL user name		--username
   	Password		MySQL user password		--password
   Labels
   	Environment				--environment
   	Region
   	Availability zone
   	Replication set				--replication-set
   	Cluster				--cluster
   	Custom labels				--custom-labels
   Additional options
   	Skip connection check				--skip-connection-check
   	Use TLS for database connections				--tls
   	Skip TLS certificate and hostname validation				--tls-skip-verify
   	Table statistics limit
   	→ Disabled				--disable-tablestats
   	→ Default				--disable-tablestats-limit
   	→ Custom				--disable-tablestats-limit
   	Use performance schema				--perfschema if selected, --slowlog if not.

4. Click Add service.

On the command line¶

1. Configure PMM Client (connect to example PMM Server at address 192.168.1.123).

   pmm-admin config --server-insecure-tls --server-url=https://192.168.1.123:443
   

2. Add the database server as a service using one of these example commands. If successful, PMM Client will print MySQL Service added with the service’s ID and name. Use the --environment and -custom-labels options to set tags for the service that help you distinguish them.

Examples – Slow query log

Default query source (slowlog), service name ({node name}-mysql), and service address/port (127.0.0.1:3306), with database server account pmm and password pass.

sudo pmm-admin add mysql --username=pmm --password=pass

Slow query log source and log size limit (1 gigabyte), service name (MYSQL_NODE) and service address/port (191.168.1.123:3306).

sudo pmm-admin add mysql --query-source=slowlog --size-slow-logs=1GB --username=pmm --password=pass MYSQL_NODE 192.168.1.123:3306

Slow query log source, disabled log management (use logrotate or some other log management tool), service name (MYSQL_NODE) and service address/port (191.168.1.123:3306).

sudo pmm-admin add mysql --query-source=slowlog --size-slow-logs=false --username=pmm --password=pass MYSQL_NODE 192.168.1.123:3306

Default query source (slowlog), service name ({node}-mysql), connect via socket.

sudo pmm-admin add mysql --username=pmm --password=pass --socket=/var/run/mysqld/mysqld.sock

Examples – Performance Schema

Performance schema query source, service name (MYSQL_NODE) and default service address/port (127.0.0.1:3306)

sudo pmm-admin add mysql --query-source=perfschema --username=pmm --password=pass MYSQL_NODE

Performance schema query source, service name (MYSQL_NODE) and default service address/port (127.0.0.1:3306) specified with flags.

sudo pmm-admin add mysql --query-source=perfschema --username=pmm --password=pass --service-name=MYSQL_NODE --host=127.0.0.1 --port=3306

Examples – Identifying services

Default query source (slowlog), environment labeled test, custom labels setting source to slowlog. (This example uses positional parameters for service name and service address.)

pmm-admin add mysql --environment=test --custom-labels='source=slowlog'  --username=root --password=password --query-source=slowlog MySQLSlowLog localhost:3306

Check the service¶

Check service - PMM user interface

1. Go to PMM → PMM Inventory.
2. Look in the Services tab for a matching Service Type (MySQL), Service name, Addresses, and any other details entered in the form.
3. Look in the Agents tab to check the desired data source is being used.

Check service - Command line

Look for your service in the output of this command.

pmm-admin inventory list services

Check data

1. Open the MySQL Instance Summary dashboard.
2. Set the Service Name to the newly-added service.

Percona Server for MySQL, MariaDB

If query response time plugin was installed, check for data in the MySQL Query Response Time Details dashboard or select a query in PMM Query Analytics to see the Query time distribution bar.

Percona XtraDB Cluster

Open the PXC/Galera Cluster Summary dashboard.

See also

• Percona Server for MySQL – Slow Query Log Extended

• Percona Server for MySQL – User Statistics

• MariaDB – Slow Query Log Overview

• MariaDB – Slow Query Log Extended Statistics

• MariaDB – User Statistics

• Percona Blog – PERFORMANCE_SCHEMA vs Slow Query Log

• Percona Blog – MySQL’s INNODB_METRICS table

• Percona Blog – Rotating MySQL Slow Logs Safely

• Percona Blog – Impact of logging on MySQL’s performance