[Warning] Could not increase number of max_open_files to more than 102400 (request: 640147)

Hi,

I am trying to set open files limits to 102400 on Ubuntu Linux server. Under [mysqld] I added in the `/etc/mysql/mariadb.conf.d/50-server.cnf:

open_files_limit                = 102400

Then I restarted the server:

sudo systemctl restart mysql

But, I am getting a warning:

Jun 25 20:19:29 db-server-1 mariadbd[30101]: 2021-06-25 20:19:29 0 [Warning] Could not increase number of max_open_files to more than 102400 (request: 640147)

I am using MariaDB version 10.5 and how do I resolve this and set up open files limit to 102400 on Ubuntu 20.04 LTS?

On all modern Linux distros, systemd used as init so older limits.conf file will not work. Run the following command to create an override ‚Äčon Ubuntu Linux 15.04 or above:

Step 1 - Set up an override for MariaDB/MySQL

sudo systemctl edit mariadb.service 

Then append the following

[Service]
LimitNOFILE=102400

Save and close the file.

Step 2 - Reload the systemd manager configuration

Then reload disk file:

sudo systemctl daemon-reload

Finally restart the mysql service:

sudo systemctl restart mysql

Step 3 - Verification

We can verify it by running:

mysql -e 'SHOW GLOBAL VARIABLES LIKE "open_files_limit";'
mysql -u user_name -h host_ip -e 'SHOW GLOBAL VARIABLES LIKE "open_files_limit";'

This should result into:

+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| open_files_limit | 102400 |
+------------------+--------+

Please note that the ulimit command is not helpful when using systemd for mysql/MariaDB server. For instance:

runuser -u mysql -- bash -c 'ulimit -n '

Make sure you read systemd - MariaDB Knowledge Base.

Another option to see Max open files for mariadb.

  1. Find mysqld/mariadb PID:
    ps auxf|grep -E -i 'maria|mysql' | grep -v grep
    The PID is 92008:
    mysql 92008 0.8 1.4 6370000 476328 ? Ssl 16:14 0:06 /usr/sbin/mariadbd
  2. List open file limits for that PID:
    cat /proc/92008/limits | grep -i "open"
    You need to get 102400 as set in your override unit:
    Max open files 102400 102400 files

Full table should look like:

cat /proc/92707/limits
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        0                    unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             128107               128107               processes 
Max open files            131167               524288               files     
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       128107               128107               signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us  

Linux sysadmin blog - Linux/Unix Howtos and Tutorials - Linux bash shell scripting wiki