Bug #35209 maximum number of threads with MySQL binary RPMs
Submitted: 11 Mar 2008 12:53 Modified: 19 Mar 2008 13:32
Reporter: Pavel Arnošt Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.45 OS:Linux (Trustix Secure Linux 3.0 / 2.6.19.2-1trsmp / glibc-2.3.5-10tr)
Assigned to: CPU Architecture:Any
Tags: count, limit, threads

[11 Mar 2008 12:53] Pavel Arnošt
Description:
I have a problem with getting over 1000 threads with statically linked RPM packages from MySQL AB. These packages should be capped to 4096 threads, but after reaching about 1000 threads, new connections are refused with "can't create a new thread".

MySQL is running on a dedicated 32-bit system with 4 GB RAM. I think that ulimit restrictions are not a problem, PAM limits are not used on this system (at least su does not use them). I would be grateful for any advice.

ulimit -a output:

core file size        (blocks, -c) 0
data seg size         (kbytes, -d) unlimited
file size             (blocks, -f) unlimited
max locked memory     (kbytes, -l) 32
max memory size       (kbytes, -m) unlimited
open files                    (-n) 1024
pipe size          (512 bytes, -p) 8
stack size            (kbytes, -s) 8192
cpu time             (seconds, -t) unlimited
max user processes            (-u) 32768
virtual memory        (kbytes, -v) unlimited

How to repeat:
Try to make over 1000 connections to the server.
[11 Mar 2008 12:54] Pavel Arnošt
show variables output

Attachment: variables.txt (text/plain), 17.46 KiB.

[11 Mar 2008 21:41] Valeriy Kravchuk
Sorry, but ulimit -a clearly says:

open files                    (-n) 1024

Please, try to exacute "ulimit -n unlimited" and then check if you still will be limited with 1000 threads.
[11 Mar 2008 22:08] Pavel Arnošt
Sorry not to mention it, but "show variables" command shows:

open_files_limit                | 65535

and in server log there is:

[Warning] Changed limits: max_open_files: 65535  max_connections: 2048  table_cache: 31738

so i thought that "open files limit" is not a problem.

Should i still try to change ulimit limits?
[19 Mar 2008 13:32] Susanne Ebrecht
Pavel,

I will set this to "not a bug". Please feel free to reopen it again when you still have problems after reading my explanations and follow my instructions:

The maximum of threads depends not on MySQL it depends on your operating system. Better of the thread library of your operating system. On Linux usually this is libpthread.

ulimit -a shows you the maximum of threads that are possible on your system. Usually this is 1024 on Linux.

And usually besides root no user can get more.

you can try:
$ ulimit -n 2000
or whatever you wish but most times you just will get a "permission denied" error message.

To get more, you have to add the following at /etc/security/limits.conf

* soft nofile 2000
* hard nofile 2000

* means all users ... of course you can add here specific usernames
and 2000 is just my example if you want to get 2000. Of course you have to type here the number of your wishes.

Now you have to login again. Otherwise this won't accepted.

When you type ulimit -a now, you will see that your open files limit is like you expect it.

After this you can start the MySQL daemon again and when you type:
mysql> show variables like '%open_file%';
you will see that the system will take this number.

Consider, the user who starts the deamon must have this higher value of open files at ulimit -a.