-   Linux - Server (
-   -   I can't increase open files limit for mysql above 1024 (

Tarikc 01-25-2013 02:09 PM

I can't increase open files limit for mysql above 1024
Hey everybody,

I just can't do it, I tried everything - I think - as below:

edited my.cnf:


edited limits.conf

mysql          soft    nofile          24000
mysql          hard    nofile          32000

su - mysql bash
and ulimit -n 5000

All did not went well.

My file-max under proc/sys is good

cat /proc/sys/fs/file-max

Can somebody please tell me what can I do to go over the 1024 limit?

Oh and I've also did it from mysql command line:


set open_files_limit = 5000;
ERROR 1238 (HY000): Variable 'open_files_limit' is a read only variable

Also rebooted the server after setting these limits, but without any help.

Any suggestion please?

jpollard 01-25-2013 03:30 PM

I think you are out of luck. The upper limit for most software using the select system call is 1024 files.

The reason I mention select is that when a server has multiple files open (as in MySQL), it traditionally uses the select system call to multiplex the file I/O.

I don't think MySQL is using the poll system call instead - it takes a bit more programming to get it to work, and select is simpler, though more limited (you have to build an array of file descriptors + events you expect, with select you just specify the fdset (a bit array) - but it only allows for simple input or output events; poll has about 8 different events possible).

grim76 01-25-2013 10:14 PM

You may have to alter the default profile to allow ulimit to be used. What Distro are you using?

jpollard 01-25-2013 11:06 PM

It would appear that the op did that - which is why the ulimit command listed a hard limit of 32000, and a soft limit of 24000.

The only thing left is the limit on files that can be handled by select. And that is not something that can be changed easily.

1 - you have to recompile the kernel to change the allocation size
2 - you have to recompile libc to get the new limit in the include files and binaries
3 - you have to recompile all applications that use select.

The reason this is so tricky is because an fdset is a static structure. Each application compiled gets the size of the structure from the header... and the runtime (and kernel) get that limit built in.

Now applications running with a large open file limit can use more than 1024 files... They just can't use a fd greater than the limit (1024) in the fdset and select system call. This was one of the reasons the poll and epoll system calls were implemented. But a lot of legacy code exists that just don't normally use that many and never migrated to the new system calls.

Tarikc 01-26-2013 12:43 AM

Dear jpollard,

Thank you very much for your replies… I think it is mission impossible for me to do what you have suggested because I'm somehow mmmmm newbie :), and no Tom Cruise :).

It is a live system I can't bring it down.

grim76, thank you for your reply, I'm using Ubuntu 12.04.1 LTS.

I wished there is an easier way to do it.

Thank you all and best regards,

grim76 01-29-2013 09:30 AM

Have you put pam_limits into your login file? There should be a line somewhere that relates to that.

All times are GMT -5. The time now is 02:32 AM.