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