how to edit open_files_limit in mysql

[dhijrwn]

Hi guys, I'm getting too many open files on mysql server and I tried to edit it but I failed.

ulimit -a
open files 1024

How can I edit it up to 4096?

I tried to use the ulimit -n 4096 and restart mysql
I'm still getting [Warning] Could not increase number of max_open_files to more than 1024 (request: 66575) on systemctl status mysql

[williamconley]

Hi guys, I'm getting too many open files on mysql server and I tried to edit it but I failed.

ulimit -a
open files 1024

How can I edit it up to 4096?

I tried to use the ulimit -n 4096 and restart mysql
I'm still getting [Warning] Could not increase number of max_open_files to more than 1024 (request: 66575) on systemctl status mysql

Please remember to post your entire installer with full version (v9 vs v9.X.X)

I don't see the actual error you are trying to correct. Probably best to post that verbatim along with where you observed the error in question (which log file?).

Note that the error occurring on the mysql server does not mean it's a mysql error, but the fact that you're trying to restart the mysql process to "activate" the change indicates you think that the problem is a mysql configuration problem rather than an OS configuration issue. Thus the request for the actual error and where you observe it.

Until then (in case you want to dive deep) here is a resource on the topic:

https://rtcamp.com/tutorials/linux/incr ... les-limit/

Code: Select all

cat /proc/sys/fs/file-max
ulimit -Hn
ulimit -Sn
cat /etc/sysctl.conf  | grep file-max
cat /etc/security/limits.conf  | grep nofile

[dhijrwn]

I just updated it to v9.0.3.

I was just checking the status of the main mysql database and I got a warning message
[Warning] Could not increase number of max_open_files to more than 1024 (request: 66575)

So I investigate, wondering if this slows down the database
the open_files_limit on the main mysql database was
show variables like '%open_files_limit%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 1024|
+------------------+-------+

upon checking the /etc/my.cnf it was
open_files_limit = 24576

but when I ran this on slave server it is way bigger.
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 66075 |
+------------------+-------+

[williamconley]

1) Where did you see this message? "just checking the status" isn't very detailed.

2) What are the specs for your DB server compared to your Replication server? Please also mention if either of them is virtual (big nono, lol).

3) Attempt

Code: Select all

ulimit -n 30000


4) Check nofile limits

/etc/security/limits.conf
nano /lib/systemd/system/mysql.servic

5) Happy Hunting!

[carpenox]

try putting this at the bottom of your /etc/security/limits.conf file

* soft nofile 1024000
* hard nofile 1024000
* soft nproc 10240
* hard nproc 10240

This should help permanently, i use it on all my setups

Chris

[dhijrwn]

Thank you guys, The values are updated after restarted the mysql service.

/etc/security/limits.conf
* soft nofile 1024000
* hard nofile 1024000
* soft nproc 10240
* hard nproc 10240

/etc/systemd/system/mysql.service
LimitNOFILE=24576
LimitMEMLOCK=24576