mysql percona-toolkit_mysql监控工具之percona-toolkit集合

1.下载安装percona-toolkit软件

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