Mysql monitoring using InfluxDB and Collectd
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 |
apt-get 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.52-cll-lve 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 <Plugin mysql> <Database "test"> Host "127.0.0.1" User "collectd" Password "gyhdjeiuyied" MasterStats false </Database> </Plugin> |
Now configure collectd to send metrics data to InfluxDB
|
1 2 3 4 5 6 |
LoadPlugin network <Plugin network> Server "127.0.0.1" "25836" </Plugin> |
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 |
apt-get 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 daemon-reload $ systemctl start grafana-server $ systemctl status grafana-server |
On Centos6
|
1 2 3 |
service grafana-server 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 🙂