Bug #13335 running out of threads at just over 1000 connections
Submitted: 20 Sep 2005 5:26 Modified: 10 Mar 2006 12:34
Reporter: Robin McMillon Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.14 OS:Linux (RedHat Enterprise Linux AS 3)
Assigned to: CPU Architecture:Any

[20 Sep 2005 5:26] Robin McMillon
Description:
Whenever we reach between 1000 and 1014 concurrent connections on our MySQL Server we begin getting the following error:

Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

Some threads get created and some get blocked with the above message but the most connections we can make is 1014 - even users with the SUPER privilege get the errno 11 error intermittently (we have lots of short-lived connections).

Because we are running the statically compiled binary with MySQL's patched LinuxThreads and glibc, we expected the stated performance of up to 4000 concurrent connections (http://dev.mysql.com/doc/mysql/en/too-many-connections.html).

The setup:
----------------
Dell 2650
2 x 3.06 GHz Xeon processors
6 GB memory (PAE extensions enabled)
4 disks in a RAID 10

RedHat Enterprise Linux AS 3 (note: uses Native POSIX Thread Library instead of Linux Threads - they backported it into the 2.4 kernel for this release)
mysql-4.1.14: standard version, statically compiled binary from MySQL AB with MySQL's statically linked and patched LinuxThreads and glibc libraries.
We're using all InnoDB tables. 

Using vmstat, memory usage seems to be staying below 2GB, usually in the 1.5-1.6 range, even during the bad times.  However, here's some example output from 'mysqladmin debug':

system okay (less than 1000 connections)
------------------------------------------------------------
Status information:

Current dir: /usr/local/mysql/data/
Running threads: 265  Stack size: 126976
Current locks:
lock: 0x9acf4a8:

lock: 0x967bb60:

lock: 0x9545760:

lock: 0x9571378:

lock: 0x6c001150: write
write     : 0xae4bfcc (30834875:5); 0x9be6a3c (30732484:5); 0x9c0192c (30843095:5); 0x2b682254 (30736414:5); 0xacf0ff4 (23502893:5); 0xb2f3184 (30867686:5); 0xb2f15f4 (30855389:5);

Key caches:
default
Buffer_size:      33554432
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:             3
not flushed:             0
w_requests:              0
writes:                  0
r_requests:              6
reads:                   3

handler status:
read_key:       258605
read_next:       51901
read_rnd             0
read_first:        739
write:           46559
delete               0
update:              0

Table status:
Opened tables:        656
Open tables:          645
Open files:             3
Open streams:           0

Alarm status:
Active alarms:   257
Max used alarms: 586
Next alarm time: 27532

Thread database.table_name          Locked/Waiting        Lock_type

49431   horde_sessions.horde_sessionLocked - write        High priority write lock

Memory status:
Non-mmapped space allocated from system: 47964160
Number of free chunks:                   887
Number of fastbin blocks:                0
Number of mmapped regions:               283
Space in mmapped regions:                1536024576
Maximum total allocated space:           0
Space available in freed fastbin blocks: 0
Total allocated space:                   24657672
Total free space:                        23306488
Top-most, releasable space:              936
Estimated memory (with thread stack):    1617637376
--------------------------------------------------------------------------------

system having trouble creating threads
--------------------------------------------------------
Status information:

Current dir: /usr/local/mysql/data/
Running threads: 1006  Stack size: 126976
Current locks:
lock: 0x58d6fd20:

lock: 0x6c0d9e90:

lock: 0x9acf4a8:

lock: 0x967bb60:

lock: 0x9545760:

lock: 0x9571378:

lock: 0x6c001150:

Key caches:
default
Buffer_size:      33554432
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:             3
not flushed:             0
w_requests:              0
writes:                  0
r_requests:              0
reads:                   0

handler status:
read_key:      3237181
read_next:      583237
read_rnd             0
read_first:       9323
write:          616310
delete               0
update:              0

Table status:
Opened tables:          3
Open tables:          649
Open files:             3
Open streams:           0

Alarm status:
Active alarms:   1004
Max used alarms: 1013
Next alarm time: 15767

Memory status:
Non-mmapped space allocated from system: 9592832
Number of free chunks:                   331
Number of fastbin blocks:                0
Number of mmapped regions:               1018
Space in mmapped regions:                -1984966656
Maximum total allocated space:           0
Space available in freed fastbin blocks: 0
Total allocated space:                   8246928
Total free space:                        1345904
Top-most, releasable space:              102296
Estimated memory (with thread stack):    -1847635968

==============================================

Below is our my.cnf file (modified version of the innodb-heavy config file supplied with the server):

[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock

back_log = 200
#skip-networking
max_connections = 2000
max_connect_errors = 10
table_cache = 2048
#external-locking
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache = 64
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
#memlock
default_table_type = INNODB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log_bin
#log_slave_updates
#log
log_warnings
log_slow_queries
long_query_time = 2
log_long_format
#tmpdir = /tmp
server-id = 1

#*** MyISAM Specific options

key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
#bulk_insert_buffer_size = 64M
#myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

# *** BDB Specific options ***
skip-bdb

# *** INNODB Specific options ***

#skip-innodb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1G
innodb_data_file_path = webmailv2:500M:autoextend
innodb_data_home_dir = /var/mysql/innodb_data
innodb_file_io_threads = 4
#innodb_force_recovery=1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
#innodb_fast_shutdown
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 120
innodb_safe_binlog
sync_binlog=1
binlog_ignore_db=horde_sessions

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open-files-limit = 8192

How to repeat:
Try to make over 1030 (we end up with close to 1200) concurrent connections to the server.   I'm not sure exactly what the exact number of connections allowed through is - our maximum ever is 1014 although depending on what else is going on, who else is logged in, we've definitely gotten bad results with closer to 1000.
[20 Sep 2005 17:15] Hartmut Holzgraefe
You are most likely hiting a ulimit restriction (eg. # of open files or # of processes),
can you please add "ulimit -a" output so that we can check this?
[21 Sep 2005 15:40] Robin McMillon
ulimit -a

as root (runs mysqhd_safe):
--------------------------------------------
core file size        (blocks, -c) 0
data seg size         (kbytes, -d) unlimited
file size             (blocks, -f) unlimited
max locked memory     (kbytes, -l) 4
max memory size       (kbytes, -m) unlimited
open files                    (-n) 1024
pipe size          (512 bytes, -p) 8
stack size            (kbytes, -s) 10240
cpu time             (seconds, -t) unlimited
max user processes            (-u) 7168
virtual memory        (kbytes, -v) unlimited
---------------------------------------------

as mysql (runs mysqld):
---------------------------------------------
core file size        (blocks, -c) 0
data seg size         (kbytes, -d) unlimited
file size             (blocks, -f) unlimited
max locked memory     (kbytes, -l) 4
max memory size       (kbytes, -m) unlimited
open files                    (-n) 1024
pipe size          (512 bytes, -p) 8
stack size            (kbytes, -s) 10240
cpu time             (seconds, -t) unlimited
max user processes            (-u) 7168
virtual memory        (kbytes, -v) unlimited
---------------------------------------------

However, in mysqld_safe, the following code is run:
---------------------------------------------
USER_OPTION=""
if test -w / -o "$USER" = "root"
then
  if test "$user" != "root" -o $SET_USER = 1
  then
    USER_OPTION="--user=$user"
  fi
  # If we are root, change the err log to the right user.
  touch $err_log; chown $user $err_log
  if test -n "$open_files"
  then
    ulimit -n $open_files
    args="--open-files-limit=$open_files $args"
  fi
  if test -n "$core_file_size"
  then
    ulimit -c $core_file_size
  fi
fi
---------------------------------------------

From our my.cnf file:
[mysqld_safe]
open-files-limit = 8192

and a 'SHOW VARIABLES;' confirms that this is the setting in mysql.

Therefore, I do not understand why we would be running up against the maximum number of open files (8192) or the maximum number of open processes (7168).
[25 Oct 2005 8:30] Hartmut Holzgraefe
what kind of mysql package are you using?
static, dynamic, built by mysql, built by redhat, compiled yourself?

the linuxthreads thread library has a hard limit of 1024 threads per process
by default, in our own static binaries we have patched this up to 4096,
looks as if you are hitting this hard limit
[26 Nov 2005 0:00] Bugs System
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".
[28 Nov 2005 16:41] Robin McMillon
To answer your questions (again), here is the information provided in my original post:

>what kind of mysql package are you using?
>static, dynamic, built by mysql, built by redhat, compiled yourself?

static
built by MySQL AB
version 4.1.14
using the version built by MySQL AB with the patched Linux Threads library that you and MySQL's website claim allows up to 4096 connections

Are you claiming that even though I am using *your* (MySQL's) patched version, I am still hitting the limit in the system Linux Threads?
[28 Nov 2005 16:49] Robin McMillon
Sorry, apparently I had two accounts:

To answer your questions (again), here is the information provided in my
original post:

>what kind of mysql package are you using?
>static, dynamic, built by mysql, built by redhat, compiled yourself?

static
built by MySQL AB
version 4.1.14
using the version built by MySQL AB with the patched Linux Threads library that
you and MySQL's website claim allows up to 4096 connections

Are you claiming that even though I am using *your* (MySQL's) patched version, I
am still hitting the limit in the system Linux Threads?
[10 Feb 2006 12:34] Valeriy Kravchuk
Just want to clarify current status of this report. If you still suffer from this problem, please, send the top command results (and SHOW PROCESSLIST results) when it will happen next time.

Please, try to use newer version, 4.1.18 (our static build) also.
[11 Mar 2006 0:00] Bugs System
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".
[15 Jun 2006 1:15] Andrew Jones
I was having this same issue and have solved it by raising the hard limit of open files in /etc/security/limits.conf
Even if you raise the value of open files in the startup scripts, It still cannot go above the hard limit, which is 1024 by default.
/etc/security/limits.conf:
mysql           soft    nofile          4096
mysql           hard    nofile          4096

This seems not to be a mysql bug, but rather OS imposed limits.

I wrote about it here:
http://www.haqthegibson.com/article/34
Cheers,
Jonesy
[15 Jun 2006 1:17] Andrew Jones
Sorry, that snippet of /etc/security/limits.conf in my last comment should have been more clearly described, that is what I added to increase the limits.

Btw, that was on RedHat Enterprise Linux but applies to other Linux as well.

Jonesy
[22 Mar 2010 4:24] Mark Garcia
I had this problem before .... I've set key_buffer_size to something lower like between 8 to 64M and set thread_stack = 356k and that worked for me ....

Hope this helps