# Percona Toolkit MySQL Summary Report ####################### System time | 2021-06-30 15:42:07 UTC (local TZ: IST +0530) # Instances ################################################## Port Data Directory Nice OOM Socket ===== ========================== ==== === ====== # MySQL Executable ########################################### # Slave Hosts ################################################ No slaves found # Report On Port 3306 ######################################## User | root@localhost Time | 2021-06-30 21:12:07 (IST) Hostname | hostname Version | 10.5.11-MariaDB-1:10.5.11+maria~focal-log mariadb.org binary distribution Built On | debian-linux-gnu x86_64 Started | 2021-06-30 02:26 (up 0+18:45:36) Databases | 6 Datadir | /var/lib/mysql/ Processes | 250 connected, 250 running Replication | Is not a slave, has 0 slaves connected Pidfile | /var/run/mysqld/mysqld.pid (exists) # Processlist ################################################ Command COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- Query 250 250 0 0 Sleep 15 0 600000 70000 User COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- monitoring 3 0 0 0 root 2 1 0 0 sysbench 250 250 0 0 system user 9 0 0 0 Host COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- 9 0 0 0 localhost 2 1 0 0 hostname.domain 3 0 0 0 hostname3.domain 250 250 0 0 db COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- NULL 15 1 0 0 sbtest 250 250 0 0 State COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- 8 0 0 0 checking permissions 1 1 0 0 closing tables 2 0 0 0 Commit 250 250 0 0 init for update 2 2 0 0 starting 2 2 0 0 Updating 1 1 0 0 Updating status 1 1 0 0 Writing to net 1 1 0 0 wsrep aborter idle 1 0 0 0 wsrep applier idle 6 0 0 0 # Status Counters (Wait 10 Seconds) ########################## Variable Per day Per second 10 secs Aborted_clients 6000 Aborted_connects 3 Access_denied_errors 3 Acl_database_grants 2 Acl_proxy_users 2 Acl_table_grants 1 Acl_users 15 Aria_pagecache_blocks_unused 20000 Aria_pagecache_blocks_used 20000 Aria_pagecache_read_requests 25000000 300 40 Aria_pagecache_reads 100000 1 Aria_pagecache_write_requests 350000 4 Aria_pagecache_writes 350000 4 Aria_transaction_log_syncs 30 Binlog_commits 400000000 4500 30000 Binlog_group_commits 250000000 3000 17500 Binlog_snapshot_position 1250000000 15000 -91058131 Binlog_bytes_written 250000000000 3000000 17500000 Binlog_cache_use 400000000 4500 30000 Bytes_received 100000000000 1250000 2000000 Bytes_sent 1750000000000 20000000 2250000 Com_admin_commands 25000 Com_begin 60000000 700 Com_change_db 1 Com_commit 60000000 700 Com_create_table 3 Com_delete 60000000 700 Com_empty_query 100000 1 1 Com_insert 60000000 700 Com_kill 20 Com_purge 250 Com_select 900000000 10000 3 Com_set_option 125000 1 2 Com_show_binlogs 1500 Com_show_databases 6 Com_show_engine_status 70000 1 Com_show_fields 200 Com_show_binlog_status 3 Com_show_plugins 3 Com_show_processlist 3 Com_show_slave_hosts 250 Com_show_slave_status 9000 Com_show_status 17500 Com_show_storage_engines 3 Com_show_tables 6 Com_show_variables 4500 Com_show_warnings 10 Com_update 600000000 7000 40000 Connections 80000 2 Created_tmp_disk_tables 20000 Created_tmp_files 6 Created_tmp_tables 60000000 700 3 Delete_scan 5 Empty_queries 175000000 2250 Feature_check_constraint 1 Feature_json 350 9 Feature_subquery 125000 1 2 Handler_commit 2250000000 25000 70000 Handler_delete 40000000 500 Handler_discover 3 Handler_prepare 1250000000 12500 60000 Handler_read_first 3 Handler_read_key 1500000000 17500 40000 Handler_read_next 17500000000 200000 Handler_read_rnd 4500000000 50000 Handler_read_rnd_next 4500000000 50000 225 Handler_rollback 1500 Handler_tmp_update 12500000 150 25 Handler_tmp_write 4500000000 50000 225 Handler_update 400000000 5000 30000 Handler_write 60000000 700 Innodb_adaptive_hash_non_hash_searches 4000000000 50000 175000 Innodb_background_log_sync 90000 1 Innodb_buffer_pool_bytes_data 225000000000 2500000 150000 Innodb_buffer_pool_bytes_dirty 200000000000 2500000 -453366579 Innodb_buffer_pool_pages_flushed 125000000 1500 30000 Innodb_buffer_pool_pages_made_not_young 15000000 175 50 Innodb_buffer_pool_pages_made_young 700000000 8000 17500 Innodb_buffer_pool_pages_old 5000000 60 3 Innodb_buffer_pool_read_ahead 25 Innodb_buffer_pool_read_requests 22500000000 250000 900000 Innodb_buffer_pool_reads 12500000 125 20 Innodb_buffer_pool_write_requests 5000000000 60000 350000 Innodb_checkpoint_age 35000000000 400000 -101732 Innodb_checkpoint_max_age 35000000000 400000 Innodb_data_fsyncs 50000000 600 12500 Innodb_data_read 200000000000 2250000 300000 Innodb_data_reads 12500000 150 20 Innodb_data_writes 125000000 1500 30000 Innodb_data_written 2000000000000 25000000 500000000 Innodb_dblwr_pages_written 125000000 1500 30000 Innodb_dblwr_writes 1000000 10 250 Innodb_history_list_length 600 80 Innodb_ibuf_free_list 400000 4 20 Innodb_ibuf_merged_delete_marks 50000000 600 2250 Innodb_ibuf_merged_deletes 50000000 600 2250 Innodb_ibuf_merged_inserts 60000000 700 2000 Innodb_ibuf_merges 1250000 15 20 Innodb_ibuf_segment_size 400000 4 Innodb_ibuf_size 30000 -19 Innodb_log_write_requests 225000000 2500 5000 Innodb_log_writes 80000 30 Innodb_lsn_current 2250000000000 25000000 12500000 Innodb_lsn_flushed 2250000000000 25000000 15000000 Innodb_lsn_last_checkpoint 2000000000000 25000000 12500000 Innodb_master_thread_idle_loops 90000 1 Innodb_max_trx_id 5000000000 60000 70000 Innodb_mem_dictionary 3500000000 40000 Innodb_os_log_fsyncs 80000 30 Innodb_os_log_written 250000000000 3000000 15000000 Innodb_pages_created 2000000 20 2 Innodb_pages_read 12500000 125 20 Innodb_pages_written 125000000 1500 30000 Innodb_row_lock_time 250 Innodb_row_lock_waits 125 Innodb_rows_deleted 40000000 500 Innodb_rows_inserted 60000000 700 Innodb_rows_read 17500000000 225000 30000 Innodb_rows_updated 400000000 5000 30000 Innodb_system_rows_deleted 8 Innodb_system_rows_inserted 8 Innodb_system_rows_read 25 Innodb_num_open_files 90 Innodb_available_undo_logs 175 Memory_used 150000000 1750 -1572 Memory_used_initial 45000000 500 Open_table_definitions 125 Opened_files 80000 2 Opened_plugin_libraries 1 Opened_table_definitions 125 Opened_tables 3500000 40 500 Opened_views 7 Queries 1750000000 20000 40000 Questions 1750000000 20000 40000 Rows_read 17500000000 225000 30000 Rows_sent 12500000000 150000 200 Rows_tmp_read 9000000000 100000 250 Select_range 250000000 3000 Select_scan 200000 2 3 Slow_launch_threads 2 Slow_queries 150000 1 Sort_range 60000000 700 Sort_rows 9000000000 100000 Sort_scan 60000000 700 Syncs 1750 Table_locks_immediate 20000 Table_open_cache_active_instances 2 Table_open_cache_hits 1500000000 17500 40000 Table_open_cache_misses 3500000 40 500 Table_open_cache_overflows 3000000 35 500 Threads_created 5000 Uptime 90000 1 1 wsrep_protocol_version 15 wsrep_last_committed 2000000000 25000 30000 wsrep_replicated 400000000 4500 30000 wsrep_replicated_bytes 300000000000 3000000 17500000 wsrep_repl_keys 1250000000 15000 90000 wsrep_repl_keys_bytes 20000000000 225000 1500000 wsrep_repl_data_bytes 225000000000 2500000 15000000 wsrep_received 4500000 50 200 wsrep_received_bytes 35000000 400 1500 wsrep_local_commits 400000000 4500 30000 wsrep_local_send_queue 250 -2 wsrep_local_send_queue_max 350 wsrep_local_recv_queue_max 3 wsrep_local_cached_downto 2000000000 22500 30000 wsrep_flow_control_paused_ns 300000000000 3500000 30000000 wsrep_flow_control_recv 22500 1 wsrep_local_state 5 wsrep_cert_index_size 1000 -5 wsrep_open_transactions 300 -1 wsrep_cluster_weight 3 wsrep_applier_thread_count 10 wsrep_cluster_conf_id 15 wsrep_cluster_size 3 wsrep_local_index 1 wsrep_rollbacker_thread_count 1 wsrep_thread_count 10 # Table cache ################################################ Size | 400 Usage | 200% # Key Percona Server features ################################ Table & Index Stats | Disabled 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 | Enabled Replica Perf Logging | Enabled Response Time Hist. | Not Supported Smooth Flushing | Not Supported HandlerSocket NoSQL | Not Supported Fast Hash UDFs | Unknown # Percona XtraDB Cluster ##################################### Cluster Name | my_wsrep_cluster Cluster Address | gcomm://hostname.domain1,hostname2.domain1,hostname3.domain1 Cluster Size | 3 Cluster Nodes | hostname2.domain1:3306,hostname.domain1:3306,hostname3.domain1:3306 Node Name | hostname Node Status | Primary SST Method | mariabackup Slave Threads | 8 Ignore Split Brain | false Ignore Quorum | false gcache Size | 16G gcache Directory | /opt/gcache gcache Name | galera.cache # Plugins #################################################### InnoDB compression | ACTIVE # Query cache ################################################ query_cache_type | OFF Size | 0.0 Usage | 0% HitToInsertRatio | 0% # Semisynchronous Replication ################################ Master | Disabled Slave | Disabled # 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 | 10.5.11 Buffer Pool Size | 424.4G Buffer Pool Fill | 40% Buffer Pool Dirty | 35% File Per Table | ON Page Size | 16k Log File Size | 1 * 30.0G = 30.0G Log Buffer Size | 256M Flush Method | O_DIRECT Flush Log At Commit | 0 XA Support | Checksums | Doublewrite | ON R/W I/O Threads | 4 4 I/O Capacity | 3000 Thread Concurrency | 0 Concurrency Tickets | 0 Commit Concurrency | 0 Txn Isolation Level | REPEATABLE-READ Adaptive Flushing | ON Adaptive Checkpoint | Checkpoint Age | 24G InnoDB Queue | Oldest Transaction | 0 Seconds History List Len | 463 Read Views | 0 Undo Log Entries | 251 transactions, 251 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 (0 log, 0 chkp); 0 pwrites Pending I/O Flushes | 0 buf pool, 0 log Transaction States | 222xACTIVE, 31xACTIVE (PREPARED), 1xCOMMITTED IN MEMORY committing, 5xnot started # MyISAM ##################################################### Key Cache | 128.0M Pct Used | 20% Unflushed | 0% # Security ################################################### Users | 10 users, 2 anon, 3 w/o pw, 5 old pw Old Passwords | OFF # Encryption ################################################# No keyring plugins found # Binary Logging ############################################# Binlogs | 167 Zero-Sized | 0 Total Size | 167.0G binlog_format | ROW expire_logs_days | 3 sync_binlog | 0 server_id | 1268975064 binlog_do_db | binlog_ignore_db | # Noteworthy Variables ####################################### Auto-Inc Incr/Offset | 3/2 default_storage_engine | InnoDB flush_time | 0 init_connect | init_file | sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION join_buffer_size | 256k sort_buffer_size | 4M read_buffer_size | 2M read_rnd_buffer_size | 1M bulk_insert_buffer | 0.00 max_heap_table_size | 32M tmp_table_size | 32M max_allowed_packet | 16M thread_stack | 292k log | log_error | /var/log/mysql/mysql_error.log log_warnings | 2 log_slow_queries | log_queries_not_using_indexes | OFF log_slave_updates | ON # Configuration File ######################################### Config File | /etc/mysql/my.cnf [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 log_error = /var/log/mysql/mysql_error.log [mysqld] core_file log_error = /var/log/mysql/mysql_error.log skip-symbolic-links local-infile = 0 user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /var/tmp/mysql lc_messages_dir = /usr/share/mysql lc_messages = en_US skip-external-locking max_connections = 10000 connect_timeout = 5 wait_timeout = 600 max_allowed_packet = 16M thread_cache_size = 128 sort_buffer_size = 4M bulk_insert_buffer_size = 16M tmp_table_size = 32M max_heap_table_size = 32M max_prepared_stmt_count = 32764 myisam_recover_options = BACKUP key_buffer_size = 128M table_open_cache = 400 myisam_sort_buffer_size = 512M concurrent_insert = 2 read_buffer_size = 2M read_rnd_buffer_size = 1M query_cache_size = 0 query_cache_type = OFF log_warnings = 2 slow_query_log = 1 slow_query_log_file = /var/log/mysql/mariadb-slow.log long_query_time = 0.5 log_slow_verbosity = query_plan server-id = 1268975064 report_host = hostname.domain1 log_bin = /var/log/mysql/mariadb-bin log_bin_index = /var/log/mysql/mariadb-bin.index expire_logs_days = 3 max_binlog_size = 1G binlog_format = ROW binlog_row_image = FULL binlog_annotate_row_events = OFF replicate_annotate_row_events = OFF log_slave_updates innodb_print_all_deadlocks = 1 sql_mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION default_storage_engine = InnoDB innodb_log_file_size = 30G innodb_buffer_pool_size = 434441M innodb_log_buffer_size = 256M innodb_file_per_table = 1 innodb_open_files = 400 innodb_io_capacity = 10000 innodb_io_capacity_max = 20000 innodb_flush_method = O_DIRECT innodb_sort_buffer_size = 64M innodb_file_format = barracuda bind-address = 0.0.0.0 innodb_flush_log_at_trx_commit = 0 [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] local-infile = 0 [isamchk] key_buffer = 16M # Memory management library ################################## # The End ####################################################