1.下载安装percona-toolkit软件
[root@tong1 ~]# wget http://www.percona.com/downloads/percona-toolkit/2.2.13/deb/percona-toolkit_2.2.13.tar.gz
[root@tong1 ~]# tar xvf percona-toolkit_2.2.13.tar.gz -C /usr/local/
[root@tong1 ~]# cd /usr/local/percona-toolkit-2.2.13/
[root@tong1 percona-toolkit-2.2.13]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for percona-toolkit
[root@tong1 percona-toolkit-2.2.13]# make && make install
[root@tong1 percona-toolkit-2.2.13]# echo $?
0
[root@tong1 percona-toolkit-2.2.13]#
[root@tong1 percona-toolkit-2.2.13]# cd bin/
[root@tong1 bin]#
2.使用pt-mysql-summary脚本查看mysql的详细信息
[root@tong1 bin]# ./pt-mysql-summary --user root --password system --socket /tmp/mysql.sock
Warning: Using a password on the command line interface can be insecure.
# Percona Toolkit MySQL Summary Report #######################
System time | 2015-03-13 09:17:08 UTC (local TZ: CST +0800)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
3306 /usr/local/mysql-5.7.4/data 0 0 /tmp/mysql.sock
# MySQL Executable ###########################################
Path to executable | /usr/local/mysql-5.7.4/bin/mysqld
Has symbols | Yes
# Report On Port 3306 ########################################
User | root@localhost
Time | 2015-03-13 17:17:08 (CST)
Hostname | tong1
Version | 5.7.4-m14-log MySQL Community Server (GPL)
Built On | linux-glibc2.5 x86_64
Started | 2015-03-05 14:31 (up 8+02:45:46)
Databases | 4
Datadir | /usr/local/mysql-5.7.4/data/
Processes | 1 connected, 1 running
Replication | Is not a slave, has 0 slaves connected
Pidfile | /usr/local/mysql-5.7.4/data/tong1.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)
------------------------------ -------- ------- --------- ---------
starting 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 10 secs
Bytes_received 5000 350
Bytes_sent 350000 4 2250
Com_admin_commands 90
Com_select 2 1
Com_set_option 1
Com_show_fields 30
Com_show_master_status 25
Com_show_processlist 25
Com_show_slave_status 25
Com_show_status 45
Com_show_tables 1
Com_show_variables 1
Connections 2 1
Created_tmp_disk_tables 5 1
Created_tmp_tables 60 6
Handler_external_lock 25
Handler_read_key 1
Handler_read_rnd_next 9000 40
Handler_write 9000 40
Innodb_buffer_pool_bytes_data 400000 4
Innodb_buffer_pool_pages_flushed 4
Innodb_buffer_pool_read_requests 150
Innodb_buffer_pool_reads 20
Innodb_buffer_pool_write_requests 40
Innodb_data_read 350000 3
Innodb_data_reads 20
Innodb_data_writes 6
Innodb_data_written 150000 1
Innodb_dblwr_pages_written 4
Innodb_os_log_written 60
Innodb_pages_created 4
Innodb_pages_read 20
Innodb_pages_written 4
Innodb_num_open_files 1
Innodb_available_undo_logs 15
Key_read_requests 1
Open_table_definitions 15
Opened_files 45 5
Opened_table_definitions 15
Opened_tables 15
Qcache_not_cached 2 1
Queries 250 4
Questions 125 4
Select_scan 30
Table_locks_immediate 15
Table_open_cache_hits 20
Table_open_cache_misses 15
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 | No
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | No
Prepared statement count | 0
# InnoDB #####################################################
Version | 5.7.4
Buffer Pool Size | 128.0M
Buffer Pool Fill | 2%
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 | 18
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 | 4
Zero-Sized | 0
Total Size | 6.7k
binlog_format | STATEMENT
expire_logs_days | 0
sync_binlog | 0
server_id | 10
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_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 | /usr/local/mysql-5.7.4/data/tong1.err
log_warnings | 2
log_slow_queries |
log_queries_not_using_indexes | OFF
log_slave_updates | OFF
# Configuration File #########################################
Config File | /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql-5.7.4
datadir = /usr/local/mysql-5.7.4/data
port = 3306
server_id = 10
socket = /tmp/mysql.sock
log-bin = mysql-bin
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# The End ####################################################
[root@tong1 bin]#
3.使用pt-diskstats脚本查看数据库对磁盘的信息
[root@tong1 bin]# ./pt-diskstats
#ts device rd_s rd_avkb rd_mb_s rd_mrg rd_cnc rd_rt wr_s wr_avkb wr_mb_s wr_mrg wr_cnc wr_rt busy in_prg io_s qtime stime
1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 3.0 8.0 0.0 50% 0.0 8.0 5% 0 3.0 0.0 8.0
1.0 sda3 0.0 0.0 0.0 0% 0.0 0.0 3.0 8.0 0.0 50% 0.0 8.0 5% 0 3.0 0.0 8.0
1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda3 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda3 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
[root@tong1 bin]#
