PerconaToolkit使用之pt-mysql-summary（代码实例）
程序员文章站
2023-03-11 23:10:39
pt-mysql-summary的功能是以优雅的形式汇总MySQL服务器信息。
用法如下：
pt-mysql-summary [OPTIONS]
pt-mysql-sum...
pt-mysql-summary的功能是以优雅的形式汇总MySQL服务器信息。
用法如下：
pt-mysql-summary [OPTIONS]
pt-mysql-summary方便地汇总出MySQL数据库服务器的状态和配置，使你可以一目了然地获得这些信息。这不是一个调优或者诊断的工具。它会生成一个报告，可以很容易地diff或者不丢失格式地复制到Email里。它应该可以很好地运行于任何现代Unix系统。
pt-mysql-summary通过连接到MySQL数据库服务器然后执行状态和配置信息查询的方式运行。它将这些零碎信息保存到临时目录的文件中，然后借助awk和其他脚本语言来整齐格式化。
要使用它，只需要运行即可。作为可选可以加双“ - ”后接你用来连接MySQL时相同的命令行选项，如下示例：
pt-mysql-summary --user=root
该工具与运行它的服务器进行最低限度地调用。它假定你运行它的服务器和要进行检查的服务器是同一个，因此，它假定可以找到配置文件，例如my.cnf。然而如果不是这种情况，它可能优雅地折损。请注意，它的输出并没有指出哪些信息来自于MySQL数据库；哪些信息来自于操作系统主机。所以如果你在一台服务器上运行该工具，然后连接访问另一台服务器上的MySQL，就有可能产生混乱的输出。
该工具的很多输出值都故意作了近似舍入处理以展示它们的量级而不是精确的细节。这称为“fuzzy-rounding ”。该设计认为一台服务器是918QPS还是921QPS并不打紧，如此小的差异微不足道，而只会使得输出同其他服务器作比较变得困难。随着输入的增大，fuzzy-rounding也以更大的量级近似舍入。刚开始它舍入到最近的5，然后是最近的10，最近的25，然后以一个10倍大的因数重复（50,100,250），以此类推，随着输入的增大。以下是该工具产生的报告的一个示例。
前两部分展示该报告生成自哪台服务器以及有哪些MySQL实例运行在该服务器上。这取自ps的输出。虽然并不是总能检查到所有的实例和参数，但往往运行效果还是不错的。
# Percona Toolkit MySQL Summary Report #######################
System time | 2012-03-30 18:46:05 UTC
(local TZ: EDT -0400)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
12345 /tmp/12345/data 0 0 /tmp/12345.sock
12346 /tmp/12346/data 0 0 /tmp/12346.sock
12347 /tmp/12347/data 0 0 /tmp/12347.sock
往下是单个MySQL实例的报告。该部分是MySQL实例的快速汇总。“ Time ”值生成自MySQL服务器，不同于之前部分打印的系统日期和时间，可以以此判断数据库时间和操作系统时间是否匹配。
# Report On Port 12345 #######################################
User | msandbox@%
Time | 2012-03-30 14:46:05 (EDT)
Hostname | localhost.localdomain
Version | 5.5.20-log MySQL Community Server (GPL)
Built On | linux2.6 i686
Started | 2012-03-28 23:33 (up 1+15:12:09)
Databases | 4
Datadir | /tmp/12345/data/
Processes | 2 connected, 2 running
Replication | Is not a slave, has 1 slaves connected
Pidfile | /tmp/12345/data/12345.pid (exists)
接下来部分是“SHOW PROCESSLIST ”输出的汇总。
# Processlist ################################################ Command COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- Binlog Dump 1 1 150000 150000 Query 1 1 0 0 User COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- msandbox 2 2 150000 150000 Host COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- localhost 2 2 150000 150000 db COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- NULL 2 2 150000 150000 State COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- Master has sent all binlog to 1 1 150000 150000 NULL 1 1 0 0
大约间隔10秒的两份“SHOW GLOBAL STATUS ”快照计算而来的select计数。
# Status Counters (Wait 10 Seconds) ########################## Variable Per day Per second 10 secs Binlog_cache_disk_use 4 Binlog_cache_use 80 Bytes_received 15000000 175 200 Bytes_sent 15000000 175 2000 Com_admin_commands 1 ...................(many lines omitted)............................ Threads_created 40 1 Uptime 90000 1 1
表缓存大小。
# Table cache ################################################
Size | 400
Usage | 15%
Percona Server特性汇总部分。标准版本MySQL因为没有启用，会显示为不支持。
# Key Percona Server features ################################
Table & Index Stats | Not Supported
Multiple I/O Threads | Enabled
Corruption Resilient | Not Supported
Durable Replication | Not Supported
Import InnoDB Tables | Not Supported
Fast Server Restarts | Not Supported
Enhanced Logging | Not Supported
Replica Perf Logging | Not Supported
Response Time Hist. | Not Supported
Smooth Flushing | Not Supported
HandlerSocket NoSQL | Not Supported
Fast Hash UDFs | Unknown
插件汇总部分。
# Plugins ####################################################
InnoDB compression | ACTIVE
查询缓冲部分。
# Query cache ################################################
query_cache_type | ON
Size | 0.0
Usage | 0%
HitToInsertRatio | 0%
schema部分。生成自“mysqldump --no-data ”而不是查询“INFORMATION_SCHEMA ”以避免对繁忙的服务器造成影响。
# Schema #####################################################
Database Tables Views SPs Trigs Funcs FKs Partn
mysql 24
performance_schema 17
sakila 16 7 3 6 3 22
Database MyISAM CSV PERFORMANCE_SCHEMA InnoDB
mysql 22 2
performance_schema 17
sakila 8 15
Database BTREE FULLTEXT
mysql 31
performance_schema
sakila 63 1
c t s e l d i t m v s
h i e n o a n i e a m
a m t u n t t n d r a
r e m g e y i c l
s b t i u h l
t l i n m a i
a o m t t r n
m b e e t
p x
t
Database === === === === === === === === === === ===
mysql 61 10 6 78 5 4 26 3 4 5 3
performance_schema 5 16 33
sakila 1 15 1 3 4 3 19 42 26
特定技术使用情况部分。
# Noteworthy Technologies ####################################
Full Text Indexing | Yes
Geospatial Types | No
Foreign Keys | Yes
Partitioning | No
InnoDB Compression | Yes
SSL | No
Explicit LOCK TABLES | No
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | No
Prepared statement count | 0
InnoDB存储引擎重要配置参数部分。
# InnoDB #####################################################
Version | 1.1.8
Buffer Pool Size | 16.0M
Buffer Pool Fill | 100%
Buffer Pool Dirty | 0%
File Per Table | OFF
Page Size | 16k
Log File Size | 2 * 5.0M = 10.0M
Log Buffer Size | 8M
Flush Method |
Flush Log At Commit | 1
XA Support | ON
Checksums | ON
Doublewrite | ON
R/W I/O Threads | 4 4
I/O Capacity | 200
Thread Concurrency | 0
Concurrency Tickets | 500
Commit Concurrency | 0
Txn Isolation Level | REPEATABLE-READ
Adaptive Flushing | ON
Adaptive Checkpoint |
Checkpoint Age | 0
InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
Oldest Transaction | 0 Seconds
History List Len | 209
Read Views | 1
Undo Log Entries | 1 transactions, 1 total undo, 1 max undo
Pending I/O Reads | 0 buf pool reads, 0 normal AIO,
0 ibuf AIO, 0 preads
Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page);
0 AIO, 0 sync, 0 log IO (0 log, 0 chkp);
0 pwrites
Pending I/O Flushes | 0 buf pool, 0 log
Transaction States | 1xnot started
MyISAM存储引擎key cache使用情况部分。
# MyISAM #####################################################
Key Cache | 16.0M
Pct Used | 10%
Unflushed | 0%
连接用户部分。生成自“ mysql ”系统库。
# Security ###################################################
Users | 2 users, 0 anon, 0 w/o pw, 0 old pw
Old Passwords | OFF
二进制日志部分。
# Binary Logging #############################################
Binlogs | 1
Zero-Sized | 0
Total Size | 21.8M
binlog_format | STATEMENT
expire_logs_days | 0
sync_binlog | 0
server_id | 12345
binlog_do_db |
binlog_ignore_db |
服务器配置参数部分。
# Noteworthy Variables #######################################
Auto-Inc Incr/Offset | 1/1
default_storage_engine | InnoDB
flush_time | 0
init_connect |
init_file |
sql_mode |
join_buffer_size | 128k
sort_buffer_size | 2M
read_buffer_size | 128k
read_rnd_buffer_size | 256k
bulk_insert_buffer | 0.00
max_heap_table_size | 16M
tmp_table_size | 16M
max_allowed_packet | 1M
thread_stack | 192k
log | OFF
log_error | /tmp/12345/data/mysqld.log
log_warnings | 1
log_slow_queries | ON
log_queries_not_using_indexes | OFF
log_slave_updates | ON
美化版的my.cnf配置文件部分。
# Configuration File #########################################
Config File | /tmp/12345/my.sandbox.cnf
[client]
user = msandbox
password = msandbox
port = 12345
socket = /tmp/12345/mysql_sandbox12345.sock
[mysqld]
port = 12345
socket = /tmp/12345/mysql_sandbox12345.sock
pid-file = /tmp/12345/data/mysql_sandbox12345.pid
basedir = /home/baron/5.5.20
datadir = /tmp/12345/data
key_buffer_size = 16M
innodb_buffer_pool_size = 16M
innodb_data_home_dir = /tmp/12345/data
innodb_log_group_home_dir = /tmp/12345/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_file_size = 5M
log-bin = mysql-bin
relay_log = mysql-relay-bin
log_slave_updates
server-id = 12345
report-host = 127.0.0.1
report-port = 12345
log-error = mysqld.log
innodb_lock_wait_timeout = 3
# The End ####################################################
以下为个人本地环境的测试数据。
root@ubuntu:~# pt-mysql-summary --host=192.168.112.129 --port=3306 --user=root --password=123456 --all-databases
mysql: [Warning] Using a password on the command line interface can be insecure.
# Percona Toolkit MySQL Summary Report #######################
System time | 2018-04-07 19:26:17 UTC (local TZ: CST +0800)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
/usr/local/mysql/data 0 0 /tmp/mysql.sock
# MySQL Executable ###########################################
Path to executable | /usr/local/mysql/bin/mysqld
Has symbols | Yes
# Slave Hosts ################################################
*************************** 1. row ***************************
Server_id: 1025
Host: 192.168.136.128
User: root
Password: 123456
Port: 3306
Master_id: 1024
Slave_UUID: b5fe6950-6c2a-11e7-a60a-000c29c6454d
# Report On Port 3306 ########################################
User | root@%
Time | 2018-04-08 03:26:16 (CST)
Hostname | ubuntu
Version | 5.7.18-log Source distribution
Built On | Linux x86_64
Started | 2018-04-07 20:30 (up 0+06:56:11)
Databases | 5
Datadir | /usr/local/mysql/data/
Processes | 2 connected, 2 running
Replication | Is not a slave, has 1 slaves connected
Pidfile | /usr/local/mysql/data/ubuntu.pid (exists)
# Processlist ################################################
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Binlog Dump GTID 1 1 50 50
Query 1 1 0 0
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
root 2 2 50 50
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
192.168.112.128 1 1 50 50
192.168.112.129 1 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
NULL 2 2 50 50
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Master has sent all binlog to 1 1 50 50
starting 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 11 secs
Bytes_received 100000 1 2000
Bytes_sent 1250000 15 20000
...................(many lines omitted)............................
Table_open_cache_misses 500 3
Threads_created 6
Uptime 90000 1 1
# Table cache ################################################
Size | 2000
Usage | 7%
# Key Percona Server features ################################
Table & Index Stats | Not Supported
Multiple I/O Threads | Enabled
Corruption Resilient | Not Supported
Durable Replication | Not Supported
Import InnoDB Tables | Not Supported
Fast Server Restarts | Not Supported
Enhanced Logging | Not Supported
Replica Perf Logging | Disabled
Response Time Hist. | Not Supported
Smooth Flushing | Not Supported
HandlerSocket NoSQL | Not Supported
Fast Hash UDFs | Unknown
# Percona XtraDB Cluster #####################################
# Plugins ####################################################
InnoDB compression | ACTIVE
# Query cache ################################################
query_cache_type | OFF
Size | 1.0M
Usage | 1%
HitToInsertRatio | 0%
# Schema #####################################################
Database Tables Views SPs Trigs Funcs FKs Partn
mysql 31
player 5 1
Database MyISAM InnoDB CSV
mysql 10 19 2
player 5
Database BTREE
mysql 38
player 12
c t s e v i f l d t b s t b m m t
h i e n a n l o a i i m e l e e i
a m t u r t o n t n g a x o d d m
r e m c a g e y i l t b i i e
s h t b t i n l u u
t a l i n t i m m
a r o m t n t b
m b e t e l
p x o
t b
Database === === === === === === === === === === === === === === === === ===
mysql 61 15 6 62 18 42 3 5 4 6 23 4 26 5 2 2 2
player 16 6 3 11
# Noteworthy Technologies ####################################
Full Text Indexing | No
Geospatial Types | No
Foreign Keys | Yes
Partitioning | No
InnoDB Compression | Yes
SSL | No
Explicit LOCK TABLES | No
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | No
Prepared statement count | 0
# InnoDB #####################################################
Version | 5.7.18
Buffer Pool Size | 128.0M
Buffer Pool Fill | 4%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 48.0M = 96.0M
Log Buffer Size | 16M
Flush Method |
Flush Log At Commit | 1
XA Support | ON
Checksums | ON
Doublewrite | ON
R/W I/O Threads | 4 4
I/O Capacity | 200
Thread Concurrency | 0
Concurrency Tickets | 5000
Commit Concurrency | 0
Txn Isolation Level | REPEATABLE-READ
Adaptive Flushing | ON
Adaptive Checkpoint |
Checkpoint Age | 9
InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
Oldest Transaction | 0 Seconds
History List Len | 0
Read Views | 0
Undo Log Entries | 0 transactions, 0 total undo, 0 max undo
Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads
Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
Pending I/O Flushes | 0 buf pool, 0 log
Transaction States | 1xnot started
# MyISAM #####################################################
Key Cache | 8.0M
Pct Used | 20%
Unflushed | 0%
# Security ###################################################
Users | 2 users, 0 anon, 0 w/o pw, 0 old pw
Old Passwords | 0
# Binary Logging #############################################
Binlogs | 28
Zero-Sized | 0
Total Size | 168.8k
binlog_format | ROW
expire_logs_days | 0
sync_binlog | 1
server_id | 1024
binlog_do_db |
binlog_ignore_db |
# Noteworthy Variables #######################################
Auto-Inc Incr/Offset | 1/1
default_storage_engine | InnoDB
flush_time | 0
init_connect |
init_file |
sql_mode | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_S
UBSTITUTION join_buffer_size | 256k
sort_buffer_size | 256k
read_buffer_size | 128k
read_rnd_buffer_size | 256k
bulk_insert_buffer | 0.00
max_heap_table_size | 16M
tmp_table_size | 16M
max_allowed_packet | 4M
thread_stack | 256k
log |
log_error | /usr/local/mysql/data/ubuntu.err
log_warnings | 2
log_slow_queries |
log_queries_not_using_indexes | OFF
log_slave_updates | ON
# Configuration File #########################################
Config File | /etc/my.cnf
[client]
user = root
password = 123456
port = 3306
socket = /tmp/mysql.sock
[mysqld]
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,N
O_ENGINE_SUBSTITUTIONinnodb_buffer_pool_instances = 1
slow_query_log = ON
long_query_time = 10
log_throttle_queries_not_using_indexes = 1
log_output = FILE
show-slave-auth-info
server-id = 1024
log-bin = master-bin
log-bin-index = master-bin.index
binlog_format = row
binlog_rows_query_log_events = ON
sync_binlog = 1
innodb_support_xa = ON
innodb_flush_log_at_trx_commit = 1
skip-slave-start
log_slave_updates = ON
relay_log_recovery = ON
gtid_mode = ON
enforce_gtid_consistency = 1
# Memory management library ##################################
jemalloc is not enabled in MySQL config for process with ID 1846
# The End #################################################### 推荐阅读
-
PerconaToolkit使用之pt-mysql-summary（代码实例）
-
js控制css中的帧动画，使动画每点击一次运行一次（代码实例）
-
JS简单小应用之暴力添加节点（代码实例）
-
javascript如何使数组去掉重复代码实例详解
-
java8 Optional的使用，Optional实例，使你的代码高大上
-
yii在视图里创建变量(保护php的代码)，在js文件里调用之前创建的变量的代码实例
-
js控制css中的帧动画，使动画每点击一次运行一次（代码实例）
-
PerconaToolkit使用之pt-mysql-summary（代码实例）
-
php使HTML标签自动补全闭合函数代码_php实例
-
JS简单小应用之暴力添加节点（代码实例）