首页 / 数据库 / MySQL / percona-toolkit 之 【pt-summary】、【pt-mysql-summary】、【pt-config-diff】、【pt-variable-advisor】说明

摘要：通过下面的这些命令在接触到新的数据库服务器的时候能更好更快的了解服务器和数据库的状况。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 | zhoujyUptime | 5 days, 23:25,3 users,load average: 1.31, 1.32, 1.27Platform | Linux Release | Ubuntu 11.10 （oneiric）Kernel | 3.0.0-32-generic-paeArchitecture | 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 = noSpeeds | 2x1600.000Models | 2xPentium（R） Dual-Core CPU E6600 @ 3.06GHzCaches | 2x2048 KB# Memory ##################################################### Total | 3.9GFree | 389.6MUsed | physical = 3.5G, swap allocated = 0.0, swap used = 0.0, virtual = 3.5G Buffers | 322.0MCaches | 1.4G Dirty | 344 kB UsedRSS | 2.2GSwappiness | 60 DirtyPolicy | 10, 5 DirtyStatus | 0, 0Locator Size Speed Form Factor TypeType Detail========= ======== ================= ============= ============= ===========# Mounted Filesystems ########################################FilesystemSize Used Type Opts Mountpoint/dev/sda146G63% ext4 rw,errors=remount-ro,commit=0//dev/sda3 272G15% ext4 rw,commit=0/home/dev/sda5 144G55% fuseblkrw,nosuid,nodev,allow_other,blksize=4096,default_permissions /media/othernone2.0G 1% tmpfsrw,noexec,nosuid,nodev,size=5242880/run/shmnone2.0G 1% tmpfsrw,nosuid,nodev/run/shmnone2.0G 1% debugfsrw /run/shmnone2.0G 1% securityfs rw /run/shmnone5.0M 0% tmpfsrw,noexec,nosuid,nodev,size=5242880/run/locknone5.0M 0% tmpfsrw,nosuid,nodev/run/locknone5.0M 0% debugfsrw /run/locknone5.0M 0% securityfs rw /run/locktmpfs 799M 1% tmpfsrw,noexec,nosuid,size=10%,mode=0755/runudev2.0G 1% devtmpfs rw,mode=0755 /dev# Disk Schedulers And Queue Size ############################# sda | [cfq] 128# Disk Partioning ############################################# Kernel Inode State #########################################dentry-state | 925127839645000 file-nr | 96320407487inode-nr | 166660105139# 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 | 60Port Range | 61000# Interface Statistics #######################################interfacerx_bytes rx_packetsrx_errors tx_bytes tx_packetstx_errors========= ========= ========== ========== ========== ========== ==========lo 1000000000 1000000 1000000000 1000000eth0 30000000005000000060000000020000000# Network Connections ########################################Connections from remote IP addresses61.135.208.76 661.135.208.771561.158.248.86 174.125.31.125 174.125.235.64 174.125.235.70 174.125.235.71 274.125.235.72 174.125.235.73 174.125.235.78 274.125.235.99 174.125.235.101374.125.235.102174.125.235.111274.125.235.161174.125.235.166174.125.235.201191.189.89.88191.189.89.144 191.189.90.411101.71.248.1951112.95.242.1701120.92.249.43 2125.39.127.17 3173.194.72.95 1180.149.134.229 1192.168.200.254192.168.200.202 1192.168.200.227 1203.208.46.2001Connections to local IP addresses192.168.200.25 60Connections to top 10 local ports56897 156898 156899 157817 158279 158283 159046 159883 160109 163791States of connectionsCLOSE_WAIT 50ESTABLISHED10LISTEN 20# Top Processes ##############################################PID USERPRNIVIRTRESSHR S %CPU %MEMTIME+COMMAND 2687 zhoujy20 0 1182m 620m43m S 24 15.5 1871:17 firefox521 zhoujy20 0504m95m28m S42.483:28.96 plugin-containe 2365 zhoujy20 0328m 105m21m S22.688:37.26 compiz 2378 zhoujy 9 -11162m 9620 7412 S20.234:09.55 pulseaudio 3136 zhoujy20 0 9222820m12m S20.5 0:27.86 gnome-terminal1 root20 03428 1896 1212 S00.0 0:00.52 init2 root20 0 000 S00.0 0:00.01 kthreadd3 root20 0 000 S00.0 0:07.45 ksoftirqd/05 root20 0 000 S00.0 0:00.51 kworker/u:0# Notable Processes ##########################################PIDOOMCOMMAND824-17sshd# Simplified and fuzzy rounded vmstat （wait please） ##########procs---swap-- -----io---- ---system---- --------cpu-------- rbsi sobibo ir csussyilwast 10 00 215 15 1013 185 0 00 00 0 0 1500 3500211069 0 00 00 0 0 1250 225016 283 0 00 00 0 0 1000 225013 285 0 10 00 0 175 1750 400035 856 0# The End ####################################################View Code 更多信息见：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 ##################################################PortData Directory Nice OOM Socket===== ========================== ==== === ====== 3307 /opt/mysql/mysql5.600 /var/run/mysqld/mysqld2.sock# MySQL Executable ########################################### Path to executable | /opt/mysql/server-5.6/bin/mysqldHas symbols | Yes Path to executable | /usr/sbin/mysqldHas symbols | No# Report On Port 3306 ######################################## User | root@192.168.200.% Time | 2013-10-23 17:20:38 （CST） Hostname | zhoujyVersion | 5.1.69-0ubuntu0.11.10.1-log （Ubuntu） Built On | debian-linux-gnu i686Started | 2013-10-17 17:41 （up 5+23:39:35）Databases | 33Datadir | /var/lib/mysql/Processes | 3 connected, 2 runningReplication | Is not a slave, has 1 slaves connectedPidfile | /var/lib/mysql/zhoujy.pid （does not exist）# Processlist ################################################CommandCOUNT（*） Working SUM（Time） MAX（Time）------------------------------ -------- ------- --------- ---------Binlog Dump 1 1 175 175Query 1 1 0 0Sleep 1 070007000User COUNT（*） Working SUM（Time） MAX（Time）------------------------------ -------- ------- --------- ---------rep 1 1 175 175root2 1 0 0Host COUNT（*） Working SUM（Time） MAX（Time）------------------------------ -------- ------- --------- ---------localhost 1 0 0 0zhoujy.local2 2 175 175db COUNT（*） Working SUM（Time） MAX（Time）------------------------------ -------- ------- --------- ---------aaa 1 0 0 0NULL2 2 175 175StateCOUNT（*） Working SUM（Time） MAX（Time）------------------------------ -------- ------- --------- ---------1 0 0 0Has sent all binlog to slave; 1 1 175 175NULL1 1 0 0# Status Counters （Wait 10 Seconds） ##########################VariablePer dayPer second 10 secsAborted_connects2Binlog_cache_use2Bytes_received12500 200Bytes_sent600001750Com_admin_commands 20Com_create_table1Com_insert1Com_select 50 1Com_set_option 70Com_show_binlogs1Com_show_create_table 1Com_show_databases1Com_show_status 1Com_show_tables 1Com_show_variables 50Connections35 1Created_tmp_disk_tables 5 1Created_tmp_tables 70 3Handler_commit6Handler_prepare 3Handler_read_first3Handler_read_key3Handler_read_next 4Handler_read_rnd_next125035Handler_write100035Innodb_buffer_pool_pages_flushed 15Innodb_buffer_pool_read_requests 450070Innodb_buffer_pool_reads250Innodb_buffer_pool_write_requests60Innodb_data_fsyncs 15Innodb_data_read450000050Innodb_data_reads 250Innodb_data_writes 20Innodb_data_written500000 5Innodb_dblwr_pages_written 15Innodb_dblwr_writes 2Innodb_log_write_requests 7Innodb_log_writes 2Innodb_os_log_fsyncs4Innodb_os_log_written5000Innodb_pages_created2Innodb_pages_read 250Innodb_pages_written 15Innodb_rows_inserted1Innodb_rows_read7Key_read_requests 5Key_reads 1Key_write_requests2Key_writes1Open_table_definitions 45Opened_files40070Opened_table_definitions 9015Opened_tables 17530Queries 300 4Questions 300 4Select_scan60Table_locks_immediate10Uptime90000 1 1# Table cache ################################################ Size | 64Usage | 100%# Key Percona Server features ################################Table & Index Stats | Not Supported Multiple I/O Threads | Enabled Corruption Resilient | Not SupportedDurable Replication | Not Supported Import InnoDB Tables | Not Supported Fast Server Restarts | Not Supported Enhanced Logging | Not Supported Replica Perf Logging | Not SupportedResponse Time Hist. | Not SupportedSmooth Flushing | Not SupportedHandlerSocket NoSQL | Not Supported Fast Hash UDFs | Unknown# Percona XtraDB Cluster ###################################### Plugins #################################################### InnoDB compression | ACTIVE# Query cache ################################################ query_cache_type | OFF Size | 0.0Usage | 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 | NoXA Transactions | NoNDB Cluster | NoPrepared 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 | ONPage Size | 16kLog File Size | 2 * 16.0M = 32.0MLog Buffer Size | 8M Flush Method | O_DIRECTFlush Log At Commit | 0 XA Support | ONChecksums | ONDoublewrite | ONR/W I/O Threads | 4 4 I/O Capacity | 200 Thread Concurrency | 0Concurrency Tickets | 500 Commit Concurrency | 0Txn Isolation Level | REPEATABLE-READAdaptive Flushing | ONAdaptive 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 undoPending 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 pwritesPending 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 pwOld Passwords | OFF# Binary Logging #############################################Binlogs | 3 Zero-Sized | 0 Total Size | 4.8kbinlog_format | ROW expire_logs_days | 10sync_binlog | 0server_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.00max_heap_table_size | 16M tmp_table_size | 16M max_allowed_packet | 64M thread_stack | 192klog | OFFlog_error | /var/log/mysql/error.log log_warnings | 1 log_slow_queries | OFFlog_queries_not_using_indexes | OFFlog_slave_updates | OFF# Configuration File #########################################Config File | /etc/mysql/my.cnf[client]port= 3306socket= /var/run/mysqld/mysqld.sock[mysqld_safe]innodb_stats_sample_pages = 16socket= /var/run/mysqld/mysqld.socknice= 0[mysqld]ft_min_word_len = 2ft_stopword_file= /var/lib/mysql/stopword.txtinnodb_adaptive_hash_index= 0low-priority-updatesshow-slave-auth-infoignore_builtin_innodbinnodb_buffer_pool_size = 500Minnodb_flush_log_at_trx_commit= 0innodb_flush_method = O_DIRECTinnodb_log_file_size= 16Minnodb_file_per_tablemyisam-recover= force,backupmyisam_block_size = 2048user= mysqlsocket= /var/run/mysqld/mysqld.sockport= 3306basedir = /usrdatadir = /var/lib/mysqltmpdir= /tmpskip-external-lockingkey_buffer_size = 16Mkb1.key_buffer_size = 10Mkb2.key_buffer_size = 10Mmax_allowed_packet= 64Mthread_stack= 192Kthread_cache_size = 8myisam-recover= BACKUPmax_connections = 600query_cache_type= 0log_error = /var/log/mysql/error.logserver-id = 1log_bin = /var/log/mysql/mysql-bin.logbinlog_format = MIXEDexpire_logs_days= 10max_binlog_size = 1024Mmax_relay_log_size= 500M[mysqldump]quickquote-namesmax_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=rootEnter MySQL password: Enter MySQL password: 49 config differencesVariablezhoujyzhoujy========================= ========================= =========================back_log50170basedir /usr/ /opt/mysql/server-5.6character_sets_dir/usr/share/mysql/chars... /opt/mysql/server-5.6/...completion_type 0 NO_CHAINconcurrent_insert 1 AUTOdatadir /var/lib/mysql/ /opt/mysql/mysql5.6/ft_min_word_len 2 4general_log_file/var/lib/mysql/zhoujy.log /opt/mysql/mysql5.6/zh...ignore_builtin_innodb ONOFFinnodb_adaptive_hash_i... OFF ONinnodb_autoextend_incr... 8 64innodb_buffer_pool_size 524288000 134217728innodb_change_buffering inserts allinnodb_concurrency_tic... 500 5000innodb_data_file_path ibdata1:10M:autoextendibdata1:12M:autoextendinnodb_file_format_checkBarracuda ONinnodb_flush_log_at_tr... 0 1innodb_flush_method O_DIRECTinnodb_log_file_size1677721650331648innodb_old_blocks_time0 1000innodb_open_files 300 2000innodb_stats_on_metadataONOFFinnodb_version5.1.691.2.10join_buffer_size131072262144log_error /var/log/mysql/error.log/var/log/mysql/error2.loglow_priority_updatesONOFFmax_binlog_cache_size 429496320018446744073709547520max_connect_errors10100open_files_limit300065535optimizer_switchindex_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/...port33063307query_cache_size0 16777216report_port 33063307secure_auth OFF ONserver_id 1 2slow_query_log_file /var/lib/mysql/zhoujy-... /opt/mysql/mysql5.6/zh...socket/var/run/mysqld/mysqld... /var/run/mysqld/mysqld...sort_buffer_size2097144 262144sql_auto_is_nullONOFFsql_modeNO_ENGINE_SUBSTITUTIONsql_slave_skip_counter0storage_engineMyISAMInnoDBtable_definition_cache256 1400table_open_cache642000version 5.1.69-0ubuntu0.11.10.... 5.6.10-logversion_comment （Ubuntu）MySQL Community Server...version_compile_osdebian-linux-gnudebian6.0View 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=123456192.168.220.245打印出来的信息包括：一些变量设置的是否合理已经给出的建议 zhoujy@zhoujy:~$ pt-variable-advisor --ask-pass --user=zjy 192.168.220.245Enter 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. View Code 更多信息见：http://www.percona.com/doc/percona-toolkit/2.2/pt-variable-advisor.html本文永久更新链接地址