MYSQL Monitoring

the mysql integration delivers near real time health and performance metrics for your mysql databases these metrics are visualized using a built in dashboard, and you can configure alerts to notify your team about important mysql conditions mysql monitoring in kloudmate helps you observe the health, performance, and behavior of your mysql databases by collecting metrics and logs using the kloudmate agent powered by opentelemetry this enables centralized monitoring of mysql instances running on aws ec2, azure virtual machines, or on premise servers mysql integration using kloudmate agents what this integration provides with mysql monitoring enabled, kloudmate collects telemetry that provides visibility into • buffer pool usage and operations • connection handling and thread states • innodb row locks, double writes, and log operations • index i/o waits and table operations • query execution, sorts, and temporary resources • mysql server uptime and opened resources • slow query logs and error logs this visibility helps identify buffer pool inefficiencies, connection bottlenecks, innodb performance issues, and slow query problems pre requisites before configuring mysql monitoring, ensure the following mysql version 8 0 or mariadb 10 11 is installed and running refer to the mysql documentation for supported versions and configuration requirements the monitoring user must have permission to execute show global status to collect query samples, performance schema must be enabled and the monitoring user must be granted access `grant select on performance schema to \<your user>@'%';` kloudmate agent installed on the mysql host refer to agent installation for https //docs kloudmate com/linux agent or https //docs kloudmate com/windows agent mysql username and password for authentication mysql logs must be enabled from configurations step 1 access agents and opentelemetry collector configuration log in to the kloudmate platform go to settings → agents select the agent running on the mysql host click actions → collector configuration yaml editor opens for configuration step 2 add required extensions and receivers extensions extensions health check pprof endpoint 0 0 0 0 1777 zpages endpoint 0 0 0 0 55679 file storage create directory true receivers two receivers are used mysql receiver – collects metrics file log receiver – collects logs (general, error, slow queries) if you only need metrics, configure only the mysql receiver receivers mysql endpoint localhost 3306 username root #change the username password 'yourstr0ngpassword!' #change the password collection interval 60s initial delay 1s statement events digest text limit 120 time limit 24h limit 250 filelog/mysqlerror include \[/var/log/mysql/error log] exclude \[/var/log/mysql/ gz] storage file storage retry on failure enabled true operators \ type regex parser parse to body regex '(?p\<time>\d{4} \d{2} \d{2}t\d{2} \d{2} \d{2} \d+z)\s(?p\<id>\d+)\s\\\[(?p\<severity>\[^ ] )\\]\s\\\[(?p\<name>\[^ ] )\\]\s\\\[(?p\<server>\[^ ] )\\]\s(?p\<message> )' timestamp parse from body timestamp layout '%y %m %d %h %m %s %f %z' severity parse from body severity mapping info \ statement \ log \ system \ type remove field body severity \ type remove field body timestamp filelog/mysqlslowquery include \[/var/log/mysql/ slow\ log] exclude \[/var/log/mysql/ gz] storage file storage multiline line start pattern '^# time \d{4} \d{2} \d{2}t\d{2} \d{2} \d{2}\\ \d{6}z' retry on failure enabled true operators \ type regex parser parse to body regex '(?p\<time>\d{4} \d{2} \d{2}t\d{2} \d{2} \d{2}\\ \d+z)\n#\suser\@host \s(?p\<user> )\s+id \s+(?p\<id>\d+)\n# query time \s(?p\<query time>\d+ \d+)\s+lock time \s(?p\<lock time>\d+ \d+)\srows sent \s(?p\<rows sent>\d+)\s+rows examined \s(?p\<rows examined>\d+)\n(?p\<query>\[\s\s]+)' timestamp parse from body timestamp layout '%y %m %d %h %m %s %f %z' severity parse from body severity mapping info \ statement \ log \ type remove field body severity \ type remove field body timestamp step 3 configure processors set up the processor component to identify resource information from the host and either append or replace the resource values in the telemetry data with this information choose one configuration based on where mysql is running server (on premise / non cloud / cloud) processors batch send batch size 5000 timeout 10s resourcedetection detectors \[system] system resource attributes host name enabled true host id enabled true os type enabled false resource attributes \ key service name action insert from attribute host name aws ec2 (optional) to collect ec2 tags, attach an iam role with ec2\ describetags permission processors batch send batch size 5000 timeout 10s resourcedetection/ec2 detectors \["ec2"] ec2 tags \ ^tag1$ \ ^tag2$ \ ^label $ \ ^name$ resource attributes \ key service name action insert from attribute ec2 tag name \ key service instance id action insert from attribute host id azure virtual machines processors batch send batch size 5000 timeout 10s resourcedetection/azure detectors \["azure"] azure tags \ ^tag1$ \ ^tag2$ \ ^label $ \ ^name$ resource attributes \ key service name action insert from attribute azure vm name \ key service instance id action insert from attribute host id step 4 configure exporter and pipelines configure the kloudmate backend exporter and define pipelines for metrics and logs exporters debug verbosity detailed otlphttp endpoint 'https //otel kloudmate com 4318' headers authorization xxxxxxxx # use the auth key service pipelines metrics receivers \[mysql] processors \[batch, resourcedetection, resource] #use the correct resourcedetection exporters \[otlphttp] logs receivers \[filelog/mysqlslowquery, filelog/mysqlerror] processors \[batch, resourcedetection, resource] #use the correct resourcedetection exporters \[otlphttp] extensions \[health check, pprof, zpages, file storage] step 5 save configuration and restart agent after updating the configuration, click save configuration in the kloudmate ui these commands restart the kloudmate agent and display its current status for windows open the services window press win + r , type services msc , and press ok or search for services from the start menu locate the kloudmate agent service right click the service and select restart after restarting the agent, verify that mysql metrics and logs are visible in the kloudmate dashboard post‑integration data validation verify that metrics are flowing into kloudmate using the explore view after the agent restarts log in to kloudmate navigate to explore select opentelemetry → metrics choose a mysql metric and run the query seeing time series data confirms that mysql telemetry is flowing successfully standard mysql dashboards kloudmate provides prebuilt mysql dashboards through https //templates kloudmate com/mysql/index html these dashboards visualize mysql database performance, query operations, connections, and resource usage to import and start using these templates, follow the steps described in https //docs kloudmate com/creating a dashboard#sfk3g default metrics 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 for the complete metrics list, refer to the https //github com/open telemetry/opentelemetry collector contrib/blob/main/receiver/mysqlreceiver/documentation md