16

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)

[mysqld_safe]
open_files_limit = 1024000


[mysqld]
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

LimitNOFILE=infinity
LimitMEMLOCK=infinity

When I log into my user account everything seems fine:

ulimit -Hn
1024000
ulimit -Sn
1024000

If I login as mysql it looks good too:

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

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?

24

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:

[Service]
LimitNOFILE=1024000

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/… – Thomas Urban Jun 6 '17 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. – Jeroen Vermeulen - MageHost Jun 6 '17 at 8:18
  • This command does not work: cat /proc/$(pgrep mysql)/limits | grep files – Basil A Jan 24 '18 at 8:52
  • 1
    @BasilA This command only works once MySQL is running. Just tested it on Ubuntu 16.04. – Jeroen Vermeulen - MageHost Jan 24 '18 at 12:17

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.