Mysql monitoring using InfluxDB and Collectd

mysql-monitoring-using-influxdb

In this article we will explain how to setup mysql monitoring using InfluxDB and “Collectd

Mysql is the worlds most popular opensource RDMS database. With its proven performance, reliability, and ease-of-use, It has become the most preferred database choice for web-based applications.

In production Mysql installations, it is critical to monitor the mysql performance and traffic , this will help us to find the bottlenecks, identify potential performance issues and make decisions on scaling.

How to monitor mysql using InfluxDB and Collectd ?

We will explain how to setup a simple monitoring stack which consists of following components

1. Collectd
2. InfluxDB
3. Grafana

Collectd” is a daemon which collects system and application performance metrics periodically and provides ways to send the metrics data to variety of datastores like Graphite, InfluxDB..

How to install and Configure Collectd?

On Centos , you can install Collectd using yum

1 2 3   yum install collectd  

On Ubuntu , you can install collectd using “apt-get”

1 2 3   aptget install collectd  

Now let us configure collectd to collect the mysql performance and statistics metrics .

First create a mysql user on your mysql server

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19   [root@test.test.com html]# mysql Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 270 Server version: 5.5.52clllve MySQL Community Server (GPL) by Atomicorp   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.   Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.   Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.   mysql> grant usage on *.* to collectd@“localhost” identified by “gyhdjeiuyied”; OK mysql>  

Now Modify “collectd” configuration and add the mysql server details like following

1 2 3 4 5 6 7 8 9 10 11 12   LoadPlugin mysql Host “127.0.0.1” User “collectd” Password “gyhdjeiuyied” MasterStats false    

Now configure collectd to send metrics data to InfluxDB

1 2 3 4 5 6   LoadPlugin network Server “127.0.0.1” “25836”  

By default InfluxDB will accept metrics from collectd on UDP port 25836 . Now we configured collectd to send metrics to same port.

Now Start collectd

On Centos6

1 2 3   service collectd start  

On Centos7

1 2 3   systemctl start collectd  

Install and Configure InfluxDB

You can install InfluxDB using

1 2 3   yum install influxdb  

On Ubuntu

1 2 3   aptget install influxdb  

Start InfluxDB using

On Centos6

1 2 3   /etc/init.d/influxdb start  

On Centos7

1 2 3   systemctl start influxdb  

Now verify its accepting connections on UDP port 25836

1 2 3 4 5   [root@test.test.com html]# netstat -nap |grep 25836 udp 0 0 :::25836 :::* 3425/influxd [root@test.test.com html]#  

By default Collectd data is stored in the database “collectd” . Let us connect to influxdb server and see if it has data from collectd

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32   [root@test.test.com html]# influx Visit https://enterprise.influxdata.com to register for updates, InfluxDB server management, and monitoring. Connected to http://localhost:8086 version 0.13.0 InfluxDB shell version: 0.13.0 > use collectd Using database collectd > show series; key mysql_value,host=127.0.0.1,instance=test,type=mysql_commands,type_instance=admin_commands mysql_value,host=127.0.0.1,instance=test,type=mysql_commands,type_instance=change_db mysql_value,host=127.0.0.1,instance=test,type=mysql_commands,type_instance=delete mysql_value,host=127.0.0.1,instance=test,type=mysql_commands,type_instance=insert mysql_value,host=127.0.0.1,instance=test,type=mysql_commands,type_instance=replace mysql_value,host=127.0.0.1,instance=test,type=mysql_commands,type_instance=select mysql_value,host=127.0.0.1,instance=test,type=mysql_commands,type_instance=show_databases mysql_value,host=127.0.0.1,instance=test,type=mysql_commands,type_instance=show_fields mysql_value,host=127.0.0.1,instance=test,type=mysql_commands,type_instance=show_status mysql_value,host=127.0.0.1,instance=test,type=mysql_commands,type_instance=show_tables mysql_value,host=127.0.0.1,instance=test,type=mysql_commands,type_instance=truncate mysql_value,host=127.0.0.1,instance=test,type=mysql_commands,type_instance=update mysql_value,host=127.0.0.1,instance=test,type=mysql_handler,type_instance=delete mysql_value,host=127.0.0.1,instance=test,type=mysql_handler,type_instance=read_first mysql_value,host=127.0.0.1,instance=test,type=mysql_handler,type_instance=read_key mysql_value,host=127.0.0.1,instance=test,type=mysql_handler,type_instance=read_next mysql_value,host=127.0.0.1,instance=test,type=mysql_handler,type_instance=read_rnd mysql_value,host=127.0.0.1,instance=test,type=mysql_handler,type_instance=read_rnd_next mysql_value,host=127.0.0.1,instance=test,type=mysql_handler,type_instance=update mysql_value,host=127.0.0.1,instance=test,type=mysql_handler,type_instance=write mysql_value,host=127.0.0.1,instance=test,type=mysql_locks,type_instance=immediate mysql_value,host=127.0.0.1,instance=test,type=mysql_locks,type_instance=waited  

You can see mysql metrics data sent by collectd in the “collectd” database.

Now let us see how to visualize this data using Grafana.

Install Grafana

On Centos , You can install Grafana using Yum directly.

1 2 3   $ sudo yum install https://grafanarel.s3.amazonaws.com/builds/grafana-4.1.2-1486989747.x86_64.rpm  

Find detailed installation details here

Start Grafana Server

On centos7

1 2 3 4 5   $ systemctl daemonreload $ systemctl start grafanaserver $ systemctl status grafanaserver  

On Centos6

1 2 3   service grafanaserver start  

Now you should be able to login to Grafana using default login details

1 2 3 4 5 6   user : admin password : admin   URL : http://127.0.0.1:3000/  

I have created a video on How to add “Influxdb” datasource in Grafana and create Graphs .

 

SUMMARY

In this article we have explained how to setup mysql monitoring using Influxdb , collectd and Grafana.

As always, feel free to drop us a note if you have any questions or feedback using our comment form below. Always happy to help ????

 

Author: , 0000-00-00