本文介绍如何使用Percona Toolkit MySQL Summary工具来监控MySQL实例的性能，包括系统时间、实例信息、MySQL执行文件详情、进程列表、状态计数器、表缓存、关键Percona Server特性、InnoDB和MyISAM存储引擎状态、安全性、二进制日志、值得注意的技术以及变量配置等。该工具提供了关于MySQL服务器运行状况的深入洞察。
[root@vm1 mysql3306]# pt-mysql-summary --help
Usage: pt-mysql-summary [OPTIONS]
For more information, 'man pt-mysql-summary' or 'perldoc /usr/local/bin/pt-mysql-summary'.
Command line options:
--all-databases mysqldump and summarize all databases.
--ask-pass Prompt for a password when connecting to MySQL.
--config Read this comma-separated list of config files.
--databases mysqldump and summarize this comma-separated list of
databases.
--defaults-file Only read mysql options from the given file.
--help Print help and exit.
--host Host to connect to.
--password Password to use when connecting.
--port Port number to use for connection.
--read-samples Create a report from the files found in this directory.
--save-samples Save the data files used to generate the summary in this
directory.
--sleep Seconds to sleep when gathering status counters.
--socket Socket file to use for connection.
--user User for login if not current user.
--version Print tool's version and exit.
Options and values after processing arguments:
--all-databases FALSE
--ask-pass FALSE
--config (No value)
--databases (No value)
--defaults-file (No value)
--help TRUE
--host (No value)
--password (No value)
--port (No value)
--read-samples (No value)
--save-samples (No value)
--sleep 10
--socket (No value)
--user (No value)
--version FALSE
[root@vm1 mysql3306]# pt-mysql-summary --host=localhost --user=root --port=3306 --password=mysql
Warning: Using a password on the command line interface can be insecure.
# Percona Toolkit MySQL Summary Report #######################
System time | 2016-05-09 02:52:56 UTC (local TZ: CST +0800)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
3306 /data/mysql3306 0 0 /tmp/mysql3306.sock
# MySQL Executable ###########################################
Path to executable | /usr/local/mysql/bin/mysqld
Has symbols | Yes
# Report On Port 3306 ########################################
User | root@localhost
Time | 2016-05-09 10:52:56 (CST)
Hostname | vm1
Version | 5.6.27-log Source distribution
Built On | Linux x86_64
Started | 2016-04-20 12:47 (up 18+22:05:15)
Databases | 7
Datadir | /data/mysql3306/
Processes | 1 connected, 1 running
Replication | Is not a slave, has 0 slaves connected
Pidfile | /data/mysql3306/vm1.pid (exists)
# Processlist ################################################
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Query 1 1 0 0
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
root 1 1 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
localhost 1 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
NULL 1 1 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
init 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 10 secs
Aborted_clients 2
Binlog_cache_disk_use 80
Binlog_cache_use 25000
Binlog_stmt_cache_use 2500
Bytes_received 40000000 450 350
Bytes_sent 175000000 2250 2250
Com_alter_table 10
Com_begin 2500
Com_commit 2500
Com_create_index 30
Com_create_table 1500
Com_delete 3000
Com_drop_table 1000
Com_insert 12500
Com_load 1
Com_lock_tables 1
Com_select 150000 1 1
Com_show_fields 5
Com_show_processlist 2
Com_update 30000
Connections 1500 1
Created_tmp_disk_tables 1
Created_tmp_files 250
Created_tmp_tables 3000 6
Handler_commit 225000 2
Handler_delete 25000
Handler_external_lock 150000 1
Handler_prepare 70000
Handler_read_first 25000
Handler_read_key 2000000 25
Handler_read_last 1250
Handler_read_next 25000000 300
Handler_read_prev 300000 3
Handler_read_rnd 700000 8
Handler_read_rnd_next 5000000 60 40
Handler_rollback 5000
Handler_update 900000 10
Handler_write 500000 5 40
Innodb_buffer_pool_bytes_data 6000000 70
Innodb_buffer_pool_pages_flushed 70000
Innodb_buffer_pool_read_ahead 225
Innodb_buffer_pool_read_ahead_evicted 9
Innodb_buffer_pool_read_requests 20000000 250
Innodb_buffer_pool_reads 25000
Innodb_buffer_pool_wait_free 1750
Innodb_buffer_pool_write_requests 4500000 50
Innodb_data_fsyncs 60000
Innodb_data_read 600000000 7000
Innodb_data_reads 35000
Innodb_data_writes 125000 1
Innodb_data_written 3000000000 35000
Innodb_dblwr_pages_written 80000
Innodb_dblwr_writes 2500
Innodb_log_write_requests 1000000 10
Innodb_log_writes 40000
Innodb_os_log_fsyncs 40000
Innodb_os_log_written 450000000 5000
Innodb_pages_created 20000
Innodb_pages_read 35000
Innodb_pages_written 80000
Innodb_row_lock_time 2
Innodb_rows_deleted 25000
Innodb_rows_inserted 200000 2
Innodb_rows_read 30000000 350
Innodb_rows_updated 200000 2
Innodb_num_open_files 5
Innodb_available_undo_logs 6
Open_table_definitions 5
Opened_files 7000 7
Opened_table_definitions 2250
Opened_tables 600
Performance_schema_digest_lost 175000 1 1
Performance_schema_file_instances_lost 2250
Qcache_not_cached 70000 1
Queries 200000 2 4
Questions 200000 2 4
Select_range 15000
Select_scan 25000
Slow_queries 10000 3
Sort_merge_passes 150
Sort_range 2500
Sort_rows 2000000 25
Sort_scan 3000
Table_locks_immediate 80000
Table_open_cache_hits 80000
Table_open_cache_misses 600
Table_open_cache_overflows 450
Uptime 90000 1 1
# Table cache ################################################
Size | 2000
Usage | 5%
# 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 #####################################################
Specify --databases or --all-databases to dump and summarize schemas
# Noteworthy Technologies ####################################
SSL | No
Explicit LOCK TABLES | Yes
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | Yes
Prepared statement count | 0
# InnoDB #####################################################
Version | 5.6.27
Buffer Pool Size | 128.0M
Buffer Pool Fill | 90%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 48.0M = 96.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 | 5000
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 | 1108
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 | 8 users, 0 anon, 2 w/o pw, 2 old pw
Old Passwords | 0
# Binary Logging #############################################
Binlogs | 29
Zero-Sized | 0
Total Size | 938.1M
binlog_format | ROW
expire_logs_days | 0
sync_binlog | 0
server_id | 11
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 | NO_ENGINE_SUBSTITUTION
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 | /data/mysql3306/vm1.err
log_warnings | 1
log_slow_queries |
log_queries_not_using_indexes | OFF
log_slave_updates | ON
# Configuration File #########################################
Config File | Cannot autodetect or find, giving up
# The End ####################################################
Usage: pt-mysql-summary [OPTIONS]
For more information, 'man pt-mysql-summary' or 'perldoc /usr/local/bin/pt-mysql-summary'.
Command line options:
--all-databases mysqldump and summarize all databases.
--ask-pass Prompt for a password when connecting to MySQL.
--config Read this comma-separated list of config files.
--databases mysqldump and summarize this comma-separated list of
databases.
--defaults-file Only read mysql options from the given file.
--help Print help and exit.
--host Host to connect to.
--password Password to use when connecting.
--port Port number to use for connection.
--read-samples Create a report from the files found in this directory.
--save-samples Save the data files used to generate the summary in this
directory.
--sleep Seconds to sleep when gathering status counters.
--socket Socket file to use for connection.
--user User for login if not current user.
--version Print tool's version and exit.
Options and values after processing arguments:
--all-databases FALSE
--ask-pass FALSE
--config (No value)
--databases (No value)
--defaults-file (No value)
--help TRUE
--host (No value)
--password (No value)
--port (No value)
--read-samples (No value)
--save-samples (No value)
--sleep 10
--socket (No value)
--user (No value)
--version FALSE
[root@vm1 mysql3306]# pt-mysql-summary --host=localhost --user=root --port=3306 --password=mysql
Warning: Using a password on the command line interface can be insecure.
# Percona Toolkit MySQL Summary Report #######################
System time | 2016-05-09 02:52:56 UTC (local TZ: CST +0800)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
3306 /data/mysql3306 0 0 /tmp/mysql3306.sock
# MySQL Executable ###########################################
Path to executable | /usr/local/mysql/bin/mysqld
Has symbols | Yes
# Report On Port 3306 ########################################
User | root@localhost
Time | 2016-05-09 10:52:56 (CST)
Hostname | vm1
Version | 5.6.27-log Source distribution
Built On | Linux x86_64
Started | 2016-04-20 12:47 (up 18+22:05:15)
Databases | 7
Datadir | /data/mysql3306/
Processes | 1 connected, 1 running
Replication | Is not a slave, has 0 slaves connected
Pidfile | /data/mysql3306/vm1.pid (exists)
# Processlist ################################################
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Query 1 1 0 0
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
root 1 1 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
localhost 1 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
NULL 1 1 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
init 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 10 secs
Aborted_clients 2
Binlog_cache_disk_use 80
Binlog_cache_use 25000
Binlog_stmt_cache_use 2500
Bytes_received 40000000 450 350
Bytes_sent 175000000 2250 2250
Com_alter_table 10
Com_begin 2500
Com_commit 2500
Com_create_index 30
Com_create_table 1500
Com_delete 3000
Com_drop_table 1000
Com_insert 12500
Com_load 1
Com_lock_tables 1
Com_select 150000 1 1
Com_show_fields 5
Com_show_processlist 2
Com_update 30000
Connections 1500 1
Created_tmp_disk_tables 1
Created_tmp_files 250
Created_tmp_tables 3000 6
Handler_commit 225000 2
Handler_delete 25000
Handler_external_lock 150000 1
Handler_prepare 70000
Handler_read_first 25000
Handler_read_key 2000000 25
Handler_read_last 1250
Handler_read_next 25000000 300
Handler_read_prev 300000 3
Handler_read_rnd 700000 8
Handler_read_rnd_next 5000000 60 40
Handler_rollback 5000
Handler_update 900000 10
Handler_write 500000 5 40
Innodb_buffer_pool_bytes_data 6000000 70
Innodb_buffer_pool_pages_flushed 70000
Innodb_buffer_pool_read_ahead 225
Innodb_buffer_pool_read_ahead_evicted 9
Innodb_buffer_pool_read_requests 20000000 250
Innodb_buffer_pool_reads 25000
Innodb_buffer_pool_wait_free 1750
Innodb_buffer_pool_write_requests 4500000 50
Innodb_data_fsyncs 60000
Innodb_data_read 600000000 7000
Innodb_data_reads 35000
Innodb_data_writes 125000 1
Innodb_data_written 3000000000 35000
Innodb_dblwr_pages_written 80000
Innodb_dblwr_writes 2500
Innodb_log_write_requests 1000000 10
Innodb_log_writes 40000
Innodb_os_log_fsyncs 40000
Innodb_os_log_written 450000000 5000
Innodb_pages_created 20000
Innodb_pages_read 35000
Innodb_pages_written 80000
Innodb_row_lock_time 2
Innodb_rows_deleted 25000
Innodb_rows_inserted 200000 2
Innodb_rows_read 30000000 350
Innodb_rows_updated 200000 2
Innodb_num_open_files 5
Innodb_available_undo_logs 6
Open_table_definitions 5
Opened_files 7000 7
Opened_table_definitions 2250
Opened_tables 600
Performance_schema_digest_lost 175000 1 1
Performance_schema_file_instances_lost 2250
Qcache_not_cached 70000 1
Queries 200000 2 4
Questions 200000 2 4
Select_range 15000
Select_scan 25000
Slow_queries 10000 3
Sort_merge_passes 150
Sort_range 2500
Sort_rows 2000000 25
Sort_scan 3000
Table_locks_immediate 80000
Table_open_cache_hits 80000
Table_open_cache_misses 600
Table_open_cache_overflows 450
Uptime 90000 1 1
# Table cache ################################################
Size | 2000
Usage | 5%
# 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 #####################################################
Specify --databases or --all-databases to dump and summarize schemas
# Noteworthy Technologies ####################################
SSL | No
Explicit LOCK TABLES | Yes
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | Yes
Prepared statement count | 0
# InnoDB #####################################################
Version | 5.6.27
Buffer Pool Size | 128.0M
Buffer Pool Fill | 90%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 48.0M = 96.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 | 5000
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 | 1108
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 | 8 users, 0 anon, 2 w/o pw, 2 old pw
Old Passwords | 0
# Binary Logging #############################################
Binlogs | 29
Zero-Sized | 0
Total Size | 938.1M
binlog_format | ROW
expire_logs_days | 0
sync_binlog | 0
server_id | 11
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 | NO_ENGINE_SUBSTITUTION
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 | /data/mysql3306/vm1.err
log_warnings | 1
log_slow_queries |
log_queries_not_using_indexes | OFF
log_slave_updates | ON
# Configuration File #########################################
Config File | Cannot autodetect or find, giving up
# The End ####################################################
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26735168/viewspace-2096330/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26735168/viewspace-2096330/
扫一扫
169
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
