External monitoring tool integrations

Available in VPC

Cloud DB for MySQL can collect and visualize performance data by integrating with various open source and commercial external tools, in addition to its internal monitoring tools. Typical method and configuration example are as follows:

Prometheus, Grafana

Cloud DB for MySQL's performance data can be collected and visualized by using Prometheus and Grafana. Prometheus collects metrics, and Grafana visualizes them. In order to collect MySQL Server's performance metrics using Prometheus, mysqld_exporter needs to be installed.

Install mysqld_exporter

You can access the MySQL Server using mysqld_exporter, an open source agent that collects MySQL Server's metrics, to collect various performance metrics.

To install mysqld_exporter, follow these steps:

1. Access the application server by referring to Getting started with Cloud DB for MySQL.
2. Create my.cnf file in the "/home/user" path, and add DB Client information under the [Client] syntax.

   [client]
   user=<username>
   password=<password>
   host=<hostname>
   port=3306
   

3. Run the following commands, in order, to run the mysqld_exporter container:

   docker network create my-mysql-network
   docker pull prom/mysqld-exporter
   
   docker run -d \
       --name mysqld-exporter \
       --network my-mysql-network \
       -v /home/user/my.cnf:/.my.cnf \
       -p 9104:9104 \
       prom/mysqld-exporter
   

Install Prometheus

You can use Prometheus, an open source system monitoring and notification tool, to collect metrics from mysqld_exporter and visualize MySQL performance data and set notification conditions by integrating with Grafana.

To install Prometheus, follow these steps:

1. Create prometheus.yml file and write the configuration file as follows so that Prometheus can collect performance data from mysqld_exporter. For more information on configuration items, see Prometheus official documentation (English).

   global:
       scrape_interval: 15s
       evaluation_interval: 15s
   
   scrape_configs:
       - job_name: 'prometheus'
       static_configs:
           - targets: ['localhost:9090']
   
   - job_name: 'mysql'
       static_configs:
           - targets: ['mysqld-exporter:9104']
   

2. Run the following command to run the Prometheus container based on the set prometheus.yml file:

   docker run \
       -d \
       -p 9090:9090 \
       --name prometheus \
       --network my-mysql-network \
       -v $(pwd)/prometheus.yml:/etc/prometheus/prometheus.yml \
       prom/prometheus
   

3. See ACG settings guide to allow the 9090 port in the ACG settings in the application server.
   
4. Access http://:9090 from a browser to access the Prometheus web UI.

Install Grafana

Grafana is an open source dashboard tool that can visualize the time series data collected in Prometheus.

To install Grafana, follow these steps:

1. Run the following commands, in order, to run the Grafana container:

   docker run -d \
       --name grafana \
       --network my-mysql-network \
       -p 3000:3000 \
       grafana/grafana
   

2. See ACG settings guide to allow the 3000 port in the ACG settings in the application server.
   

Create Grafana dashboard

You can create dashboards in Grafana by using Prometheus as a data source.

To create Prometheus dashboards in Grafana, follow these steps:

1. Access http://:3000 from a browser to access the Grafana web UI.
   • The default user name and password are both "admin," and a password change is required at the initial login.
2. Click Connection > Data sources in the basic Grafana menu, and add Prometheus as a data source.
3. Specify the name of the data source, and set the URL field to http://:9090.
4. Click [Save & Test] to check if the data source is configured properly.
5. Click Dashboards in the basic Grafana menu, and click [New] to create a dashboard. Click [Import] to use provided templates.

Telegraf, InfluxDB, Grafana

Telegraf is an open source plugin-based server agent for collecting and transmitting time series data. It provides more than 300 input/output plugins and can collect metrics from various sources, such as databases, cloud services, and IoT sensors. InfluxDB is an open source database that is optimized to save and manage time series data. It enables flexible queries with InfluxQL and Flux language and can optimize long-term data storage through downsampling and retention policy settings.

To monitor Cloud DB for MySQL, install Telegraf in the application server and use the inputs.mysql plugin to set to collect MySQL Server's performance metrics. Collected data is transmitted to the InfluxDB with the outputs.influxdb plugin and can be visualized in Grafana with dashboards.

Percona Monitoring and Management (PMM)

Percona Monitoring and Management (PMM) is an open source monitoring tool from Percona that collects and saves data with Prometheus and visualizes them with Grafana. With it, you can view performance metrics from the entire DB cluster to the single query level on a single dashboard. You can use the Query Analytics feature to quickly identify queries that are either with large execution costs or slow to respond and solve bottleneck problems.

PMM consists of 2 components; Server and Client. PMM Server saves the monitoring data, and PMM Client is to be installed in the monitored server and collects the server- and database-related data. You can deploy it to various infrastructures flexibly, such as on-premise, cloud, and hybrid environments.

In the Cloud DB for MySQL environment, the PMM Server accesses the DB instance externally to collect major performance metrics and query information, without having PMM Client installed.

Percona Toolkit

Percona Toolkit is a group of various command-line tools for database management. It can perform high-quality DB performance analysis tasks just by running commands, without a separate server installed.

Typical tools for Cloud DB for MySQL monitoring are as follows:

pt-mysql-summary

pt-mysql-summary is a tool that summarizes MySQL Server's state, settings, and system information and provides the results in the text format. It provides various output control options, and you can filter the information or adjust the summary range as necessary. For more information, see pt-mysql-summary official documentation (English).

How to use pt-mysql-summary for Cloud DB for MySQL monitoring is as follows:

pt-mysql-summary --host=<hostname> --user=<username> --password=<password>

pt-query-digest

pt-query-digest is a tool that analyzes the log queries and provides the result in the text format. It basically groups queries in the fingerprint unit and shows them from the slowest query and on by sorting them based on the total execution time. It provides various output control options, and you can adjust the grouping method, filtering conditions, and so on. For more information, see pt-query-digest official documentation (English).

How to use pt-query-digest for Cloud DB for MySQL monitoring is as follows:

1. In the VPC environment of the NAVER Cloud Platform console, navigate to > Services > Database > Cloud DB for MySQL.
2. Click the DB Server menu.
3. Click the MySQL Server to manage and navigate to [DB Status] > [DB Server logs] tab menu.
4. Select Slow log in the dropdown menu.
   
5. Click the log file to manage and click [Send to Object Storage].
6. When the Send to Object Storage popup window appears, select the bucket to upload the file to and click [Send to Object Storage].
   
7. From the NAVER Cloud Platform console, navigate to Services > Database > Object Storage.
8. Click the Bucket management menu.
9. Click the bucket you want in the bucket list.
10. When a list of folders/files appear, click the file you want and click [Download].
11. When the file is downloaded to the local PC, run the following command:

    pt-query-digest –-type=’slowlog’ <file name>
    

MySQL Workbench

You can perform basic performance monitoring for Cloud DB for MySQL by using Performance Tool from Workbench, a MySQL client. With Workbench, you can use the monitoring feature just by accessing the DB, without a separate installation process. Its major features are as follows:

• Performance Dashboard: Server status summary dashboard.
  
• Performance Schema Reports: Shows major performance metrics.
  
• Query Statistics: Shows query statistics.
  

To access the MySQL Server from Workbench, see the Access DB Server outside cloud guide to set the connection. Once you have the access, you can use the Performance Tool feature of Workbench.