0

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!

15
  • "250 sessions/thread" -- Please elaborate on what it going on! – Rick James Dec 22 '18 at 22:49
  • 1
    Here's my understanding... Every new connection gets its own thread. Assuming no thread pool is in use, every new connection makes for the creation of a new thread, and a disconnect causes for that thread's destruction. Hence, there is a 1-1 mapping between connections and active threads. But then, there is a thread pool, which means there can be threads which are not associated with any connection. So, the number of threads is greater than or equal to the number of connections. I'm also confused. – Shawn_M Dec 23 '18 at 16:32
  • 1
    @WilsonHauck, I should be available Monday Dec 24th in the afternoon after 1:00 PM CST. – Shawn_M Dec 24 '18 at 2:23
  • 1
    @Wilson Hauck, thanks for your followup. Since it's a prod server, haven't had a chance to apply your suggestions yet. I should be able to do so this weekend. I'll be happy to have a call with you. Tried to call, but we'd missed each other. Let's try for 1:30 CST Monday the 31st? – Shawn_M Dec 26 '18 at 22:19
  • 1
    @Wilson Hauck - I've posted the global status. I'm unable to post the global variables as it will bust the 30000 character limit for the stack post. – Shawn_M Jan 4 '19 at 12:47
0

Since the connections are not disconnecting on their own accord, change the global wait_timeout. This will indirectly decrease the number of idle threads and the pressure on max_connections.

1
  • @Shawn_M Per RJ comment of Dec 24, suggestion for your customer's my.cnf [mysqld] section, wait_timeout=7200 # from 28800 for 2 hour vs 8 hour disconnect. – Wilson Hauck Jan 10 '19 at 21:40
0

Suggestions to consider for your my.ini [mysqld] section

join_buffer_size=256K  # from 250M to reduce RAM request
read_buffer_size=128K  # from 62.50M to reduce RAM request
read_rnd_buffer_size=256K  # from 250M to reduce RAM request
sort_buffer_size=2M  # from 250M to reduce RAM request

the first 4 are RAM requests PER connection and your present config is 100X default for most of them and causing the server to EXCEED AVAILABLE RAM. You may detect this in your error log of MySQL.

For additional suggestions, view my profile, Network profile for contact info and get in touch via Skype, please.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.