Hi @Vadim_Yalovets
I didn’t add custom queries. I installed an agent to additionally register the current server(hostname:test) to the pmm server I am already using.
– process
root 2462200 1 0 00:35 ? 00:00:04 /usr/sbin/pmm-agent --config-file=/usr/local/percona/pmm2/config/pmm-agent.yaml
root 2466954 2462200 1 00:37 ? 00:07:35 /usr/local/percona/pmm2/exporters/node_exporter --collector.bonding --collector.buddyinfo --collector.cpu --collector.diskstats --collector.entropy --collector.filefd --collector.filesystem --collector.hwmon --collector.loadavg --collector.meminfo --collector.meminfo_numa --collector.netdev --collector.netstat --collector.netstat.fields=^(.*_(InErrors|InErrs|InCsumErrors)|Tcp_(ActiveOpens|PassiveOpens|RetransSegs|CurrEstab|AttemptFails|OutSegs|InSegs|EstabResets|OutRsts|OutSegs)|Tcp_Rto(Algorithm|Min|Max)|Udp_(RcvbufErrors|SndbufErrors)|Udp(6?|Lite6?)_(InDatagrams|OutDatagrams|RcvbufErrors|SndbufErrors|NoPorts)|Icmp6?_(OutEchoReps|OutEchos|InEchos|InEchoReps|InAddrMaskReps|InAddrMasks|OutAddrMaskReps|OutAddrMasks|InTimestampReps|InTimestamps|OutTimestampReps|OutTimestamps|OutErrors|InDestUnreachs|OutDestUnreachs|InTimeExcds|InRedirects|OutRedirects|InMsgs|OutMsgs)|IcmpMsg_(InType3|OutType3)|Ip(6|Ext)_(InOctets|OutOctets)|Ip_Forwarding|TcpExt_(Listen.*|Syncookies.*|TCPTimeouts))$ --collector.processes --collector.standard.go --collector.standard.process --collector.stat --collector.textfile.directory.hr=/usr/local/percona/pmm2/collectors/textfile-collector/high-resolution --collector.textfile.directory.lr=/usr/local/percona/pmm2/collectors/textfile-collector/low-resolution --collector.textfile.directory.mr=/usr/local/percona/pmm2/collectors/textfile-collector/medium-resolution --collector.textfile.hr --collector.textfile.lr --collector.textfile.mr --collector.time --collector.uname --collector.vmstat --collector.vmstat.fields=^(pg(steal_(kswapd|direct)|refill|alloc)_(movable|normal|dma3?2?)|nr_(dirty.*|slab.*|vmscan.*|isolated.*|free.*|shmem.*|i?n?active.*|anon_transparent_.*|writeback.*|unstable|unevictable|mlock|mapped|bounce|page_table_pages|kernel_stack)|drop_slab|slabs_scanned|pgd?e?activate|pgpg(in|out)|pswp(in|out)|pgm?a?j?fault)$ --no-collector.arp --no-collector.bcache --no-collector.conntrack --no-collector.drbd --no-collector.edac --no-collector.infiniband --no-collector.interrupts --no-collector.ipvs --no-collector.ksmd --no-collector.logind --no-collector.mdadm --no-collector.mountstats --no-collector.netclass --no-collector.nfs --no-collector.nfsd --no-collector.ntp --no-collector.qdisc --no-collector.runit --no-collector.sockstat --no-collector.supervisord --no-collector.systemd --no-collector.tcpstat --no-collector.timex --no-collector.wifi --no-collector.xfs --no-collector.zfs --web.disable-exporter-metrics --web.listen-address=:42000
root 2469726 2462200 0 00:37 ? 00:03:22 /usr/local/percona/pmm2/exporters/mysqld_exporter --collect.binlog_size --collect.custom_query.hr --collect.custom_query.hr.directory=/usr/local/percona/pmm2/collectors/custom-queries/mysql/high-resolution --collect.custom_query.lr --collect.custom_query.lr.directory=/usr/local/percona/pmm2/collectors/custom-queries/mysql/low-resolution --collect.custom_query.mr --collect.custom_query.mr.directory=/usr/local/percona/pmm2/collectors/custom-queries/mysql/medium-resolution --collect.engine_innodb_status --collect.engine_tokudb_status --collect.global_status --collect.global_variables --collect.heartbeat --collect.info_schema.clientstats --collect.info_schema.innodb_cmp --collect.info_schema.innodb_cmpmem --collect.info_schema.innodb_metrics --collect.info_schema.innodb_tablespaces --collect.info_schema.processlist --collect.info_schema.query_response_time --collect.info_schema.userstats --collect.perf_schema.eventsstatements --collect.perf_schema.eventswaits --collect.perf_schema.file_events --collect.perf_schema.file_instances --collect.slave_status --collect.standard.go --collect.standard.process --exporter.conn-max-lifetime=55s --exporter.global-conn-pool --exporter.max-idle-conns=3 --exporter.max-open-conns=3 --web.listen-address=:42001
– directory and file
[root@test:/usr/local/percona/pmm2/collectors/custom-queries/mysql]# ll
total 0
drwxr-xr-x 2 pmm-agent pmm-agent 76 May 18 00:35 high-resolution
drwxr-xr-x 2 pmm-agent pmm-agent 32 May 18 00:35 low-resolution
drwxr-xr-x 2 pmm-agent pmm-agent 32 May 18 00:35 medium-resolution
[root@test:/usr/local/percona/pmm2/collectors/custom-queries/mysql]# cd high-resolution/
[root@test:/usr/local/percona/pmm2/collectors/custom-queries/mysql/high-resolution]# ll
total 16
-rw-rw---- 1 pmm-agent pmm-agent 9772 Mar 20 05:57 queries-mysqld-group-replication.yml
-rw-rw---- 1 pmm-agent pmm-agent 1108 Mar 20 05:57 queries-mysqld.yml
[root@test:/usr/local/percona/pmm2/collectors/custom-queries/mysql/high-resolution]# cat queries-mysqld-group-replication.yml
mysql_perf_schema_replication_group:
query: "/*!80000 SELECT CHANNEL_NAME as channel_name, MEMBER_ID as member_id, MEMBER_HOST as member_host, MEMBER_PORT as member_port, MEMBER_STATE as member_state, MEMBER_ROLE as member_role, MEMBER_VERSION as member_version, CASE WHEN MEMBER_STATE = 'ONLINE' THEN 1 WHEN MEMBER_STATE = 'RECOVERING' THEN 2 WHEN MEMBER_STATE = 'OFFLINE' THEN 3 WHEN MEMBER_STATE = 'ERROR' THEN 4 WHEN MEMBER_STATE = 'UNREACHABLE' THEN 5 END as member_info FROM performance_schema.replication_group_members WHERE MEMBER_ID=@@server_uuid */"
metrics:
- channel_name:
usage: "LABEL"
description: "Name of the Group Replication channel."
- member_id:
usage: "LABEL"
description: "The member server UUID. This has a different value for each member in the group."
- member_host:
usage: "LABEL"
description: "Network address of this member (host name or IP address)."
- member_port:
usage: "LABEL"
description: "Port on which the server is listening."
- member_state:
usage: "LABEL"
description: "Current state of this member."
- member_role:
usage: "LABEL"
description: "Role of the member in the group, either PRIMARY or SECONDARY."
- member_version:
usage: "LABEL"
description: "MySQL version of the member."
- member_info:
usage: "GAUGE"
description: "Show Group Replication Server States."
mysql_perf_schema_replication_group_5:
query: "/*!50700 SELECT CHANNEL_NAME as channel_name, MEMBER_ID as member_id, MEMBER_HOST as member_host, MEMBER_PORT as member_port, MEMBER_STATE as member_state, CASE WHEN MEMBER_STATE = 'ONLINE' THEN 1 WHEN MEMBER_STATE = 'RECOVERING' THEN 2 WHEN MEMBER_STATE = 'OFFLINE' THEN 3 WHEN MEMBER_STATE = 'ERROR' THEN 4 WHEN MEMBER_STATE = 'UNREACHABLE' THEN 5 END as member_info FROM performance_schema.replication_group_members WHERE MEMBER_ID=@@server_uuid and (SELECT SUBSTRING(@@VERSION,1,1) = 5) */"
metrics:
- channel_name:
usage: "LABEL"
description: "Name of the Group Replication channel."
- member_id:
usage: "LABEL"
description: "The member server UUID. This has a different value for each member in the group."
- member_host:
usage: "LABEL"
description: "Network address of this member (host name or IP address)."
- member_port:
usage: "LABEL"
description: "Port on which the server is listening."
- member_state:
usage: "LABEL"
description: "Current state of this member."
- member_info:
usage: "GAUGE"
description: "Show Group Replication Server States."
mysql_perf_schema:
query: "/*!80000 SELECT COUNT_TRANSACTIONS_IN_QUEUE as transactions_in_queue, COUNT_TRANSACTIONS_CHECKED as transactions_checked_total, COUNT_CONFLICTS_DETECTED as conflicts_detected_total, COUNT_TRANSACTIONS_ROWS_VALIDATING as transactions_rows_validating_total, COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE as transactions_remote_in_applier_queue, COUNT_TRANSACTIONS_REMOTE_APPLIED as transactions_remote_applied_total, COUNT_TRANSACTIONS_LOCAL_PROPOSED as transactions_local_proposed_total, COUNT_TRANSACTIONS_LOCAL_ROLLBACK as transactions_local_rollback_total FROM performance_schema.replication_group_member_stats WHERE MEMBER_ID=@@server_uuid */"
metrics:
- transactions_in_queue:
usage: "GAUGE"
description: "The number of transactions in the queue pending conflict detection checks."
- transactions_checked_total:
usage: "GAUGE"
description: "The number of transactions that have been checked for conflicts."
- conflicts_detected_total:
usage: "GAUGE"
description: "The number of transactions that have not passed the conflict detection check."
- transactions_rows_validating_total:
usage: "GAUGE"
description: "Number of transaction rows which can be used for certification, but have not been garbage collected."
- transactions_remote_in_applier_queue:
usage: "GAUGE"
description: "The number of transactions that this member has received from the replication group which are waiting to be applied."
- transactions_remote_applied_total:
usage: "GAUGE"
description: "Number of transactions this member has received from the group and applied."
- transactions_local_proposed_total:
usage: "GAUGE"
description: "Number of transactions which originated on this member and were sent to the group."
- transactions_local_rollback_total:
usage: "GAUGE"
description: "Number of transactions which originated on this member and were rolled back by the group."
mysql_perf_schema_5:
query: "/*!50700 SELECT COUNT_TRANSACTIONS_IN_QUEUE as transactions_in_queue, COUNT_TRANSACTIONS_CHECKED as transactions_checked_total, COUNT_CONFLICTS_DETECTED as conflicts_detected_total, COUNT_TRANSACTIONS_ROWS_VALIDATING as transactions_rows_validating_total FROM performance_schema.replication_group_member_stats WHERE MEMBER_ID=@@server_uuid and (SELECT SUBSTRING(@@VERSION,1,1) = 5) */"
metrics:
- transactions_in_queue:
usage: "GAUGE"
description: "The number of transactions in the queue pending conflict detection checks."
- transactions_checked_total:
usage: "GAUGE"
description: "The number of transactions that have been checked for conflicts."
- conflicts_detected_total:
usage: "GAUGE"
description: "The number of transactions that have not passed the conflict detection check."
- transactions_rows_validating_total:
usage: "GAUGE"
description: "Number of transaction rows which can be used for certification, but have not been garbage collected."
- transactions_local_rollback_total:
usage: "GAUGE"
description: "Number of transactions which originated on this member and were rolled back by the group."
mysql_perf_schema_replication_group_worker:
query: "/*!80000 SELECT conn_status.channel_name as channel_name,
conn_status.service_state as IO_thread,
applier_status.service_state as SQL_thread,
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP - LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP 'rep_delay_seconds',
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP - LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP 'transport_time_seconds',
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP - LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP 'time_RL_seconds',
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP - LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP 'apply_time_seconds',
if(GTID_SUBTRACT(LAST_QUEUED_TRANSACTION, LAST_APPLIED_TRANSACTION) = '','0' , abs(time_to_sec(if(time_to_sec(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)=0,0,timediff(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,now()))))) `lag_in_seconds`
FROM performance_schema.replication_connection_status AS conn_status JOIN performance_schema.replication_applier_status_by_worker AS applier_status
ON applier_status.channel_name = conn_status.channel_name WHERE conn_status.service_state = 'ON' ORDER BY lag_in_seconds, lag_in_seconds desc */"
metrics:
- channel_name:
usage: "LABEL"
description: "The replication channel which this row is displaying. There is always a default replication channel, and more replication channels can be added."
- IO_thread:
usage: "LABEL"
description: "ON (thread exists and is active or idle), OFF (thread no longer exists), or CONNECTING (thread exists and is connecting to the source)."
- SQL_thread:
usage: "LABEL"
description: "ON (thread exists and is active or idle) or OFF (thread no longer exists)."
- rep_delay_seconds:
usage: "GAUGE"
description: "Time difference from the moment the transaction has being COMMITTED on the Primary and the time transaction exit the queue to be apply in the local Replica"
- transport_time_seconds:
usage: "GAUGE"
description: "Time difference from the moment the transaction is COMMITTED on the Primary and the time transaction ENTER the local queue on the Replica"
- time_RL_seconds:
usage: "GAUGE"
description: "Time spent by transaction inside the local queue in the Replica"
- apply_time_seconds:
usage: "GAUGE"
description: "Time pass to apply the transaction on the local node"
- lag_in_seconds:
usage: "GAUGE"
description: "Lag in seconds from when the LAST transaction is COMMITTED in the Primary and the time on local Replica "
mysql_perf_schema_replication_group_worker_5:
query: "/*!50700 SELECT conn_status.channel_name as channel_name,
conn_status.service_state as IO_thread,
applier_status.service_state as SQL_thread,
1 as info
FROM performance_schema.replication_connection_status AS conn_status JOIN performance_schema.replication_applier_status_by_worker AS applier_status
ON applier_status.channel_name = conn_status.channel_name WHERE conn_status.service_state = 'ON' and (SELECT SUBSTRING(@@VERSION,1,1) = 5) */"
metrics:
- channel_name:
usage: "LABEL"
description: "The replication channel which this row is displaying. There is always a default replication channel, and more replication channels can be added."
- IO_thread:
usage: "LABEL"
description: "ON (thread exists and is active or idle), OFF (thread no longer exists), or CONNECTING (thread exists and is connecting to the source)."
- SQL_thread:
usage: "LABEL"
description: "ON (thread exists and is active or idle) or OFF (thread no longer exists)."
- info:
usage: "GAUGE"
description: "Info value"
[root@test:/usr/local/percona/pmm2/collectors/custom-queries/mysql/high-resolution]# cat queries-mysqld.yml
## Custom query example.
#mysql_performance_schema: ## The namespace (prefix of the metric name) for the custom query. See https://prometheus.io/docs/practices/naming/#metric-names for details.
# query: "SELECT event_name, current_count, high_count FROM sys.memory_global_by_current_bytes WHERE current_count > 0;"
# metrics: ## List of metrics.
# - event_name: ## The alias mapped to a value returned by a query to the Prometheus label https://prometheus.io/docs/practices/naming/#labels
# usage: "LABEL" ## If usage is LABEL this value will be used as Prometheus dimension.
# description: "Performance Schema Event Name"
# - current_count: ## The name of the metric. See https://prometheus.io/docs/practices/naming/#metric-names
# usage: "GAUGE" ## The type of the metric. It should be be the one from the following: COUNTER, GAUGE, MAPPEDMETRIC, DURATION, DISCARD.
# description: "Memory currently allocated to the Event"
# - high_count:
# usage: "GAUGE"
# description: "High Water Mark of Memory allocated to the Event" ## Description of the metric.