how to edit open_files_limit in mysql

All installation and configuration problems and questions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

how to edit open_files_limit in mysql

Postby dhijrwn » Wed Jul 13, 2022 9:41 pm

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
Cluster setup i7-9700 cpu @ 3.00ghz 32GB ram 1xDB WEB ARCH 11xTEL 4core
ViciBox v.9.0.3 ISO VERSION:2.14-853a BUILD: 220328-1420
SVN: 3595 DB Schema: 1657 | Asterisk 13.29.2-vici
Zoiper 5 | VICIPhone| No Digium/Sangoma Hardware
dhijrwn
 
Posts: 149
Joined: Tue Jan 07, 2020 6:12 am

Re: how to edit open_files_limit in mysql

Postby williamconley » Thu Jul 14, 2022 4:36 pm

dhijrwn wrote: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
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: how to edit open_files_limit in mysql

Postby dhijrwn » Sat Jul 16, 2022 10:46 pm

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 |
+------------------+-------+
Cluster setup i7-9700 cpu @ 3.00ghz 32GB ram 1xDB WEB ARCH 11xTEL 4core
ViciBox v.9.0.3 ISO VERSION:2.14-853a BUILD: 220328-1420
SVN: 3595 DB Schema: 1657 | Asterisk 13.29.2-vici
Zoiper 5 | VICIPhone| No Digium/Sangoma Hardware
dhijrwn
 
Posts: 149
Joined: Tue Jan 07, 2020 6:12 am

Re: how to edit open_files_limit in mysql

Postby williamconley » Mon Jul 18, 2022 7:20 pm

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!
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: how to edit open_files_limit in mysql

Postby carpenox » Tue Aug 02, 2022 7:50 pm

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
Alma Linux 9.3 | Version: 2.14-911a | SVN Version: 3815 | DB Schema Version: 1710 | Asterisk 18.18.1
www.dialer.one -:- 1-833-DIALER-1 -:- https://linktr.ee/CyburDial -:- WhatsApp: +19549477572 -:- Skype: live:carpenox_3
carpenox
 
Posts: 2230
Joined: Wed Apr 08, 2020 2:02 am
Location: Coral Springs, FL

Re: how to edit open_files_limit in mysql

Postby dhijrwn » Wed Aug 03, 2022 12:43 am

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
Cluster setup i7-9700 cpu @ 3.00ghz 32GB ram 1xDB WEB ARCH 11xTEL 4core
ViciBox v.9.0.3 ISO VERSION:2.14-853a BUILD: 220328-1420
SVN: 3595 DB Schema: 1657 | Asterisk 13.29.2-vici
Zoiper 5 | VICIPhone| No Digium/Sangoma Hardware
dhijrwn
 
Posts: 149
Joined: Tue Jan 07, 2020 6:12 am


Return to Support

Who is online

Users browsing this forum: Bing [Bot] and 87 guests