Extending and Customizing Percona Monitoring and Management

1 .Extending and Customizing Percona Monitoring and Management (PMM) Agustín Gallego Support Engineer 

2 .Agenda • What is Percona Monitoring and Management (PMM)? • How to extend its functionality ▪ Adding external exporters ▪ Getting data from custom queries ▪ Extending collected metrics ▪ Editing dashboards ▪ Providing semantics to graphs with annotations 

3 .What is Percona Monitoring and Management? 

4 .What is Percona Monitoring and Management? • Open Source software (as all Percona software) • A collection of tools: ▪ Prometheus ▪ Grafana ▪ Nginx ▪ Consul ▪ Query Analytics ▪ and more... ▪ https://github.com/percona/pmm/tree/PMM-2.0 

5 .What is Percona Monitoring and Management? 

6 .What is Percona Monitoring and Management? • It's easy to deploy and test drive! ▪ https://www.percona.com/doc/percona-monitoring-and- management/deploy/index.html ▪ There are three deployment methods: ▪ Docker ▪ OVA (Open Virtual Appliance) ▪ AMI (Amazon Machine Instance) 

7 .What is Percona Monitoring and Management? • https://pmmdemo.percona.com/ 

8 .PMM functionality 

9 .Out-of-the-box Support • PMM offers native support for: ▪ MySQL / Percona Server for MySQL ▪ MariaDB ▪ MongoDB / Percona Server for MongoDB ▪ PostgreSQL ▪ Percona XtraDB Cluster ▪ ProxySQL ▪ Amazon RDS / Aurora MySQL ▪ Linux (OS metrics) 

10 .Extending PMM's Functionality 

11 .Extending PMM's functionality • We are going to go through five different ways: ▪ Adding external exporters ▪ Getting data from custom queries ▪ Getting data from custom scripts ▪ Extending dashboards ▪ Providing semantics to graphs with annotations 

12 .Adding external exporters 

13 .Adding external exporters • Introducing ClickHouse ▪ https://clickhouse.yandex/ 

14 .Adding external exporters • We will use Docker to emulate our environment: ▪ one ClickHouse container ▪ using ports 9000 (CLI) and 8123 (HTTP) ▪ one ClickHouse exporter container ▪ using port 9116 

15 .Adding external exporters agustin@bm-support01 ~ $ docker network create --driver bridge clickhouse-network d7f02a5841bceffb2cf3455aa0322244c9bef74a8aa4607665ea5f255085bda0 agustin@bm-support01 ~ $ docker run -d \ > --publish 8123:8123 \ > --publish 9000:9000 \ > --name clickhouse \ > --network clickhouse-network \ > guriandoro/clickhouse-pmm:1.0 0c5bc6e217ebab9a862076d90fe1ebc0681c71093770cb170bbaea9353380993 agustin@bm-support01 ~ $ curl 'http://localhost:8123/' Ok. 

16 .Adding external exporters agustin@bm-support01 ~ $ docker run -it --rm --network host yandex/clickhouse-client -- host localhost ClickHouse client version 19.5.2.6 (official build). Connecting to localhost:9000 as user default. Connected to ClickHouse server version 1.1.54380 revision 54380. 0c5bc6e217eb :) show databases; SHOW DATABASES !"name""""# $ default $ $ system $ %"""""""""& 2 rows in set. Elapsed: 0.014 sec. 

17 .Adding external exporters agustin@bm-support01 ~ $ docker run -d \ > --publish 9116:9116 \ > --name clickhouse-exporter \ > --network clickhouse-network \ > f1yegor/clickhouse-exporter -scrape_uri=http://clickhouse:8123/ b8c9e30cc057e75eef2894892ca36f13b7e09946818904d33c414c7c1c3985df agustin@bm-support01 ~ $ curl -s 'http://localhost:9116/metrics' | head -n6 # HELP clickhouse_arena_alloc_bytes_total Number of ArenaAllocBytes total processed # TYPE clickhouse_arena_alloc_bytes_total counter clickhouse_arena_alloc_bytes_total 4096 # HELP clickhouse_arena_alloc_chunks_total Number of ArenaAllocChunks total processed # TYPE clickhouse_arena_alloc_chunks_total counter clickhouse_arena_alloc_chunks_total 1 

18 .Adding external exporters agustin@bm-support01 ~ $ pmm-admin add external:metrics clickhouse 172.31.0.3:9116 External metrics added. agustin@bm-support01 ~ $ pmm-admin list pmm-admin 1.17.1 PMM Server | 127.0.0.1 (password-protected) Client Name | bm-support01.bm.int.percona.com Client Address | 172.17.0.1 Service Manager | linux-systemd -------------- ----------- ----------- -------- -------------------------------------------- -------- SERVICE TYPE NAME LOCAL PORT RUNNING DATA SOURCE OPTIONS -------------- ----------- ----------- -------- -------------------------------------------- -------- mysql:metrics perf_mysql 42002 YES root:***@tcp(127.0.0.1:19125) mysql:metrics ps_5.7 42003 YES root:***@unix(/tmp/mysql_sandbox22389.sock) Job name Scrape interval Scrape timeout Metrics path Scheme Target Labels Health clickhouse 1m0s 10s /metrics http 172.31.0.3:9116 

19 .Adding external exporters • We now need to add a Dashboard that can show the newly collected data 

20 .Adding external exporters 

21 .Adding external exporters 

22 .Adding external exporters 

23 .Adding external exporters 

24 .Getting data from custom queries 

25 .Getting data from custom queries • Example from DGB's detailed blogpost: ▪ PMM’s Custom Queries in Action: Adding a Graph for InnoDB mutex waits 

26 .Getting data from custom queries • Example from DGB's detailed blogpost: ▪ PMM’s Custom Queries in Action: Adding a Graph for InnoDB mutex waits ▪ Introduced in PMM 1.15.0 ▪ By default checks the following file (every 60 seconds): ▪ /usr/local/percona/pmm-client/queries-mysqld.yml ▪ But it can be overridden with: ▪ pmm-admin add mysql:metrics -- --queries-file- name=\
 /usr/local/percona/pmm-client/custom-query.yml 

27 .Getting data from custom queries mysql> SELECT @@global.performance_schema; +-----------------------------+ | @@global.performance_schema | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.00 sec) mysql> UPDATE performance_schema.setup_instruments SET enabled='YES' WHERE name LIKE 'wait/ synch/mutex/innodb%'; Query OK, 63 rows affected (0.01 sec) Rows matched: 63 Changed: 63 Warnings: 0 mysql> UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name LIKE 'events_waits%'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 

28 .Getting data from custom queries agustin@bm-support01 ~ $ cat /usr/local/percona/pmm-client/queries-mysqld.yml mysql_global_status_innodb_mutex: query: "SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT FROM performance_schema.events_waits_summary_global_by_event_name WHERE EVENT_NAME LIKE 'wait/synch/mutex/innodb/%'" metrics: - EVENT_NAME: usage: "LABEL" description: "Name of the mutex" - COUNT_STAR: usage: "COUNTER" description: "Number of calls" - SUM_TIMER_WAIT: usage: "GAUGE" description: "Duration" 

29 .Getting data from custom queries