I'm running Ubuntu 16.04 Server on XenServer and I'm running into an issue with MySql's open file limit.

Here's what I've done so far:

sudo nano /etc/security/limits.conf (reference)

* soft nofile 1024000
* hard nofile 1024000
* soft nproc 102400
* hard nproc 102400
mysql soft nofile 1024000
mysql hard nofile 1024000

sudo nano /etc/init/mysql.conf (reference)

limit nofile 1024000 1024000
limit nproc 102400 102400

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf (reference)

open_files_limit = 1024000

open_files_limit = 1024000

When the above didn't work, I went on to the the following:

sudo nano /etc/sysctl.conf

fs.file-max = 1024000

sudo nano /etc/pam.d/common-session

session required pam_limits.so

sudo nano /etc/pam.d/common-session-noninteractive

session required pam_limits.so

sudo nano /lib/systemd/system/mysql.service


When I log into my user account everything seems fine:

ulimit -Hn
ulimit -Sn

If I login as mysql it looks good too:

mysql@server:~$ ulimit -Hn
mysql@server:~$ ulimit -Sn

However, when I look at the proc:

ps -ef | grep mysql
cat /proc/1023/limits | grep open
Max open files  65536 65536 files   

Or when I look at it in MySql:

mysql> show global variables like 'open%';
| Variable_name    | Value |
| open_files_limit | 65536 |

From the logs (/var/log/mysql/error.log ):

2016-07-25T05:44:35.453668Z 0 [Warning] Could not increase number of max_open_files to more than 65536 (request: 1024000)

I'm completely out of ideas here. In the beginning I did start with open_files_limit at 1024, and one of the above must have changed it, but I need it to go higher. I'm hitting this limit already as I have a lot of databases and tables that sometimes have a lot of partitions.

I've even tried numbers less aggressive than 1024000, with no luck.

Any ideas out there?

1 Answer 1


This worked for me on Ubuntu Xenial 16.04:

Create the dir /etc/systemd/system/mysql.service.d

Put in /etc/systemd/system/mysql.service.d/override.conf:


Now execute

systemctl daemon-reload
systemctl restart mysql.service

Yes indeed, LimitNOFILE=infinity actually seems to set it to 65536.

You can validate the above after starting MySQL by doing:

cat /proc/$(pgrep mysql)/limits | grep files
  • 1
    Since I came here first but felt a bit unhappy with adjusting some system file I found another thread explaining how to fix w/o risking service file overwritten on next upgrade: stackoverflow.com/questions/27849331/… Jun 6, 2017 at 7:27
  • Thanks @cepharum. I updated the answer. Indeed we already experienced problems on our servers because we updated the repo file /lib/systemd/system/mysql.service. So we already moved to the method you refered to. I did forget to update my answer here. Thanks again for the reminder. Jun 6, 2017 at 8:18
  • This command does not work: cat /proc/$(pgrep mysql)/limits | grep files
    – Basil A
    Jan 24, 2018 at 8:52
  • 1
    @BasilA This command only works once MySQL is running. Just tested it on Ubuntu 16.04. Jan 24, 2018 at 12:17
  • Can you help me. I tried make over 102400 , Like 10 mln it not possible. I need somethink do in kernel? i have pagination over 99.biliosn records Apr 28, 2020 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.