MySQL的内部运行信息，可以通过状态变量、一些系统命令和系统表查看。可以用show global status命令采集状态变量。有的状态变量是累加值，有的状态变量是当前值，采集或使用时需要注意。如果用时序数据库来存储指标数据，一般都会提供一些函数（如rate）来计算指标的差值。如果用传统的数据库存储指标数据，一般会在采集的时候就计算好指标的差值。
Purge done for trx's n:o < 117495 undo n:o < 0 state: running but idle History list length 0
Ibuf: size 1, free list len 0, seg size 2, 0 merges Log sequence number 3090029475 Log buffer assigned up to 3090029475 Log buffer completed up to 3090029475 Log written up to 3090029475 Log flushed up to 3090029475 Added dirty pages up to 3090029475 Pages flushed up to 3090029475 Last checkpoint at 3090029475 ...
0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB
# at 1722630 #241031 15:01:15 server id 234 end_log_pos 1722716 CRC32 0xb2d35e04 GTID last_committed=23 sequence_number=24 rbr_only=yes original_committed_timestamp=1730358075343041 immediate_commit_timestamp=1730358075365889 transaction_length=333
使用下面这两个SQL，分别获取协调线程处理事件的延迟，以及Worker线程应用事务的延迟。
1 2 3 4 5 6 7 8 9 10 11
select CHANNEL_NAME, service_state, LAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP - LAST_PROCESSED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP as last_process_delay, PROCESSING_TRANSACTION_START_BUFFER_TIMESTAMP - PROCESSING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP as current_process_delay from replication_applier_status_by_coordinator;
select CHANNEL_NAME, service_state, worker_id, LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP - LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP as last_apply_delay, APPLYING_TRANSACTION_START_APPLY_TIMESTAMP - APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP as current_apply_delay from replication_applier_status_by_worker;
SELECT c.table_schema, c.table_name, column_name, auto_increment, pow(2, case data_type when 'tinyint' then 7 when 'smallint' then 15 when 'mediumint' then 23 when 'int' then 31 when 'bigint' then 63 end+(column_type like '% unsigned'))-1 as max_int FROM information_schema.columns c STRAIGHT_JOIN information_schema.tables t ON ( c.table_schema=t.table_schema AND c.table_name=t.table_name) WHERE c.extra = 'auto_increment' AND t.auto_increment IS NOT NULL and c.table_schema not in ('mysql') +--------------+------------+-------------+----------------+------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | AUTO_INCREMENT | max_int | +--------------+------------+-------------+----------------+------------+ | db01 | t02 | id | 32767 | 32767 | | db01 | t01 | id | 2147483647 | 2147483647 | +--------------+------------+-------------+----------------+------------+
表占用的空间，特别是大表的空间，也可以监控起来。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ifnull(ENGINE, 'NONE') as ENGINE, ifnull(TABLE_ROWS, '0') as TABLE_ROWS, ifnull(DATA_LENGTH, '0') as DATA_LENGTH, ifnull(INDEX_LENGTH, '0') as INDEX_LENGTH, ifnull(DATA_FREE, '0') as DATA_FREE FROM information_schema.tables WHERE TABLE_SCHEMA not in ('mysql', 'information_schema', 'performance_schema', 'sys') +--------------+------------+------------+--------+------------+-------------+--------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | +--------------+------------+------------+--------+------------+-------------+--------------+-----------+ | db01 | ta | BASE TABLE | InnoDB | 2 | 16384 | 0 | 5242880 | | db01 | t01 | BASE TABLE | InnoDB | 1 | 16384 | 0 | 0 |
CREATE USER 'mon'@'172.16.%' IDENTIFIED BY 'somepass' WITH MAX_USER_CONNECTIONS 10; GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'mon'@'172.16.%';
CREATE USER 'mon'@'127.0.0.1' IDENTIFIED BY 'somepass' WITH MAX_USER_CONNECTIONS 10; GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'mon'@'127.0.0.1';
# cat .my.cnf [client] user = mon password = somepass
mysqld_exporter默认端口是9104，访问这个端口，确认exporter是否正常。
1 2 3 4 5 6 7 8
# curl http://172.16.121.234:9104/metrics
# HELP go_gc_duration_seconds A summary of the wall-time pause (stop-the-world) duration in garbage collection cycles. # TYPE go_gc_duration_seconds summary go_gc_duration_seconds{quantile="0"} 3.4301e-05 go_gc_duration_seconds{quantile="0.25"} 7.3643e-05 go_gc_duration_seconds{quantile="0.5"} 0.000115865 ...
... # HELP mysql_global_status_buffer_pool_pages Innodb buffer pool pages by state. # TYPE mysql_global_status_buffer_pool_pages gauge mysql_global_status_buffer_pool_pages{state="data"} 2486 mysql_global_status_buffer_pool_pages{state="free"} 5689 mysql_global_status_buffer_pool_pages{state="misc"} 17
# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 70540cffcdc4 percona/pmm-server:2 "/opt/entrypoint.sh" 16 hours ago Up 16 hours (unhealthy) 80/tcp, 0.0.0.0:443->443/tcp pmm-server
