17

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?

Community
  • 1
J Pierret
  • 171
  • 1
  • 1
  • 6

1 Answers1

28

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
Jeroen Vermeulen - MageHost
  • 654
  • 5
  • 15
  • 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: https://stackoverflow.com/questions/27849331/how-to-set-nginx-max-open-files/36423859#36423859 – Thomas Urban Jun 06 '17 at 07: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 06 '17 at 08:18
  • This command does not work: cat /proc/$(pgrep mysql)/limits | grep files – Basil A Jan 24 '18 at 08: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
  • 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 – Kamil Dąbrowski Apr 28 '20 at 13:49