이전에 docker 를 사용하여 PMM 구성하는 법을 다룬 적이 있는데 이번 글에서는 docker 가 아닌 수동으로 구성하는 방법에 대해 정리해보겠습니다.

https://sarc.io/index.php/mariadb/1268-mariadb-monitoring-pmm

https://sarc.io/index.php/mariadb/1269-mariadb-monitoring-pmm-2

 

-. 설치파일

wget https://github.com/prometheus/prometheus/releases/download/v1.2.3/prometheus-1.2.3.linux-amd64.tar.gz
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.9.0/mysqld_exporter-0.9.0.linux-amd64.tar.gz
wget https://github.com/prometheus/node_exporter/releases/download/v0.13.0-rc.1/node_exporter-0.13.0-rc.1.linux-amd64.tar.gz
wget https://dl.grafana.com/oss/release/grafana-6.4.2.linux-amd64.tar.gz
 
 

- prometheus 설정 (기본 포트 9090)

[root@b871f6768909 prometheus-1.2.3.linux-amd64]# cat prometheus.yml
global:
  scrape_interval:     1m
  scrape_timeout :     1s
scrape_configs:
  - job_name: linux
    static_configs:
      - targets: ['localhost:9100']  #내서버 ip:9100 node_exporter
        labels:
          instance : mysql_OS
  - job_name: mysql
    static_configs:
      - targets: ['localhost:9104'] #내서버 ip:9104 mysql_exporter
        labels:
          instance : mysql_DB
 
[root@b871f6768909 prometheus-1.2.3.linux-amd64]# ./prometheus --config.file=prometheus.yml &
[1] 51

[root@b871f6768909 prometheus-1.2.3.linux-amd64]# INFO[0000] Starting prometheus (version=1.2.3, branch=master, revision=c1eee5b0da2540b9dfd2f70752015b0fce83b616)  source=main.go:75
INFO[0000] Build context (go=go1.7.3, user=root@d8eb84e17a12, date=20161103-21:45:14)  source=main.go:76
INFO[0000] Loading configuration file prometheus.yml     source=main.go:247
INFO[0000] Loading series map and head chunks...         source=storage.go:354
INFO[0000] 648 series loaded.                            source=storage.go:359
WARN[0000] No AlertManagers configured, not dispatching any alerts  source=notifier.go:176
INFO[0000] Listening on :9090                            source=web.go:240
INFO[0000] Starting target manager...                    source=targetmanager.go:76
 
 

-. exporter 설정

 
 ./node_exporter -collectors.enabled="diskstats,filesystem,loadavg,meminfo,netdev,stat,time,uname,vmstat" -log.level=info &

root@55dbcd6f1eaa:/engn001/mysqld_exporter-0.9.0.linux-amd64# cat my.cnf
[client]
user=root
password=!dlatl00
port=3310
socket=/var/run/mysqld/mysqld.sock

./mysqld_exporter -config.my-cnf="./my.cnf" -collect.binlog_size=true -collect.info_schema.processlist=true -web.listen-address=:9104 -log.level=info 
=> mysqld_exporter 가 DB에 접속해서 metric을 수집해야 하기 때문에 접속할 DB정보를 설정해야함
 
root@55dbcd6f1eaa:/engn001/mysqld_exporter-0.9.0.linux-amd64# ./mysqld_exporter --help

Usage of ./mysqld_exporter:
  -collect.auto_increment.columns
     Collect auto_increment columns and max values from information_schema
  -collect.binlog_size
     Collect the current size of all registered binlog files
  -collect.engine_innodb_status
     Collect from SHOW ENGINE INNODB STATUS
  -collect.engine_tokudb_status
     Collect from SHOW ENGINE TOKUDB STATUS
  -collect.global_status
     Collect from SHOW GLOBAL STATUS (default true)
  -collect.global_variables
     Collect from SHOW GLOBAL VARIABLES (default true)
  -collect.info_schema.clientstats
     If running with userstat=1, set to true to collect client statistics
  -collect.info_schema.innodb_metrics
     Collect metrics from information_schema.innodb_metrics
  -collect.info_schema.innodb_tablespaces
     Collect metrics from information_schema.innodb_sys_tablespaces
  -collect.info_schema.processlist
     Collect current thread state counts from the information_schema.processlist
  -collect.info_schema.processlist.min_time int
     Minimum time a thread must be in each state to be counted
  -collect.info_schema.query_response_time
     Collect query response time distribution if query_response_time_stats is ON.
  -collect.info_schema.tables
     Collect metrics from information_schema.tables (default true)
  -collect.info_schema.tables.databases string
     The list of databases to collect table stats for, or '*' for all (default "*")
  -collect.info_schema.tablestats
     If running with userstat=1, set to true to collect table statistics
  -collect.info_schema.userstats
     If running with userstat=1, set to true to collect user statistics
  -collect.perf_schema.eventsstatements
     Collect metrics from performance_schema.events_statements_summary_by_digest
  -collect.perf_schema.eventsstatements.digest_text_limit int
     Maximum length of the normalized statement text (default 120)
  -collect.perf_schema.eventsstatements.limit int
     Limit the number of events statements digests by response time (default 250)
  -collect.perf_schema.eventsstatements.timelimit int
     Limit how old the 'last_seen' events statements can be, in seconds (default 86400)
  -collect.perf_schema.eventswaits
     Collect metrics from performance_schema.events_waits_summary_global_by_event_name
  -collect.perf_schema.file_events
     Collect metrics from performance_schema.file_summary_by_event_name
  -collect.perf_schema.indexiowaits
     Collect metrics from performance_schema.table_io_waits_summary_by_index_usage
  -collect.perf_schema.tableiowaits
     Collect metrics from performance_schema.table_io_waits_summary_by_table
  -collect.perf_schema.tablelocks
     Collect metrics from performance_schema.table_lock_waits_summary_by_table
  -collect.slave_status
     Collect from SHOW SLAVE STATUS (default true)
  -config.my-cnf string
     Path to .my.cnf file to read MySQL credentials from. (default "/root/.my.cnf")
  -log.format value
     If set use a syslog logger or JSON logging. Example: logger:syslog?appname=bob&local=7 or logger:stdout?json=true. Defaults to stderr.
  -log.level value
     Only log messages with the given severity or above. Valid levels: [debug, info, warn, error, fatal]. (default info)
  -log_slow_filter
     Add a log_slow_filter to avoid exessive MySQL slow logging.  NOTE: Not supported by Oracle MySQL.
=> 거의 모든 정보를 수집하고 있어서 grafana 에서 무언가를 더 보고 싶을 때는 exporter 를 수정할 게 아니라
prometheus 쪽 쿼리를 작성하는 게 더 간편함
 
root@55dbcd6f1eaa:/engn001/mysqld_exporter-0.9.0.linux-amd64# ps -ef | grep exporter
root       199   186  0 12:27 pts/1    00:00:00 ./node_exporter -collectors.enabled=diskstats,filesystem,loadavg,meminfo,netdev,stat,time,uname,vmstat -log.level=info
root       228   186  0 13:33 pts/1    00:00:00 ./mysqld_exporter -config.my-cnf=./my.cnf -collect.binlog_size=true -collect.info_schema.processlist=true -web.listen-address=:9104 -log.level=info
 

-. 여러 인스턴스 띄운다면?

- mysqld_exporter 설치 디렉토리(3개의 디렉토리에 각각 설치)
/data/pmm_client/mysqld_exporter/3306
/data/pmm_client/mysqld_exporter/3307
/data/pmm_client/mysqld_exporter/3308

- mysqld_exporter.cnf 파일 생성(3개의 디렉토리에 각각 생성)
/home/mysql/mysqld_exporter_3306/mysqld_exporter_3306.cnf
/home/mysql/mysqld_exporter_3307/mysqld_exporter_3307.cnf
/home/mysql/mysqld_exporter_3308/mysqld_exporter_3308.cnf

- mysqld_exporter 프로세스 기동(3개의 프로세스를 각각 다른 웹 서비스 포트로 기동한다.)
cd /home/mysql/mysqld_exporter_3306/
nohup ./mysqld_exporter -config.my-cnf="/home/mysql/mysqld_exporter_3306/mysqld_exporter_3306.cnf" -collect.binlog_size=true -collect.info_schema.processlist=true -web.listen-address=:9104 -log.level=info > /home/mysql/mysqld_exporter_3306/mysqld_exporter.log 2>&1 &

cd /home/mysql/mysqld_exporter_3307/
nohup ./mysqld_exporter -config.my-cnf="/home/mysql/mysqld_exporter_3307/mysqld_exporter_3307.cnf" -collect.binlog_size=true -collect.info_schema.processlist=true -web.listen-address=:9105 -log.level=info > /home/mysql/mysqld_exporter_3307/mysqld_exporter.log 2>&1 &

cd /home/mysql/mysqld_exporter_3308/
nohup ./mysqld_exporter -config.my-cnf="/home/mysql/mysqld_exporter_3308/mysqld_exporter_3308.cnf" -collect.binlog_size=true -collect.info_schema.processlist=true -web.listen-address=:9106 -log.level=info > /home/mysql/mysqld_exporter_3308/mysqld_exporter.log 2>&1 &

 

-grafana 설정

 
vi ~/grafana/conf/default.ini

[dashboards.json]
enabled = true
path = /engn001/mysql_monitor/grafana/grafana-6.3.6/public/dashboards
 
git clone https://github.com/percona/grafana-dashboards.gitcp grafana-dashboards/dashboards/MySQL*  /engn001/mysql_monitor/grafana/grafana-6.3.6/public/dashboards/

cp grafana-dashboards/dashboards/System* /engn001/mysql_monitor/grafana/grafana-6.3.6/public/dashboards/
cp grafana-dashboards/dashboards/Disk* /engn001/mysql_monitor/grafana/grafana-6.3.6/public/dashboards/
cp grafana-dashboards/dashboards/Trends* /engn001/mysql_monitor/grafana/grafana-6.3.6/public/dashboards/
cp grafana-dashboards/dashboards/Summary* /engn001/mysql_monitor/grafana/grafana-6.3.6/public/dashboards/
=> 필요한 dashboard 들을 download 받아 사용 가능
/engn001/mysql_monitor/grafana/grafana-6.3.6/bin/grafana-server -config ../conf/default.ini start &
 => grafana start
 

- 정리

   * grafana : 3000
   * prometheus : 9090
   * node_exporter : 9100
   * mysqld_exprter :9104( -web.listen-address 포트변경시)