Cacti percona mariadb Monitoring

Cacti percona template 을 이용하여 mariadb 를 모니터링 할수 있습니다.

자세한 정보는 아래 사이트에서 확인하실수 있습니다.

Percona PDF 매뉴얼 site:  https://www.percona.com/software/database-tools/percona-monitoring-plugins

site: https://www.percona.com/doc/percona-monitoring-plugins/LATEST/cacti/index.html

percona-cacti-templates 설치 파일의 경우 https://www.percona.com/downloads/percona-monitoring-plugins/ 에서 Down 할수 있습니다.

 

2017.12.22일 기준

percona-cacti-templates Down 및 설치

[root@centos74 ~]# wget https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.7/binary/redhat/7/x86_64/percona-cacti-templates-1.1.7-2.noarch.rpm

[root@centos74 ~]# yum install percona-cacti-templates-1.1.7-2.noarch.rpm

perl-Digest , perl-Digest-MD5 Package 필요합니다.

 

 

Percona Directory 

/usr/share/cacti/resource/percona
[root@centos74 ~]# ls -al /usr/share/cacti/resource/percona/templates/
total 1896
drwxr-xr-x 2 root root   4096 Dec 22 16:57 .
drwxr-xr-x 6 root root     65 Dec 22 16:57 ..
-rw-r--r-- 1 root root  74554 Dec 10  2016 cacti_host_template_percona_apache_server_ht_0.8.6i-sver1.1.7.xml
-rw-r--r-- 1 root root 104933 Dec 10  2016 cacti_host_template_percona_galera_server_ht_0.8.6i-sver1.1.7.xml
-rw-r--r-- 1 root root 273814 Dec 10  2016 cacti_host_template_percona_gnu_linux_server_ht_0.8.6i-sver1.1.7.xml
-rw-r--r-- 1 root root  54091 Dec 10  2016 cacti_host_template_percona_jmx_server_ht_0.8.6i-sver1.1.7.xml
-rw-r--r-- 1 root root  76166 Dec 10  2016 cacti_host_template_percona_memcached_server_ht_0.8.6i-sver1.1.7.xml
-rw-r--r-- 1 root root  93264 Dec 10  2016 cacti_host_template_percona_mongodb_server_ht_0.8.6i-sver1.1.7.xml
-rw-r--r-- 1 root root 888147 Dec 10  2016 cacti_host_template_percona_mysql_server_ht_0.8.6i-sver1.1.7.xml
-rw-r--r-- 1 root root  42607 Dec 10  2016 cacti_host_template_percona_nginx_server_ht_0.8.6i-sver1.1.7.xml
-rw-r--r-- 1 root root 169753 Dec 10  2016 cacti_host_template_percona_openvz_server_ht_0.8.6i-sver1.1.7.xml
-rw-r--r-- 1 root root  99944 Dec 10  2016 cacti_host_template_percona_rds_server_ht_0.8.6i-sver1.1.7.xml
-rw-r--r-- 1 root root  37873 Dec 10  2016 cacti_host_template_percona_redis_server_ht_0.8.6i-sver1.1.7.xml
[root@centos74 ~]#

 

 

ss_get_mysql_stats.php 스크립트 수정

[root@centos74 ~]# cd /usr/share/cacti/scripts/
[root@centos74 scripts]# vi ss_get_mysql_stats.php
$mysql_user = 'cacti';
$mysql_pass = 'password';
$mysql_port = 3306;

 

mariadb cacti User 설정

[root@centos74 scripts]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 356
Server version: 10.1.29-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> GRANT SUPER, PROCESS ON *.* TO 'cacti'@'%' IDENTIFIED BY "password";
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit;
Bye
[root@centos74 scripts]#

 

mysql 템플릿 추가

[root@centos74 ~]# php /usr/share/cacti/cli/import_template.php --filename=/usr/share/cacti/resource/percona/templates/cacti_host_template_percona_mysql_server_ht_0.8.6i-sver1.1.7.xml
Read 888147 bytes of XML data
Import ResultsCacti has imported the following items for the Template:
CDEF
[success] Percona Turn Into Bits CDEF [new]
[success] Percona Negate CDEF [new]
GPRINT Preset
[success] Percona MySQL Server Checksum 837598bec663319c08a1041f6749996a [new]
[success] Percona MySQL Server Version t1.1.7:s1.1.7 [new]
[success] Percona Normal [new]
Data Input Method
[success] Percona Get MySQL Stats/MyISAM Indexes IM [new]
[success] Percona Get MySQL Stats/MyISAM Key Cache IM [new]
[success] Percona Get MySQL Stats/InnoDB Buffer Pool IM [new]
[success] Percona Get MySQL Stats/InnoDB I/O IM [new]
[success] Percona Get MySQL Stats/InnoDB Insert Buffer IM [new]
[success] Percona Get MySQL Stats/InnoDB Insert Buffer Usage IM [new]
[success] Percona Get MySQL Stats/InnoDB Semaphores IM [new]
[success] Percona Get MySQL Stats/InnoDB Row Operations IM [new]
[success] Percona Get MySQL Stats/MySQL Table Locks IM [new]
[success] Percona Get MySQL Stats/MySQL Connections IM [new]
[success] Percona Get MySQL Stats/MySQL Replication IM [new]
[success] Percona Get MySQL Stats/MySQL Query Cache IM [new]
[success] Percona Get MySQL Stats/MySQL Query Cache Memory IM [new]
[success] Percona Get MySQL Stats/MySQL Command Counters IM [new]
[success] Percona Get MySQL Stats/MySQL Select Types IM [new]
[success] Percona Get MySQL Stats/MySQL Sorts IM [new]
[success] Percona Get MySQL Stats/MySQL Temporary Objects IM [new]
[success] Percona Get MySQL Stats/MySQL Network Traffic IM [new]
[success] Percona Get MySQL Stats/InnoDB Buffer Pool Activity IM [new]
[success] Percona Get MySQL Stats/InnoDB I/O Pending IM [new]
[success] Percona Get MySQL Stats/InnoDB Log IM [new]
[success] Percona Get MySQL Stats/MySQL Binary/Relay Logs IM [new]
[success] Percona Get MySQL Stats/InnoDB Transactions IM [new]
[success] Percona Get MySQL Stats/InnoDB Transactions Active/Locked IM [new]
[success] Percona Get MySQL Stats/MySQL Files and Tables IM [new]
[success] Percona Get MySQL Stats/MySQL Threads IM [new]
[success] Percona Get MySQL Stats/InnoDB Memory Allocation IM [new]
[success] Percona Get MySQL Stats/InnoDB Adaptive Hash Index IM [new]
[success] Percona Get MySQL Stats/InnoDB Internal Hash Memory Usage IM [new]
[success] Percona Get MySQL Stats/InnoDB Tables In Use IM [new]
[success] Percona Get MySQL Stats/InnoDB Current Lock Waits IM [new]
[success] Percona Get MySQL Stats/InnoDB Lock Structures IM [new]
[success] Percona Get MySQL Stats/InnoDB Checkpoint Age IM [new]
[success] Percona Get MySQL Stats/InnoDB Row Lock Time IM [new]
[success] Percona Get MySQL Stats/InnoDB Row Lock Waits IM [new]
[success] Percona Get MySQL Stats/InnoDB Semaphore Waits IM [new]
[success] Percona Get MySQL Stats/InnoDB Semaphore Wait Time IM [new]
[success] Percona Get MySQL Stats/MySQL Processlist IM [new]
[success] Percona Get MySQL Stats/MySQL Transaction Handler IM [new]
[success] Percona Get MySQL Stats/MySQL Handlers IM [new]
[success] Percona Get MySQL Stats/MySQL Query Time Histogram (Count) IM [new]
[success] Percona Get MySQL Stats/MySQL Query Response Time (Microseconds) IM [new]
[success] Percona Get MySQL Stats/InnoDB Buffer Pool Efficiency IM [new]
Data Template
[success] Percona MyISAM Indexes DT [new]
[success] Percona MyISAM Key Cache DT [new]
[success] Percona InnoDB Buffer Pool DT [new]
[success] Percona InnoDB I/O DT [new]
[success] Percona InnoDB Insert Buffer DT [new]
[success] Percona InnoDB Insert Buffer Usage DT [new]
[success] Percona InnoDB Semaphores DT [new]
[success] Percona InnoDB Row Operations DT [new]
[success] Percona MySQL Table Locks DT [new]
[success] Percona MySQL Connections DT [new]
[success] Percona MySQL Replication DT [new]
[success] Percona MySQL Query Cache DT [new]
[success] Percona MySQL Query Cache Memory DT [new]
[success] Percona MySQL Command Counters DT [new]
[success] Percona MySQL Select Types DT [new]
[success] Percona MySQL Sorts DT [new]
[success] Percona MySQL Temporary Objects DT [new]
[success] Percona MySQL Network Traffic DT [new]
[success] Percona InnoDB Buffer Pool Activity DT [new]
[success] Percona InnoDB I/O Pending DT [new]
[success] Percona InnoDB Log DT [new]
[success] Percona MySQL Binary/Relay Logs DT [new]
[success] Percona InnoDB Transactions DT [new]
[success] Percona InnoDB Transactions Active/Locked DT [new]
[success] Percona MySQL Files and Tables DT [new]
[success] Percona MySQL Threads DT [new]
[success] Percona InnoDB Memory Allocation DT [new]
[success] Percona InnoDB Adaptive Hash Index DT [new]
[success] Percona InnoDB Internal Hash Memory Usage DT [new]
[success] Percona InnoDB Tables In Use DT [new]
[success] Percona InnoDB Current Lock Waits DT [new]
[success] Percona InnoDB Lock Structures DT [new]
[success] Percona InnoDB Checkpoint Age DT [new]
[success] Percona InnoDB Row Lock Time DT [new]
[success] Percona InnoDB Row Lock Waits DT [new]
[success] Percona InnoDB Semaphore Waits DT [new]
[success] Percona InnoDB Semaphore Wait Time DT [new]
[success] Percona MySQL Processlist DT [new]
[success] Percona MySQL Transaction Handler DT [new]
[success] Percona MySQL Handlers DT [new]
[success] Percona MySQL Query Time Histogram (Count) DT [new]
[success] Percona MySQL Query Response Time (Microseconds) DT [new]
[success] Percona InnoDB Buffer Pool Efficiency DT [new]
Graph Template
[success] Percona MyISAM Indexes GT [new]
[success] Percona MyISAM Key Cache GT [new]
[success] Percona InnoDB Buffer Pool GT [new]
[success] Percona InnoDB I/O GT [new]
[success] Percona InnoDB Insert Buffer GT [new]
[success] Percona InnoDB Insert Buffer Usage GT [new]
[success] Percona InnoDB Semaphores GT [new]
[success] Percona InnoDB Row Operations GT [new]
[success] Percona MySQL Table Locks GT [new]
[success] Percona MySQL Connections GT [new]
[success] Percona MySQL Replication GT [new]
[success] Percona MySQL Query Cache GT [new]
[success] Percona MySQL Query Cache Memory GT [new]
[success] Percona MySQL Command Counters GT [new]
[success] Percona MySQL Select Types GT [new]
[success] Percona MySQL Sorts GT [new]
[success] Percona MySQL Temporary Objects GT [new]
[success] Percona MySQL Network Traffic GT [new]
[success] Percona InnoDB Buffer Pool Activity GT [new]
[success] Percona InnoDB I/O Pending GT [new]
[success] Percona InnoDB Log GT [new]
[success] Percona MySQL Binary/Relay Logs GT [new]
[success] Percona InnoDB Transactions GT [new]
[success] Percona InnoDB Transactions Active/Locked GT [new]
[success] Percona MySQL Files and Tables GT [new]
[success] Percona MySQL Threads GT [new]
[success] Percona InnoDB Memory Allocation GT [new]
[success] Percona InnoDB Adaptive Hash Index GT [new]
[success] Percona InnoDB Internal Hash Memory Usage GT [new]
[success] Percona InnoDB Tables In Use GT [new]
[success] Percona InnoDB Current Lock Waits GT [new]
[success] Percona InnoDB Lock Structures GT [new]
[success] Percona InnoDB Checkpoint Age GT [new]
[success] Percona InnoDB Row Lock Time GT [new]
[success] Percona InnoDB Row Lock Waits GT [new]
[success] Percona InnoDB Semaphore Waits GT [new]
[success] Percona InnoDB Semaphore Wait Time GT [new]
[success] Percona MySQL Processlist GT [new]
[success] Percona MySQL Transaction Handler GT [new]
[success] Percona MySQL Handlers GT [new]
[success] Percona MySQL Query Time Histogram (Count) GT [new]
[success] Percona MySQL Query Response Time (Microseconds) GT [new]
[success] Percona InnoDB Buffer Pool Efficiency GT [new]
Device Template
[success] Percona MySQL Server HT [new]
[root@centos74 ~]#

 

ss_get_mysql_stats.php 스크립트 테스트

[root@centos74 ~]# php -q /usr/share/cacti/scripts/ss_get_mysql_stats.php --host localhost --items mm,mw,mx,my
mm:48885 mw:0 mx:0 my:0
[root@centos74 ~]#

 

Cacti 설정

http://ip-addr/cacti 로 접속 합니다.

Templates -> Device 매뉴에 보면  추가된 Percona MySQL Server HT 항목을 확인할수 있습니다.

 

Data Source 수정

Templates -> Data Source 로 이동하여 Percona Template 를 검색합니다.

 

Custom Data 항목을 수정합니다.

mariadb cacti 계정정보  를 입력 합니다.

Hostname: localhost
Password: password
Port: 3306
Serviceid: 공란
Username: cacti

 

 

Management -> Devices 로 이동하여 새로운 Device 를 추가합니다.

화면 상단에 있는 Add 버튼을 클릭하여 새로운 Device 를 생성 할수 있습니다.

 

신규 Device 생성 Ex)

General Device Options

Description : Service 명 또는 Device name 입력

Hostname : hostname 또는 ip 주소 입력

Device Template : Percona MySQL Server HT 입력

하단에 있는 Create 버튼을 클릭하여 신규 Device 설정을 마무리 합니다.

 

mariadb 그래프 생성

화면 상단에 있는 Create Graphs for this Device 를 클릭합니다.

모니터링 항목을 선택 합니다.

ex)

Percona InnoDB Buffer Pool GT
Percona InnoDB I/O GT
Percona InnoDB Transactions GT
Percona MySQL Connections GT
Percona MySQL Handlers GT
Percona MySQL Query Cache GT

Create 버튼을 클릭하여 그래프를 생성 합니다.

 

Graphs -> List View 로  이동하여 생성한 그래프를 추가 합니다.

View 버튼을 눌러 설정을 마무리 합니다.

Device 항목에서 : mariadb Device 를 선택한후 10분 정도후 부터 생성된 그래프를 볼수 있습니다.

 

5 ~ 15분이 지나면 아래와 같은 그래프를 확인 할수 있습니다.

DB 모니터링의 경우 System 모니터링보다 시간이 걸립니다.

수고 하셨습니다. 🙂