percona-toolkit
percona-toolkit
藏劍丶 发表于1年前
percona-toolkit
  • 发表于 1年前
  • 阅读 27
  • 收藏 1
  • 点赞 0
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

摘要:

通过下面的这些命令在接触到新的数据库服务器的时候能更好更快的了解服务器和数据库的状况。

1:pt-summary:查看系统摘要报告

执行:

pt-summary

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

 

# Percona Toolkit System Summary Report ######################
        Date | 2013-10-23 09:06:37 UTC (local TZ: CST +0800)
    Hostname | zhoujy
      Uptime | 5 days, 23:25,  3 users,  load average: 1.31, 1.32, 1.27
    Platform | Linux
     Release | Ubuntu 11.10 (oneiric)
      Kernel | 3.0.0-32-generic-pae
Architecture | CPU = 64-bit, OS = 32-bit
   Threading | NPTL 2.13
     SELinux | No SELinux detected
 Virtualized | No virtualization detected
# Processor ##################################################
  Processors | physical = 1, cores = 2, virtual = 2, hyperthreading = no
      Speeds | 2x1600.000
      Models | 2xPentium(R) Dual-Core CPU E6600 @ 3.06GHz
      Caches | 2x2048 KB
# Memory #####################################################
       Total | 3.9G
        Free | 389.6M
        Used | physical = 3.5G, swap allocated = 0.0, swap used = 0.0, virtual = 3.5G
     Buffers | 322.0M
      Caches | 1.4G
       Dirty | 344 kB
     UsedRSS | 2.2G
  Swappiness | 60
 DirtyPolicy | 10, 5
 DirtyStatus | 0, 0
  Locator   Size     Speed             Form Factor   Type          Type Detail
  ========= ======== ================= ============= ============= ===========
# Mounted Filesystems ########################################
  Filesystem  Size Used Type       Opts                                                         Mountpoint
  /dev/sda1    46G  63% ext4       rw,errors=remount-ro,commit=0                                /
  /dev/sda3   272G  15% ext4       rw,commit=0                                                  /home
  /dev/sda5   144G  55% fuseblk    rw,nosuid,nodev,allow_other,blksize=4096,default_permissions /media/other
  none        2.0G   1% tmpfs      rw,noexec,nosuid,nodev,size=5242880                          /run/shm
  none        2.0G   1% tmpfs      rw,nosuid,nodev                                              /run/shm
  none        2.0G   1% debugfs    rw                                                           /run/shm
  none        2.0G   1% securityfs rw                                                           /run/shm
  none        5.0M   0% tmpfs      rw,noexec,nosuid,nodev,size=5242880                          /run/lock
  none        5.0M   0% tmpfs      rw,nosuid,nodev                                              /run/lock
  none        5.0M   0% debugfs    rw                                                           /run/lock
  none        5.0M   0% securityfs rw                                                           /run/lock
  tmpfs       799M   1% tmpfs      rw,noexec,nosuid,size=10%,mode=0755                          /run
  udev        2.0G   1% devtmpfs   rw,mode=0755                                                 /dev
# Disk Schedulers And Queue Size #############################
         sda | [cfq] 128
# Disk Partioning ############################################
# Kernel Inode State #########################################
dentry-state | 92512    78396    45    0    0    0
     file-nr | 9632    0    407487
    inode-nr | 166660    105139
# LVM Volumes ################################################
Unable to collect information
# LVM Volume Groups ##########################################
Unable to collect information
# RAID Controller ############################################
  Controller | No RAID controller detected
# Network Config #############################################
  Controller | Atheros Communications AR8151 v2.0 Gigabit Ethernet (rev c0)
 FIN Timeout | 60
  Port Range | 61000
# Interface Statistics #######################################
  interface  rx_bytes rx_packets  rx_errors   tx_bytes tx_packets  tx_errors
  ========= ========= ========== ========== ========== ========== ==========
  lo       1000000000     100000          0 1000000000     100000          0
  eth0     3000000000    5000000          0  600000000    2000000          0
# Network Connections ########################################
  Connections from remote IP addresses
    61.135.208.76       6
    61.135.208.77      15
    61.158.248.86       1
    74.125.31.125       1
    74.125.235.64       1
    74.125.235.70       1
    74.125.235.71       2
    74.125.235.72       1
    74.125.235.73       1
    74.125.235.78       2
    74.125.235.99       1
    74.125.235.101      3
    74.125.235.102      1
    74.125.235.111      2
    74.125.235.161      1
    74.125.235.166      1
    74.125.235.201      1
    91.189.89.88        1
    91.189.89.144       1
    91.189.90.41        1
    101.71.248.195      1
    112.95.242.170      1
    120.92.249.43       2
    125.39.127.17       3
    173.194.72.95       1
    180.149.134.229     1
    192.168.200.25      4
    192.168.200.202     1
    192.168.200.227     1
    203.208.46.200      1
  Connections to local IP addresses
    192.168.200.25     60
  Connections to top 10 local ports
    56897               1
    56898               1
    56899               1
    57817               1
    58279               1
    58283               1
    59046               1
    59883               1
    60109               1
    6379                1
  States of connections
    CLOSE_WAIT         50
    ESTABLISHED        10
    LISTEN             20
# Top Processes ##############################################
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 2687 zhoujy    20   0 1182m 620m  43m S   24 15.5   1871:17 firefox
  521 zhoujy    20   0  504m  95m  28m S    4  2.4  83:28.96 plugin-containe
 2365 zhoujy    20   0  328m 105m  21m S    2  2.6  88:37.26 compiz
 2378 zhoujy     9 -11  162m 9620 7412 S    2  0.2  34:09.55 pulseaudio
 3136 zhoujy    20   0 92228  20m  12m S    2  0.5   0:27.86 gnome-terminal
    1 root      20   0  3428 1896 1212 S    0  0.0   0:00.52 init
    2 root      20   0     0    0    0 S    0  0.0   0:00.01 kthreadd
    3 root      20   0     0    0    0 S    0  0.0   0:07.45 ksoftirqd/0
    5 root      20   0     0    0    0 S    0  0.0   0:00.51 kworker/u:0
# Notable Processes ##########################################
  PID    OOM    COMMAND
  824    -17    sshd
# Simplified and fuzzy rounded vmstat (wait please) ##########
  procs  ---swap-- -----io---- ---system---- --------cpu--------
   r  b    si   so    bi    bo     ir     cs  us  sy  il  wa  st
   1  0     0    0     2    15     15     10  13   1  85   0    
   0  0     0    0     0     0   1500   3500  21  10  69   0    
   0  0     0    0     0     0   1250   2250  16   2  83   0    
   0  0     0    0     0     0   1000   2250  13   2  85   0    
   1  0     0    0     0   175   1750   4000  35   8  56   0    
# The End ####################################################

复制代码

更多信息见:http://www.percona.com/doc/percona-toolkit/2.2/pt-summary.html

2:pt-mysql-summary:查看mysql各个统计信息

执行:

pt-mysql-summary --user=root --password=123456 --host=192.168.200.25 --port=3306

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

zhoujy@zhoujy:~$ pt-mysql-summary --user=root --password=123456 --host=192.168.200.25 --port=3306
# Percona Toolkit MySQL Summary Report #######################
              System time | 2013-10-23 09:20:38 UTC (local TZ: CST +0800)
# Instances ##################################################
  Port  Data Directory             Nice OOM Socket
  ===== ========================== ==== === ======
   3307 /opt/mysql/mysql5.6        0    0   /var/run/mysqld/mysqld2.sock
# MySQL Executable ###########################################
       Path to executable | /opt/mysql/server-5.6/bin/mysqld
              Has symbols | Yes
       Path to executable | /usr/sbin/mysqld
              Has symbols | No
# Report On Port 3306 ########################################
                     User | root@192.168.200.%
                     Time | 2013-10-23 17:20:38 (CST)
                 Hostname | zhoujy
                  Version | 5.1.69-0ubuntu0.11.10.1-log (Ubuntu)
                 Built On | debian-linux-gnu i686
                  Started | 2013-10-17 17:41 (up 5+23:39:35)
                Databases | 33
                  Datadir | /var/lib/mysql/
                Processes | 3 connected, 2 running
              Replication | Is not a slave, has 1 slaves connected
                  Pidfile | /var/lib/mysql/zhoujy.pid (does not exist)
# Processlist ################################################

  Command                        COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  Binlog Dump                           1       1       175       175
  Query                                 1       1         0         0
  Sleep                                 1       0      7000      7000

  User                           COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  rep                                   1       1       175       175
  root                                  2       1         0         0

  Host                           COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  localhost                             1       0         0         0
  zhoujy.local                          2       2       175       175

  db                             COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  aaa                                   1       0         0         0
  NULL                                  2       2       175       175

  State                          COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
                                        1       0         0         0
  Has sent all binlog to slave;         1       1       175       175
  NULL                                  1       1         0         0

# Status Counters (Wait 10 Seconds) ##########################
Variable                                Per day  Per second     10 secs
Aborted_connects                              2                        
Binlog_cache_use                              2                        
Bytes_received                            12500                     200
Bytes_sent                                60000                    1750
Com_admin_commands                           20                        
Com_create_table                              1                        
Com_insert                                    1                        
Com_select                                   50                       1
Com_set_option                               70                        
Com_show_binlogs                              1                        
Com_show_create_table                         1                        
Com_show_databases                            1                        
Com_show_status                               1                        
Com_show_tables                               1                        
Com_show_variables                           50                        
Connections                                  35                       1
Created_tmp_disk_tables                       5                       1
Created_tmp_tables                           70                       3
Handler_commit                                6                        
Handler_prepare                               3                        
Handler_read_first                            3                        
Handler_read_key                              3                        
Handler_read_next                             4                        
Handler_read_rnd_next                      1250                      35
Handler_write                              1000                      35
Innodb_buffer_pool_pages_flushed             15                        
Innodb_buffer_pool_read_requests           4500                      70
Innodb_buffer_pool_reads                    250                        
Innodb_buffer_pool_write_requests            60                        
Innodb_data_fsyncs                           15                        
Innodb_data_read                        4500000          50            
Innodb_data_reads                           250                        
Innodb_data_writes                           20                        
Innodb_data_written                      500000           5            
Innodb_dblwr_pages_written                   15                        
Innodb_dblwr_writes                           2                        
Innodb_log_write_requests                     7                        
Innodb_log_writes                             2                        
Innodb_os_log_fsyncs                          4                        
Innodb_os_log_written                      5000                        
Innodb_pages_created                          2                        
Innodb_pages_read                           250                        
Innodb_pages_written                         15                        
Innodb_rows_inserted                          1                        
Innodb_rows_read                              7                        
Key_read_requests                             5                        
Key_reads                                     1                        
Key_write_requests                            2                        
Key_writes                                    1                        
Open_table_definitions                       45                        
Opened_files                                400                      70
Opened_table_definitions                     90                      15
Opened_tables                               175                      30
Queries                                     300                       4
Questions                                   300                       4
Select_scan                                  60                        
Table_locks_immediate                        10                        
Uptime                                    90000           1           1
# Table cache ################################################
                     Size | 64
                    Usage | 100%
# 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
# Percona XtraDB Cluster #####################################
# Plugins ####################################################
       InnoDB compression | ACTIVE
# Query cache ################################################
         query_cache_type | OFF
                     Size | 0.0
                    Usage | 0%
         HitToInsertRatio | 0%
# Schema #####################################################
Would you like to mysqldump -d the schema and analyze it? y/n                 
Skipping schema analysis
# 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.1.69
         Buffer Pool Size | 500.0M
         Buffer Pool Fill | 4%
        Buffer Pool Dirty | 0%
           File Per Table | ON
                Page Size | 16k
            Log File Size | 2 * 16.0M = 32.0M
          Log Buffer Size | 8M
             Flush Method | O_DIRECT
      Flush Log At Commit | 0
               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 | 107
               Read Views | 1
         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 | 16.0M
                 Pct Used | 10%
                Unflushed | 0%
# Security ###################################################
                    Users | 8 users, 0 anon, 0 w/o pw, 0 old pw
            Old Passwords | OFF
# Binary Logging #############################################
                  Binlogs | 3
               Zero-Sized | 0
               Total Size | 4.8k
            binlog_format | ROW
         expire_logs_days | 10
              sync_binlog | 0
                server_id | 1
             binlog_do_db | 
         binlog_ignore_db | 
# Noteworthy Variables #######################################
     Auto-Inc Incr/Offset | 1/1
   default_storage_engine | 
               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 | 64M
             thread_stack | 192k
                      log | OFF
                log_error | /var/log/mysql/error.log
             log_warnings | 1
         log_slow_queries | OFF
log_queries_not_using_indexes | OFF
        log_slave_updates | OFF
# Configuration File #########################################
              Config File | /etc/mysql/my.cnf

[client]
port                                = 3306
socket                              = /var/run/mysqld/mysqld.sock

[mysqld_safe]
innodb_stats_sample_pages           = 16
socket                              = /var/run/mysqld/mysqld.sock
nice                                = 0

[mysqld]
ft_min_word_len                     = 2
ft_stopword_file                    = /var/lib/mysql/stopword.txt
innodb_adaptive_hash_index          = 0
low-priority-updates
show-slave-auth-info
ignore_builtin_innodb
innodb_buffer_pool_size             = 500M
innodb_flush_log_at_trx_commit      = 0
innodb_flush_method                 = O_DIRECT
innodb_log_file_size                = 16M
innodb_file_per_table
myisam-recover                      = force,backup
myisam_block_size                   = 2048
user                                = mysql
socket                              = /var/run/mysqld/mysqld.sock
port                                = 3306
basedir                             = /usr
datadir                             = /var/lib/mysql
tmpdir                              = /tmp
skip-external-locking
key_buffer_size                     = 16M
kb1.key_buffer_size                 = 10M
kb2.key_buffer_size                 = 10M
max_allowed_packet                  = 64M
thread_stack                        = 192K
thread_cache_size                   = 8
myisam-recover                      = BACKUP
max_connections                     = 600
query_cache_type                    = 0
log_error                           = /var/log/mysql/error.log
server-id                           = 1
log_bin                             = /var/log/mysql/mysql-bin.log
binlog_format                       = MIXED
expire_logs_days                    = 10
max_binlog_size                     = 1024M
max_relay_log_size                  = 500M

[mysqldump]
quick
quote-names
max_allowed_packet                  = 16M

[mysql]

[isamchk]
key_buffer                          = 16M
# The End ####################################################

View Code

更多信息见:http://www.percona.com/doc/percona-toolkit/2.2/pt-mysql-summary.html

3:pt-config-diff:对比配置文件的异同,类似Linux的diff命令

执行:

pt-config-diff h=localhost,P=3306 h=192.168.200.25,P=3307 --user=root --password=123456

打印出来的信息包括:指定MySQL它们配置文件的不同。

zhoujy@zhoujy:~$ pt-config-diff --ask-pass h=localhost,P=3306 h=192.168.200.25,P=3307 --user=root
Enter MySQL password: 
Enter MySQL password: 
49 config differences
Variable                  zhoujy                    zhoujy
========================= ========================= =========================
back_log                  50                        170
basedir                   /usr/                     /opt/mysql/server-5.6
character_sets_dir        /usr/share/mysql/chars... /opt/mysql/server-5.6/...
completion_type           0                         NO_CHAIN
concurrent_insert         1                         AUTO
datadir                   /var/lib/mysql/           /opt/mysql/mysql5.6/
ft_min_word_len           2                         4
general_log_file          /var/lib/mysql/zhoujy.log /opt/mysql/mysql5.6/zh...
ignore_builtin_innodb     ON                        OFF
innodb_adaptive_hash_i... OFF                       ON
innodb_autoextend_incr... 8                         64
innodb_buffer_pool_size   524288000                 134217728
innodb_change_buffering   inserts                   all
innodb_concurrency_tic... 500                       5000
innodb_data_file_path     ibdata1:10M:autoextend    ibdata1:12M:autoextend
innodb_file_format_check  Barracuda                 ON
innodb_flush_log_at_tr... 0                         1
innodb_flush_method       O_DIRECT                  
innodb_log_file_size      16777216                  50331648
innodb_old_blocks_time    0                         1000
innodb_open_files         300                       2000
innodb_stats_on_metadata  ON                        OFF
innodb_version            5.1.69                    1.2.10
join_buffer_size          131072                    262144
log_error                 /var/log/mysql/error.log  /var/log/mysql/error2.log
low_priority_updates      ON                        OFF
max_binlog_cache_size     4294963200                18446744073709547520
max_connect_errors        10                        100
open_files_limit          3000                      65535
optimizer_switch          index_merge=on,index_m... index_merge=on,index_m...
pid_file                  /var/lib/mysql/zhoujy.pid /opt/mysql/mysql5.6/zh...
plugin_dir                /usr/lib/mysql/plugin     /opt/mysql/server-5.6/...
port                      3306                      3307
query_cache_size          0                         16777216
report_port               3306                      3307
secure_auth               OFF                       ON
server_id                 1                         2
slow_query_log_file       /var/lib/mysql/zhoujy-... /opt/mysql/mysql5.6/zh...
socket                    /var/run/mysqld/mysqld... /var/run/mysqld/mysqld...
sort_buffer_size          2097144                   262144
sql_auto_is_null          ON                        OFF
sql_mode                                            NO_ENGINE_SUBSTITUTION
sql_slave_skip_counter                              0
storage_engine            MyISAM                    InnoDB
table_definition_cache    256                       1400
table_open_cache          64                        2000
version                   5.1.69-0ubuntu0.11.10.... 5.6.10-log
version_comment           (Ubuntu)                  MySQL Community Server...
version_compile_os        debian-linux-gnu          debian6.0

View Code

更多信息见:http://www.percona.com/doc/percona-toolkit/2.2/pt-config-diff.html

4:pt-variable-advisor:通过该命令,分析MySQL的变量(my.cnf),并对可能存在的问题提出建议

执行:

从指定地址获取变量值:
pt-variable-advisor --user=root --password=123456  192.168.220.245

打印出来的信息包括:一些变量设置的是否合理已经给出的建议

 

zhoujy@zhoujy:~$ pt-variable-advisor --ask-pass --user=zjy 192.168.220.245
Enter password: 
# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# WARN innodb_flush_log_at_trx_commit-1: InnoDB is not configured in strictly ACID mode.

# WARN innodb_flush_log_at_trx_commit-2: Setting innodb_flush_log_at_trx_commit to 0 has no performance benefits over setting it to 2, and more types of data loss are possible.

# NOTE innodb_max_dirty_pages_pct: The innodb_max_dirty_pages_pct is lower than the default.

# NOTE log_warnings-2: Log_warnings must be set greater than 1 to log unusual events such as aborted connections.

# NOTE max_connect_errors: max_connect_errors should probably be set as large as your platform allows.

# WARN slave_net_timeout: This variable is set too high.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# NOTE innodb_flush_method: Most production database servers that use InnoDB should set innodb_flush_method to O_DIRECT to avoid double-buffering, unless the I/O system is very low performance.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.

 

更多信息见:http://www.percona.com/doc/percona-toolkit/2.2/pt-variable-advisor.html

共有 人打赏支持
粉丝 0
博文 54
码字总数 15281
×
藏劍丶
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: