[MySQL 工具] pt-mysql-summary使用分析mysq数据库

pt-mysql-summary：本地安装、然后远程收集MySQL Server的基本信息

在安装percona-toolkit的时候，这个组件也一起安装完成了：安装参考：http://blog.csdn.net/mchdba/article/details/14209765

要先在数据库里面建立监控帐号：

grant all on *.* to pt@''%" identified by 'pt';

执行命令，察看结果

[root@472322 ~]#pt-mysql-summary --user=pt --password='pt' --port=3308 -S /data56m1/mysql3308.sock

Warning: Using a password on the command line interface can be insecure.
# Percona Toolkit MySQL Summary Report #######################
System time | 2013-11-07 03:38:14 UTC (local TZ: CST -0600)

（1）第一部分实例信息，有个参数需要特别注意、Time 指的是MySQL 服务器的当前时间、而不是MySQL 服务器所在的系统的时间

# Instances ##################################################

Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
3309 /data56m2 0 0 /data56m2/mysql3309.sock
3308 /data56m1 0 0 /data56m1/mysql3308.sock
/data56 0 0
# MySQL Executable ###########################################
Path to executable | /usr/local/mysql56/bin/mysqld
Has symbols | Yes
Path to executable | /usr/local/mysql56m1/bin/mysqld
Has symbols | Yes
Path to executable | /usr/local/mysql56m2/bin/mysqld
Has symbols | Yes
# Report On Port 3308 ########################################
User | pt@localhost
Time | 2013-11-06 21:38:14 (CDT)
Hostname | 472322.ea.com
Version | 5.6.13-log Source distribution
Built On | Linux x86_64
Started | 2013-09-12 02:21 (up 55+19:16:16)
Databases | 4
Datadir | /data56m1/
Processes | 2 connected, 2 running
Replication | Is a slave, has 1 slaves connected
Pidfile | /data56m1/472322.ea.com.pid (exists)
# Processlist ################################################

（2）第二部分是从show full processlist;中来的信息，对每一列进行了一些分析统计。
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Binlog Dump 1 1 1750000 1750000
Connect 2 2 2000000 1750000
Query 1 1 0 0


User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
pt 1 1 0 0
repl 1 1 1750000 1750000
system user 2 2 2000000 1750000


Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
2 2 2000000 1750000
472322-esxapp16.ea.com 1 1 1750000 1750000
localhost 1 1 0 0


db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
NULL 4 4 3500000 1750000


State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
init 1 1 0 0
Master has sent all binlog to 1 1 1750000 1750000
Slave has read all relay log; 1 1 175000 175000
Waiting for master to send eve 1 1 1750000 1750000

（3）第三部分是从取自 show global status 、仅收集哪些变化的量，比较有价值的应该是第三列Per day Per second、取自两张快照、除以变化量、算出每秒的变化

# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 11 secs
Bytes_received 3000 350
Bytes_sent 7000 2000
Com_select 1 1
Com_set_option 2
Connections 1 1
Created_tmp_disk_tables 1
Created_tmp_tables 3 5
Handler_commit 1
Handler_external_lock 6
Handler_prepare 1
Handler_read_rnd_next 40 35
Handler_write 35 35
Innodb_buffer_pool_bytes_data 125000 1
Innodb_buffer_pool_pages_flushed 10
Innodb_buffer_pool_read_requests 125
Innodb_buffer_pool_reads 3
Innodb_buffer_pool_write_requests 60
Innodb_data_fsyncs 5
Innodb_data_read 125000 1
Innodb_data_reads 5
Innodb_data_writes 9
Innodb_data_written 400000 4
Innodb_dblwr_pages_written 10
Innodb_log_write_requests 9
Innodb_log_writes 1
Innodb_os_log_fsyncs 1
Innodb_os_log_written 5000
Innodb_pages_created 3
Innodb_pages_read 3
Innodb_pages_written 10
Innodb_available_undo_logs 2
Open_table_definitions 1
Opened_files 9 4
Opened_table_definitions 1
Opened_tables 2
Qcache_not_cached 1 1
Queries 10 3
Questions 9 3
Select_scan 2
Slave_received_heartbeats 45
Table_locks_immediate 3
Table_open_cache_hits 3
Table_open_cache_misses 1
Uptime 90000 1 1

（4）第四部分显示表缓存的大小、Usage表示在用的百分比，是一个比较近似的值。

# Table cache ################################################
Size | 2000
Usage | 4%

（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 #####################################

（6）第六部分显示当前的插件以及状态。
# Plugins ####################################################
InnoDB compression | ACTIVE

（7）第七部分显示Query cache的参数值以及命中率。
# Query cache ################################################
query_cache_type | OFF
Size | 1.0M
Usage | 1%
HitToInsertRatio | 0%
# Schema #####################################################

（8）第八部分显示当前db下需要关注的一些点，比如SSL，Delayed Insert等。

Would you like to mysqldump -d the schema and analyze it? y/n n
Skipping schema analysis
# Noteworthy Technologies ####################################
SSL | No
Explicit LOCK TABLES | No
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | No
Prepared statement count | 0
# InnoDB #####################################################
# MyISAM #####################################################
Key Cache | 8.0M
Pct Used | 20%
Unflushed | 0%
# Security ###################################################
Users | 9 users, 2 anon, 6 w/o pw, 6 old pw
Old Passwords | 0
# Binary Logging #############################################
Binlogs | 4
Zero-Sized | 0
Total Size | 1.2M
binlog_format | MIXED
expire_logs_days | 0
sync_binlog | 0
server_id | 11
binlog_do_db |
binlog_ignore_db |

（9）第九部分显示当前的一些buffer参数值。

# 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_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 | /data56m1/error.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 ####################################################