MYSQL Integration with KloudMate using OpenTelemetry

the mysql integration provides almost real time health and performance metrics for your mysql database you can visualize these metrics with the included dashboard and set up alerts to inform your team about different mysql conditions this example demonstrates how to use opentelemetry and kloudmate to monitor mysql performance, establish baseline metrics, and proactively address performance issues, ensuring optimal operation of your mysql database pre requisites mysql installed and running install the opentelemetry collector on the specific server that requires monitoring check installing and configuring opentelemetry collector mysql username and password for authentication the mysql logs must be enabled from configurations step 1 configure the receivers to scrape metrics as well as logs to start monitoring mysql with otel collector, you need to configure mysql mysql receiver linux users open the file located at /etc/otelcol contrib/config yaml /etc/otelcol contrib/config yaml using your preferred text editor windows users create a new file called config yaml config yaml in the c \program files\opentelemetry collector c \program files\opentelemetry collector folder you can use notepad or any text editor to do this add the suitable extensions extensions health check pprof endpoint 0 0 0 0 1777 zpages endpoint 0 0 0 0 55679 file storage create directory true in this step, two receivers are integrated to retrieve telemetry data from mysql mysql receiver to retrieve metrics file log receiver to retrieve logs to get the metrics use only 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 2 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 please choose one of the following options for configuration based on your provider (aws ec2, azure vm or on premises server) server(can be on premise, non cloud or 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 retrieve aws ec2 instance tags along with logs and metrics, users need to associate an iam role with the ec2 instance that includes the ec2\ describetags ec2\ describetags policy 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 3 configure the exporter, extension and save the configuration set up the kloudmate backend on the exporter part of the open telemetry configuration file and configure the pipeline 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 4 to restart and verify the status of the opentelemetry (otel) collector, follow these steps for linux execute the following commands sudo systemctl restart otelcol contrib sudo systemctl status otelcol contrib these commands will restart the otel collector and display its current status for windows open the services window press win + r win + r , type services msc services msc , and press ok ok alternatively, search for " services services " in the windows start menu in the services window, locate the " opentelemetry collector opentelemetry collector " service right click the service and select " restart restart " subsequently, monitor the metrics on the kloudmate dashboard and set up an alarm to receive notifications if the potential metrics for a specific application rise 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 full details on metrics, click here