重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: Percona-toolkit 之 pt-mysql-summary 用于巡检MYSQL [打印本页]

作者: 郑全 时间: 5 天前
标题: Percona-toolkit 之 pt-mysql-summary 用于巡检MYSQL

[root@mysql-node1 ~]# pt-mysql-summary --host 127.0.0.1 -port=3306 --user root --ask-pass

Enter MySQL password: mysql: [Warning] Using a password on the command line interface can be insecure.
# Percona Toolkit MySQL Summary Report #######################
            System time | 2026-04-25 10:05:35 UTC (local TZ: CST +0800)
# Instances ##################################################
  Port  Data Directory          Nice OOM Socket
  ===== ========================== ==== === ======
3306 /data/mysql/data          0 0 /data/mysql/tmp/mysqld.sock
# MySQL Executable ###########################################
   Path to executable | /usr/local/mysql/bin/mysqld
            Has symbols | Yes
# Slave Hosts ################################################
No slaves found
# Report On Port 3306 ########################################
                  User | root@127.0.0.1
                  Time | 2026-04-25 10:05:35 (+00:00)
               Hostname | mysql-node1
               Version | 8.4.8 Source distribution
               Built On | Linux x86_64
               Started | 2026-04-25 09:31 (up 0+00:33:50)
            Databases | 8
               Datadir | /data/mysql/data/
            Processes | 5 connected, 4 running
            Replication | Is not a slave, has 0 slaves connected
               Pidfile | mysql.pid (does not exist)
# Processlist ################################################

  Command                      COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  Connect                            1    1    2000    2000
  Daemon                               1    1    2000    2000
  Group Replication Data Stream       2    2    2000    2000
  Query                               6    6       0       0
  Sleep                               2    0       0       0

  User                         COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  event_scheduler                      1    1    2000    2000
  mysql_innodb_cluster_2             1    1       70       70
  mysql_innodb_cluster_3             1    1    2000    2000
  root                               1    1       0       0
  system user                         6    6    2000    2000
  zabbix                               2    0       0       0

  Host                         COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  10.60.73.186                         1    1       70       70
  10.60.73.204                         1    1    2000    2000
  127.0.0.1                            3    1       0       0
                                    6    6    2000    2000
  localhost                            1    1    2000    2000

  db                            COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  NULL                               10    10    6000    2000

  State                         COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
                                    2    0       0       0
  Connection delegated to Group       2    2    2000    2000
  init                               1    1       0       0
  Replica has read all relay log       1    1       0       0
  Waiting for an event from Coor       3    3       0       0
  waiting for handler commit          2    2    2000    2000
  Waiting on empty queue             1    1    2000    2000

# Status Counters (Wait 10 Seconds) ##########################
Variable                               Per day  Per second    11 secs
Aborted_clients                            250
Aborted_connects                         30000
Binlog_cache_use                      30000000       350       100
Bytes_received                      225000000       2500       2000
Bytes_sent                         2500000000    30000    15000
Com_admin_commands                      25000
Com_alter_table                            125
Com_begin                            30000000       350       100
Com_change_db                               45
Com_commit                            30000000       350       100
Com_create_db                               45
Com_create_table                         1750
Com_delete                            700000          8
Com_flush                                  45
Com_insert                            40000000       450       80
Com_select                            250000          3          5
Com_set_option                         80000
Com_show_binlogs                         1500
Com_show_databases                         90
Com_show_engine_status                   9000
Com_show_grants                         9000
Com_show_plugins                         1500
Com_show_processlist                      90
Com_show_replica_status                   9000
Com_show_status                         20000
Com_show_variables                      6000
Com_stmt_execute                         7000
Com_stmt_close                            7000
Com_stmt_prepare                         7000
Com_update                            12500000       125       100
Connections                            125000          1          2
Created_tmp_disk_tables                10000
Created_tmp_files                         1250
Created_tmp_tables                      80000                      1
Deprecated_use_i_s_processlist_count       9000
Deprecated_use_i_s_processlist_last_timestamp 80000000000000000 900000000000    900000
Error_log_buffered_bytes             40000000       450          9
Error_log_buffered_events             250000          2
Error_log_latest_write             80000000000000000 900000000000    900000
Flush_commands                            125
Gr_all_consensus_proposals_count       90000                      1
Gr_all_consensus_time_sum             40000000       450       450
Gr_certification_garbage_collector_count       1500
Gr_certification_garbage_collector_time_sum 45000000       500       500
Gr_consensus_bytes_received_sum    60000000000    700000    300000
Gr_consensus_bytes_sent_sum       17500000000    200000    70000
Gr_control_messages_sent_bytes_sum    20000000       225       225
Gr_control_messages_sent_count          90000          1          1
Gr_control_messages_sent_roundtrip_time_sum  1250000000    15000       600
Gr_empty_consensus_proposals_count    30000000       350       100
Gr_extended_consensus_count             1000
Gr_total_messages_sent_count             90000          1          1
Handler_commit                      175000000       2000       600
Handler_delete                         800000          9          9
Handler_external_lock                100000000       1250       400
Handler_prepare                      175000000       2000       600
Handler_read_first                      60000                      2
Handler_read_key                      90000000       1000       450
Handler_read_next                      1500000       15          4
Handler_read_rnd                      22500000       250       175
Handler_read_rnd_next                175000000       2000       2500
Handler_rollback                            45
Handler_update                      22500000       250       175
Handler_write                         60000000       700       350
Innodb_buffer_pool_bytes_data    225000000000    2500000    450000
Innodb_buffer_pool_bytes_dirty    6000000000    70000    -373853
Innodb_buffer_pool_pages_flushed    35000000       400       250
Innodb_buffer_pool_read_ahead          3000000       35
Innodb_buffer_pool_read_requests    1500000000    17500    12500
Innodb_buffer_pool_reads             10000000       125       25
Innodb_buffer_pool_write_requests    600000000       7000       4500
Innodb_data_fsyncs                   70000000       800       300
Innodb_data_read                   200000000000    2500000    400000
Innodb_data_reads                   15000000       175       25
Innodb_data_writes                   250000000       3000       700
Innodb_data_written                700000000000    8000000    4500000
Innodb_dblwr_pages_written          35000000       400       250
Innodb_dblwr_writes                   450000          5          3
Innodb_redo_log_uuid             80000000000    900000
Innodb_redo_log_checkpoint_lsn    4500000000000 50000000    225000
Innodb_redo_log_current_lsn       4500000000000 50000000    300000
Innodb_redo_log_flushed_to_disk_lsn 4500000000000 50000000    300000
Innodb_redo_log_logical_size       600000000       7000    80000
Innodb_redo_log_physical_size       1000000000    12500    300000
Innodb_redo_log_capacity_resized    4500000000    50000
Innodb_log_write_requests          500000000       6000       4000
Innodb_log_writes                   200000000       2500       450
Innodb_os_log_fsyncs                60000000       700       300
Innodb_os_log_written             125000000000    1500000    450000
Innodb_pages_created                   1750000       20          5
Innodb_pages_read                   12500000       150       25
Innodb_pages_written                35000000       400       250
Innodb_rows_deleted                   800000          8          9
Innodb_rows_inserted                40000000       450       125
Innodb_rows_read                      25000000       300       200
Innodb_rows_updated                   22500000       250       175
Innodb_system_rows_deleted             700000          7          9
Innodb_system_rows_inserted             700000          7          9
Innodb_system_rows_read             175000000       2000       2250
Innodb_system_rows_updated             25000
Innodb_num_open_files                   150000          1
Innodb_undo_tablespaces_total             90
Innodb_undo_tablespaces_implicit          90
Innodb_undo_tablespaces_active             90
Mysqlx_notified_by_group_replication       450
Mysqlx_port                            1500000       15
Mysqlx_ssl_ctx_verify_mode                225
Mysqlx_worker_threads                      90
Open_table_definitions                   9000
Opened_files                               400
Opened_table_definitions                12500
Opened_tables                         125000          1       10
Performance_schema_session_connect_attrs_longest_seen       8000
Queries                               80000000       1000       300
Questions                               600000          6       15
Select_full_join                         15000
Select_range                            4500
Select_scan                            200000          2          3
Sort_rows                               80000                      1
Sort_scan                               17500
Ssl_accepts                            30000                      1
Ssl_finished_accepts                   30000                      1
Ssl_session_cache_hits                   600
Ssl_session_cache_timeout                12500
Table_locks_immediate                   200000          2          2
Table_open_cache_hits                50000000       600       200
Table_open_cache_misses                125000          1       10
Table_open_cache_overflows             45000                      9
Threads_created                            450
Uptime                                  90000          1          1
# Table cache ################################################
                  Size | 2048
                  Usage | 80%
# 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
# Schema #####################################################
Specify --databases or --all-databases to dump and summarize schemas
# Noteworthy Technologies ####################################
                  SSL | Yes
   Explicit LOCK TABLES | No
         Delayed Insert | No
      XA Transactions | No
            NDB Cluster | No
   Prepared Statements | Yes
Prepared statement count | 0
# InnoDB #####################################################
               Version | 8.4.8
      Buffer Pool Size | 8.0G
      Buffer Pool Fill | 60%
      Buffer Pool Dirty | 1%
         File Per Table | ON
            Page Size | 16k
         Log File Size | 2 * 48.0M = 96.0M
      Log Buffer Size | 32M
         Flush Method | O_DIRECT
   Flush Log At Commit | 1
            XA Support |
            Checksums |
            Doublewrite | ON
      R/W I/O Threads | 8 8
         I/O Capacity | 4000
   Thread Concurrency | 0
   Concurrency Tickets | 5000
   Commit Concurrency | 0
   Txn Isolation Level |
      Adaptive Flushing | ON
   Adaptive Checkpoint |
         Checkpoint Age | 13M
         InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
   Oldest Transaction | 0 Seconds
      History List Len | 213
            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 | 11xnot started
# MyISAM #####################################################
            Key Cache | 32.0M
               Pct Used | 20%
            Unflushed | 0%
# Security ###################################################
                  Users | 16 users, 0 anon, 0 w/o pw, 16 old pw
         Old Passwords |
# Encryption #################################################
No keyring plugins found
# Binary Logging #############################################
# Noteworthy Variables #######################################
   Auto-Inc Incr/Offset | 1/2
default_storage_engine | InnoDB
            flush_time | 0
         init_connect |
            init_file |
               sql_mode | NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
      join_buffer_size | 2M
      sort_buffer_size | 2M
      read_buffer_size | 8M
   read_rnd_buffer_size | 4M
   bulk_insert_buffer | 0.00
   max_heap_table_size | 32M
         tmp_table_size | 32M
   max_allowed_packet | 32M
         thread_stack | 512k
                  log |
            log_error | /data/mysql/logs/error.log
         log_warnings |
      log_slow_queries |
log_queries_not_using_indexes | OFF
      log_slave_updates | ON
# Configuration File #########################################
            Config File | /etc/my.cnf

[client]
port                               = 3306
socket                            = /data/mysql/tmp/mysqld.sock

[mysql]
prompt                            = "\u@\h \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
user                               = mysql
port                               = 3306
admin_address                      = 127.0.0.1
basedir                            = /usr/local/mysql
datadir                            = /data/mysql/data
socket                            = /data/mysql/tmp/mysqld.sock
pid-file                         = mysql.pid
character-set-server             = utf8mb4
skip_name_resolve                = 1
plugin_dir                         = /usr/local/mysql/lib/plugin
open_files_limit                   = 65535
back_log                         = 1024
max_connections                   = 2000
max_connect_errors                = 1000000
table_open_cache                   = 2048
table_definition_cache             = 2048
table_open_cache_instances       = 64
thread_stack                      = 512K
external-locking                   = FALSE
max_allowed_packet                = 32M
sort_buffer_size                   = 2M
join_buffer_size                   = 2M
thread_cache_size                = 1536
interactive_timeout                = 600
wait_timeout                      = 600
tmp_table_size                   = 32M
max_heap_table_size                = 32M
slow_query_log                   = 1
log_timestamps                   = SYSTEM
slow_query_log_file                = /data/mysql/logs/slow.log
log-error                         = /data/mysql/logs/error.log
long_query_time                   = 2
log_queries_not_using_indexes    = 0
log_throttle_queries_not_using_indexes = 0
min_examined_row_limit             = 100
log_slow_admin_statements          = 1
log_slow_extra                   = on
server-id                         = 1
log-bin                            = /data/mysql/binlogs/mysql-bin
sync_binlog                      = 1
binlog_cache_size                = 4M
max_binlog_cache_size             = 2G
max_binlog_size                   = 1G
binlog_expire_logs_seconds       = 2592000
gtid_mode                         = on
enforce_gtid_consistency          = 1
binlog_checksum                   = NONE
binlog_row_image                   = FULL
relay_log_recovery                = 1
relay-log-purge                   = 1
key_buffer_size                   = 32M
read_buffer_size                   = 8M
read_rnd_buffer_size             = 4M
bulk_insert_buffer_size          = 64M
myisam_sort_buffer_size          = 128M
myisam_max_sort_file_size          = 10G
lock_wait_timeout                = 3600
explicit_defaults_for_timestamp    = 1
innodb_thread_concurrency          = 0
innodb_sync_spin_loops             = 100
innodb_spin_wait_delay             = 30
transaction_isolation             = READ-COMMITTED
innodb_buffer_pool_size          = 8G
innodb_buffer_pool_instances       = 4
innodb_buffer_pool_load_at_startup  = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path             = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit    = 1
innodb_log_buffer_size             = 32M
innodb_max_undo_log_size          = 4G
innodb_undo_directory             = /data/mysql/undolog
innodb_io_capacity                = 4000
innodb_io_capacity_max             = 8000
innodb_flush_sync                = 0
innodb_flush_neighbors             = 0
innodb_write_io_threads          = 8
innodb_read_io_threads             = 8
innodb_purge_threads             = 4
innodb_page_cleaners             = 4
innodb_open_files                = 65535
innodb_max_dirty_pages_pct       = 50
innodb_flush_method                = O_DIRECT
innodb_lru_scan_depth             = 4000
innodb_checksum_algorithm          = crc32
innodb_lock_wait_timeout          = 10
innodb_rollback_on_timeout       = 1
innodb_print_all_deadlocks       = 1
innodb_file_per_table             = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata          = 0
log_error_verbosity                = 3
innodb_print_ddl_logs             = 1
binlog_expire_logs_seconds       = 2592000
innodb_status_file                = 1
innodb_status_output             = 0
innodb_status_output_locks       = 0
performance_schema                = 1
performance_schema_instrument    = '%memory%=on'
performance_schema_instrument    = '%lock%=on'
innodb_monitor_enable             = "module_innodb"
innodb_monitor_enable             = "module_server"
innodb_monitor_enable             = "module_dml"
innodb_monitor_enable             = "module_ddl"
innodb_monitor_enable             = "module_trx"
innodb_monitor_enable             = "module_os"
innodb_monitor_enable             = "module_purge"
innodb_monitor_enable             = "module_log"
innodb_monitor_enable             = "module_lock"
innodb_monitor_enable             = "module_buffer"
innodb_monitor_enable             = "module_index"
innodb_monitor_enable             = "module_ibuf_system"
innodb_monitor_enable             = "module_buffer_page"
innodb_monitor_enable             = "module_adaptive_hash"
loose-group_replication_group_name  = "cc654ef6-1ec1-11f1-b0af-000c29bb2c79"
loose-group_replication_start_on_boot = on
loose-group_replication_local_address = "10.60.73.71:33006"
loose-group_replication_group_seeds = "10.60.73.71:33006,10.60.73.186:33006,10.60.73.204:33006"
loose-group_replication_bootstrap_group = off
loose-group_replication_single_primary_mode = on
loose-group_replication_recovery_get_public_key = 1
loose-group_replication_member_weight = 70
default-time-zone                = '+00:00'
sql_mode                         = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
lower_case_table_names             = 1

[mysqldump]
quick
max_allowed_packet                = 32M

[mysqld_safe]
nice                               = -19
open-files-limit                   = 65535
# Memory management library ##################################
jemalloc is not enabled in mysql config for process with id 6663
# The End ####################################################
[root@mysql-node1 ~]#

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)