[root@db2 ~]# pt-mysql-summary --ask-pass # Percona Toolkit MySQL Summary Report ####################### System time | 2018-07-23 05:36:34 UTC (local TZ: WEST +0100) # Instances ################################################## Port Data Directory Nice OOM Socket ===== ========================== ==== === ====== 3306 /var/lib/mysql 0 0 /var/lib/mysql/mysql.sock # MySQL Executable ########################################### Path to executable | /usr/sbin/mysqld Has symbols | Yes # Slave Hosts ################################################ *************************** 1. row *************************** Server_id: 2 Host: Port: 3306 Master_id: 1 Slave_UUID: 6d446688-ae7f-11e7-8a10-38eaa710ebb0 # Report On Port 3306 ######################################## User | root@localhost Time | 2018-07-23 06:36:34 (WEST) Hostname | db2 Version | 5.6.33-log MySQL Community Server (GPL) Built On | Linux x86_64 Started | 2018-07-20 07:09 (up 2+23:27:20) Databases | 54 Datadir | /var/lib/mysql/ Processes | 100 connected, 2 running Replication | Is a slave, has 1 slaves connected Pidfile | /var/lib/mysql/db2.pid (exists) # Processlist ################################################ Command COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- Binlog Dump 1 1 250000 250000 Connect 2 2 500000 250000 Query 1 1 0 0 Sleep 100 0 3500 1000 User COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- cmpuser 6 0 0 0 inwhappy 4 0 0 0 maintainuser 8 0 0 0 profuser 10 0 0 0 replica 1 1 250000 250000 respuser 4 0 0 0 root 1 1 0 0 sdpuser 9 0 0 0 sdpusr 6 0 0 0 servcuser 30 0 0 0 sharuser 5 0 0 0 smusr 15 0 0 0 system user 2 2 500000 250000 Host COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- 2 2 500000 250000 db1drc 3 0 0 0 db2 4 0 0 0 db2drc 2 1 250000 250000 gw1 20 0 0 0 gw1drc 20 0 0 0 gw2 35 0 0 0 gw2drc 20 0 0 0 localhost 1 1 0 0 db COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- error_codes_handling 4 0 0 0 inma_happy_day 4 0 0 0 inwi_data_sharing 5 0 0 0 inwi_services_configs_mig 30 0 0 0 NULL 4 4 800000 250000 occampaigns 6 0 0 0 oc_entity_notifications 6 0 0 0 ocmaintenance 8 0 0 0 oc_sdp_services 9 0 0 0 oc_service_db 15 0 0 0 ocuserprofiles 10 0 0 0 State COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- 100 0 0 0 init 1 1 0 0 Master has sent all binlog to 1 1 250000 250000 Slave has read all relay log; 1 1 250000 250000 Waiting for master to send eve 1 1 250000 250000 # Status Counters (Wait 10 Seconds) ########################## Variable Per day Per second 11 secs Aborted_clients 100 Binlog_cache_use 7000000 80 9 Binlog_stmt_cache_disk_use 17500 Binlog_stmt_cache_use 9000000 100 Bytes_received 9000000000 100000 8000 Bytes_sent 25000000000 300000 50000 Com_admin_commands 700000 7 5 Com_alter_table 2 Com_begin 225000 2 1 Com_call_procedure 10000000 125 100 Com_change_db 3500 Com_commit 225000 2 1 Com_create_table 15000 Com_create_view 10 Com_dealloc_sql 600 Com_delete 150000 1 Com_drop_table 5 Com_drop_view 10 Com_execute_sql 600 Com_flush 1 Com_insert 6000000 70 Com_kill 40 Com_prepare_sql 600 Com_replace 4500000 50 5 Com_replace_select 80000 Com_select 35000000 400 250 Com_set_option 3000000 35 4 Com_show_create_table 1 Com_show_databases 1 Com_show_fields 25000 Com_show_keys 1 Com_show_processlist 70 Com_show_slave_status 1 Com_show_status 5 Com_show_tables 400000 4 Com_show_variables 5 Com_show_warnings 7000 Com_stmt_close 600 Com_stmt_execute 5000 Com_stmt_prepare 2000 Com_stmt_reprepare 900 Com_update 5000000 60 3 Connections 900000 10 2 Created_tmp_disk_tables 60000 1 Created_tmp_files 8000 Created_tmp_tables 12500000 150 125 Flush_commands 1 Handler_commit 45000000 500 150 Handler_delete 30000 Handler_external_lock 125000000 1500 500 Handler_prepare 12500000 150 15 Handler_read_first 10000000 125 125 Handler_read_key 350000000 4000 350 Handler_read_last 600 Handler_read_next 1500000000 17500 500 Handler_read_prev 2250000 25 Handler_read_rnd 50000000 600 Handler_read_rnd_next 300000000 3500 1250 Handler_update 175000000 2000 5 Handler_write 350000000 4000 100 Innodb_buffer_pool_bytes_data 1750000000 20000 Innodb_buffer_pool_pages_flushed 5000000 60 15 Innodb_buffer_pool_read_requests 1250000000 15000 3000 Innodb_buffer_pool_reads 100000 1 Innodb_buffer_pool_write_requests 45000000 500 60 Innodb_data_fsyncs 500000 5 4 Innodb_data_read 1750000000 20000 Innodb_data_reads 100000 1 Innodb_data_writes 5000000 60 20 Innodb_data_written 90000000000 1000000 300000 Innodb_log_write_requests 6000000 70 7 Innodb_log_writes 70000 Innodb_os_log_fsyncs 90000 1 1 Innodb_os_log_written 3000000000 35000 4000 Innodb_pages_created 17500 Innodb_pages_read 100000 1 Innodb_pages_written 5000000 60 15 Innodb_rows_deleted 30000 Innodb_rows_inserted 2500000 30 1 Innodb_rows_read 1500000000 17500 1750 Innodb_rows_updated 4000000 45 5 Innodb_num_open_files 150 Innodb_available_undo_logs 45 Key_read_requests 450000000 5000 Key_reads 15000000 175 Key_write_requests 80000000 1000 Key_writes 30000000 350 Open_table_definitions 450 Opened_files 2250000 25 60 Opened_table_definitions 500000 5 Opened_tables 1000000 10 Performance_schema_accounts_lost 90000 1 1 Queries 175000000 2000 1250 Questions 40000000 500 125 Select_full_join 125000 1 Select_full_range_join 1250 Select_range 3000000 35 4 Select_scan 12500000 125 125 Slave_received_heartbeats 6000 Sort_merge_passes 2 Sort_range 500 Sort_rows 700000 8 Sort_scan 225000 2 1 Table_locks_immediate 60000000 700 250 Table_locks_waited 225000 2 Table_open_cache_hits 60000000 700 250 Table_open_cache_misses 1000000 10 Table_open_cache_overflows 1000000 10 Threads_created 12500 Uptime 90000 1 1 # Table cache ################################################ Size | 2000 Usage | 100% # 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 | 0.0 Usage | 0% 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 | Yes Prepared statement count | 1 # InnoDB ##################################################### Version | 5.6.33 Buffer Pool Size | 5.0G Buffer Pool Fill | 100% Buffer Pool Dirty | 0% File Per Table | ON Page Size | 16k Log File Size | 2 * 3.0G = 6.0G Log Buffer Size | 64M Flush Method | O_DIRECT Flush Log At Commit | 0 XA Support | ON Checksums | ON Doublewrite | OFF R/W I/O Threads | 4 4 I/O Capacity | 200 Thread Concurrency | 96 Concurrency Tickets | 5000 Commit Concurrency | 0 Txn Isolation Level | REPEATABLE-READ Adaptive Flushing | ON Adaptive Checkpoint | Checkpoint Age | 1k InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue Oldest Transaction | 0 Seconds History List Len | 2866 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 | 32xnot started # MyISAM ##################################################### Key Cache | 8.0M Pct Used | 100% Unflushed | 0% # Security ################################################### Users | 441 users, 0 anon, 0 w/o pw, 0 old pw Old Passwords | 0 # Encryption ################################################# Warning: Using a password on the command line interface can be insecure. No keyring plugins found # Binary Logging ############################################# Binlogs | 597 Zero-Sized | 0 Total Size | 58.2G binlog_format | MIXED expire_logs_days | 5 sync_binlog | 0 server_id | 1 binlog_do_db | binlog_ignore_db | # Noteworthy Variables ####################################### Auto-Inc Incr/Offset | 2/1 default_storage_engine | InnoDB flush_time | 0 init_connect | SET NAMES utf8 init_file | sql_mode | 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 | 512M tmp_table_size | 512M max_allowed_packet | 16M thread_stack | 256k log | log_error | /var/log/mysqld.log log_warnings | 1 log_slow_queries | log_queries_not_using_indexes | OFF log_slave_updates | OFF # Configuration File ######################################### Config File | /etc/my.cnf [client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock user = mysql port = 3306 skip-external-locking explicit_defaults_for_timestamp = 1 sql_mode = 'NO_ENGINE_SUBSTITUTION' group_concat_max_len = 1000000 init_connect = 'SET collation_connection = utf8_unicode_ci' init_connect = 'SET NAMES utf8' character-set-server = utf8 collation_server = utf8_unicode_ci skip-character-set-client-handshake myisam_repair_threads = 4 myisam_use_mmap = 1 bulk_insert_buffer_size = 64M tmp_table_size = 512M max_heap_table_size = 512M max_allowed_packet = 16M table_open_cache = 2000 max_connections = 500 table_open_cache_instances = 16 query_cache_type = 0 query_cache_size = 0 open_files_limit = 10000 slow_query_log = 1 slow_query_log_file = /var/log/mysql-slow-queries.log long_query_time = 5 innodb_buffer_pool_size = 5G innodb_buffer_pool_instances = 5 innodb_log_file_size = 3G innodb_status_file = 0 innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 0 innodb_log_buffer_size = 64M innodb_lock_wait_timeout = 60 innodb_thread_concurrency = 96 innodb_flush_method = O_DIRECT innodb-doublewrite = 0 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 1 innodb_checksum_algorithm = crc32 default-storage-engine = INNODB innodb_numa_interleave = 1 server-id = 1 log-bin = mysql-bin relay-log = relay-bin expire_logs_days = 5 max_binlog_size = 100M binlog_format = MIXED slave-net-timeout = 30 auto_increment_increment = 2 auto_increment_offset = 1 #set this to 1 on the first node and 2 on the second node. replicate-ignore-db = mysql replicate-ignore-db = information_schema replicate-ignore-db = performance_schema replicate-ignore-db = dummyDevApi [mysqld_safe] log-error = /var/log/mysqld.log pid-file = /var/run/mysqld.pid [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer_size = 512M read_buffer = 8M write_buffer = 8M [myisamchk] key_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout # Memory management library ################################## jemalloc is not enabled in mysql config for process with id 13037 # The End ############################