How to Monitor MySQL Database Performance
MySQL is an open source, object-relational database built with speed and reliability in mind. Its large and active developer community has created many third-party applications, tools, and libraries that expand MySQL’s functionality.
DigitalOcean Managed Databases include metrics visualizations so you can monitor performance and health of your database cluster.
Cluster metrics monitor the performance of the nodes in a database cluster. Cluster metrics cover primary and standby nodes; metrics for each read-only node are displayed independently. This data can help guide capacity planning and optimization. You can also set up alerting on cluster metrics.
Database metrics monitor the performance of the database itself. This data can help assess the health of the database, pinpoint performance bottlenecks, and identify unusual use patterns that may indicate an application bug or security breach.
There are two groups of MySQL database metrics: main server metrics, which are metrics on all databases in the cluster, and database metrics, which are metrics on individual database performance.
View MySQL Metrics
To view performance metrics for a MySQL database cluster, click the name of the database to go to its Overview page, then click the Insights tab.
The Select object drop-down menu lists the cluster itself and all of the databases in the cluster. Choose the database to view its metrics.
In the Select Period drop-down menu, you can choose a time frame for the x-axis of the graphs, ranging from 1 hour to 30 days. Each line in the graphs displays about 300 data points.
By default, the summary to the right shows the most recent metrics values. When you hover over a different time in a graph, the summary displays the values from that time instead.
Note
If you recently provisioned the cluster or changed its configuration, it may take a few minutes for the metrics data to finish processing before you see it on the Insights page.
If you have 200 or more databases on a single cluster, you may be unable to retrieve their metrics. If you reach this limit, create any additional databases in a new cluster.
MySQL Main Server Metrics Details
MySQL-specific main server metrics include:
-
Connection status: the number of threads created, connected, and running in relation to the connection limit.
-
Index vs. sequential reads: The reads using an index as a proportion of the total reads across all databases on the server.
-
Operations throughput: Throughput of fetch, insert, update and delete operations across all databases on the server.
Main server metrics are displayed in the same view as cluster performance metrics.
Connection Status
The connection status plot displays:
- The total number of threads connected to the database per second
- The number of newly created threads per second
- The number of active threads connected to the database per second
If the number of connected threads regularly approaches or exceeds the connection limit, consider implementing client-side connection pooling or upgrading your database plan to increase your connection limit.
Index vs. Sequential Reads
The index vs. sequential reads plot presents the proportion of reads that use an index over the total number of reads across all databases (schemas) on the main server.
In general, queries across large tables can be optimized through the use of an index. Use the MySQL query statistics on the Logs & Queries tab and the EXPLAIN statement to identify slow queries that may benefit from the addition of an index.
Operations throughput
The operations throughput plot displays the rate of fetch, insert, update and delete operations per second across all databases (schemas) on the main server.
You can compare this plot with node performance metrics to identify potential resource constraints. For more insights, look at the individual database-level metrics and the query statistics on the Logs & Queries page.
MySQL Database Metrics
MySQL databases have the following metrics:
- Throughput: row-based throughput of fetches, inserts, updates, and deletes
- Latency: latency of fetches, inserts, updates, and deletes
Throughput
The throughput plot shows the rate of fetches, inserts, updates, and deletes in rows per second.
If you observe bottlenecks, look for slow queries in the query statistics on the Logs & Queries page, then use the EXPLAIN statement to explore opportunities for query optimization.
Latency
The latency plot shows the average time, in seconds, of fetches, inserts, updates, and deletes.
If you observe bottlenecks, use the EXPLAIN statement for optimization recommendations.
Access the Metrics Endpoint
You can also view your database cluster’s metrics programatically via the metrics endpoint. This endpoint includes over twenty times the metrics you can access in the Insights tab in the control panel.
You can access the metrics endpoint with a cURL command or a monitoring system like Prometheus.
Get Hostname and Credentials
First, you need to retrieve your cluster’s metrics hostname by sending a GET request to https://api.digitalocean.com/v2/databases/${UUID}. In the following example, the target database cluster has a standby node, which requires a second host/port pair:
curl --silent -XGET --location 'https://api.digitalocean.com/v2/databases/${UUID}' --header 'Content-Type: application/json' --header "Authorization: Bearer $RO_DIGITALOCEAN_TOKEN" | jq '.database.metrics_endpoints'
Which returns the following host/port pairs:
[
{
"host": "db-test-for-metrics.c.db.ondigitalocean.com",
"port": 9273
},
{
"host": "replica-db-test-for-metrics.c.db.ondigitalocean.com",
"port": 9273
}
]
Next, you need your cluster’s metrics credentials. You can retrieve these by making a GET request to https://api.digitalocean.com/v2/databases/metrics/credentials with an admin or write token:
curl --silent -XGET --location 'https://api.digitalocean.com/v2/databases/metrics/credentials' --header 'Content-Type: application/json' --header "Authorization: Bearer $RW_DIGITALOCEAN_TOKEN" | jq '.'
Which returns the following credentials:
{
"credentials": {
"basic_auth_username": "..."
"basic_auth_password": "...",
}
}
Access with cURL
To access the endpoint using cURL, make a GET request to https://$HOST:9273/metrics, replacing the hostname, username, and password variables with the credentials you found in the previous steps:
curl -XGET -k -u $USERNAME:$PASSWORD https://$HOST:9273/metrics
Access with Prometheus
To access the endpoint using Prometheus, first copy the following configuration into a file prometheus.yml, replacing the hostname, username, password, and path to CA cert. This configures Prometheus to use all the credentials necessary to access the endpoint:
# prometheus.yml
global:
scrape_interval: 15s
evaluation_interval: 15s
scrape_configs:
- job_name: 'dbaas_cluster_metrics_svc_discovery'
scheme: https
tls_config:
ca_file: /path/to/ca.crt
dns_sd_configs:
- names:
- $TARGET_ADDRESS
type: 'A'
port: 9273
refresh_interval: 15s
metrics_path: '/metrics'
basic_auth:
username: $BASIC_AUTH_USERNAME
password: $BASIC_AUTH_PASSWORD
Then, copy the following connection script into a file named up.sh. This script runs envsubst and starts a Prometheus container with the config from the previous step:
#!/bin/bash
envsubst < prometheus.yml > /tmp/dbaas-prometheus.yml
docker run -p 9090:9090 \
-v /tmp/dbaas-prometheus.yml:/etc/prometheus/prometheus.yml \
prom/prometheus
Go to http://localhost:9090/targets in a browser to confirm that multiple hosts are up and healthy.
Then, navigate to http://localhost:9090/graph to query Prometheus for metrics.
For more details, see the Prometheus DNS SD docs and TLS config docs.
Additional Resources
For more details on each available metric, see the MySQL documentation.