欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

PerconaToolkit使用之pt-mysql-summary(代码实例)

程序员文章站 2023-03-11 23:10:39
pt-mysql-summary的功能是以优雅的形式汇总MySQL服务器信息。 用法如下: pt-mysql-summary [OPTIONS] pt-mysql-sum...

pt-mysql-summary的功能是以优雅的形式汇总MySQL服务器信息。

用法如下:

pt-mysql-summary [OPTIONS]

pt-mysql-summary方便地汇总出MySQL数据库服务器的状态和配置,使你可以一目了然地获得这些信息。这不是一个调优或者诊断的工具。它会生成一个报告,可以很容易地diff或者不丢失格式地复制到Email里。它应该可以很好地运行于任何现代Unix系统

pt-mysql-summary通过连接到MySQL数据库服务器然后执行状态和配置信息查询的方式运行。它将这些零碎信息保存到临时目录的文件中,然后借助awk和其他脚本语言来整齐格式化。

要使用它,只需要运行即可。作为可选可以加双“ - ”后接你用来连接MySQL时相同的命令行选项,如下示例:

pt-mysql-summary --user=root

该工具与运行它的服务器进行最低限度地调用。它假定你运行它的服务器和要进行检查的服务器是同一个,因此,它假定可以找到配置文件,例如my.cnf。然而如果不是这种情况,它可能优雅地折损。请注意,它的输出并没有指出哪些信息来自于MySQL数据库;哪些信息来自于操作系统主机。所以如果你在一台服务器上运行该工具,然后连接访问另一台服务器上的MySQL,就有可能产生混乱的输出。

该工具的很多输出值都故意作了近似舍入处理以展示它们的量级而不是精确的细节。这称为“fuzzy-rounding ”。该设计认为一台服务器是918QPS还是921QPS并不打紧,如此小的差异微不足道,而只会使得输出同其他服务器作比较变得困难。随着输入的增大,fuzzy-rounding也以更大的量级近似舍入。刚开始它舍入到最近的5,然后是最近的10,最近的25,然后以一个10倍大的因数重复(50,100,250),以此类推,随着输入的增大。以下是该工具产生的报告的一个示例。

前两部分展示该报告生成自哪台服务器以及有哪些MySQL实例运行在该服务器上。这取自ps的输出。虽然并不是总能检查到所有的实例和参数,但往往运行效果还是不错的。

# Percona Toolkit MySQL Summary Report #######################
              System time | 2012-03-30 18:46:05 UTC
                            (local TZ: EDT -0400)
# Instances ##################################################
  Port  Data Directory             Nice OOM Socket
  ===== ========================== ==== === ======
  12345 /tmp/12345/data            0    0   /tmp/12345.sock
  12346 /tmp/12346/data            0    0   /tmp/12346.sock
  12347 /tmp/12347/data            0    0   /tmp/12347.sock

往下是单个MySQL实例的报告。该部分是MySQL实例的快速汇总。“ Time ”值生成自MySQL服务器,不同于之前部分打印的系统日期和时间,可以以此判断数据库时间和操作系统时间是否匹配。

# Report On Port 12345 #######################################
                     User | msandbox@%
                     Time | 2012-03-30 14:46:05 (EDT)
                 Hostname | localhost.localdomain
                  Version | 5.5.20-log MySQL Community Server (GPL)
                 Built On | linux2.6 i686
                  Started | 2012-03-28 23:33 (up 1+15:12:09)
                Databases | 4
                  Datadir | /tmp/12345/data/
                Processes | 2 connected, 2 running
              Replication | Is not a slave, has 1 slaves connected
                  Pidfile | /tmp/12345/data/12345.pid (exists)

接下来部分是“SHOW PROCESSLIST ”输出的汇总。

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

  Command                        COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  Binlog Dump                           1       1    150000    150000
  Query                                 1       1         0         0

  User                           COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  msandbox                              2       2    150000    150000

  Host                           COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  localhost                             2       2    150000    150000

  db                             COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  NULL                                  2       2    150000    150000

  State                          COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  Master has sent all binlog to         1       1    150000    150000
  NULL                                  1       1         0         0

大约间隔10秒的两份“SHOW GLOBAL STATUS ”快照计算而来的select计数。

# Status Counters (Wait 10 Seconds) ##########################
Variable                            Per day  Per second     10 secs
Binlog_cache_disk_use                     4
Binlog_cache_use                         80
Bytes_received                     15000000         175         200
Bytes_sent                         15000000         175        2000
Com_admin_commands                        1
...................(many lines omitted)............................
Threads_created                          40                       1
Uptime                                90000           1           1

表缓存大小。

# Table cache ################################################
                     Size | 400
                    Usage | 15%

Percona Server特性汇总部分。标准版本MySQL因为没有启用,会显示为不支持。

# 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 | Not Supported
      Response Time Hist. | Not Supported
          Smooth Flushing | Not Supported
      HandlerSocket NoSQL | Not Supported
           Fast Hash UDFs | Unknown

插件汇总部分。

# Plugins ####################################################
       InnoDB compression | ACTIVE

查询缓冲部分。

# Query cache ################################################
         query_cache_type | ON
                     Size | 0.0
                    Usage | 0%
         HitToInsertRatio | 0%

schema部分。生成自“mysqldump --no-data ”而不是查询“INFORMATION_SCHEMA ”以避免对繁忙的服务器造成影响。

# Schema #####################################################

  Database           Tables Views SPs Trigs Funcs   FKs Partn
  mysql                  24
  performance_schema     17
  sakila                 16     7   3     6     3    22

  Database           MyISAM CSV PERFORMANCE_SCHEMA InnoDB
  mysql                  22   2
  performance_schema                            17
  sakila                  8                            15

  Database           BTREE FULLTEXT
  mysql                 31
  performance_schema
  sakila                63        1

                       c   t   s   e   l   d   i   t   m   v   s
                       h   i   e   n   o   a   n   i   e   a   m
                       a   m   t   u   n   t   t   n   d   r   a
                       r   e       m   g   e       y   i   c   l
                           s           b   t       i   u   h   l
                           t           l   i       n   m   a   i
                           a           o   m       t   t   r   n
                           m           b   e           e       t
                           p                           x
                                                       t
  Database           === === === === === === === === === === ===
  mysql               61  10   6  78   5   4  26   3   4   5   3
  performance_schema               5          16          33
  sakila               1  15   1   3       4   3  19      42  26

特定技术使用情况部分。

# Noteworthy Technologies ####################################
       Full Text Indexing | Yes
         Geospatial Types | No
             Foreign Keys | Yes
             Partitioning | No
       InnoDB Compression | Yes
                      SSL | No
     Explicit LOCK TABLES | No
           Delayed Insert | No
          XA Transactions | No
              NDB Cluster | No
      Prepared Statements | No
 Prepared statement count | 0

InnoDB存储引擎重要配置参数部分。

# InnoDB #####################################################
                  Version | 1.1.8
         Buffer Pool Size | 16.0M
         Buffer Pool Fill | 100%
        Buffer Pool Dirty | 0%
           File Per Table | OFF
                Page Size | 16k
            Log File Size | 2 * 5.0M = 10.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 | 500
       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 | 209
               Read Views | 1
         Undo Log Entries | 1 transactions, 1 total undo, 1 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使用情况部分。

# MyISAM #####################################################
                Key Cache | 16.0M
                 Pct Used | 10%
                Unflushed | 0%

连接用户部分。生成自“ mysql ”系统库。

# Security ###################################################
                    Users | 2 users, 0 anon, 0 w/o pw, 0 old pw
            Old Passwords | OFF

二进制日志部分。

# Binary Logging #############################################
                  Binlogs | 1
               Zero-Sized | 0
               Total Size | 21.8M
            binlog_format | STATEMENT
         expire_logs_days | 0
              sync_binlog | 0
                server_id | 12345
             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 |
         join_buffer_size | 128k
         sort_buffer_size | 2M
         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 | 1M
             thread_stack | 192k
                      log | OFF
                log_error | /tmp/12345/data/mysqld.log
             log_warnings | 1
         log_slow_queries | ON
log_queries_not_using_indexes | OFF
        log_slave_updates | ON

美化版的my.cnf配置文件部分。

# Configuration File #########################################
              Config File | /tmp/12345/my.sandbox.cnf
[client]
user                                = msandbox
password                            = msandbox
port                                = 12345
socket                              = /tmp/12345/mysql_sandbox12345.sock
[mysqld]
port                                = 12345
socket                              = /tmp/12345/mysql_sandbox12345.sock
pid-file                            = /tmp/12345/data/mysql_sandbox12345.pid
basedir                             = /home/baron/5.5.20
datadir                             = /tmp/12345/data
key_buffer_size                     = 16M
innodb_buffer_pool_size             = 16M
innodb_data_home_dir                = /tmp/12345/data
innodb_log_group_home_dir           = /tmp/12345/data
innodb_data_file_path               = ibdata1:10M:autoextend
innodb_log_file_size                = 5M
log-bin                             = mysql-bin
relay_log                           = mysql-relay-bin
log_slave_updates
server-id                           = 12345
report-host                         = 127.0.0.1
report-port                         = 12345
log-error                           = mysqld.log
innodb_lock_wait_timeout            = 3
# The End ####################################################

以下为个人本地环境的测试数据。

root@ubuntu:~# pt-mysql-summary --host=192.168.112.129 --port=3306 --user=root --password=123456 --all-databases
mysql: [Warning] Using a password on the command line interface can be insecure.
# Percona Toolkit MySQL Summary Report #######################
              System time | 2018-04-07 19:26:17 UTC (local TZ: CST +0800)
# Instances ##################################################
  Port  Data Directory             Nice OOM Socket
  ===== ========================== ==== === ======
        /usr/local/mysql/data      0    0   /tmp/mysql.sock
# MySQL Executable ###########################################
       Path to executable | /usr/local/mysql/bin/mysqld
              Has symbols | Yes
# Slave Hosts ################################################
*************************** 1. row ***************************
 Server_id: 1025
      Host: 192.168.136.128
      User: root
  Password: 123456
      Port: 3306
 Master_id: 1024
Slave_UUID: b5fe6950-6c2a-11e7-a60a-000c29c6454d
# Report On Port 3306 ########################################
                     User | root@%
                     Time | 2018-04-08 03:26:16 (CST)
                 Hostname | ubuntu
                  Version | 5.7.18-log Source distribution
                 Built On | Linux x86_64
                  Started | 2018-04-07 20:30 (up 0+06:56:11)
                Databases | 5
                  Datadir | /usr/local/mysql/data/
                Processes | 2 connected, 2 running
              Replication | Is not a slave, has 1 slaves connected
                  Pidfile | /usr/local/mysql/data/ubuntu.pid (exists)
# Processlist ################################################

  Command                        COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  Binlog Dump GTID                      1       1        50        50
  Query                                 1       1         0         0

  User                           COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  root                                  2       2        50        50

  Host                           COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  192.168.112.128                       1       1        50        50
  192.168.112.129                       1       1         0         0

  db                             COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  NULL                                  2       2        50        50

  State                          COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  Master has sent all binlog to         1       1        50        50
  starting                              1       1         0         0

# Status Counters (Wait 10 Seconds) ##########################
Variable                                Per day  Per second     11 secs
Bytes_received                           100000           1        2000
Bytes_sent                              1250000          15       20000
...................(many lines omitted)............................
Table_open_cache_misses                     500                       3
Threads_created                               6                        
Uptime                                    90000           1           1
# Table cache ################################################
                     Size | 2000
                    Usage | 7%
# 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 #####################################################

  Database Tables Views SPs Trigs Funcs   FKs Partn
  mysql        31                                  
  player        5                           1      

  Database MyISAM InnoDB CSV
  mysql        10     19   2
  player               5    

  Database BTREE
  mysql       38
  player      12

             c   t   s   e   v   i   f   l   d   t   b   s   t   b   m   m   t
             h   i   e   n   a   n   l   o   a   i   i   m   e   l   e   e   i
             a   m   t   u   r   t   o   n   t   n   g   a   x   o   d   d   m
             r   e       m   c       a   g   e   y   i   l   t   b   i   i   e
                 s           h       t   b   t   i   n   l           u   u    
                 t           a           l   i   n   t   i           m   m    
                 a           r           o   m   t       n           t   b    
                 m                       b   e           t           e   l    
                 p                                                   x   o    
                                                                     t   b    
  Database === === === === === === === === === === === === === === === === ===
  mysql     61  15   6  62  18  42   3   5   4   6  23   4  26   5   2   2   2
  player                    16   6           3      11                        

# Noteworthy Technologies ####################################
       Full Text Indexing | No
         Geospatial Types | No
             Foreign Keys | Yes
             Partitioning | No
       InnoDB Compression | Yes
                      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.18
         Buffer Pool Size | 128.0M
         Buffer Pool Fill | 4%
        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 | 0
               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 | 28
               Zero-Sized | 0
               Total Size | 168.8k
            binlog_format | ROW
         expire_logs_days | 0
              sync_binlog | 1
                server_id | 1024
             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_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_S
UBSTITUTION         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/data/ubuntu.err
             log_warnings | 2
         log_slow_queries | 
log_queries_not_using_indexes | OFF
        log_slave_updates | ON
# Configuration File #########################################
              Config File | /etc/my.cnf

[client]
user                                = root
password                            = 123456
port                                = 3306
socket                              = /tmp/mysql.sock

[mysqld]
socket                              = /tmp/mysql.sock
basedir                             = /usr/local/mysql
datadir                             = /usr/local/mysql/data
sql_mode                            = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,N
O_ENGINE_SUBSTITUTIONinnodb_buffer_pool_instances        = 1 
slow_query_log                      = ON
long_query_time                     = 10
log_throttle_queries_not_using_indexes = 1
log_output                          = FILE
show-slave-auth-info
server-id                           = 1024
log-bin                             = master-bin
log-bin-index                       = master-bin.index
binlog_format                       = row 
binlog_rows_query_log_events        = ON
sync_binlog                         = 1 
innodb_support_xa                   = ON
innodb_flush_log_at_trx_commit      = 1
skip-slave-start
log_slave_updates                   = ON
relay_log_recovery                  = ON
gtid_mode                           = ON
enforce_gtid_consistency            = 1
# Memory management library ##################################
jemalloc is not enabled in MySQL config for process with ID 1846
# The End ####################################################