Ubuntu 16.04 Server MySql open_file_limit won't go higher than 65536

Question

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?

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:

[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