pt-summary

功能:统计系统信息Summarize system information nicely.

 

作用:

    打印出来的信息包括:CPU、内存、硬盘、网卡等信息,还包括文件系统、磁盘调度和队列大小、LVM、RAID、网络链接信息、netstat 的统计,以及前10的负载占用信息和vmstat信息。 

 

示例:

# pt-summary【下图为部分截图】

wKioL1cyzYfwvkpUAACfPZdA9jA141.png




pt-mysql-summary

功能:

    查看mysql各个统计信息。

打印出来的信息包括:版本信息、数据目录、命令的统计、用户,数据库以及复制等信息还包括各个变量(status、variables)信息和各个变量的比例信息,还有配置文件等信息。

 

示例:

# 将结果写入文件

# pt-mysql-summary --user=root--password=root --host='192.168.2.11' > /root/node1_summary.log


例如:

# pt-mysql-summary--user=root --password=root --host=localhost

输出的结果如下【这是我在线上的一台MariaDB主节点截取的信息,很多参数还未做优化】:

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

             System time | 2016-05-0911:21:31 UTC (local TZ: CST +0800)

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

 Port  Data Directory             Nice OOM Socket

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

  3306 /data/mysql                0   0   /tmp/mysql.sock

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

      Path to executable | /usr/local/mysql/bin/mysqld

              Has symbols | Yes

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

                    User |root@localhost

                     Time | 2016-05-09 19:21:31(CST)

                 Hostname |localhost.localdomain

                  Version | 10.0.12-MariaDB-logSource distribution

                 Built On | Linux x86_64

                 Started | 2016-05-0703:15 (up 2+16:06:28)

                Databases | 13

                  Datadir | /data/mysql/

                Processes | 30 connected, 2running

              Replication | Is not a slave, has1 slaves connected

                 Pidfile |/data/mysql/localhost.localdomain.pid (exists)

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

 

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

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

 Binlog Dump                          1       1    125000   125000

 Query                                1       1         0         0

 Sleep                               30       0     22500     7000

 

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

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

 KF_Mobile                            6       0         0         0

 KF_Mobile_user                       1       0         0         0

 repluser                             1       1    125000   125000

 root                                 1       1         0         0

 zabbix                              20       0         0         0

 

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

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

 116.228.235.114                      2       0         0         0

 172.16.10.10                        25       1    125000   125000

 localhost                             1       1        0         0

 

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

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

 KF_Mobile                            7       0         0         0

  NULL                                  2       2   125000    125000

 zabbix                              20       0         0         0

 

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

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

                                       30       0        0         0

 init                                 1       1         0         0

 Master has sent all binlog to        1       1    125000   125000

 

# Status Counters (Wait 10 Seconds) ##########################            # 下面是很多的统计信息,可以awk做后续处理

Variable                                Per day  Per second    10 secs

Aborted_clients                              20                       

Aborted_connects                           4500                       

Access_denied_errors                          1                       

Aria_pagecache_blocks_unused               6000                       

Aria_pagecache_blocks_used                  250                       

Aria_pagecache_read_requests           80000000        1000          25

Aria_pagecache_reads                      40000                       1

Aria_pagecache_write_requests          17500000         200           1

Binlog_commits                            60000                        

Binlog_group_commits                      60000                       

Binlog_snapshot_position               35000000         400         400

Binlog_bytes_written                   35000000         400         400

Binlog_cache_use                          50000                       

Binlog_stmt_cache_use                     12500                       

Bytes_received                        500000000        6000        1250

Bytes_sent                           6000000000       60000       6000

Com_admin_commands                       100000           1           

Com_begin                                 30000                       

Com_change_db                             30000                       

Com_commit                                35000                       

Com_delete                                15000                       

Com_help                                      1                       

Com_insert                                50000                       

Com_insert_select                             3                       

Com_kill                                      3                       

Com_lock_tables                              10                       

Com_optimize                               4000                       

Com_select                              4000000          45           5

Com_set_option                            90000           1           

Com_show_collations                          20                       

Com_show_create_db                           20                       

Com_show_create_table                      3500                       

Com_show_databases                            6                       

Com_show_engine_status                        1                       

Com_show_fields                            2000                       

Com_show_grants                               7                       

Com_show_processlist                         50                       

Com_show_status                           17500                       

Com_show_table_status                      1000                       

Com_show_tables                             500                       

Com_show_triggers                          1000                        

Com_show_variables                         1500                       

Com_unlock_tables                            10                       

Com_update                                15000                       

Connection_errors_peer_address             2500                       

Connections                               45000                       1

Created_tmp_disk_tables                   45000                       1

Created_tmp_files                             2                        

Created_tmp_tables                       100000           1           1

Empty_queries                            150000           1           

Feature_subquery                           6000                       

Feature_timezone                              4                       

Handler_commit                          4000000          45           4

Handler_delete                           400000           4           

Handler_icp_attempts                    1750000          20           

Handler_icp_match                       1750000          20           

Handler_prepare                          200000           2           1

Handler_read_first                        40000                       

Handler_read_key                       50000000         600           6

Handler_read_last                           900                       

Handler_read_next                      60000000         700           1

Handler_read_prev                        300000           3           

Handler_read_rnd                        7000000          80           5

Handler_read_rnd_deleted                1000000          10          40

Handler_read_rnd_next                 250000000        3000         300

Handler_rollback                              6                       

Handler_tmp_update                       300000           3           

Handler_tmp_write                      50000000         600          60

Handler_update                            15000                       

Handler_write                            450000           5           5

Innodb_available_undo_logs                   50                       

Innodb_background_log_sync                90000                       1

Innodb_buffer_pool_bytes_data          50000000         600           

Innodb_buffer_pool_bytes_dirty           175000           1      -45875

Innodb_buffer_pool_pages_flushed         600000           6           9

Innodb_buffer_pool_pages_made_not_young     4500000          50           

Innodb_buffer_pool_pages_made_young        3000                       

Innodb_buffer_pool_pages_old               1250                       

Innodb_buffer_pool_read_ahead              4000                       

Innodb_buffer_pool_read_requests      450000000        5000         175

Innodb_buffer_pool_reads                  15000                       

Innodb_buffer_pool_write_requests       4500000          50          15

Innodb_checkpoint_age                      1250                    -354

Innodb_checkpoint_max_age              30000000         350           

Innodb_data_fsyncs                       150000           1           2

Innodb_data_read                      300000000        3500           

Innodb_data_reads                         20000                        

Innodb_data_writes                       700000           8          10

Innodb_data_written                 20000000000      225000      300000

Innodb_dblwr_pages_written               600000           6           9

Innodb_dblwr_writes                       15000                       

Innodb_history_list_length                  225                       

Innodb_ibuf_free_list                         1                       

Innodb_ibuf_merged_inserts                  200                       

Innodb_ibuf_merges                          175                       

Innodb_ibuf_segment_size                      2                       

Innodb_log_write_requests                350000           3           1

Innodb_log_writes                         70000                       

Innodb_lsn_current                  15000000000      150000         700

Innodb_lsn_flushed                  15000000000      150000         700

Innodb_lsn_last_checkpoint          15000000000      150000        1000

Innodb_master_thread_active_loops         60000                       

Innodb_master_thread_idle_loops           22500                       

Innodb_max_trx_id                      30000000         300           4

Innodb_mem_adaptive_hash                5000000          60           

Innodb_mem_dictionary                   1000000          15           

Innodb_mem_total                       60000000         700           

Innodb_mutex_os_waits                     17500                       

Innodb_mutex_spin_rounds                 700000           7          10

Innodb_mutex_spin_waits                  400000           4           

Innodb_os_log_fsyncs                      90000           1           

Innodb_os_log_written                 200000000        2250        1000

Innodb_pages_created                       2000                       

Innodb_pages_read                         20000                       

Innodb_pages_written                     600000           6           9

Innodb_purge_trx_id                    30000000         300           

Innodb_read_views_memory                   1000                       

Innodb_row_lock_time                        175                       

Innodb_row_lock_waits                        45                        

Innodb_rows_deleted                      400000           4           

Innodb_rows_inserted                     450000           5           5

Innodb_rows_read                      300000000        3000         250

Innodb_rows_updated                       12500                       

Innodb_s_lock_os_waits                    17500                       

Innodb_s_lock_spin_rounds                700000           8           9

Innodb_s_lock_spin_waits                  60000                       

Innodb_x_lock_os_waits                      450                       

Innodb_x_lock_spin_rounds                 60000                       

Innodb_x_lock_spin_waits                   4000                       

Key_read_requests                           800                       

Key_reads                                   700                       

Memory_used                            60000000         700        2250

Open_table_definitions                      150                       

Opened_files                             200000           2           5

Opened_table_definitions                   8000                       

Opened_tables                             70000                       1

Queries                                 4000000          50           9

Questions                               4000000          50           9

Rows_read                             300000000        3000         250

Rows_sent                              40000000         450          60

Rows_tmp_read                          50000000         600          70

Select_full_join                           8000                       

Select_range                              40000                       

Select_scan                              450000           5           1

Sort_range                               300000           3           

Sort_rows                              10000000         125           6

Sort_scan                                250000           3           1

Subquery_cache_hit                          100                       

Subquery_cache_miss                       12500                       

Syncs                                         6                       

Table_locks_immediate                   4000000          45           5

Table_locks_waited                           25                       

Threads_created                              20                       

Uptime                                    90000           1           1

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

                     Size | 64

                    Usage | 100%    # 从这里我们可以看出这个参数需要优化下【set GLOBAL table_open_cache = 1024;或者停MySQL再修改】

# Key Percona Server features################################

     Table & Index Stats | Disabled

    Multiple I/O Threads | Enabled

    Corruption Resilient | Enabled

     Durable Replication | Not Supported

    Import InnoDB Tables | Not Supported

    Fast Server Restarts | Not Supported

        Enhanced Logging | Disabled

    Replica Perf Logging | Not Supported

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

                    Usage | 0%

        HitToInsertRatio | 0%

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

Specify --databases or --all-databases todump and summarize schemas

# Noteworthy Technologies####################################

                      SSL | No

    Explicit LOCK TABLES | Yes

          Delayed Insert | No

         XA Transactions | No

              NDB Cluster | No

     Prepared Statements | No

 Prepared statement count | 0

# InnoDB#####################################################

                  Version | 5.6.17-65.0

         Buffer Pool Size | 160.0M

         Buffer Pool Fill | 90%          # 缓冲池基本用光了,需要优化下。【修改my.cnf,调大innodb_buffer_pool_size=2G】

       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       当前并发为0

      Concurrency Tickets | 5000    可用的票为5000(有点类似于工作票的意思)

      Commit Concurrency | 0

     Txn Isolation Level | REPEATABLE-READ

       Adaptive Flushing | ON

     Adaptive Checkpoint |

          Checkpoint Age | 3k

            InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue

      Oldest Transaction | 0 Seconds

        History List Len | 589

589

               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, 0sync, 0 log IO (0 log, 0 chkp); 0 pwrites

     Pending I/O Flushes | 0 buf pool, 0 log

      Transaction States | 60xnot started

# MyISAM #####################################################

                Key Cache | 16.0M

                 Pct Used | 20%

                Unflushed | 0%

# Security###################################################

                    Users | 20 users, 0 anon, 0w/o pw, 0 old pw

           Old Passwords | OFF

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

                  Binlogs | 7

               Zero-Sized | 0

               Total Size | 579.4M

           binlog_format | MIXED       # 日志格式为MIXED

        expire_logs_days | 20

              sync_binlog | 0           # 刷写binlog的频率【0表示mysql不管,由系统自己决定什么时候刷写到磁盘】

               server_id | 1

            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 | 512k

        read_buffer_size | 256k

    read_rnd_buffer_size | 512k

      bulk_insert_buffer | 0.00

     max_heap_table_size | 16M

          tmp_table_size | 16M

      max_allowed_packet | 20M

            thread_stack | 288k

                      log |

                log_error |/data/mysql/localhost.localdomain.err

             log_warnings | 1   【设定是否将警告信息记录进错误日志。默认设定为1,表示启用;可以将其设置为0以禁用;大于1的数值时表示将新发起连接时产生的“失败的连接”和“拒绝访问”类的错误信息也记录进错误日志。】

        log_slow_queries |

log_queries_not_using_indexes | OFF

       log_slave_updates | OFF     # 表示允许备库将其重放的事件记录到自身的二进制日志中【这个参数只能配置在从节点上】

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

              Config File | /etc/my.cnf

 

[client]

port                                = 3306

socket                              = /tmp/mysql.sock

 

[mysqld]

port                                = 3306

socket                              = /tmp/mysql.sock

skip-external-locking

key_buffer_size                     = 16M

table_open_cache                    = 64

sort_buffer_size                    = 512K

net_buffer_length                   = 8K

read_buffer_size                    = 256K

read_rnd_buffer_size                = 512K

myisam_sort_buffer_size             = 8M

skip_name_resolve                   = on

slow_query_log                      = on

long_query_time                     = 5

thread_cache_size                   = 300

query_cache_size                    = 0

query_cache_type                    = OFF

max_allowed_packet                  = 20M

max_connections                     = 1000

character_set_server                = utf8

log-bin                             = mysql-bin

binlog_format                       = mixed

max_binlog_size                     = 500M

expire_logs_days                    = 20

server-id                           = 1

innodb_file_per_table               = on

innodb_buffer_pool_size             = 160M

innodb_log_buffer_size              = 8M

innodb_flush_log_at_trx_commit      = 1

 

[mysqldump]

quick

max_allowed_packet                  = 20M

 

[mysql]

no-auto-rehash

 

[myisamchk]

key_buffer_size                     = 20M

sort_buffer_size                    = 20M

read_buffer                         = 2M

write_buffer                        = 2M

 

[mysqlhotcopy]

interactive-timeout

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

 


上面的参数很多,可以根据其结果修改参数。以达到调优的目的。