percona之資料庫資訊統計工具介紹
1. pt-summary
功能介紹：統計伺服器系統的執行狀態及配置，包含：CPU、記憶體、硬碟、網路卡等資訊，還包括檔案系統、磁碟排程和佇列大小、LVM、RAID、網路連結資訊、netstat 的統計，以及前10的負載佔用資訊和vmstat資訊。
使用說明shell> pt-summary [OPTIONS] 詳情可參考 shell> pt-summary --help使用示例
shell> pt-summary
# Percona Toolkit System Summary Report ######################
Date | 2015-03-27 02:17:24 UTC (local TZ: CST +0800)
Hostname | localhost.localdomain
Uptime | 581 days, 18:32, 2 users, load average: 0.38, 0.26, 0.24
Platform | Linux
Release | CentOS release 5.9 (Final)
Kernel | 2.6.18-348.el5
Architecture | CPU = 64-bit, OS = 64-bit
Threading | NPTL 2.5
Compiler | GNU CC version 4.1.2 20080704 (Red Hat 4.1.2-52).
SELinux | Disabled
Virtualized | No virtualization detected
# Processor ##################################################
Processors | physical = 2, cores = 12, virtual = 24, hyperthreading = yes
Speeds | 24x2500.091
Models | 24xIntel(R) Xeon(R) CPU E5-2640 0 @ 2.50GHz
Caches | 24x15360 KB
# Memory #####################################################
Total | 31.3G
Free | 12.8G
Used | physical = 18.5G, swap allocated = 39.1G, swap used = 292.0k, virtual = 18.5G
Buffers | 282.1M
Caches | 10.9G
Dirty | 308 kB
UsedRSS | 6.8G
Swappiness | 2
DirtyPolicy | 40, 10
DirtyStatus | 0, 0
Locator Size Speed Form Factor Type Type Detail
========= ======== ================= ============= ============= ===========
# Mounted Filesystems ########################################
Filesystem Size Used Type Opts Mountpoint
/dev/sda1 99M 13% ext3 rw /boot
/dev/sda2 154G 1% ext3 rw /
/dev/sda3 757G 36% ext3 rw /app
/dev/sda5 114G 6% ext3 rw /backup
/dev/sda7 9.5G 11% ext3 rw /usr/local
/dev/sda8 9.5G 26% ext3 rw /usr
tmpfs 16G 0% tmpfs rw /dev/shm
# Disk Schedulers And Queue Size #############################
md0 | UNREADABLE
sda | [cfq] 128
sr0 | [cfq] 128
# Disk Partioning ############################################
# Kernel Inode State #########################################
dentry-state | 26789 22019 45 0 0 0
file-nr | 5610 0 655350
inode-nr | 25307 300
# LVM Volumes ################################################
Unable to collect information
# LVM Volume Groups ##########################################
Unable to collect information
# RAID Controller ############################################
Controller | LSI Logic MegaRAID SAS
RAID controller software not found; try getting it from
your package repository or the manufacturer's website
# Network Config #############################################
Controller | Intel Corporation I350 Gigabit Network Connection (rev 01)
Controller | Intel Corporation I350 Gigabit Network Connection (rev 01)
Controller | Intel Corporation I350 Gigabit Network Connection (rev 01)
Controller | Intel Corporation I350 Gigabit Network Connection (rev 01)
FIN Timeout | 3
Port Range | 65535
# Interface Statistics #######################################
interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors
========= ========= ========== ========== ========== ========== ==========
lo 125000000 600000 0 125000000 600000 0
usb0 0 0 0 0 0 0
eth0 35000000 700000000 0 2000000000 300000000 0
eth1 0 0 0 0 0 0
eth2 0 0 0 0 0 0
eth3 0 0 0 0 0 0
sit0 0 0 0 0 0 0
# Network Connections ########################################
Connections from remote IP addresses
192.168.1.175 40
192.168.1.217 70
192.168.1.53 1
192.168.1.216 500
192.168.1.217 60
192.168.1.218 450
Connections to local IP addresses
192.168.1.52 1500
Connections to top 10 local ports
22 2
3306 1500
States of connections
ESTABLISHED 1500
LISTEN 9
# Top Processes ##############################################
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
32115 mysql 15 0 8135m 6.7g 7412 S 3.9 21.5 181342:13 mysqld
11589 svoid 16 0 12892 1180 740 R 2.0 0.0 0:00.01 top
1 root 15 0 10372 696 588 S 0.0 0.0 0:09.51 init
2 root RT -5 0 0 0 S 0.0 0.0 4:41.76 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.07 ksoftirqd/0
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
5 root RT -5 0 0 0 S 0.0 0.0 0:17.48 migration/1
6 root 34 19 0 0 0 S 0.0 0.0 0:00.02 ksoftirqd/1
7 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/1
# Notable Processes ##########################################
PID OOM COMMAND
28716 -17 sshd
# The End ####################################################
2. pt-mysql-summary
功能介紹：統計MySQL資料庫的執行狀態及配置，包含：版本資訊、資料目錄、命令的統計、使用者，資料庫以及複製等資訊還包括各個變數（status、variables）資訊和各個變數的比例資訊，還有配置檔案等資訊。
使用說明shell> pt-mysql-summary [OPTIONS] 詳情可參考 shell> pt-mysql-summary --help使用示例
shell> pt-mysql-summary --host=127.0.0.1 --port=3306 --user=root --password=mypass
# Percona Toolkit MySQL Summary Report #######################
System time | 2015-03-27 02:57:10 UTC (local TZ: CST +0800)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
3306 /app/mysql/data 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 | 2015-03-27 10:57:10 (CST)
Hostname | localhost.localdomain
Version | 5.5.33-log MySQL Community Server (GPL)
Built On | linux2.6 x86_64
Started | 2014-07-14 08:04 (up 256+02:52:54)
Databases | 8
Datadir | /app/mysql/data/
Processes | 1500 connected, 3 running
Replication | Is not a slave, has 1 slaves connected
Pidfile | /app/mysql/data/localhost.localdomain.pid (exists)
# Processlist ################################################
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Binlog Dump 1 1 22500000 22500000
Daemon 1 1 10000 10000
Query 1 1 0 0
Sleep 1500 0 70000 4500
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
event_scheduler 1 1 10000 10000
root 900 1 0 0
slave 1 1 22500000 22500000
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
192.168.1.217 70 0 0 0
192.168.1.216 500 0 0 0
192.168.1.217 60 0 0 0
192.168.1.218 450 0 0 0
192.168.1.53 1 1 22500000 22500000
localhost 2 2 10000 10000
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
test 800 0 0 0
NULL 4 3 22500000 22500000
db_test 800 0 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
1500 0 0 0
Master has sent all binlog to 1 1 22500000 22500000
NULL 1 1 0 0
Waiting for next activation 1 1 10000 10000
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 10 secs
Aborted_clients 1500
Aborted_connects 175
Binlog_cache_use 125000 1
Binlog_stmt_cache_disk_use 175
Binlog_stmt_cache_use 125000 1
Bytes_received 500000000 6000 12500
Bytes_sent 35000000000 400000 50000
Com_admin_commands 1750
Com_alter_table 9
Com_begin 15
Com_call_procedure 15
Com_change_db 600
Com_commit 2500
Com_create_table 70
Com_delete 1250
Com_drop_table 70
Com_flush 1
Com_help 80
Com_insert 80000
Com_insert_select 60000000 700
Com_kill 1
Com_lock_tables 5
Com_replace 6
Com_replace_select 8
Com_rollback 3
Com_select 1250000 15 20
Com_set_option 15000
Com_show_collations 1750
Com_show_create_func 5
Com_show_create_proc 125
Com_show_create_table 2000
Com_show_create_trigger 6
Com_show_databases 40
Com_show_fields 1250
Com_show_function_status 5
Com_show_keys 70
Com_show_master_status 15
Com_show_procedure_status 5
Com_show_profiles 30
Com_show_status 300
Com_show_storage_engines 1
Com_show_table_status 600
Com_show_tables 1000000 15 9
Com_show_triggers 600
Com_show_variables 1750
Com_show_warnings 40
Com_stmt_close 200
Com_stmt_execute 200
Com_stmt_prepare 200
Com_truncate 1
Com_unlock_tables 6
Com_update 175000 1
Com_update_multi 35
Connections 2250 1
Created_tmp_disk_tables 5000 2
Created_tmp_files 9
Created_tmp_tables 175000000 2000 20
Flush_commands 1
Handler_commit 60000000 700 20
Handler_delete 22500
Handler_prepare 225000 2 1
Handler_read_first 175000000 2250 4
Handler_read_key 1250000000 15000 150000
Handler_read_last 7000
Handler_read_next 1500000000 17500 175000
Handler_read_prev 125000000 1500
Handler_read_rnd 15000000 175 800
Handler_read_rnd_next 17500000000 200000 400000
Handler_rollback 125
Handler_update 450000000 5000 60000
Handler_write 60000000 700 2500
Innodb_buffer_pool_bytes_data 17500000 200
Innodb_buffer_pool_bytes_dirty 250 50000
Innodb_buffer_pool_pages_flushed 225000 2 3
Innodb_buffer_pool_read_ahead 70000
Innodb_buffer_pool_read_ahead_evicted 2250
Innodb_buffer_pool_read_requests 4500000000 50000 500000
Innodb_buffer_pool_reads 70000
Innodb_buffer_pool_write_requests 1750000 20 20
Innodb_data_fsyncs 40000 1
Innodb_data_read 2250000000 25000
Innodb_data_reads 150000 1
Innodb_data_writes 500000 5 5
Innodb_data_written 7000000000 90000 100000
Innodb_dblwr_pages_written 225000 2 3
Innodb_dblwr_writes 7000
Innodb_log_write_requests 350000 3
Innodb_log_writes 250000 2 1
Innodb_os_log_fsyncs 25000
Innodb_os_log_written 250000000 3000 1500
Innodb_pages_created 3000
Innodb_pages_read 150000 1
Innodb_pages_written 225000 2 3
Innodb_row_lock_time 90000
Innodb_row_lock_waits 80
Innodb_rows_deleted 20000
Innodb_rows_inserted 125000 1
Innodb_rows_read 12500000000 150000 400000
Innodb_rows_updated 150000 1
Key_read_requests 2500000000 30000 100000
Key_reads 800000 9 100
Key_write_requests 900000 10 1
Key_writes 600000 6
Open_table_definitions 2
Opened_files 30000 20
Opened_table_definitions 700
Opened_tables 2000
Qcache_hits 250000 3 3
Qcache_inserts 700000 8 20
Qcache_lowmem_prunes 6000
Qcache_not_cached 500000 5 2
Queries 250000000 3000 35
Questions 3000000 35 35
Select_full_join 125000000 1250 2
Select_full_range_join 200
Select_range 15000
Select_scan 7000000 80 15
Slow_queries 450
Sort_merge_passes 1
Sort_range 175000 2 9
Sort_rows 15000000 200 800
Sort_scan 250000 2 3
Table_locks_immediate 400000000 5000 45
Table_locks_waited 1250
Threads_created 1000
Uptime 90000 1 1
# Table cache ################################################
Size | 4096
Usage | 35%
# 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 | ON
Size | 128.0M
Usage | 30%
HitToInsertRatio | 35%
# Schema #####################################################
Specify --databases or --all-databases to dump and summarize schemas
# Noteworthy Technologies ####################################
SSL | No
Explicit LOCK TABLES | Yes
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | Yes
Prepared statement count | 0
# InnoDB #####################################################
Version | 5.5.33
Buffer Pool Size | 4.0G
Buffer Pool Fill | 100%
Buffer Pool Dirty | 0%
File Per Table | OFF
Page Size | 16k
Log File Size | 3 * 512.0M = 1.5G
Log Buffer Size | 16M
Flush Method |
Flush Log At Commit | 2
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 | 820
InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
Oldest Transaction | 0 Seconds
History List Len | 1511
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 | 180xnot started
# MyISAM #####################################################
Key Cache | 16.0M
Pct Used | 100%
Unflushed | 0%
# Security ###################################################
Users | 14 users, 2 anon, 5 w/o pw, 5 old pw
Old Passwords | OFF
# Binary Logging #############################################
Binlogs | 86
Zero-Sized | 0
Total Size | 82.4G
binlog_format | MIXED
expire_logs_days | 0
sync_binlog | 0
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 | 16M
sort_buffer_size | 16M
read_buffer_size | 256k
read_rnd_buffer_size | 512k
bulk_insert_buffer | 0.00
max_heap_table_size | 128M
tmp_table_size | 128M
max_allowed_packet | 32M
thread_stack | 512k
log | OFF
log_error | /app/mysql/data/localhost.localdomain.err
log_warnings | 1
log_slow_queries | ON
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
back_log = 300
max_connections = 3000
max_connect_errors = 30
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 32M
table_open_cache = 4096
binlog_cache_size = 4M
max_heap_table_size = 128M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 16
thread_concurrency = 8
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
thread_stack = 512K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 128M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
character_set_server = utf8
log-bin = mysql-bin
binlog_format = mixed
server-id = 1
innodb_data_home_dir = /app/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /app/mysql/data
innodb_buffer_pool_size = 8G
innodb_additional_mem_pool_size = 64M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 120
default-storage-engine = innodb
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 1024M
sort_buffer_size = 512M
read_buffer = 32M
write_buffer = 32M
[mysqlhotcopy]
interactive-timeout
# The End ####################################################
3. pt-config-diff
功能介紹：對比mysql配置檔案和伺服器引數，配置源可以指定為檔案或DSN，需提供至少兩個配置源，類似Unix中的diff，如果配置完全一樣就不會輸出任何東西。
用法介紹shell> pt-config-diff [OPTIONS] CONFIG CONFIG [CONFIG...] 詳情可參考 shell> pt-config-diff --help使用示例
shell> pt-config-diff h=localhost,P=3306 h=192.168.90.129,P=3307 --user=root --password=mypass
4. pt-variable-advisor
功能介紹：分析mysql的引數變數，並對可能存在的問題提出建議
用法介紹shell> pt-variable-advisor [OPTIONS] [DSN] 詳情可參考 shell> pt-variable-advisor --help使用示例
shell> pt-variable-advisor --user=svoid --password=123qwe localhost # WARN delay_key_write: MyISAM index blocks are never flushed until necessary. # WARN innodb_additional_mem_pool_size: This variable generally doesn't need to be larger than 20MB. # WARN innodb_flush_log_at_trx_commit-1: InnoDB is not configured in strictly ACID mode. # WARN innodb_lock_wait_timeout: This option has an unusually long value, which can cause system overload if locks are not being released. # NOTE log_warnings-2: Log_warnings must be set greater than 1 to log unusual events such as aborted connections. # WARN max_connections: If the server ever really has more than a thousand threads running, then the system is likely to spend more time scheduling threads than really doing useful work. # NOTE read_buffer_size-1: The read_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it. # NOTE read_rnd_buffer_size-1: The read_rnd_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it. # 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 sort_buffer_size-2: 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 myisam_recover_options: myisam_recover_options should be set to some value such as BACKUP,FORCE to ensure that table corruption is noticed. # WARN sync_binlog: Binary logging is enabled, but sync_binlog isn't configured so that every transaction is flushed to the binary log for durability.
整理自網路
Svoid
2015-03-27
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29733787/viewspace-1478678/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- percona之資料庫開發工具介紹資料庫
- percona之複製工具介紹
- percona之tcprstat 介紹TCP
- percona 實用工具之pt-kill使用介紹
- Oracle 統計資訊介紹Oracle
- 常用Oracle資料庫調優工具介紹Oracle資料庫
- 資料庫介紹資料庫
- Oracle資料庫審計功能介紹Oracle資料庫
- percona-toolkit效能類工具的使用介紹
- SequoiaDB資料庫之環境介紹資料庫
- MySQL資料庫備份工具Mydumper使用介紹MySql資料庫
- 資料庫的智慧之源-統計資訊之自定義多維統計資料庫
- 資料庫介紹--初識資料庫資料庫
- 6 收集資料庫統計資訊資料庫
- IndexedDB資料庫介紹Index資料庫
- 2 Day DBA-介紹-管理資料庫的工具資料庫
- L10資料庫——資料庫介紹資料庫
- Percona MySQL 5.6 HINT介紹MySql
- MySQL資料庫鎖介紹MySql資料庫
- postgresql資料庫鎖介紹SQL資料庫
- oracle統計資訊包--dbms_stats介紹Oracle
- 大資料常用分析工具介紹大資料
- 資料倉儲—資料庫—Oracle 介紹資料庫Oracle
- 達夢資料庫dexp邏輯匯出工具使用介紹資料庫
- 達夢資料庫統計資訊詳解資料庫
- 資料庫安全知識介紹資料庫
- QuestDB時序資料庫介紹資料庫
- 資料字典生成工具及文件工具作用介紹
- 收集資料庫統計資訊需要收集直方圖資訊.資料庫直方圖
- Linux系統上資料庫管理和開發工具DBeaver使用安裝介紹Linux資料庫
- 資料介面測試工具 Postman 介紹Postman
- MongoDB資料比較工具dbHash介紹MongoDB
- 資料倉儲—資料庫—SQL Server 介紹資料庫SQLServer
- percona-toolkit安裝方法和主要工具用途彙總介紹
- 資料庫實時轉移之Confluent介紹（一）資料庫
- 資料庫效能調優之始： analyze統計資訊資料庫
- 資料庫遷移後的統計資訊更新資料庫
- 資料庫物件事件與屬性統計 | performance_schema全方位介紹資料庫物件事件ORM