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 ????