pt-mysql-summary # Percona Toolkit MySQL Summary Report ####################### System time | 2017-10-20 11:41:44 UTC (local TZ: BRST -0200) # Instances ################################################## Port Data Directory Nice OOM Socket ===== ========================== ==== === ====== /var/lib/mysql 0 0 /var/run/mysqld/mysqld.sock # MySQL Executable ########################################### Path to executable | /usr/sbin/mysqld Has symbols | No # Report On Port 3306 ######################################## User | bruno@% Time | 2017-10-20 09:41:44 (BRST) Hostname | percona-01 Version | 5.7.19-17-57-log Percona XtraDB Cluster (GPL), Release rel17, Revision c10027a, WSREP version 29.22, wsrep_29.22 Built On | debian-linux-gnu x86_64 Started | 2017-10-19 19:24 (up 0+14:17:13) Databases | 4 Datadir | /var/lib/mysql/ Processes | 20 connected, 3 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 1 1 0 0 User COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- bruno 1 1 0 0 Host COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- localhost 1 1 0 0 db COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- NULL 1 1 0 0 State COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- starting 1 1 0 0 # Status Counters (Wait 10 Seconds) ########################## Variable Per day Per second 11 secs Aborted_clients 1 Aborted_connects 1 Binlog_snapshot_position 20000000 225 1250 Binlog_cache_use 10000 Bytes_received 600000000 7000 60000 Bytes_sent 125000000000 1250000 150000 Com_admin_commands 800 Com_begin 50000 2 Com_change_db 90000 1 1 Com_commit 30000 2 Com_delete 2250 Com_flush 1 Com_insert 50000 2 Com_insert_select 300 Com_release_savepoint 10 Com_rollback 90 Com_rollback_to_savepoint 450 Com_savepoint 400 Com_select 1000000 15 90 Com_set_option 300000 3 10 Com_show_collations 6 Com_show_create_table 900 Com_show_create_trigger 15 Com_show_databases 15 Com_show_engine_status 15 Com_show_fields 1750 Com_show_keys 1 Com_show_slave_hosts 3 Com_show_slave_status 3 Com_show_status 90000 1 Com_show_storage_engines 5 Com_show_table_status 500 Com_show_tables 20 Com_show_triggers 450 Com_show_variables 70 Com_show_warnings 70 Com_unlock_tables 10 Com_update 90000 1 4 Com_update_multi 3500 Connections 175000 2 3 Created_tmp_disk_tables 3000 1 Created_tmp_files 250 Created_tmp_tables 100000 1 9 Flush_commands 1 Handler_commit 2250000 25 200 Handler_delete 2250 Handler_external_lock 3000000 35 225 Handler_prepare 450000 5 25 Handler_read_first 70000 9 Handler_read_key 1500000000 17500 12500 Handler_read_last 300 Handler_read_next 700000000 8000 12500 Handler_read_prev 25000 Handler_read_rnd 4000000 50 45 Handler_read_rnd_next 1500000000 17500 100000 Handler_rollback 600 Handler_savepoint 450 Handler_savepoint_rollback 900 Handler_update 25000000 300 4 Handler_write 45000000 500 450 Innodb_background_log_sync 90000 Innodb_buffer_pool_bytes_data 35000000000 400000 -1489 Innodb_buffer_pool_bytes_dirty 125000000 1250 200000 Innodb_buffer_pool_pages_flushed 6000000 70 Innodb_buffer_pool_pages_made_not_young 45000000 500 Innodb_buffer_pool_pages_made_young 450000 5 Innodb_buffer_pool_pages_old 800000 9 Innodb_buffer_pool_read_ahead 2000000 20 Innodb_buffer_pool_read_requests 4000000000 45000 35000 Innodb_buffer_pool_reads 6000000 70 Innodb_buffer_pool_wait_free 80 Innodb_buffer_pool_write_requests 2500000 30 300 Innodb_checkpoint_age 9000000 100 4500 Innodb_checkpoint_max_age 12500000000 125000 Innodb_data_fsyncs 450000 4 Innodb_data_read 125000000000 1500000 1500 Innodb_data_reads 8000000 90 Innodb_data_writes 700000 8 Innodb_data_written 8000000000 90000 8000 Innodb_dblwr_pages_written 450000 4 Innodb_dblwr_writes 125000 1 Innodb_ibuf_free_list 200 Innodb_ibuf_segment_size 200 Innodb_log_write_requests 400000 4 10 Innodb_log_writes 70000 Innodb_lsn_current 2500000000000 30000000 4500 Innodb_lsn_flushed 2500000000000 30000000 4500 Innodb_lsn_last_checkpoint 2500000000000 30000000 Innodb_master_thread_active_loops 40000 Innodb_master_thread_idle_loops 45000 Innodb_max_trx_id 150000000 1750 7 Innodb_mem_adaptive_hash 1250000000 15000 1500 Innodb_mem_dictionary 150000000 1750 Innodb_oldest_view_low_limit_trx_id 150000000 1750 -8363472 Innodb_os_log_fsyncs 100000 1 Innodb_os_log_written 300000000 3500 8000 Innodb_pages_created 10000 Innodb_pages_read 8000000 90 Innodb_pages_written 450000 5 Innodb_purge_trx_id 150000000 1750 7 Innodb_row_lock_waits 1 Innodb_rows_deleted 2250 Innodb_rows_inserted 400000 4 80 Innodb_rows_read 3500000000 40000 125000 Innodb_rows_updated 150000 1 4 Innodb_num_open_files 500 Innodb_available_undo_logs 225 Innodb_secondary_index_triggered_cluster_reads 600000000 7000 1500 Key_read_requests 12500 Key_reads 45 Open_table_definitions 800 Opened_files 3000 Opened_table_definitions 800 Opened_tables 9000 Performance_schema_stage_classes_lost 6 Queries 2500000 30 125 Questions 1750000 20 125 Select_full_join 1750 Select_full_range_join 175 Select_range 40000 Select_range_check 1 Select_scan 225000 2 10 Slow_queries 125 Sort_merge_passes 600 Sort_range 60000 8 Sort_rows 50000000 600 250 Sort_scan 8000 Ssl_accepts 60000 1 Ssl_finished_accepts 60000 1 Ssl_session_cache_overflows 60000 1 Ssl_used_session_cache_entries 225 Table_locks_immediate 90000 1 1 Table_open_cache_hits 1500000 15 125 Table_open_cache_misses 9000 Table_open_cache_overflows 3000 Threads_created 40 Uptime 90000 1 1 wsrep_protocol_version 10 wsrep_last_committed 12500000 150 3 wsrep_replicated 10000 wsrep_replicated_bytes 25000000 300 1250 wsrep_repl_keys 80000 6 wsrep_repl_keys_bytes 900000 10 70 wsrep_repl_data_bytes 25000000 300 1250 wsrep_received 50000 2 wsrep_received_bytes 150000000 1750 3000 wsrep_local_commits 10000 wsrep_local_send_queue_max 3 wsrep_local_recv_queue_max 6 wsrep_local_cached_downto 12500000 150 wsrep_flow_control_interval_low 300 wsrep_flow_control_interval_high 300 wsrep_local_state 6 wsrep_cert_index_size 125 9 wsrep_cert_bucket_count 200000 2 wsrep_gcache_pool_size 200000000 2250 4500 wsrep_cluster_conf_id 175 wsrep_cluster_size 5 wsrep_local_bf_aborts 1 wsrep_local_index 3 # Table cache ################################################ Size | 4096 Usage | 90% # Key Percona Server features ################################ Table & Index Stats | Disabled Multiple I/O Threads | Enabled Corruption Resilient | Enabled Durable Replication | Not Supported Import InnoDB Tables | Not Supported Fast Server Restarts | Not Supported Enhanced Logging | Disabled Replica Perf Logging | Disabled Response Time Hist. | Not Supported Smooth Flushing | Not Supported HandlerSocket NoSQL | Not Supported Fast Hash UDFs | Enabled # 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 | Yes Explicit LOCK TABLES | No Delayed Insert | No XA Transactions | No NDB Cluster | No Prepared Statements | No Prepared statement count | 0 # InnoDB ##################################################### Version | 5.7.19-17 Buffer Pool Size | 20.0G Buffer Pool Fill | 100% Buffer Pool Dirty | 0% File Per Table | ON Page Size | 16k Log File Size | 2 * 4.0G = 8.0G Log Buffer Size | 8M Flush Method | O_DIRECT Flush Log At Commit | 0 XA Support | ON Checksums | ON Doublewrite | ON R/W I/O Threads | 4 4 I/O Capacity | 200 Thread Concurrency | 0 Concurrency Tickets | 5000 Commit Concurrency | 0 Txn Isolation Level | REPEATABLE-READ Adaptive Flushing | ON Adaptive Checkpoint | # MyISAM ##################################################### Key Cache | 16.0M Pct Used | 20% Unflushed | 0% # Security ################################################### Users | Old Passwords | 0 # Binary Logging ############################################# # Noteworthy Variables ####################################### Auto-Inc Incr/Offset | 3/3 default_storage_engine | InnoDB flush_time | 0 init_connect | init_file | sql_mode | NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION join_buffer_size | 200M sort_buffer_size | 200M read_buffer_size | 128k read_rnd_buffer_size | 200M bulk_insert_buffer | 0.00 max_heap_table_size | 256M tmp_table_size | 256M max_allowed_packet | 16M thread_stack | 256k log | log_error | /var/log/mysql/mysql-error.log log_warnings | 2 log_slow_queries | log_queries_not_using_indexes | OFF log_slave_updates | OFF # Configuration File ######################################### Config File | /etc/mysql/my.cnf [mysql] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld] federated performance_schema explicit_defaults_for_timestamp = 1 log_timestamps = SYSTEM innodb_force_recovery = 0 pxc_strict_mode = PERMISSIVE user = mysql default_storage_engine = InnoDB socket = /var/run/mysqld/mysqld.sock pid_file = /var/run/mysqld/mysqld.pid bind_address = 0.0.0.0 skip-name-resolve datadir = /var/lib/mysql/ tmpdir = /var/lib/mysql/tmp key_buffer_size = 16M myisam_recover_options = FORCE,BACKUP wsrep_provider = /usr/lib/libgalera_smm.so binlog_format = ROW default_storage_engine = InnoDB innodb_autoinc_lock_mode = 2 wsrep_node_address = 10.10.10.121 wsrep_sst_method = rsync wsrep_provider_options = "gcs.max_packet_size=1048576; evs.send_window=512; evs.user_send_window=512; evs.inactive_timeout = PT90S; evs.suspect_timeout = PT30S; evs.install_timeout = PT60S; evs.keepalive_period = PT6S; evs.max_install_timeouts = 8 " wsrep_replicate_myisam = 1 wsrep_cluster_name = ev-cluster-mysql wsrep_node_name = percona-01 wsrep_slave_threads = 8 wsrep_debug = 0 wsrep_retry_autocommit = 1 wsrep_convert_LOCK_to_trx = 1 wsrep_certify_nonPK = 1 innodb_locks_unsafe_for_binlog = 1 log_bin = /var/lib/mysql/mysql-bin expire_logs_days = 5 sync_binlog = 1 server_id = 10 max_allowed_packet = 16M max_connect_errors = 1000000 skip_name_resolve sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE sysdate_is_now = 1 innodb = FORCE innodb_strict_mode = 1 tmp_table_size = 256M max_heap_table_size = 256M query_cache_type = 0 query_cache_size = 0 max_connections = 100 thread_cache_size = 120 open_files_limit = 100000 table_definition_cache = 4096 table_open_cache = 4096 query_cache_limit = 1M sort_buffer_size = 200M read_rnd_buffer_size = 200M join_buffer_size = 200M binlog_cache_size = 16M innodb_flush_method = O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 4G innodb_flush_log_at_trx_commit = 0 innodb_file_per_table = 1 innodb_buffer_pool_size = 20G thread_pool_size = 36 innodb_doublewrite = 1 innodb_thread_concurrency = 0 innodb_max_dirty_pages_pct = 80 innodb_thread_concurrency = 0 innodb_buffer_pool_instances = 20 innodb_log_buffer_size = 8M innodb_lock_wait_timeout = 120 thread_pool_idle_timeout = 3600 log_error = /var/log/mysql/mysql-error.log slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log general_log_file = /var/log/mysql/mysql-general.log general_log = 1 innodb_print_all_deadlocks = 1 event_scheduler = 1