msgbartop
MAC OS X, Linux, Windows and other IT Tips and Tricks
msgbarbottom

30 Oct 16 Resolving Mysql error: Too many open files

Introduction:
As I upgraded from Mysql 5.5 to 5.6 suddenly some sites were showing the following error:
...... Too many open files
The issue has to do with the present limitations given to the system and PAM system to open max 1024 files. After doing some research I found this site below here which is in German in which the following is explained:
https://www.filewalker.de/ulimit-mysql-open_files_limit/

STEPS:
Check the files-open limits or running mysql server:
mysql -p -u root
mysql> SHOW VARIABLES LIKE 'open%';

The very possible output:
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 1024 |
+------------------+-------+
1 row in set (0.00 sec)

That means that Mysql server gets to open maximum 1024 which seems too little for Mysql 5.6.

Raising this limit
Edit the file /etc/security/limits.conf and add the following lines:
mysql hard nofile 65535
mysql soft nofile 65535

This will raise the limit of open files to 65535 for the user mysql only.
If you want to rise this limit for all users then replace the word mysql for *
eg.
* hard nofile 65535
* soft nofile 65535

And according to this site edit the file /etc/pam.d/common-session and add this line at the end:
session required pam_limits.so
Note: I’m not sure this step is really needed though. Some people did try without it and it also worked. For me, in Debian Wheezy, I had to do this otherwise I was still getting the error.

For systems that run systemd instead of InitV do the following:
Edit file /usr/lib/systemd/system/mysqld.service
Add these 2 lines at the end:
LimitNOFILE=65535
LimitNPROC=65535

Restart Mysql server and test it again
service mysql restart
mysql -p -u root
mysql>> SHOW VARIABLES LIKE 'open%';

The hopeful output:
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+
1 row in set (0.00 sec)

This error should no more appear.

Reader's Comments

  1.    

    Thanks a lot You saved my day

    Reply to this comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: