Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Observability | How to Use Prometheus to Monitor MySQL

Observability | How to Use Prometheus to Monitor MySQL

Part 3 of this series discusses building a metric observation and alert system based on Prometheus.

By Zaifeng

As one of the most popular relational database management systems, MySQL has existed in the backend storage of many systems and is widely used in all walks of life. At the same time, as the core component of application services, the database directly affects the operation of application services. The bottleneck of the database is often the bottleneck of the entire system. Therefore, it is essential to monitor MySQL. Detecting exceptions in MySQL operation in a timely manner can improve system availability and user experience. Therefore, it is an important task for the O&M Team to observe the key metrics of MySQL and monitor the availability and performance of databases in real-time.

Key Metrics Interpretation

Before building a MySQL metric observation system, we need to sort the dimensions and metrics we pay attention to in the daily O&M process to complete the task better and faster. Google proposes Latency, Traffic, Saturation, and Errors as the golden metrics for system monitoring. As MySQL emerges as a resource service system, we refine the metrics from five dimensions: availability, database connection, query, traffic, and file.

1

Building a Metric Observation and Alert System Based on Prometheus

After designing the required observation metrics, we can select the corresponding observation tools. As the most popular database, MySQL has a wide range of monitoring tools for us to choose from (such as MySQL Enterprise Monitor, Prometheus, and other database-based, commercial, open-source tools with different attributes).

Prometheus has become the best choice for MySQL metric monitoring in the cloud-native era, as it meets enterprise-level requirements (such as open-source friendliness, avoiding vendor lock-in, and building a multi-cloud full-stack observable system). Prometheus has MySQL Exporter specially designed and developed by the community for collecting MySQL database monitoring metrics.

Compared with self-managed Prometheus, Alibaba Cloud Prometheus needs to deploy Exporter, pass in the connection information of MySQL instances, configure service discovery, and create dashboards. Alibaba Cloud Prometheus Monitoring integrates with MySQL Exporter quickly and provides an out-of-the-box dedicated monitoring dashboard and alerts. It makes configurations and operations automatic, reducing the workload of configuration services.

Based on Alibaba Cloud practices, it transforms the common MySQL alert rules into preset templates to help O&M teams quickly build kanban and alert systems without worrying about refining their experience or selecting alert metrics.

  • MySQL Shutdown: If the value of this metric is 0, the current database is not running as expected. If the value is 1, the database is running as expected. You can use ${instance} to generate alerts for specific instances.
mysql_up{${instance}} != 1
  • MySQL Instance Runtime: Prometheus Service provides default alert thresholds to monitor MySQL instances that run for less than half an hour. You can modify the thresholds based on your requirements.
mysql_global_status_uptime{${instance}} < 1800
  • MySQL Slow Query: This metric can be used to determine whether the current database has SQL statements that need to be optimized.
rate(mysql_global_status_slow_queries{${instance}}[5m]) > 0
  • MySQL Connection Errors: Connection error is one of the major errors in the database. Through the alert rules provided by Prometheus Services, when an alert is triggered, you can receive alert information (such as the error type and the number of queries).
rate(mysql_global_status_connection_errors_total{${instance}}[5m]) > 0
  • MySQL Connection Usage: If a connection error is reported, it is mostly because the number of connections is insufficient. You can view the MySQL connection usage to troubleshoot the problem.
100 * mysql_global_status_threads_connected{${instance}}
/ mysql_global_variables_max_connections{${instance}} > 90

Note: When the usage reaches a certain threshold, the MySQL instance starts to reject connections. You can solve the problem by increasing the number of connections. However, before you increase the number of connections, be sure to check the number of files that can be opened on the current system using the following statement:

mysql_global_variables_open_files_limit - mysql_global_variables_innodb_open_files
  • MySQL Log Wait Time
rate(mysql_global_status_innodb_log_waits{${instance}}[5m])

Best Practices

Precondition

  • Open Alibaba Cloud Prometheus Service
  • Install an Alibaba Cloud Prometheus instance (Prometheus for Container Service and Prometheus for ECS). Please see Create a Prometheus Instance for more information
  • Prepare the connection information of the MySQL instance, including the MySQL address, MySQL port, user name, and password

Install MySQL Monitoring in the Integration Center

2

  • Enter the MySQL address, MySQL port, user name, and password.

3

Note: Connectivity can be checked through the connection test.

  • After MySQL is installed, you can view information, including dashboard, metrics, and target, and configure related alerts.

4
5

Alert Configuration of MySQL Monitoring

Alibaba Cloud Prometheus Service provides many default Prometheus alert rules, focusing on hotspot metrics for MySQL integration.

  • After MySQL monitoring is installed, you can create an alert rule by clicking MySQL Integration - Alert - Create Prometheus Alert Rule.

6

  • Enter an alert name, select an alert group and the required alert metric, and filter conditions

7

MySQL Monitoring Dashboard

Prometheus Service creates a MySQL monitoring dashboard based on hot metrics. You can use the dashboard to view monitoring data (such as availability, database query, network traffic, connections, and memory).

  • Availability, QPS, and database connection

8

  • Database query

9

  • Network traffic and memory usage

10

  • Files

11

Alibaba Cloud Prometheus Service

Alibaba Cloud Prometheus Service is a fully managed observation service built based on the cloud-native observability standard, open-source Prometheus. By default, it integrates common cloud services and is compatible with mainstream open-source components. The service provides comprehensive observation of businesses, applications, middleware, and systems. In addition, it uses an out-of-the-box Grafana dashboard and intelligent alerting feature to optimize probe performance and system availability, helping enterprises quickly build a comprehensive metric observation system. Therefore, enterprises can quickly identify and locate problems in businesses, reduce the impact of faults on businesses, and eliminate the workload of system construction and routine maintenance, thus effectively improving the efficiency of O&M and observation.

12

At the same time, as an important component of Alibaba Cloud Observability Suite (ACOS), Prometheus is integrated with Grafana and Tracing Analysis to form an observable data layer that supports storage and analysis of metrics and trace data and integration of heterogeneous data sources. Prometheus also allows you to view dashboards, configure the alerting feature, and explore data with the standard PromQL or SQL syntax. Also, it gives data value to different scenarios (such as IT cost management, enterprise risk governance, intelligent O&M, and business continuity assurance), so observable data can go beyond observation.

MySQL middleware Best practices cloud native Prometheus Observability Monitor
0 1 0
Share on

Read previous post:

Observability | What Metrics Should We Focus on When We Use Prometheus Service to Monitor SNMP?

Read next post:

Observability | How to Use Prometheus to Achieve Observability of Performance Test Metrics

Alibaba Cloud Native

208 posts | 12 followers

You may also like

Comments

Alibaba Cloud Native

208 posts | 12 followers

Related Products

More Posts by Alibaba Cloud Native

See All