MySQL max_open_files more than 1024
Solution 1
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
Solution 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
Solution 3
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
Solution 4
You can see the official instruction in the mariadb.service file;
[[email protected] /]# 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...
Related videos on Youtube
user977828
Updated on September 18, 2022Comments
-
user977828 over 1 year
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?
-
text over 9 yearsHave 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?
-
user977828 over 9 yearsI 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?
-
-
sivann about 9 yearsPlease note that at least on systemd version 209, infinity means 65535. If you want more than that, just issue the number, not infinity.
-
IcarusNM about 9 yearsFor 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!
-
Ty. over 8 yearsThis doesn't work in Ubuntu 14.04 with MySQL 5.7. The service files don't exist and the systemctl package isn't installed.
-
Luka over 6 yearsCheck
/etc/systemd/system/mysql.service.d/limits.conf
or/etc/systemd/system/mariadb.service.d/limits.conf
It worked for me flawlessly -
DanMan almost 6 yearsI didn't need to reboot on Fedora 28. It only asked me to run
systemctl daemon-reload
when restarting MariaDB.