MySQL Monitoring
This document provides a guide on how to send MySQL logs and metrics to KloudMate, using OpenTelemetry collector.
This guide demonstrates how to use OpenTelemetry and KloudMate, to monitor SQL performance, establish performance benchmarks, and preemptively tackle issues, all to ensure your SQL database operates at its best.
Step 1: Prerequisites:
- This receiver supports MySQL version 8.0. So you must have SQL installed and running
- The OpenTelemetry collector must be active and running. Here is more information on installing the OpenTelemetry Collector and using it.
- Collecting most metrics requires the ability to execute SHOW GLOBAL STATUS.
Step 2: Configure OpenTelemetry collector to send telemetry data to KloudMate backend by modifying your YAML config file as shown in the following sample config file:
- To get the DB metrics make sure the user name used under mysql receiver can execute SHOW GLOBAL STATUS.
- To get the file log, the collector user (which is responsible for running the collector service) must have access to the file path. For example you can change the user name otel-contrib to root under /usr/lib/systemd/system/otelcol-contrib.service.
After successfully instrumenting your MySQL database with OpenTelemetry and integrating it with KloudMate, you can capture and monitor the following metrics (among others) within KloudMate:
Metric Name | Description |
mysql.operations | Total operations including fsync, reads and writes |
mysql_buffer_pool.data_pages | Number of data pages for an InnoDB buffer pool |
mysql_buffer_pool.limit | Configured size of the InnoDB buffer pool |
mysql_buffer_pool.operations | Number of operations on the InnoDB buffer pool |
mysql_buffer_pool.page_flushes | Sum of Requests to flush pages for the InnoDB buffer pool |
mysql_mysqlx_connections | Total MySQLx connections |
mysql.tmp_resources | Number of temporary resources created |
mysql_uptime | Number of seconds since the server has been up |
mysql_index_io_wait_count | Total time of I/O wait events for a particular index |
mysql_handlers | Number of requests to various MySQL handlers |
mysql_locks | Total MySQL locks |
mysql_log_operations | Number of InnoDB log operations |
mysql.index.io.wait.time | Total time of I/O wait events for a particular index |
mysql_row_locks | Total InnoDB row locks present |
mysql_buffer_pool_usage | Number of bytes in the InnoDB buffer pool |
mysql.page_operations | Total operation on InnoDB pages |
mysql.double_writes | Number of writes to the InnoDB doublewrite buffer pool |
mysql.table.io.wait.count | Total I/O wait events for a specific table |
mysql.table.io.wait.time | Total wait time for I/O events for a table |
mysql.prepared_statements | Number of times each type of Prepared statement command got issued |
mysql.sorts | Total MySQL sort execution |
mysql.row_operations | Total row operations executed |
mysql.threads | Current state of MySQL threads |
mysql.opened_resources | Total opened resources |
mysql.buffer_pool.pages | Sum of pages in the InnoDB buffer pool |