[percona-toolkit] pt-mysql-summary分析mysq数据库
来源:互联网 发布:jsp如何连接sqlserver 编辑:程序博客网 时间:2024/06/06 09:11
wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm
yum install percona-toolkit-2.2.19-1.noarch.rpm
pt-mysql-summary也是percona-toolkit-2.2.8-1.noarch.rpm工具集的一部分。
安装工具集:
http://blog.itpub.net/29254281/viewspace-1208415/
MySQL的show status命令 仅仅是一个状态的计数器。
通常情况下,需要采集一个间隔的show status命令,然后分析它的趋势。
一定时间间隔的采集可以使用mysqladmin extended-status
但是更简单的方法是使用pt-mysql-summary工具。
关于pt-mysql-summary工具的介绍请参考:
http://www.cnblogs.com/zhoujinyi/p/3384667.html
下面的脚本通过一个时间间隔的状态采集,
得到这个间隔的增、删、改、查、提交、回滚的总数,然后通过gnuplot画图工具,绘制出图形。
脚本如下(Probe.sh):
#!/bin/bash
#采集间隔时间,单位s
asleep=10
user=root
password=root
probe_file=probe.log
output_image=/tmp/probe.png
while [ 1 ] ; do
a=$(pt-mysql-summary --user $user --password $password | grep -i "com_insert\|com_delete\|com_update\|com_select\|com_commit\|com_rollback" | awk '{if(length($4)==0) print 0 ;else print $4}' | tr -t '\n' ' '); echo $(date "+%Y-%m-%d_%H:%M:%S") $a >> $probe_file
gnuplot -e "set terminal png size 3000,1000 ; set size ratio 0.9 ; set output '$output_image' ; set xtic rotate by 90 ; set xdata time ; set grid;set timefmt '%Y-%m-%d_%H:%M:%S' ; plot 'probe.log' using 1:2 with line title 'Commit', 'probe.log' using 1:3 with line title 'Delete', 'probe.log' using 1:4 with line title 'insert','probe.log' using 1:5 with line title 'rollback','probe.log' using 1:6 with line title 'select','probe.log' using 1:7 with line title 'update' ; "
done;
运行脚本,并且使用TPCC-MySQL基准测试工具开始测试(加一些流量容易看出效果)
TPCC-MySQL
http://blog.itpub.net/29254281/viewspace-1195589/
关于Gnuplot画图工具的简单使用
http://www.heboliang.cn/archive/timedata_gnuplot.html
执行命令,察看结果
[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 ####################################################
- [percona-toolkit] pt-mysql-summary分析mysq数据库
- [MySQL 工具] pt-mysql-summary使用分析mysq数据库
- [MySQL 工具] percona-toolkit, pt-variable-advisor
- Percona Toolkit for MySQL
- [percona-toolkit]pt-query-digest用法
- percona-toolkit 之 【pt-table-checksum】、【pt-table-sync】说明
- percona-toolkit 之 【pt-table-checksum】、【pt-table-sync】说明
- percona-toolkit 之 【pt-table-checksum】、【pt-table-sync】说明
- MySQL常用工具之percona-toolkit
- percona-toolkit工具集pt-kill使用举例
- percona-toolkit 之 【pt-online-schema-change】说明
- percona-toolkit 之 【pt-online-schema-change】说明
- percona-toolkit工具之pt-table-checksum使用
- Percona Toolkit 2.1.9 发布,MySQL 管理工具
- Percona Toolkit 2.2.12 发布,MySQL 管理工具
- percona-toolkit验证MySQL主从一致性
- mysql工具percona-toolkit之安装
- percona-toolkit pt-table-checksum和pt-table-sync的使用方法
- uva 674 && hdu 2069 coin change(动态规划、母函数)
- 使用ShaderToy来处理VR畸变
- 步进电机综合控制
- Ionic2 Action Sheet详解
- Android Studio Android项目内嵌入React Native混合开发
- [percona-toolkit] pt-mysql-summary分析mysq数据库
- codeforces 777D. Cloud of Hashtags (逆序思维)
- 在本地用命令行创建一个git仓库,并推送到远程
- js里声明函数有几种方式? var abc = function(x){} 和 function abc(x){} 这两种声明方法有什么不同?
- safari浏览器数字显示长度不一致的问题
- Android项目针对libs(armeabi,armeabi-v7a,x86)进行平台兼容
- [BZOJ2082][Poi2010][质因数分解][乱搞]Divine divisor
- python-numpy: linalg.solve中报错 Singular Matrix
- [Leetcode] 23. Merge k Sorted Lists