Monitoring MySQL

Configuration

In order to enable in-depth metric monitoring, you need to inform the Agent about the credentials to access monitoring information. You can configure it in the agent <agent_install_dir>/etc/instana/configuration.yaml:

com.instana.plugin.mysql:
  user: ''
  password: ''
  schema_excludes: ['INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA']

Schema names inserted in the schema_excludes field will be excluded from gathering statistics per schema and from monitoring in Schemas graph.

Required DB Permissions

For the sensor to be able to collect performance information about the DBMS, read-only access to the schema PERFORMANCE_SCHEMA is required.

This is an example how to create an account with the required rights:

GRANT SELECT ON PERFORMANCE_SCHEMA.* TO '<instana_mon_user>'@'localhost' IDENTIFIED BY 'instana_mon_pwd';
GRANT REPLICATION CLIENT ON *.* TO '<instana_mon_user>'@'localhost' IDENTIFIED BY 'instana_mon_pwd';

For MySQL version 8.0.0 and above:

GRANT SELECT ON PERFORMANCE_SCHEMA.* TO '<instana_mon_user>'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO '<instana_mon_user>'@'localhost';

MySQL version 8.0.0 and above support

Instana cannot provide automatic MySQL monitoring for MySQL, version 8.0.0 and above, for licensing reasons. Therefore, the MySQL driver jar file needs to be manually obtained from https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.20 and added to the Instana agent deploy folder: <agent_install_dir>/deploy/
The MySQL driver jar file obtained from Maven repository should have version 8.0.16 or newer.

Metrics collection

Configuration data

  • Process ID
  • Port
  • Version
  • Start time
  • Role
  • Max connections

Performance metrics

Metric Description Granularity
Queries The total number of queries 1 second
SELECTS The number of select queries 1 second
UPDATES The number of update queries 1 second
INSERTS The number of insert queries 1 second
DELETES The number of delete queries 1 second
OTHER The number of other queries 1 second
Slow Queries The number of slow queries 1 second
Errors The number of errors 1 second
Connections The number of connections 1 second
Wait Events io file, socket, table; sync cond, mutex, rwlock 1 second
Max used connections The maximum number of used connections 1 second
Aborted connects The number of aborted connections 1 second
Read Requests The number of read requests 1 second
Write Requests The number of write requests 1 second
Queries per schema The number of queries for every monitored schema 1 second
Average Query Latency per schema The average query latency for every monitored schema 1 second

Health Signatures

For each sensor, there is a curated knowledgebase of health signatures that are evaluated continuously against the incoming metrics and are used to raise issues or incidents depending on user impact.

Built-in events trigger issues or incidents based on failing health signatures on entities, and custom events trigger issues or incidents based on the thresholds of an individual metric of any given entity.

For information about built-events for the MySQL sensor, see the Built-in events reference.

Troubleshooting

Performance Schema disabled by default

If an error message, similar to:

java.sql.SQLSyntaxErrorException: (conn=2) Table 'performance_schema.global_status' doesn't exist

appears in Instana agent log, it is most likely, that your MySQL server copy has been compiled with performance schema disabled by default. This is a known bug.

To fix this, you could try to enable it from MySQL CLI:

mysql> set @@global.show_compatibility_56=ON;
Query OK, 0 rows affected (0.00 sec)

and to check it's enabled:

mysql>select @@show_compatibility_56;
+-------------------------+
| @@show_compatibility_56 |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

Once enabled, mysql>show status; should now work. Please restart Instana agent.

Timezone setup

If an error message, similar to:

The server time zone value 'CEST' is unrecognized or represents more than one time zone.
You must configure either the server or JDBC driver (via the serverTimezone configuration property) 
to use a more specifc time zone value if you want to utilize time zone support.

appears in Instana agent log, the server timezone needs to be configured.

This is a known issuehttps://bugs.mysql.com/bug.php?id=90813 since MySQL is not reading the host time-zone appropriately.

The error can be fixed as explained in the MySQL Server Time Zone Support.