[root@server-mysql bin]# pt-ioprofile -p 9655 -c times Sun Jul 3 17:02:34 CST 2016 Tracing process ID 9655 total pread read pwrite write fsync open close lseek fcntl filename 0.025765 0.000000 0.000000 0.000428 0.000000 0.025072 0.000176 0.000033 0.000000 0.000056 /data5/test/yy.ibd 0.013211 0.000000 0.000000 0.000217 0.000000 0.012994 0.000000 0.000000 0.000000 0.000000 /data5/ib_logfile0 0.009396 0.000000 0.000000 0.001237 0.000000 0.008159 0.000000 0.000000 0.000000 0.000000 /data5/ibdata1 0.005577 0.000099 0.000261 0.000059 0.000470 0.003984 0.000332 0.000095 0.000277 0.000000 /data5/test/yy.frm 0.001581 0.000000 0.000000 0.000000 0.000000 0.001581 0.000000 0.000000 0.000000 0.000000 /data5/mysql/innodb_index_stats.ibd 0.000813 0.000000 0.000000 0.000000 0.000000 0.000813 0.000000 0.000000 0.000000 0.000000 /data5/mysql/innodb_table_stats.ibd 0.000224 0.000000 0.000000 0.000000 0.000000 0.000000 0.000224 0.000000 0.000000 0.000000 /data5/test/ [root@server-mysql bin]# pt-ioprofile -p 9655 -c count Sun Jul 3 17:04:28 CST 2016 Tracing process ID 9655 total pwrite fsync filename 3 1 2 /data5/ib_logfile0 3 1 2 /data5/ibdata1 1 0 1 /data5/test/yy.ibd
[root@server-mysql bin]# pt-summary # Percona Toolkit System Summary Report ###################### Date | 2016-07-03 09:12:05 UTC (local TZ: CST +0800) Hostname | server-mysql Uptime | 2:09, 3 users, load average: 0.00, 0.02, 0.00 System | VMware, Inc.; VMware Virtual Platform; vNone (Other) Service Tag | VMware-56 4d 4c a0 7b ea 92 4a-35 a8 8a 4d cd eb 12 91 Platform | Linux Release | Red Hat Enterprise Linux Server release 6.3 (Santiago) Kernel | 2.6.32-279.el6.x86_64 Architecture | CPU = 64-bit, OS = 64-bit Threading | NPTL 2.12 Compiler | GNU CC version 4.4.6 20110731 (Red Hat 4.4.6-3). SELinux | Enforcing Virtualized | VMWare # Processor ################################################## Processors | physical = 1, cores = 2, virtual = 2, hyperthreading = no Speeds | 2x2800.000 Models | 2xPentium(R) Dual-Core CPU E5500 @ 2.80GHz Caches | 2x2048 KB # Memory ##################################################### Total | 1.3G Free | 83.1M Used | physical = 1.2G, swap allocated = 2.9G, swap used = 404.0k, virtual = 1.2G Shared | 0.0 Buffers | 200.6M Caches | 322.6M Dirty | 236 kB UsedRSS | 679.8M Swappiness | 60 DirtyPolicy | 20, 10 DirtyStatus | 0, 0 Locator Size Speed Form Factor Type Type Detail ========= ======== ================= ============= ============= =========== RAM slot #0 1024 MB Unknown DIMM DRAM EDO RAM slot #2 128 MB Unknown DIMM DRAM EDO RAM slot #4 16 MB Unknown DIMM DRAM EDO RAM slot #1 256 MB Unknown DIMM DRAM EDO RAM slot #6 4 MB Unknown DIMM DRAM EDO RAM slot #3 64 MB Unknown DIMM DRAM EDO RAM slot #5 8 MB Unknown DIMM DRAM EDO RAM slot #10 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #11 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #12 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #13 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #14 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #15 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #16 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #17 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #18 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #19 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #20 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #21 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #22 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #23 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #24 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #25 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #26 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #27 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #28 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #29 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #30 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #31 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #32 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #33 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #34 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #35 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #36 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #37 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #38 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #39 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #40 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #41 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #42 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #43 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #44 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #45 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #46 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #47 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #48 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #49 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #50 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #51 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #52 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #53 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #54 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #55 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #56 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #57 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #58 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #59 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #60 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #61 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #62 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #63 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #7 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #8 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #9 {EMPTY} Unknown DIMM DRAM Unknown # Mounted Filesystems ######################################## Filesystem Size Used Type Opts Mountpoint /dev/sda1 194M 18% ext4 rw /boot /dev/sda3 22G 85% ext4 rw / tmpfs 667M 1% tmpfs rw,rootcontext="system_u:object_r:tmpfs_t:s0" /dev/shm # Disk Schedulers And Queue Size ############################# sda | [cfq] 128 sr0 | [cfq] 128 # Disk Partioning ############################################ Device Type Start End Size ============ ==== ========== ========== ================== /dev/sda Disk 26843545600 /dev/sda1 Part 1 26 205632000 /dev/sda2 Part 26 409 3150282240 /dev/sda3 Part 409 3264 23483174400 # Kernel Inode State ######################################### dentry-state | 62390 53452 45 0 0 0 file-nr | 2560 0 132022 inode-nr | 78317 16583 # LVM Volumes ################################################ Unable to collect information # LVM Volume Groups ########################################## Unable to collect information # RAID Controller ############################################ Controller | No RAID controller detected # Network Config ############################################# Controller | Intel Corporation 82545EM Gigabit Ethernet Controller (Copper) (rev 01) FIN Timeout | 60 Port Range | 61000 # Interface Statistics ####################################### interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors ========= ========= ========== ========== ========== ========== ========== lo 350 6 0 350 6 0 eth0 60000000 50000 0 3000000 30000 0 virbr0 0 0 0 6000 70 0 virbr0-nic 0 0 0 0 0 0 # Network Devices ############################################ Device Speed Duplex ========= ========= ========= virbr0-nic 10Mb/s Full eth0 1000Mb/s Full virbr0 # Network Connections ######################################## Connections from remote IP addresses 192.168.1.152 3 Connections to local IP addresses 192.168.1.200 3 Connections to top 10 local ports 22 3 States of connections ESTABLISHED 3 LISTEN 10 # Top Processes ############################################## PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 11080 root 20 0 15024 1092 808 R 1.9 0.1 0:00.05 top 1 root 20 0 19344 1508 1244 S 0.0 0.1 0:03.62 init 2 root 20 0 0 0 0 S 0.0 0.0 0:00.04 kthreadd 3 root RT 0 0 0 0 S 0.0 0.0 0:00.78 migration/0 4 root 20 0 0 0 0 S 0.0 0.0 0:00.22 ksoftirqd/0 5 root RT 0 0 0 0 S 0.0 0.0 0:00.03 migration/0 6 root RT 0 0 0 0 S 0.0 0.0 0:00.02 watchdog/0 7 root RT 0 0 0 0 S 0.0 0.0 0:00.83 migration/1 8 root RT 0 0 0 0 S 0.0 0.0 0:00.02 migration/1 # Notable Processes ########################################## PID OOM COMMAND 2263 -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 50 35 50 60 1 1 98 1 0 1 0 0 0 60 0 1500 700 13 39 49 0 0 0 0 0 0 0 125 1250 700 12 34 54 0 0 0 0 0 0 0 0 30 60 0 0 100 0 0 0 0 0 0 0 0 50 125 0 0 100 0 0 # The End ####################################################
[root@server-mysql 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 2.0 6.0 0.0 33% 0.2 59.7 18% 0 2.0 0.0 59.7 1.0 sda3 0.0 0.0 0.0 0% 0.0 0.0 2.0 6.0 0.0 33% 0.2 59.7 18% 0 2.0 0.0 59.7
[root@server-mysql bin]# pt-mysql-summary --user=root --password=Aa@12345 -S /tmp/mysql.sock5 # Percona Toolkit MySQL Summary Report ####################### System time | 2016-07-03 09:22:28 UTC (local TZ: CST +0800) # Instances ################################################## Port Data Directory Nice OOM Socket ===== ========================== ==== === ====== 3310 /data5 0 0 /tmp/mysql.sock5 # MySQL Executable ########################################### Path to executable | /usr/local/mysql-5.6.27-linux-glibc2.5-x86_64/bin/mysqld Has symbols | Yes # Report On Port 3310 ######################################## User | root@localhost Time | 2016-07-03 17:22:28 (CST) Hostname | server-mysql Version | 5.6.27 MySQL Community Server (GPL) Built On | linux-glibc2.5 x86_64 Started | 2016-07-03 16:55 (up 0+00:27:06) Databases | 9 Datadir | /data5/ Processes | 2 connected, 1 running Replication | Is not a slave, has 0 slaves connected Pidfile | /data5/server-mysql.pid (exists) # Processlist ################################################ Command COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- Query 1 1 0 0 Sleep 1 0 400 400 User COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- root 2 1 0 0 Host COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- localhost 2 1 0 0 db COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- NULL 1 1 0 0 test 1 0 0 0 State COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- 1 0 0 0 init 1 1 0 0 # Status Counters (Wait 10 Seconds) ########################## Variable Per day Per second 11 secs Bytes_received 50000 175 Bytes_sent 1250000 15 2000 Com_change_db 50 Com_commit 50 Com_create_table 50 Com_insert_select 300 Com_select 250 1 Com_show_databases 50 Com_show_fields 100 Com_show_status 100 Com_show_tables 50 Com_show_variables 50 Connections 250 1 Created_tmp_files 300 Created_tmp_tables 250 5 Flush_commands 50 Handler_commit 500 Handler_external_lock 8000 Handler_read_first 300 Handler_read_key 150 Handler_read_rnd_next 45000 35 Handler_write 40000 35 Innodb_buffer_pool_bytes_data 350000000 4000 Innodb_buffer_pool_pages_flushed 2500 Innodb_buffer_pool_read_requests 250000 3 Innodb_buffer_pool_reads 22500 Innodb_buffer_pool_write_requests 7000 Innodb_data_fsyncs 3500 Innodb_data_read 450000000 5000 Innodb_data_reads 30000 Innodb_data_writes 4500 Innodb_data_written 80000000 1000 Innodb_dblwr_pages_written 2500 Innodb_dblwr_writes 500 Innodb_log_write_requests 1000 Innodb_log_writes 900 Innodb_os_log_fsyncs 1500 Innodb_os_log_written 900000 10 Innodb_pages_created 225 Innodb_pages_read 22500 Innodb_pages_written 2500 Innodb_rows_inserted 300 Innodb_num_open_files 500 Innodb_available_undo_logs 7000 Open_table_definitions 4000 Opened_files 7000 4 Opened_table_definitions 4000 Opened_tables 4000 Qcache_not_cached 250 1 Queries 1250 3 Questions 1250 3 Select_scan 250 Table_locks_immediate 4000 Table_open_cache_hits 450 Table_open_cache_misses 4000 Threads_created 100 Uptime 90000 1 1 # Table cache ################################################ Size | 2000 Usage | 3% # 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.6.27 Buffer Pool Size | 32.0M Buffer Pool Fill | 20% 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 | 178 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 | 2xnot started # MyISAM ##################################################### Key Cache | 8.0M Pct Used | 20% Unflushed | 0% # Security ################################################### Users | 7 users, 2 anon, 6 w/o pw, 6 old pw Old Passwords | 0 # Binary Logging ############################################# # 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 | /data5/server-mysql.err log_warnings | 1 log_slow_queries | log_queries_not_using_indexes | OFF log_slave_updates | ON # Configuration File ######################################### Config File | /etc/my.cnf [mysqld] enforce-gtid-consistency = 1 log-slave-updates = 1 innodb_buffer_pool_size = 20M character-set-server = utf8 collation-server = utf8_general_ci