MySQL Bugs: #25479: Getting Can't create a new thread (errno 11) after increasing my.cnf values

Bug #25479	Getting Can't create a new thread (errno 11) after increasing my.cnf values
Submitted:	8 Jan 2007 21:07	Modified:	22 Mar 2008 23:21
Reporter:	David Waizer	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.18, 5.0.45	OS:	Solaris (Solaris 8)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
We are getting a Can't create a new thread (errno 11) after 30 - 40 connections to the database, I increased the following values on my.cnf to try fixing the problem but it did not help:

thread_concurrency = 64
max_connections=1250
thread_cache_size=38
back_log=128

The server has 14GB of Ram and 80% is unused, normally we get about 60 - 70 Concurrent connections to the mysql server.

Below is my complete my.cnf:

[client]
port            = 3306
socket          = /opt/xampp/var/mysql/mysql.sock
# server
[mysqld]
port            = 3306
socket          = /opt/xampp/var/mysql/mysql.sock
ft_min_word_len=3
old-passwords=1
join_buffer=956M
max_allowed_packet=164M
thread_concurrency = 64 
default-character-set=latin1
max_connections=1250
query_cache_size=912M
tmp_table_size=2048M
max_heap_table_size=164M
thread_cache_size=38
thread_cache=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=928M
read_buffer_size=11768K
read_rnd_buffer_size=11768K
key_buffer_size=2048M
table_cache=512M
sort_buffer_size=512M
back_log=128
bulk_insert_buffer_size=134217728
query_cache_limit=67108864
log-slow-queries

Thank you.

How to repeat:
As the connections to the mysql server increase the server responds with Can't create a new thread (errno 11)

Thank you for the bug report. Please see bug: http://bugs.mysql.com/bug.php?id=5656 as similar issue.

Miguel, Unfortunately, the bug that you referred me to is not my case. We ALWAYS have 8GB available of RAM and our server has a total of 14GB.

Our variables are set not to use all the available memory.

Thanks for your help.

David

Miguel, Unfortunately, the bug that you referred me to is not my case. We ALWAYS have at least 8GB available of RAM and our server has a total of 14GB.

Our variables are set not to use all the available memory.

Any further feedback would be appreciated.

Thank you.

David Waizer

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Using formula key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections, replaced max_connections with minimum quantity of connections you met problems (30) and your setting I got:

$echo "(2048 * 1024 + (11768 + 512 * 1024) * 30) / (1024 * 1024)" | bc --
17

It means even with 30 connections you can use 17 GB RAM and you only have 14 GB RAM

Sveta, thank you for taking the time to help us sort this out.

I understand your logic but again, it does not apply to this case. Like I posted before, we have had at ALL times at least 8GB of free RAM, it is not a matter of running low on RAM.

A strange scenario ocurred yesterday when we lowered the number of concurrent_threads to 28 instead of 64, our number of treads in mysql increased up to 76, but then we once again started receiving the same error again everytime we would try to establish a new connection (Can't create a new thread (errno 11))

Thank you once again.

David

Please upgrade to current 5.0.27 version, try it and inform us about result.

Please also indicate number of CPU you have

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have the problem here with v5.0.45 running under windows xp sp2, 2G of RAM, MYSQL runs exclusivelly on this machine, nothing else runs there.

The settings are:
key_buffer_size=286MB
read_buffer_size=64KB
sort_buffer_size=256KB
max_connections=63

So, using the above mentioned formula the memory needed is 305MB, *WAY* below the system limits. I cannot reproduce the problem, it happens from time to time but to me it surely looks like another problem than just not meeting the minimum memory amount for the choosen configuration.

Any suggestion?

Ovi,

thank you for the feedback. Please provide full error log file.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".