[mariadb] cacti percona plugins

Mariadb 모니터링

Cacti 설치시 시스템의 Disk , Network , Memory , CPU 등의 자원을 모니터링 할수 있습니다.

Percona 설치후 Apache , Nginx , mysql 등의 서비스를 모니터링 할수 있습니다.

자세한 내용은 Percona Site 에서 확인 하실수 있습니다.

Percona Site 에서 확인시 아래와 같은 Templaters 를 사용할수 있으며  Freebsd 에서는

Templaters for Cacti

Percona Monitoring Plugins for Cacti
Frequently Asked Questions on Cacti Templates
Installing Percona Monitoring Plugins for Cacti
Customizing Percona Monitoring Plugins for Cacti
Percona MySQL Monitoring Template for Cacti
Percona Galera/MySQL Monitoring Template for Cacti
Installing SSH-Based Templates
Percona Apache Monitoring Template for Cacti
Percona JMX Monitoring Template for Cacti
Percona Linux Monitoring Template for Cacti
Percona Memcached Monitoring Template for Cacti
Percona MongoDB Monitoring Template for Cacti
Percona Nginx Monitoring Template for Cacti
Percona OpenVZ Monitoring Template for Cacti
Percona Redis Monitoring Template for Cacti
Percona Amazon RDS Monitoring Template for Cacti
Cacti Templates Developer Documentation
Hardening Cacti setup
Upgrading Percona Monitoring Plugins for Cacti

 

freebsd percona-plugins ports 설치시 제공 템플릿

apache_server
galera_server
gnu_linux_server
jmx_server
memcached_server
mongodb_server
mysql_server
nginx_server
openvz_server
rds_server
redis_server

 

percona plugins 설치

root@bsd11:~ # 
root@bsd11:~ # whereis percona-monitoring-plugins
percona-monitoring-plugins: /usr/ports/net-mgmt/percona-monitoring-plugins
root@bsd11:~ # cd /usr/ports/net-mgmt/percona-monitoring-plugins/ && make install clean

 

설치완료후 메세지

====> Compressing man pages (compress-man)
===>  Installing for percona-monitoring-plugins-1.1.3_3
===>  Checking if percona-monitoring-plugins already installed
===>   Registering installation for percona-monitoring-plugins-1.1.3_3
Installing percona-monitoring-plugins-1.1.3_3...
===>  Cleaning for p5-DBD-mysql-4.043
===>  Cleaning for p5-DBI-1.637
===>  Cleaning for p5-Devel-CheckLib-1.11
===>  Cleaning for bash-4.4.12_3
===>  Cleaning for wget-1.19.2
===>  Cleaning for libidn2-2.0.4
===>  Cleaning for libunistring-0.9.7
===>  Cleaning for percona-monitoring-plugins-1.1.3_3
root@bsd11:/usr/ports/net-mgmt/percona-monitoring-plugins #

 

percona-scripts 설정

root@bsd11:~ # 
root@bsd11:/usr/local/share/cacti/scripts # vi ss_get_mysql_stats.php
$mysql_user = 'cacti';
$mysql_pass = 'password';
$mysql_port = 3306;


 

mysql 설정

root@bsd11:~ #
root@bsd11:~ #
root@bsd11:~ # mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 57
Server version: 10.2.10-MariaDB-log FreeBSD Ports

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@bsd11:~ #

 

 

 

/usr/local/share/percona-monitoring-plugins/cacti/templates 디렉토리에 모니터링을 할수 있는 Daemon 의 템플릿들이 있습니다. 

ex) mysql , apache 등등

root@bsd11:/usr/local/share/percona-monitoring-plugins/cacti/templates # ls
cacti_host_template_percona_apache_server_ht_0.8.6i-sver1.1.3.xml
cacti_host_template_percona_galera_server_ht_0.8.6i-sver1.1.3.xml
cacti_host_template_percona_gnu_linux_server_ht_0.8.6i-sver1.1.3.xml
cacti_host_template_percona_jmx_server_ht_0.8.6i-sver1.1.3.xml
cacti_host_template_percona_memcached_server_ht_0.8.6i-sver1.1.3.xml
cacti_host_template_percona_mongodb_server_ht_0.8.6i-sver1.1.3.xml
cacti_host_template_percona_mysql_server_ht_0.8.6i-sver1.1.3.xml
cacti_host_template_percona_nginx_server_ht_0.8.6i-sver1.1.3.xml
cacti_host_template_percona_openvz_server_ht_0.8.6i-sver1.1.3.xml
cacti_host_template_percona_rds_server_ht_0.8.6i-sver1.1.3.xml
cacti_host_template_percona_redis_server_ht_0.8.6i-sver1.1.3.xml
root@bsd11:/usr/local/share/percona-monitoring-plugins/cacti/templates #

 

mysql 템플릿 추가

root@bsd11:~ # 
root@bsd11:~ # 
root@bsd11:~ # php /usr/local/share/cacti/cli/import_template.php --filename=/usr/local/share/percona-monitoring-plugins/cacti/templates/cacti_host_template_percona_mysql_server_ht_0.8.6i-sver1.1.3.xml
Read 888147 bytes of XML data
Import ResultsCacti has imported the following items for the Template:
CDEF
[success] Percona Turn Into Bits CDEF [unchanged]
[success] Percona Negate CDEF [unchanged]
GPRINT Preset
[success] Percona MySQL Server Checksum 348cfd8fa45fa8a5357ab261aa9b00ad [new]
[success] Percona MySQL Server Version t1.1.3:s1.1.3 [new]
[success] Percona Normal [unchanged]
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@bsd11:~ #

 

ss_get_mysql_stats.php 스크립트 테스트

root@bsd11:~ # php -q /usr/local/share/cacti/scripts/ss_get_mysql_stats.php --host localhost --items mm,mw,mx,my
mm:62762 mw:0 mx:0 my:0root@bsd11:~ #

 

 

웹브라우저 설정

data source 수정

 

Mariadb-Server Device 생성

 

Create Graphs for this Device 를 눌러 모니터링 항목을 지정 합니다.

 

모니터링 항목지정

모니터링 항목 지정후 Create 를 눌러 그래프를 생성 합니다.

 

모니터링 결과 확인

(그래프의 경우 약 5 ~ 10분후 확인 가능 합니다.)

 

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

 

 

 

참고 자료

Import 참고 // php 명령어로 Import 시킬수 있으며 Web 브라우저에서도 Import 가능 합니다.  

 

 

 

 

댓글 남기기