MySQL Bugs: #35760: Can't create a new thread (errno 12) after 60 connections

Bug #35760	Can't create a new thread (errno 12) after 60 connections
Submitted:	2 Apr 2008 4:40	Modified:	4 Apr 2008 3:36
Reporter:	Qiwen Zhao	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	mysql-5.0.45	OS:	Windows (Windows 2000 Server)
Assigned to:		CPU Architecture:	Any

Description:
After 30-40 users connected to the server, we will get this error, and no one can make new connections.

Here is my my.ini

[client]
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
default-character-set=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=300
query_cache_size=64M
table_cache=1520
tmp_table_size=100M
thread_cache_size=100
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=59M
key_buffer_size=64M
read_buffer_size=256K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_data_home_dir="D:/MySQL Datafiles/"
#skip-innodb
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=10M
innodb_buffer_pool_size=949M
innodb_log_file_size=190M
innodb_thread_concurrency=2

How to repeat:
I guess setup the server as my box and make 40 connections :-).

Thank you for a problem report. Is it possible that you have not enough free space on any of the disks? Please, upload your error log (compressed).

Please, send SHOW GLOBAL STATUS also when 30 or so typical connections run.

The problem is I don't have any errors in the error log. The connection is actually around 60, I have about 40 now, here is the result of

show global status

Aborted_clients	0
Aborted_connects	0
Binlog_cache_disk_use	0
Binlog_cache_use	0
Bytes_received	445186
Bytes_sent	5025187
Com_admin_commands	3
Com_alter_db	0
Com_alter_table	0
Com_analyze	0
Com_backup_table	0
Com_begin	0
Com_call_procedure	0
Com_change_db	3
Com_change_master	0
Com_check	0
Com_checksum	0
Com_commit	0
Com_create_db	0
Com_create_function	0
Com_create_index	0
Com_create_table	0
Com_create_user	0
Com_dealloc_sql	0
Com_delete	2
Com_delete_multi	0
Com_do	0
Com_drop_db	0
Com_drop_function	0
Com_drop_index	0
Com_drop_table	0
Com_drop_user	0
Com_execute_sql	0
Com_flush	0
Com_grant	0
Com_ha_close	0
Com_ha_open	0
Com_ha_read	0
Com_help	0
Com_insert	648
Com_insert_select	0
Com_kill	0
Com_load	0
Com_load_master_data	0
Com_load_master_table	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_rename_table	0
Com_repair	0
Com_replace	0
Com_replace_select	0
Com_reset	0
Com_restore_table	0
Com_revoke	0
Com_revoke_all	0
Com_rollback	0
Com_savepoint	0
Com_select	944
Com_set_option	52
Com_show_binlog_events	0
Com_show_binlogs	0
Com_show_charsets	0
Com_show_collations	0
Com_show_column_types	0
Com_show_create_db	0
Com_show_create_table	0
Com_show_databases	1
Com_show_errors	0
Com_show_fields	0
Com_show_grants	0
Com_show_innodb_status	0
Com_show_keys	0
Com_show_logs	0
Com_show_master_status	0
Com_show_ndb_status	0
Com_show_new_master	0
Com_show_open_tables	0
Com_show_privileges	0
Com_show_processlist	1
Com_show_slave_hosts	0
Com_show_slave_status	0
Com_show_status	1
Com_show_storage_engines	0
Com_show_tables	0
Com_show_triggers	0
Com_show_variables	0
Com_show_warnings	0
Com_slave_start	0
Com_slave_stop	0
Com_stmt_close	0
Com_stmt_execute	0
Com_stmt_fetch	0
Com_stmt_prepare	0
Com_stmt_reset	0
Com_stmt_send_long_data	0
Com_truncate	0
Com_unlock_tables	0
Com_update	399
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
Compression	OFF
Connections	52
Created_tmp_disk_tables	0
Created_tmp_files	5
Created_tmp_tables	14
Delayed_errors	0
Delayed_insert_threads	0
Delayed_writes	0
Flush_commands	1
Handler_commit	997
Handler_delete	0
Handler_discover	0
Handler_prepare	0
Handler_read_first	99
Handler_read_key	28972
Handler_read_next	54269
Handler_read_prev	237
Handler_read_rnd	74
Handler_read_rnd_next	47604
Handler_rollback	52
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	0
Handler_write	1058
Innodb_buffer_pool_pages_data	432
Innodb_buffer_pool_pages_dirty	5
Innodb_buffer_pool_pages_flushed	787
Innodb_buffer_pool_pages_free	60302
Innodb_buffer_pool_pages_latched	0
Innodb_buffer_pool_pages_misc	2
Innodb_buffer_pool_pages_total	60736
Innodb_buffer_pool_read_ahead_rnd	1
Innodb_buffer_pool_read_ahead_seq	1
Innodb_buffer_pool_read_requests	102335
Innodb_buffer_pool_reads	244
Innodb_buffer_pool_wait_free	0
Innodb_buffer_pool_write_requests	6026
Innodb_data_fsyncs	1188
Innodb_data_pending_fsyncs	0
Innodb_data_pending_reads	0
Innodb_data_pending_writes	0
Innodb_data_read	7917568
Innodb_data_reads	360
Innodb_data_writes	1851
Innodb_data_written	27662848
Innodb_dblwr_pages_written	787
Innodb_dblwr_writes	71
Innodb_log_waits	0
Innodb_log_write_requests	3058
Innodb_log_writes	920
Innodb_os_log_fsyncs	985
Innodb_os_log_pending_fsyncs	0
Innodb_os_log_pending_writes	0
Innodb_os_log_written	1841152
Innodb_page_size	16384
Innodb_pages_created	82
Innodb_pages_read	350
Innodb_pages_written	787
Innodb_row_lock_current_waits	0
Innodb_row_lock_time	0
Innodb_row_lock_time_avg	0
Innodb_row_lock_time_max	0
Innodb_row_lock_waits	0
Innodb_rows_deleted	16
Innodb_rows_inserted	596
Innodb_rows_read	106408
Innodb_rows_updated	310
Key_blocks_not_flushed	0
Key_blocks_unused	57388
Key_blocks_used	0
Key_read_requests	0
Key_reads	0
Key_write_requests	0
Key_writes	0
Last_query_cost	0.000000
Max_used_connections	42
Not_flushed_delayed_rows	0
Open_files	12
Open_streams	0
Open_tables	17
Opened_tables	23
Prepared_stmt_count	0
Qcache_free_blocks	19
Qcache_free_memory	66609936
Qcache_hits	417
Qcache_inserts	793
Qcache_lowmem_prunes	0
Qcache_not_cached	153
Qcache_queries_in_cache	49
Qcache_total_blocks	125
Questions	2582
Rpl_status	NULL
Select_full_join	0
Select_full_range_join	0
Select_range	9
Select_range_check	0
Select_scan	100
Slave_open_temp_tables	0
Slave_retried_transactions	0
Slave_running	OFF
Slow_launch_threads	0
Slow_queries	0
Sort_merge_passes	0
Sort_range	0
Sort_rows	268
Sort_scan	16
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_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	1893
Table_locks_waited	0
Tc_log_max_pages_used	0
Tc_log_page_size	0
Tc_log_page_waits	0
Threads_cached	1
Threads_connected	41
Threads_created	42
Threads_running	1
Uptime	1046
Uptime_since_flush_status	1046

Missed one question. I have plenty of disk space on all partitions, 2GB Ram, MySQL is using about 500MB at the time start seeing the error.

Please, try to set

table_cache=100 # was 1520

and check if it will make any difference.

unfortunately, it did not work, here is the result of show global status. 

Aborted_clients	18
Aborted_connects	24
Binlog_cache_disk_use	0
Binlog_cache_use	0
Bytes_received	796045
Bytes_sent	801168326
Com_admin_commands	13
Com_alter_db	0
Com_alter_table	0
Com_analyze	0
Com_backup_table	0
Com_begin	0
Com_call_procedure	0
Com_change_db	0
Com_change_master	0
Com_check	0
Com_checksum	0
Com_commit	0
Com_create_db	0
Com_create_function	0
Com_create_index	0
Com_create_table	0
Com_create_user	0
Com_dealloc_sql	0
Com_delete	0
Com_delete_multi	0
Com_do	0
Com_drop_db	0
Com_drop_function	0
Com_drop_index	0
Com_drop_table	0
Com_drop_user	0
Com_execute_sql	0
Com_flush	0
Com_grant	0
Com_ha_close	0
Com_ha_open	0
Com_ha_read	0
Com_help	0
Com_insert	741
Com_insert_select	0
Com_kill	0
Com_load	0
Com_load_master_data	0
Com_load_master_table	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_rename_table	0
Com_repair	0
Com_replace	0
Com_replace_select	0
Com_reset	0
Com_restore_table	0
Com_revoke	0
Com_revoke_all	0
Com_rollback	0
Com_savepoint	0
Com_select	1493
Com_set_option	120
Com_show_binlog_events	0
Com_show_binlogs	0
Com_show_charsets	0
Com_show_collations	0
Com_show_column_types	0
Com_show_create_db	0
Com_show_create_table	0
Com_show_databases	1
Com_show_errors	0
Com_show_fields	0
Com_show_grants	0
Com_show_innodb_status	0
Com_show_keys	0
Com_show_logs	0
Com_show_master_status	0
Com_show_ndb_status	0
Com_show_new_master	0
Com_show_open_tables	0
Com_show_privileges	0
Com_show_processlist	0
Com_show_slave_hosts	0
Com_show_slave_status	0
Com_show_status	1
Com_show_storage_engines	0
Com_show_tables	0
Com_show_triggers	0
Com_show_variables	0
Com_show_warnings	0
Com_slave_start	0
Com_slave_stop	0
Com_stmt_close	0
Com_stmt_execute	0
Com_stmt_fetch	0
Com_stmt_prepare	0
Com_stmt_reset	0
Com_stmt_send_long_data	0
Com_truncate	0
Com_unlock_tables	0
Com_update	376
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
Compression	OFF
Connections	144
Created_tmp_disk_tables	0
Created_tmp_files	5
Created_tmp_tables	10
Delayed_errors	0
Delayed_insert_threads	0
Delayed_writes	0
Flush_commands	1
Handler_commit	1117
Handler_delete	0
Handler_discover	0
Handler_prepare	0
Handler_read_first	479
Handler_read_key	416969
Handler_read_next	664686
Handler_read_prev	117
Handler_read_rnd	36
Handler_read_rnd_next	4884612
Handler_rollback	0
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	0
Handler_write	1051
Innodb_buffer_pool_pages_data	26749
Innodb_buffer_pool_pages_dirty	0
Innodb_buffer_pool_pages_flushed	683
Innodb_buffer_pool_pages_free	33985
Innodb_buffer_pool_pages_latched	0
Innodb_buffer_pool_pages_misc	2
Innodb_buffer_pool_pages_total	60736
Innodb_buffer_pool_read_ahead_rnd	7
Innodb_buffer_pool_read_ahead_seq	8
Innodb_buffer_pool_read_requests	14484584
Innodb_buffer_pool_reads	26183
Innodb_buffer_pool_wait_free	0
Innodb_buffer_pool_write_requests	6044
Innodb_data_fsyncs	1416
Innodb_data_pending_fsyncs	0
Innodb_data_pending_reads	0
Innodb_data_pending_writes	0
Innodb_data_read	439914496
Innodb_data_reads	26727
Innodb_data_writes	1929
Innodb_data_written	23929856
Innodb_dblwr_pages_written	683
Innodb_dblwr_writes	94
Innodb_log_waits	0
Innodb_log_write_requests	2100
Innodb_log_writes	1067
Innodb_os_log_fsyncs	1152
Innodb_os_log_pending_fsyncs	0
Innodb_os_log_pending_writes	0
Innodb_os_log_written	1505792
Innodb_page_size	16384
Innodb_pages_created	32
Innodb_pages_read	26717
Innodb_pages_written	683
Innodb_row_lock_current_waits	0
Innodb_row_lock_time	0
Innodb_row_lock_time_avg	0
Innodb_row_lock_time_max	0
Innodb_row_lock_waits	0
Innodb_rows_deleted	0
Innodb_rows_inserted	741
Innodb_rows_read	5657938
Innodb_rows_updated	313
Key_blocks_not_flushed	0
Key_blocks_unused	57388
Key_blocks_used	0
Key_read_requests	0
Key_reads	0
Key_write_requests	0
Key_writes	0
Last_query_cost	0.000000
Max_used_connections	68
Not_flushed_delayed_rows	0
Open_files	0
Open_streams	0
Open_tables	6
Opened_tables	52
Prepared_stmt_count	0
Qcache_free_blocks	33
Qcache_free_memory	66619960
Qcache_hits	1424
Qcache_inserts	1008
Qcache_lowmem_prunes	0
Qcache_not_cached	487
Qcache_queries_in_cache	125
Qcache_total_blocks	295
Questions	4205
Rpl_status	NULL
Select_full_join	0
Select_full_range_join	0
Select_range	20
Select_range_check	0
Select_scan	474
Slave_open_temp_tables	0
Slave_retried_transactions	0
Slave_running	OFF
Slow_launch_threads	0
Slow_queries	1
Sort_merge_passes	0
Sort_range	0
Sort_rows	703
Sort_scan	41
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_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	2680
Table_locks_waited	0
Tc_log_max_pages_used	0
Tc_log_page_size	0
Tc_log_page_waits	0
Threads_cached	4
Threads_connected	63
Threads_created	90
Threads_running	1
Uptime	33068
Uptime_since_flush_status	33068

This seems to be the same one as bug 20815, after upgraded to 5051a, the problem is gone.