I’m having a problem with MySQL 5.7 with « too many connections » causing services crashes. The max_connections system variable is set at 1000 and on average there are +/- 250 sessions/thread, so it’s odd that the max connections are being reached. The issue appears mostly at night between 10 and 11 pm at certain weeknights.
The machine is a Windows 2008 R2 Enterprise Server with 32 Gb RAM and Dual Xeon CPUs. Here's some more environmental information:
Variable | Max Connection Memory
-------------------------------------------------
join_buffer_size | 250.00 MB
read_buffer_size | 62.50 MB
read_rnd_buffer_size | 250.00 MB
sort_buffer_size | 250.00 MB
max_connections = 1000 | 812.50 MB
Timeouts | VALUE
-------------------------------------------
connect_timeout | 10
delayed_insert_timeout | 300
have_statement_timeout | YES
innodb_flush_log_at_timeout | 1
innodb_lock_wait_timeout | 50
innodb_rollback_on_timeout | OFF
interactive_timeout | 28800
lock_wait_timeout | 31536000
net_read_timeout | 30
net_write_timeout | 60
rpl_stop_slave_timeout | 31536000
slave_net_timeout | 60
wait_timeout | 28800
-------------------------------------------
max_allowed_packet | 33554432
slave_max_allowed_packet | 1073741824
Here's my sample log file:
Aborted connection 27933 to db: 'wms_mysql' user: 'mysql' host: 'eifprdrds01.domain.com' (Got an error reading communication packets)
Aborted connection 26736 to db: 'wms_mysql' user: 'mysql' host: 'eifprdrds01.domain.com' (Got an error reading communication packets)
Aborted connection 27200 to db: 'wms_mysql' user: 'mysql' host: 'eifprdrds01.domain.com' (Got an error reading communication packets)
Aborted connection 27356 to db: 'wms_mysql' user: 'mysql' host: 'eifprdrds01.domain.com' (Got an error reading communication packets)
Aborted connection 29119 to db: 'wms_mysql' user: 'mysql' host: 'pc286.domain.com' (Got an error reading communication packets)
Aborted connection 16274 to db: 'wms_mysql' user: 'mysql' host: 'pc828.domain.com' (Got timeout reading communication packets)
Aborted connection 24916 to db: 'wms_mysql' user: 'mysql' host: 'pc830.domain.com' (Got an error reading communication packets)
Aborted connection 19357 to db: 'wms_mysql' user: 'mysql' host: 'pc830.domain.com' (Got an error reading communication packets)
Aborted connection 19343 to db: 'wms_mysql' user: 'mysql' host: 'pc830.domain.com' (Got an error reading communication packets)
Here are some additional environmental parameters (global status):
Variable_name | Value
Aborted_clients | 579
Aborted_connects | 1
Binlog_cache_disk_use | 0
Binlog_cache_use | 0
Binlog_stmt_cache_disk_use | 0
Binlog_stmt_cache_use | 0
Bytes_received | 112705860256
Bytes_sent | 1320858513743
Com_admin_commands | 6343
Com_assign_to_keycache | 0
Com_alter_db | 0
Com_alter_db_upgrade | 0
Com_alter_event | 0
Com_alter_function | 0
Com_alter_instance | 0
Com_alter_procedure | 0
Com_alter_server | 0
Com_alter_table | 0
Com_alter_tablespace | 0
Com_alter_user | 0
Com_analyze | 0
Com_begin | 368010
Com_binlog | 0
Com_call_procedure | 0
Com_change_db | 14
Com_change_master | 0
Com_change_repl_filter | 0
Com_check | 0
Com_checksum | 0
Com_commit | 367880
Com_create_db | 0
Com_create_event | 0
Com_create_function | 0
Com_create_index | 0
Com_create_procedure | 0
Com_create_server | 0
Com_create_table | 0
Com_create_trigger | 0
Com_create_udf | 0
Com_create_user | 0
Com_create_view | 0
Com_dealloc_sql | 0
Com_delete | 1899441
Com_delete_multi | 0
Com_do | 0
Com_drop_db | 0
Com_drop_event | 0
Com_drop_function | 0
Com_drop_index | 0
Com_drop_procedure | 0
Com_drop_server | 0
Com_drop_table | 0
Com_drop_trigger | 0
Com_drop_user | 0
Com_drop_view | 0
Com_empty_query | 0
Com_execute_sql | 0
Com_explain_other | 0
Com_flush | 0
Com_get_diagnostics | 0
Com_grant | 0
Com_ha_close | 0
Com_ha_open | 0
Com_ha_read | 0
Com_help | 0
Com_insert | 5932889
Com_insert_select | 0
Com_install_plugin | 0
Com_kill | 1
Com_load | 0
Com_lock_tables | 0
Com_optimize | 0
Com_preload_keys | 0
Com_prepare_sql | 0
Com_purge | 0
Com_purge_before_date | 0
Com_release_savepoint | 0
Com_rename_table | 0
Com_rename_user | 0
Com_repair | 0
Com_replace | 0
Com_replace_select | 0
Com_reset | 0
Com_resignal | 0
Com_revoke | 0
Com_revoke_all | 0
Com_rollback | 107
Com_rollback_to_savepoint | 0
Com_savepoint | 0
Com_select | 305377361
Com_set_option | 412902
Com_signal | 0
Com_show_binlog_events | 0
Com_show_binlogs | 0
Com_show_charsets | 9
Com_show_collations | 9
Com_show_create_db | 0
Com_show_create_event | 0
Com_show_create_func | 0
Com_show_create_proc | 0
Com_show_create_table | 0
Com_show_create_trigger | 0
Com_show_databases | 20
Com_show_engine_logs | 0
Com_show_engine_mutex | 0
Com_show_engine_status | 0
Com_show_events | 0
Com_show_errors | 0
Com_show_fields | 33672455
Com_show_function_code | 0
Com_show_function_status | 8
Com_show_grants | 2
Com_show_keys | 34409046
Com_show_master_status | 0
Com_show_open_tables | 0
Com_show_plugins | 19
Com_show_privileges | 0
Com_show_procedure_code | 0
Com_show_procedure_status | 8
Com_show_processlist | 2
Com_show_profile | 0
Com_show_profiles | 0
Com_show_relaylog_events | 0
Com_show_slave_hosts | 0
Com_show_slave_status | 6
Com_show_status | 337719
Com_show_storage_engines | 9
Com_show_table_status | 0
Com_show_tables | 11
Com_show_triggers | 634
Com_show_variables | 221
Com_show_warnings | 0
Com_show_create_user | 0
Com_shutdown | 0
Com_slave_start | 0
Com_slave_stop | 0
Com_group_replication_start | 0
Com_group_replication_stop | 0
Com_stmt_execute | 2
Com_stmt_close | 2
Com_stmt_fetch | 0
Com_stmt_prepare | 2
Com_stmt_reset | 0
Com_stmt_send_long_data | 0
Com_truncate | 0
Com_uninstall_plugin | 0
Com_unlock_tables | 0
Com_update | 3160289
Com_update_multi | 0
Com_xa_commit | 0
Com_xa_end | 0
Com_xa_prepare | 0
Com_xa_recover | 0
Com_xa_rollback | 0
Com_xa_start | 0
Com_stmt_reprepare | 0
Compression | OFF
Connection_errors_accept | 0
Connection_errors_internal | 0
Connection_errors_max_connections | 94
Connection_errors_peer_address | 0
Connection_errors_select | 0
Connection_errors_tcpwrap | 0
Connections | 412961
Created_tmp_disk_tables | 34412942
Created_tmp_files | 53367
Created_tmp_tables | 71692427
Delayed_errors | 0
Delayed_insert_threads | 0
Delayed_writes | 0
Flush_commands | 1
Handler_commit | 315946909
Handler_delete | 5540520
Handler_discover | 0
Handler_external_lock | 688867588
Handler_mrr_init | 0
Handler_prepare | 0
Handler_read_first | 34976935
Handler_read_key | 2188865194
Handler_read_last | 17485
Handler_read_next | 54290082542
Handler_read_prev | 19085786
Handler_read_rnd | 1186807611
Handler_read_rnd_next | 2955796362
Handler_rollback | 148
Handler_savepoint | 0
Handler_savepoint_rollback | 0
Handler_update | 428611514
Handler_write | 646923649
Innodb_buffer_pool_dump_status | Dumping of buffer pool not started
Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 181219 19:56:57
Innodb_buffer_pool_resize_status |
Innodb_buffer_pool_pages_data | 966720
Innodb_buffer_pool_bytes_data | 2953838592
Innodb_buffer_pool_pages_dirty | 0
Innodb_buffer_pool_bytes_dirty | 0
Innodb_buffer_pool_pages_flushed | 5329236
Innodb_buffer_pool_pages_free | 8197
Innodb_buffer_pool_pages_misc | 73659
Innodb_buffer_pool_pages_total | 1048576
Innodb_buffer_pool_read_ahead_rnd | 0
Innodb_buffer_pool_read_ahead | 34382
Innodb_buffer_pool_read_ahead_evicted | 0
Innodb_buffer_pool_read_requests | 3904592079
Innodb_buffer_pool_reads | 747465
Innodb_buffer_pool_wait_free | 0
Innodb_buffer_pool_write_requests | 1045142981
Innodb_data_fsyncs | 1695449
Innodb_data_pending_fsyncs | 0
Innodb_data_pending_reads | 0
Innodb_data_pending_writes | 0
Innodb_data_read | 644895232
Innodb_data_reads | 1088377
Innodb_data_writes | 14566224
Innodb_data_written | 3881918464
Innodb_dblwr_pages_written | 4497979
Innodb_dblwr_writes | 398532
Innodb_log_waits | 0
Innodb_log_write_requests | 22692404
Innodb_log_writes | 8771102
Innodb_os_log_fsyncs | 322535
Innodb_os_log_pending_fsyncs | 0
Innodb_os_log_pending_writes | 0
Innodb_os_log_written | 18935997952
Innodb_page_size | 16384
Innodb_pages_created | 134590
Innodb_pages_read | 1087932
Innodb_pages_written | 5329236
Innodb_row_lock_current_waits | 0
Innodb_row_lock_time | 3466511
Innodb_row_lock_time_avg | 35015
Innodb_row_lock_time_max | 51754
Innodb_row_lock_waits | 99
Innodb_rows_deleted | 5540520
Innodb_rows_inserted | 564189585
Innodb_rows_read | 2017392003
Innodb_rows_updated | 3601631
Innodb_num_open_files | 300
Innodb_truncated_status_writes | 0
Innodb_available_undo_logs | 128
Key_blocks_not_flushed | 0
Key_blocks_unused | 6698
Key_blocks_used | 4
Key_read_requests | 124
Key_reads | 29
Key_write_requests | 0
Key_writes | 0
Last_query_cost | 0
Last_query_partial_plans | 0
Locked_connects | 0
Max_execution_time_exceeded | 0
Max_execution_time_set | 0
Max_execution_time_set_failed | 0
Max_used_connections | 701
Max_used_connections_time | 12/19/2018 23:27
Not_flushed_delayed_rows | 0
Ongoing_anonymous_transaction_count | 0
Open_files | 1
Open_streams | 0
Open_table_definitions | 876
Open_tables | 2000
Opened_files | 57710
Opened_table_definitions | 876
Opened_tables | 24810492
Performance_schema_accounts_lost | 0
Performance_schema_cond_classes_lost | 0
Performance_schema_cond_instances_lost | 0
Performance_schema_digest_lost | 0
Performance_schema_file_classes_lost | 0
Performance_schema_file_handles_lost | 0
Performance_schema_file_instances_lost | 0
Performance_schema_hosts_lost | 0
Performance_schema_index_stat_lost | 0
Performance_schema_locker_lost | 0
Performance_schema_memory_classes_lost | 0
Performance_schema_metadata_lock_lost | 0
Performance_schema_mutex_classes_lost | 0
Performance_schema_mutex_instances_lost | 0
Performance_schema_nested_statement_lost | 0
Performance_schema_prepared_statements_lost | 0
Performance_schema_program_lost | 0
Performance_schema_rwlock_classes_lost | 0
Performance_schema_rwlock_instances_lost | 0
Performance_schema_session_connect_attrs_lost | 0
Performance_schema_socket_classes_lost | 0
Performance_schema_socket_instances_lost | 0
Performance_schema_stage_classes_lost | 0
Performance_schema_statement_classes_lost | 0
Performance_schema_table_handles_lost | 0
Performance_schema_table_instances_lost | 0
Performance_schema_table_lock_stat_lost | 0
Performance_schema_thread_classes_lost | 0
Performance_schema_thread_instances_lost | 0
Performance_schema_users_lost | 0
Prepared_stmt_count | 0
Qcache_free_blocks | 0
Qcache_free_memory | 0
Qcache_hits | 0
Qcache_inserts | 0
Qcache_lowmem_prunes | 0
Qcache_not_cached | 0
Qcache_queries_in_cache | 0
Qcache_total_blocks | 0
Queries | 386369511
Questions | 386351756
Select_full_join | 8242
Select_full_range_join | 97092
Select_range | 57964836
Select_range_check | 4
Select_scan | 69826287
Slave_heartbeat_period | 0
Slave_last_heartbeat |
Slave_open_temp_tables | 0
Slave_received_heartbeats | 0
Slave_retried_transactions | 0
Slave_running | OFF
Slow_launch_threads | 0
Slow_queries | 44
Sort_merge_passes | 34863
Sort_range | 1764316
Sort_rows | 299335094
Sort_scan | 2888984
Ssl_accept_renegotiates | 0
Ssl_accepts | 0
Ssl_callback_cache_hits | 0
Ssl_cipher |
Ssl_cipher_list |
Ssl_client_connects | 0
Ssl_connect_renegotiates | 0
Ssl_ctx_verify_depth | 0
Ssl_ctx_verify_mode | 0
Ssl_default_timeout | 0
Ssl_finished_accepts | 0
Ssl_finished_connects | 0
Ssl_server_not_after |
Ssl_server_not_before |
Ssl_session_cache_hits | 0
Ssl_session_cache_misses | 0
Ssl_session_cache_mode | NONE
Ssl_session_cache_overflows | 0
Ssl_session_cache_size | 0
Ssl_session_cache_timeouts | 0
Ssl_sessions_reused | 0
Ssl_used_session_cache_entries | 0
Ssl_verify_depth | 0
Ssl_verify_mode | 0
Ssl_version |
Table_locks_immediate | 67608
Table_locks_waited | 0
Table_open_cache_hits | 386002409
Table_open_cache_misses | 24810492
Table_open_cache_overflows | 24808485
Tc_log_max_pages_used | 0
Tc_log_page_size | 0
Tc_log_page_waits | 0
Threads_cached | 8
Threads_connected | 10
Threads_created | 1388
Threads_running | 1
Uptime | 1018391
Uptime_since_flush_status | 1018391
I'm somewhat at a loss as to what's happening. Any advice would be very useful!
