pt mysql summary_[percona-toolkit] pt-mysql-summary分析mysq数据库

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

在安装percona-toolkit的时候，这个组件也一起安装完成了：

安装：

yum install percona-toolkit-2.2.19-1.noarch.rpm

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

create user  pt IDENTIFIED by 'pt';

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 服务器所在的系统的时间

# Percona Toolkit MySQL Summary Report #######################

System time | 2016-09-01 09:59:25 UTC (local TZ: PDT -0700)

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

Port Data Directory Nice OOM Socket

===== ========================== ==== === ======

0 0

# MySQL Executable ###########################################

Path to executable | /usr/sbin/mysqld

Has symbols | Yes

# Report On Port 3306 ########################################

User | pt@%

Time | 2016-09-01 02:59:25 (PDT)

Hostname | localhost.localdomain

Version | 5.7.14 MySQL Community Server (GPL)

Built On | Linux x86_64

Started | 2016-08-28 23:23 (up 3+03:36:14)

Databases | 5

Datadir | /var/lib/mysql/

Processes | 1 connected, 1 running

Replication | Is not a slave, has 0 slaves connected

Pidfile | /var/run/mysqld/mysqld.pid (exists)

# Processlist ################################################

(2)第二部分是从show full processlist;中来的信息，对每一列进行了一些分析统计。

Command COUNT(*) Working SUM(Time) MAX(Time)

------------------------------ -------- ------- --------- ---------

Query 1 1 0 0

User COUNT(*) Working SUM(Time) MAX(Time)

------------------------------ -------- ------- --------- ---------

pt 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)

------------------------------ -------- ------- --------- ---------

starting 1 1 0 0

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

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

# Table cache ################################################

Size | 2000

Usage | 15%

(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等。

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

Version | 5.7.14

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 | 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 | 995

Read Views | 0

Undo Log Entries | 2 transactions, 4 total undo, 3 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 | 9 users, 2 anon, 6 w/o pw, 6 old pw

Old Passwords | 0

# Binary Logging #############################################

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

# Noteworthy Variables #######################################

Auto-Inc Incr/Offset | 1/1

default_storage_engine | InnoDB

flush_time | 0

init_connect |

init_file |

sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,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 | /var/log/mysqld.log

log_warnings | 2

log_slow_queries |

log_queries_not_using_indexes | OFF

log_slave_updates | OFF

# Configuration File #########################################

Config File | /etc/my.cnf

[mysqld]

datadir = /var/lib/mysql

socket = /var/lib/mysql/mysql.sock

port = 3306

symbolic-links = 0

log-error = /var/log/mysqld.log

pid-file = /var/run/mysqld/mysqld.pid

performance_schema = ON

# The End ####################################################