Use Managed Service for Prometheus to monitor MySQL databases - Managed Service for Prometheus

This topic describes how to use Alibaba Cloud Managed Service for Prometheus to monitor MySQL databases.

Prerequisites

Prometheus instances are created. For more information, see the following topics:
- Create a Prometheus instance to monitor an ACK cluster
- Create a Prometheus instance to monitor an ECS instance
The information about the MySQL database that you want to monitor, such as the address, the port number, and the username and password that are used to connect to the MySQL database, is available.

Step 1: Install and configure a MySQL exporter

Log on to the Managed Service for Prometheus console.
In the left-side navigation pane, click Instances.
Click the name of the Prometheus instance instance that you want to manage to go to the Integration Center page.
Install or add the MySQL component.
- If you install the MySQL component for the first time, perform the following operation:
  In the Not Installed section of the Integration Center page, find the MySQL component and click Install.
- If you have installed the MySQL component, add the component again:
  In the Installed section of the Integration Center page, click Add next to MySQL.

On the Settings tab in the STEP2 section, configure the parameters and click OK. The following table describes the parameters.

Parameter	Description
Component Name	The unique name of the MySQL exporter. The name must meet the following requirements: It can contain only lowercase letters, digits, and hyphens (-), and cannot start or end with a hyphen (-). It must be unique. Note If you do not specify this parameter, the system uses the default name, which consists of the exporter type and a numeric suffix.
MySQL URL	The URL that is used to access the MySQL database. Note The URL of the MySQL database that is deployed in ACK, ECS, or ApsaraDB RDS is automatically displayed in the drop-down list.
MySQL Port	The port number of the MySQL database. Example: 3306.
Username	The username and password of the MySQL database. Important We recommend that you do not use an administrator account to avoid data breach risks. Create a MySQL account for mysqld_exporter and grant minimum permissions to the account. For more information, see Permissions required for mysqld_exporter to access a MySQL database.
Password

Note

You can view the monitoring metrics on the Metrics tab in the STEP2 section.

You can also configure the resource quotas for the exporter in the Advanced Settings: Exporter Resource Limits section. The default value of the CPU (Cores) parameter is 100m, and the default value of the memory parameter is 50Mi.

The installed components are displayed in the Installed section of the Integration Center page. Click the component. In the panel that appears, you can view information such as targets, metrics, dashboard, alerts, service discovery configurations, and exporters. For more information, see Integration center.

Step 2: Configure alert rules for the MySQL exporter

On the Integration Center page in the Application Real-time Monitoring Service (ARMS) console, click the MySQL exporter in the Installed section. In the panel that appears, click the Alerts tab to view the alert rules for MySQL databases.

Managed Service for Prometheus provides multiple default alert rules based on key MySQL metrics. You can add alert rules based on your business requirements. For information about how to add alert rules, see Create an alert rule for a Prometheus instance.

Note

For information about the key MySQL metrics, see Key metrics.
For information about the default alert rules, see Use Managed Service for Prometheus to view monitoring data and configure alert rules.

Step 3: View the dashboards for the MySQL database

You can use the dashboards to view monitoring data, such as the service availability, database queries, network traffic, connections, and memory usage. To find the dashboards, you can perform the following steps:

Log on to the Managed Service for Prometheus console.
In the left-side navigation pane, click Instances.
Click the name of the Prometheus instance instance that you want to manage to go to the Integration Center page.
Click the MySQL exporter in the Installed section. In the panel that appears, click the Dashboards tab and then click the thumbnail of the Grafana dashboard that you want to view. You can view the following dashboards:
- Service availability, queries per second (QPS), and database connections
- Database queries
- Traffic and memory usage
- File monitoring data

Key metrics

Type	Metric	Description
Availability	mysql_up	Indicates whether the MySQL database is available.
Availability	mysql_global_status_uptime	Indicates the running duration of the MySQL database. You can add an alert rule based on this metric to identify MySQL databases whose running duration is less than 30 minutes.
Database connections	mysql_global_status_connection_errors_total	Indicates connection errors that are one of the major database errors. You can use this metric to view the information about a specific error and the number of connection errors.
	mysql_global_status_threads_connected	Indicates the number of threads on which connections to the MySQL database are established.
	mysql_global_status_threads_running	Indicates the number of threads on which the requests to connect to the MySQL database are sent but do not succeed yet.
	mysql_global_status_max_used_connections	Indicates the maximum number of connections to the MySQL database.
	mysql_global_variables_max_connections	Indicates the upper limit on the connections to the MySQL database. When the number of connections reaches the upper limit, requests to establish connections are denied.
	mysql_global_status_aborted_connects	Indicates the failed connection attempts.
	mysql_global_status_aborted_clients	Indicates the connections that timed out.
Queries	mysql_global_status_slow_queries	Indicates the slow queries of the MySQL database.
Queries	mysql_global_status_queries	Indicates the QPS of the MySQL database.
Network traffic	mysql_global_status_bytes_received	Indicates inbound traffic.
Network traffic	mysql_global_status_bytes_sent	Indicates outbound traffic.
File monitoring data	mysql_global_status_opened_files	Indicates the files that are being opened.
	mysql_global_status_open_files	Indicates the files that are opened in the MySQL database.
	mysql_global_variables_open_files_limit	Indicates the files that can be opened.
	mysql_global_status_innodb_num_open_files	Indicates the files that are opened by InnoDB.

Use Managed Service for Prometheus to view monitoring data and configure alert rules

If you use a self-managed Managed Service for Prometheus to monitor a MySQL database, you must perform the following complex operations: install the MySQL exporter, enter information about the connection to the MySQL database, configure service discovery, and build dashboards. Alibaba Cloud Managed Service for Prometheus provides the built-in MySQL exporter and out-of-the-box dashboards and alert configurations in the visualized console. This simplifies service deployment and reduces workloads.

Alibaba Cloud Managed Service for Prometheus creates default alert rules for MySQL databases based on practices. You can use the default alert rules to quickly configure your dashboards and alerting system. Alibaba Cloud Managed Service for Prometheus provides the default alert rules based on the following metrics:

MySQL database availability: If the metric value is 0, the MySQL database is unavailable. If the metric value is 1, the MySQL database is running as expected. You can replace the ${instance} variable with the MySQL database that you want to monitor.
```
mysql_up{${instance}} != 1
```
Running duration of the MySQL database: Managed Service for Prometheus provides a default alert threshold of 30 minutes. You can change the threshold based on your business requirements.
```
mysql_global_status_uptime{${instance}} < 1800
```
Slow queries in the MySQL database: You can use this metric to identify SQL statements that can be improved in the database.
```
rate(mysql_global_status_slow_queries{${instance}}[5m]) > 0
```
Connection errors of the MySQL database: Connection errors are one of the major database errors. Managed Service for Prometheus provides an alert rule based on this metric. When an alert is generated, you can receive a notification that contains the error type, QPS, and other error details.
```
rate(mysql_global_status_connection_errors_total{${instance}}[5m]) > 0
```
Connection usage of the MySQL database: Most connection errors are caused by insufficient number of connections. You can troubleshoot connection errors based on this metric.
```
100 * mysql_global_status_threads_connected{${instance}} 
      / mysql_global_variables_max_connections{${instance}} > 90
```
When the connection usage reaches the upper limit, the MySQL database denies connection requests. To resolve this issue, you can increase the upper limit. Before you increase the upper limit on connections, you must run the following command to check the upper limit on the files that can be opened.
```
mysql_global_variables_open_files_limit - mysql_global_variables_innodb_open_files
```
Log wait time of the MySQL database: You can specify a period of time during which a log write must wait for the undo log to be flushed.
```
rate(mysql_global_status_innodb_log_waits{${instance}}[5m])
```