pt-mysql-summary
pt-mysql-summary:官方地址
说明
用途：获取MySQL数据库概要信息，如：状态和配置
语法：
pt-mysql-summary [OPTIONS]
具体示例
[root@BigData ~]# pt-mysql-summary --user=root --password=123
# Percona Toolkit MySQL Summary Report #######################
System time | 2019-07-31 03:19:32 UTC (local TZ: CST +0800)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
3306 /var/lib/mysql 0 0 /var/lib/mysql/mysql.sock
# MySQL Executable ###########################################
Path to executable | /usr/libexec/mysqld
Has symbols | No
# Slave Hosts ################################################
No slaves found
# Report On Port 3306 ########################################
User | root@%
Time | 2019-07-31 11:19:32 (CST)
Hostname | BigData
Version | 5.5.60-MariaDB MariaDB Server
Built On | Linux x86_64
Started | 2019-07-22 14:12 (up 8+21:06:55)
Databases | 11
Datadir | /var/lib/mysql/
Processes | 60 connected, 1 running
Replication | Is not a slave, has 0 slaves connected
Pidfile | /var/run/mariadb/mariadb.pid (exists)
# Processlist ################################################
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Query 1 1 0 0
Sleep 60 0 60 1
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
monitordba 60 0 0 0
root 1 1 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
BigData 15 0 0 0
localhost 45 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
DBMonitor2.0 15 0 0 0
dbmonitor 45 0 0 0
NULL 1 1 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
60 0 0 0
NULL 1 1 0 0
# Status Counters (Wait 10 Seconds) ########################## -------通过SHOW GLOBAL STATUS命令查看的状态参数值是累计增加的参数
Variable Per day Per second 11 secs
Aborted_clients 15
Aborted_connects 6
Access_denied_errors 6
Aria_pagecache_blocks_unused 1750
Aria_pagecache_read_requests 45
Aria_pagecache_reads 8
Aria_pagecache_write_requests 10
Binlog_commits 500000 5 15
Binlog_group_commits 500000 5 15
Binlog_snapshot_position 300000 3 3000
Binlog_bytes_written 150000000 1750 3000
Binlog_cache_disk_use 15
Binlog_cache_use 450000 5 10
Binlog_stmt_cache_use 45000
Bytes_received 3500000000 40000 45000
Bytes_sent 6000000000 70000 80000
Com_admin_commands 35000
Com_change_db 8000
Com_commit 2250000 25 40
Com_delete 1250
Com_drop_table 1
Com_insert 500000 5 15
Com_rollback 175
Com_select 4000000 50 60
Com_set_option 12500000 150 175
Com_show_create_table 40
Com_show_databases 2
Com_show_fields 9
Com_show_function_status 1
Com_show_keys 2
Com_show_procedure_status 1
Com_show_processlist 3
Com_show_status 40000 1
Com_show_storage_engines 6
Com_show_table_status 10
Com_show_tables 10
Com_show_variables 15000
Com_update 9000
Connections 2250000 25 30
Created_tmp_disk_tables 25
Created_tmp_files 50
Created_tmp_tables 3000000 35 35
Empty_queries 400000 4 10
Feature_subquery 3000000 35 30
Handler_commit 4000000 45 80
Handler_delete 400000 4
Handler_icp_attempts 12500000 125 125
Handler_icp_match 12500000 125 125
Handler_prepare 1750000 20 50
Handler_read_first 25
Handler_read_key 22500000 250 250
Handler_read_next 15000000 175 125
Handler_read_rnd 1500000 20 15
Handler_read_rnd_deleted 45000
Handler_read_rnd_next 2000000000 25000 200000
Handler_tmp_update 600
Handler_tmp_write 35000000 400 800
Handler_update 60
Handler_write 500000 5 15
Innodb_adaptive_hash_cells 1250000 15
Innodb_adaptive_hash_hash_searches 5000000 60 25
Innodb_adaptive_hash_heap_buffers 50
Innodb_adaptive_hash_non_hash_searches 1750000 20 15
Innodb_background_log_sync 80000 1
Innodb_buffer_pool_bytes_data 125000000 1500
Innodb_buffer_pool_bytes_dirty 175000 2 125000
Innodb_buffer_pool_pages_flushed 1000000 10 15
Innodb_buffer_pool_pages_made_young 500
Innodb_buffer_pool_pages_old 3000
Innodb_buffer_pool_read_ahead 4000
Innodb_buffer_pool_read_requests 2000000000 25000 200000
Innodb_buffer_pool_reads 1500
Innodb_buffer_pool_write_requests 7000000 90 90
Innodb_checkpoint_age 2000 2500
Innodb_checkpoint_max_age 900000 10
Innodb_checkpoint_target_age 800000 9
Innodb_data_fsyncs 1000000 10 25
Innodb_data_read 100000000 1000
Innodb_data_reads 6000
Innodb_data_writes 2000000 25 40
Innodb_data_written 35000000000 400000 500000
Innodb_dblwr_pages_written 1000000 10 15
Innodb_dblwr_writes 60000
Innodb_descriptors_memory 900
Innodb_dict_tables 25
Innodb_history_list_length 2
Innodb_ibuf_free_list 1
Innodb_ibuf_merged_delete_marks 22500
Innodb_ibuf_merged_inserts 15
Innodb_ibuf_merges 400
Innodb_ibuf_segment_size 1
Innodb_log_write_requests 500000 5 9
Innodb_log_writes 900000 10 25
Innodb_lsn_current 4000000000 45000 5000
Innodb_lsn_flushed 4000000000 45000 5000
Innodb_lsn_last_checkpoint 4000000000 45000 2250
Innodb_master_thread_1_second_loops 90000
Innodb_master_thread_10_second_loops 9000
Innodb_master_thread_background_loops 5
Innodb_master_thread_main_flush_loops 5
Innodb_master_thread_sleeps 90000
Innodb_max_trx_id 35000000 400 30
Innodb_mem_adaptive_hash 10000000 125
Innodb_mem_dictionary 2500000 30
Innodb_mem_total 600000000 7000
Innodb_mutex_os_waits 12500
Innodb_mutex_spin_rounds 10000000 125 400
Innodb_mutex_spin_waits 20000000 225 900
Innodb_oldest_view_low_limit_trx_id 35000000 400
Innodb_os_log_fsyncs 900000 10 25
Innodb_os_log_written 700000000 8000 17500
Innodb_pages_created 1750
Innodb_pages_read 6000
Innodb_pages_written 1000000 10 15
Innodb_purge_trx_id 35000000 400
Innodb_read_views_memory 450
Innodb_rows_deleted 350000 4
Innodb_rows_inserted 450000 5 10
Innodb_rows_read 2000000000 25000 200000
Innodb_rows_updated 3
Innodb_s_lock_os_waits 9000
Innodb_s_lock_spin_rounds 300000 3 2
Innodb_s_lock_spin_waits 15000
Innodb_x_lock_os_waits 5000
Innodb_x_lock_spin_rounds 225000 2
Innodb_x_lock_spin_waits 6000
Key_blocks_warm 1
Key_read_requests 15000000 175 150
Key_reads 15000
Key_write_requests 250000 2 1
Key_writes 125000 1 1
Open_table_definitions 30
Opened_files 600 1
Opened_table_definitions 30
Opened_tables 125
Queries 22500000 250 350
Questions 22500000 250 350
Rows_read 2000000000 25000 200000
Rows_sent 30000000 350 700
Rows_tmp_read 22500000 250 600
Select_full_join 50000
Select_range 1250
Select_scan 1250000 15 25
Slow_queries 400
Sort_merge_passes 20
Sort_range 175000 1 1
Sort_rows 3000000 35 20
Sort_scan 500000 5 15
Subquery_cache_hit 45000
Subquery_cache_miss 10000000 125 125
Syncs 10
Table_locks_immediate 6000000 60 70
Table_locks_waited 400
Threads_created 15
Uptime 90000 1 1
# Table cache ################################################
Size | 400
Usage | 100%
# Key Percona Server features ################################
Table & Index Stats | Disabled
Multiple I/O Threads | Enabled
Corruption Resilient | Enabled
Durable Replication | Disabled
Import InnoDB Tables | Disabled
Fast Server Restarts | Disabled
Enhanced Logging | Disabled
Replica Perf Logging | Not Supported
Response Time Hist. | Not Supported
Smooth Flushing | Enabled
HandlerSocket NoSQL | Not Supported
Fast Hash UDFs | Unknown
# Percona XtraDB Cluster #####################################
# Plugins ####################################################
InnoDB compression | ACTIVE
# Query cache ################################################
query_cache_type | ON
Size | 0.0
Usage | 0%
HitToInsertRatio | 0%
# 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 | No
Prepared statement count | 0
# InnoDB #####################################################
Version | 5.5.59-MariaDB-38.11
Buffer Pool Size | 5.0G
Buffer Pool Fill | 20%
Buffer Pool Dirty | 0%
File Per Table | OFF
Page Size | 16k
Log File Size | 2 * 5.0M = 10.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 | 500
Commit Concurrency | 0
Txn Isolation Level | REPEATABLE-READ
Adaptive Flushing | ON
Adaptive Checkpoint |
Checkpoint Age | 18k
InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
Oldest Transaction | 0 Seconds
History List Len | 18
Read Views | 1
Undo Log Entries | 1 transactions, 1 total undo, 1 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 (1 log, 0 chkp); 0 pwrites
Pending I/O Flushes | 0 buf pool, 1 log
Transaction States | 1xACTIVE (PREPARED), 15xnot started
# MyISAM #####################################################
Key Cache | 128.0M
Pct Used | 25%
Unflushed | 0%
# Security ###################################################
Users | 3 users, 0 anon, 1 w/o pw, 1 old pw
Old Passwords | OFF
# Encryption #################################################
No keyring plugins found
# Binary Logging #############################################
Binlogs | 13
Zero-Sized | 0
Total Size | 1.2G
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 | InnoDB
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 | 20M
thread_stack | 288k
log | OFF
log_error | /var/log/mariadb/mariadb.log
log_warnings | 1
log_slow_queries | ON
log_queries_not_using_indexes | OFF
log_slave_updates | OFF
# Configuration File #########################################
Config File | /etc/my.cnf
[mysql]
no-auto-rehash
socket = /var/lib/mysql/mysql.sock
prompt = "\\u@\\h \\R:\\m:\\s >"
[mysqld]
default-storage-engine = INNODB
character_set_server = utf8mb4
port = 3306
user = mysql
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
max_allowed_packet = 20M
interactive_timeout = 2592000
wait_timeout = 2592000
open_files_limit = 65535
wait_timeout = 1000
lock_wait_timeout = 3600
interactive_timeout = 1000
connect_timeout = 20
max-user-connections = 1000
max_connect_errors = 1000
max_connections = 1500
thread_cache_size = 250
slow_query_log = on
long_query_time = 2
slow_query_log_file = /var/lib/mysql/mysql.slow
innodb_buffer_pool_size = 5368709120
innodb_buffer_pool_instances = 5
performance_schema = ON
server-id = 1
log_bin = /var/lib/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog-format = STATEMENT
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld_safe]
log-error = /var/log/mariadb/mariadb.log
pid-file = /var/run/mariadb/mariadb.pid
# Memory management library ##################################
jemalloc is not enabled in mysql config for process with id 7577
# The End ####################################################
