11

While starting MariaDB I got [Warning] Could not increase number of max_open_files to more than 1024 (request: 4607)

$ sudo systemctl status mysqld
● mysqld.service - MariaDB database server
  Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)
  Active: activating (start-post) since Tue 2014-08-26 14:12:01 EST; 2s ago
Main PID: 8790 (mysqld);         : 8791 (mysqld-post)
  CGroup: /system.slice/mysqld.service
      ├─8790 /usr/bin/mysqld --pid-file=/run/mysqld/mysqld.pid
      └─control
    ├─8791 /bin/sh /usr/bin/mysqld-post
    └─8841 sleep 1

Aug 26 14:12:01 acpfg mysqld[8790]: 140826 14:12:01 [Warning] Could not increase number of max_open_files to more than 1024 (request: 4607)

I tried unsuccessfully to fix the problem with max_open_files inside this file:

$ sudo nano /etc/security/limits.conf 
mysql           hard    nofile          8192
mysql           soft    nofile          1200

I even restarted the computer again, but I got the same problem.

The /etc/mysql/my.cnf looks like this:

[mysql]

# CLIENT #
port                           = 3306
socket                         = /home/u/tmp/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /home/u/tmp/mysql/mysql.sock
pid-file                       = /home/u/tmp/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now                 = 1
innodb                         = FORCE
innodb-strict-mode             = 1

# DATA STORAGE #
datadir                        = /home/u/tmp/mysql/

# BINARY LOGGING #
log-bin                        = /home/u/tmp/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2G

# LOGGING #
log-error                      = /home/u/tmp/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /home/u/tmp/mysql/mysql-slow.log

How is it possible to fix the problem with max_open_files?

  • Have you restarted mySql since making the limits change? These things generally do not simply propogate from a file change, the process usually has to be restarted to pick up the change. Also you can verify the limits using the ulimit command. Have you rebooted since the change? – mdpc Aug 26 '14 at 6:10
  • I restarted the computer after, I changed the limits. Looking at the ulimit output my changes did not worked out: $ ulimit unlimited $ ulimit -Sa | grep "open files" open files (-n) 1024 $ ulimit -Ha | grep "open files" open files (-n) 4096. What could get wrong? – user977828 Aug 26 '14 at 10:15
16

Edit /etc/security/limits.conf and add the following lines

mysql soft nofile 65535
mysql hard nofile 65535

then reboot.

Then edit /usr/lib/systemd/system/mysqld.service or /usr/lib/systemd/system/mariadb.service and add

LimitNOFILE=infinity
LimitMEMLOCK=infinity

Then restart the db service:

systemctl reload mariadb.service
  • 1
    Please note that at least on systemd version 209, infinity means 65535. If you want more than that, just issue the number, not infinity. – sivann May 8 '15 at 9:17
  • 3
    For Mariadb 5.5 in RHEL 7, at least, the comments in that file (/usr/lib/systemd/system/mariadb.service) warn you not to edit that file itself, but rather to create a service.d directory containing a file like: /etc/systemd/system/mariadb.service.d/foo.conf. BE SURE TO ADD "[Service]" at the top of that file, before those two Limit lines. It also advises "systemctl --system daemon-reload" after any change. These details drove me nuts for an additional hour of hair pulling! – IcarusNM May 15 '15 at 17:01
  • This doesn't work in Ubuntu 14.04 with MySQL 5.7. The service files don't exist and the systemctl package isn't installed. – Ty. Feb 14 '16 at 20:31
  • Check /etc/systemd/system/mysql.service.d/limits.conf or /etc/systemd/system/mariadb.service.d/limits.conf It worked for me flawlessly – Luka Sep 21 '17 at 19:15
2

Another reason is that the:
You must pay attention to the table_open_cach

mysql code in mysqld.cc

wanted_files= 10 + max_connections + table_cache_size * 2;

try with lower table_open_cach value

1

You can see the official instruction in the mariadb.service file;

[root@mariadb5.5 /]# cat /usr/lib/systemd/system/mariadb.service | grep exam -A 5
# For example, if you want to increase mariadb's open-files-limit to 10000,
# you need to increase systemd's LimitNOFILE setting, so create a file named
# "/etc/systemd/system/mariadb.service.d/limits.conf" containing:
#       [Service]
#       LimitNOFILE=10000

It needs to reboot your OS. Though I think this should be written in the official manual...

  • 1
    I didn't need to reboot on Fedora 28. It only asked me to run systemctl daemon-reload when restarting MariaDB. – DanMan Aug 7 '18 at 10:45
0

I had the same issue with Ubuntu 15.10 and mysql and fixed it with the previous answer with some minor differences.

I first changed /etc/security/limits.conf as above.

I added (nothing more)

LimitNOFILE=infinity

to /lib/systemd/system/mysql.service (little location difference)

and then did

systemctl daemon-reload

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.