Change open files limit mysql

zye

Well-Known Member
Dec 6, 2002
123
4
168
Hi

i am not able to change mysql open_files_limit anything other than 65536 ( sql stops responding if it hits the limit )

i edited /etc/my.cnf tried several different values beneath and above the 65536 but there is no change

running
CLOUDLINUX 7.2 x86_64 standard – WHM 56.0 (build 14)

DELL PowerEdge R730
64 GB Ram / CPU E5-2630 v3

sql: [ERROR] /usr/sbin/mysqld: Can't open file: './db.frm' (errno: 24 - Too many open files)

thx
 
  • Like
Reactions: sandra lynn

ssfred

Well-Known Member
Jan 6, 2012
65
4
58
India
cPanel Access Level
Root Administrator
Twitter
Hello :)

Check whether you actually exceeds the open_files limit by executing the following SQL queries

SHOW GLOBAL STATUS LIKE 'Open_files';

The above command will give you the number of currently open files. Also confirm the maximum value set for the open_files

SHOW VARIABLES LIKE 'open_files_limit';

If they are with in the limits, check the mysql log for any relevant information.
 
  • Like
Reactions: sandra lynn

zye

Well-Known Member
Dec 6, 2002
123
4
168
its running stable since sunday - but i would likt to raise the limit


Server version: 5.6.30 MySQL Community Server (GPL)

mysql> SHOW GLOBAL STATUS LIKE 'Open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 38972 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 65536 |
+------------------+-------+
1 row in set (0.01 sec)

i just restarted sql server
mysql (/bin/sh /usr/bin/mysqld_safe) is running as mysql with PID 342544 (systemd check method).
mysql (/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/servername.com.err --open-files-limit=150000 --pid-file=/var/lib/mysql/servername.pid) is running as mysql with PID 342917 (systemd check method).

the startup with 150000 open files is correct but there is still 65536 open files max
 
Last edited:

ssfred

Well-Known Member
Jan 6, 2012
65
4
58
India
cPanel Access Level
Root Administrator
Twitter
Hello
Glad that your server is stable now. The limit can't be increased dynamically and hence I would suggest you to stop the server first and then modify the /etc/my.cnf file with the altered value. After that restart the server and check the new limit using the SQL query given above.

You may use the official documentation MySQL :: MySQL 5.7 Reference Manual :: 6.1.3 Server Command Options for quick reference during the process.
 

zye

Well-Known Member
Dec 6, 2002
123
4
168
hi - i know that i have to restart to get new config loaded :) thx though
 

zye

Well-Known Member
Dec 6, 2002
123
4
168
ok now i get a serious problem - sql server just crashed


2016-05-17 16:48:24 771169 [Warning] Buffered warning: Could not increase number of max_open_files to more than 65536 (request: 331582)

how to increase the open files limit?
 

zye

Well-Known Member
Dec 6, 2002
123
4
168
solved

Solution:

the limitation is being set by the systemd service file:
====
[17:37:01 hostname [email protected] ~]cPs# grep -i limit /etc/systemd/system/mysql.service
LimitNOFILE=65536
====

To remove that limit, you can replace the numerical value with 'infinity' and reload the systemd service file, then restart MySQL.

If you want to do that, you could try executing the following command:
====
cp -av /etc/systemd/system/mysql.service{,.7550513.bak}; sed -i 's/65536/infinity/g' /etc/systemd/system/mysql.service; systemctl daemon-reload; /scripts/restartsrv_mysql;
====

# systemctl daemon-reload


/scripts/restartsrv_mysql
 
  • Like
Reactions: ssfred

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,237
463
Hello,

I'm happy to see you were able to address the issue. Thank you for updating us with the outcome.
 

zye

Well-Known Member
Dec 6, 2002
123
4
168
cpupdate today did overwrite the file /etc/systemd/system/mysql.service and sql crashed again. after searching i did cp -av /etc/systemd/system/mysql.service{,.7550513.bak}; sed -i 's/65536/infinity/g' /etc/systemd/system/mysql.service; systemctl daemon-reload; /scripts/restartsrv_mysql;
====

# systemctl daemon-reload


/scripts/restartsrv_mysql

and now the process in ps axuf shows
mysql 90016 0.0 0.0 113124 1580 ? Ss 10:18 0:00 /bin/sh /usr/bin/mysqld_safe
mysql 90410 18.6 10.5 19657988 6919108 ? Sl 10:18 2:28 \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/xxx.xxx.com.err --open-files-limit=250000

but

mysql> SHOW VARIABLES LIKE '%files%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| character_set_filesystem | binary |
| innodb_log_files_in_group | 2 |
| innodb_open_files | 32663 |
| keep_files_on_create | OFF |
| large_files_support | ON |
| open_files_limit | 65536 |


how can i get open_files_limit | 65536 to 250000 as it is in my /etc/my.cnf file ??


error log shows
(errno: 24 - Too many open files)


and why the heck is the file /etc/systemd/system/mysql.service overwritten by cpupdate??? this sucks really big time!!!
 

zye

Well-Known Member
Dec 6, 2002
123
4
168
solution by support ticket - thx for the fast response!


Hello,

Something is setting the open files ulimit to 65536
# cat /proc/$(pidof mysqld)/limits |grep files
Max open files 65536 65536 files

Apparently, the programmers of systemd version 219 decided it was clever to make 'infinity' mean '65536' instead of unlimited. I have changed

LimitNOFILE=infinity

to

LimitNOFILE=655350

and can verify that the new ulimit is set;

# cat /proc/$(pidof mysqld)/limits |grep files
Max open files 655350 655350 files
# mysqladmin variables|grep open_files_l
| open_files_limit | 655350 |

If you wish for a number as close to unlimited as possible,

LimitNOFILE=2147483648

may work, as that's the maximum value for a 32 bit signed integer, which is used commonly in C applications such as systemd.

Best regards,
Robin Holec
Technical Analyst
cPanel, Inc.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,237
463
Hello,

To update, internal case CPANEL-7882 is open to address an issue where systemd disregards the open_files_limit set in /etc/my.cnf in favor of the LimitNOFILE parameter in /etc/systemd/system/mysql.service on CentOS/RHEL 7.x systems running MySQL. This breaks the "Allow cPanel & WHM to determine the best value for your MySQL open_files_limit configuration" option in WHM >> Tweak Settings. The current workaround is to manually modify the LimitNOFILE value in /etc/systemd/system/mysql.service and then run the systemctl daemon-reload command:

Code:
vi /etc/systemd/system/mysql.service
systemctl daemon-reload
I'll update this thread with more information on CPANEL-7882 as it becomes available.

Thank you.
 
  • Like
Reactions: eva2000

wizzy420

Well-Known Member
Nov 13, 2007
127
2
68
Is this fixed? I just migrated some CentOS 5 servers to C7 and upon installing some standardized my.cnf files I blew up all the servers til I found this thread.

Oddly some of the servers have a /etc/systemd/system/mysql.service file, and some don't.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,237
463
Hello,

The resolution associated with internal case CPANEL-7882 is scheduled for inclusion with cPanel version 60.

Thank you.
 

IPXVIII

Registered
Nov 19, 2014
3
0
51
cPanel Access Level
Root Administrator
Hello,

I have the same issue, my is not changing.

I'm running:
  • CLOUDLINUX 7.3 x86_64 standard
  • WHM 60.0 (build 35)
  • Mariadb 10.1
I tried everything what is suggested here and also in google but I can NOT set the open-files-limit higher or even change it.

mysql-error.log:
Code:
[Warning] Could not increase number of max_open_files to more than 10000 (request: 1058587)
innodb_open_files should not be greater than the open_files_limit.
my.cnf
Code:
open-files-limit               = 5310700
innodb-open-files               = 65535
I changed LimitNOFILE in /usr/lib/systemd/system/mariadb.service

also adds a /etc/systemd/system/mariadb.service.d/limits.conf with:
[Service]
LimitNOFILE=5310700

run a 'systemctl --system daemon-reload' and reboot the server few times but it stuck in:

cat /proc/$(pidof mysqld)/limits |grep files
Code:
Max open files            10000                10000                files
mysqladmin variables|grep open_files_l
Code:
| open_files_limit                                       | 10000

Is there something I'm missing?
 

IPXVIII

Registered
Nov 19, 2014
3
0
51
cPanel Access Level
Root Administrator
I got a step a step further.

There was a file in /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf with the content:
[Service]
LimitNOFILE=10000

This was overwriting all my settings. After removing this file my limit jumped to 65536.

cat /proc/$(pidof mysqld)/limits | grep files
Code:
Max open files            65536                65536                files

But I want the limit much higher to 5310700.

There is still something blocking it, does someone know where it is blocked/overwritten?

My current setting:

/etc/my.cnf
Code:
...
max-connections                = 10000
table-open-cache               = 124288
...
open-files-limit               = 5310700
innodb-open-files               = 55536
...
/etc/systemd/system/mysql.service
Code:
[Unit]
Description=MariaDB database server
After=syslog.target
After=network.target
BindsTo=mysqld.service
BindsTo=mysql.service

[Service]
Type=simple
User=mysql
Group=mysql

ExecStartPre=/usr/sbin/mariadb-check-socket
ExecStartPre=/usr/sbin/mariadb-prepare-db-dir %n
# Note: we set --basedir to prevent probes that might trigger SELinux alarms,
# per bug #547485
ExecStart=/usr/bin/mysqld_safe --basedir=/usr
ExecStartPost=/usr/sbin/mariadb-wait-ready $MAINPID
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

# Place temp files in a secure directory, not /tmp
LimitNOFILE=5310700

[Install]
WantedBy=multi-user.target
Alias=mysqld.service mysql.service

/etc/systemd/system/mariadb.service.d/limits.conf
Code:
[Service]

LimitNOFILE=5310700
LimitMEMLOCK=5310700

MySQL/mariadb is running as
Code:
/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/mysql-error.log --open-files-limit=5310700 --pid-file=/var/lib/mysql/mysql.pid --socket=/var/lib/mysql/mysql.sock --port=3306
ERROR Log said /var/lib/mysql/mysql-error.log
Code:
[Warning] Could not increase number of max_open_files to more than 65536 (request: 258587)
 

IPXVIII

Registered
Nov 19, 2014
3
0
51
cPanel Access Level
Root Administrator
The cPanel support got the issue solved:
It appears that when a particular ceiling is hit, the open files limit value gets capped to 65536.
I could set the value successfully to 1000000, everything above puts the limit to 65536.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,237
463
Hello,

I'm happy to see our Support team was able to help answer your question. Thank you for updating us with the outcome.
 

Jaison V John

Member
Apr 15, 2016
17
3
3
India
cPanel Access Level
Root Administrator
@cPanelMucheal

I just increased the limit to 5310700 in mysql.service, ran systemctl daemon-reload, but the problem still persists.

Any other ideas?

---------------------------------------------------------------------------------------------------------------------------
[email protected] [~]# cat /etc/systemd/system/mysql.service | grep -i Limit
LimitNOFILE=5310700
[email protected] [~]# systemctl daemon-reload

[email protected] [~]# grep -i open_files_limi /etc/my.cnf
open_files_limit = 50000

[email protected] [~]# mysqladmin variables|grep open_files_l
| open_files_limit | 12000 |

[email protected] [~]# mysqladmin proc stat | grep -i uptime ===>> Just to let you know that mysql is restarted.

Uptime: 5 Threads: 5 Questions: 152 Slow queries: 0 Opens: 84 Flush tables: 1 Open tables: 77 Queries per second avg: 30.400
[email protected] [~]#

---------------------------------------------------------------------------------------------------------------------------
 

Jaison V John

Member
Apr 15, 2016
17
3
3
India
cPanel Access Level
Root Administrator
@cPanelMicheal

I just increased the limit to 5310700 in mysql.service, ran systemctl daemon-reload, but the problem still persists.

Any other ideas?



---------------------------------------------------------------------------------------------------------------------------
[email protected] [~]# cat /etc/systemd/system/mysql.service | grep -i Limit
LimitNOFILE=5310700
[email protected] [~]# systemctl daemon-reload

[email protected] [~]# grep -i open_files_limi /etc/my.cnf
open_files_limit = 50000

[email protected] [~]# mysqladmin variables|grep open_files_l
| open_files_limit | 12000 |

[email protected] [~]# mysqladmin proc stat | grep -i uptime ===>> Just to let you know that mysql is restarted.

Uptime: 5 Threads: 5 Questions: 152 Slow queries: 0 Opens: 84 Flush tables: 1 Open tables: 77 Queries per second avg: 30.400
[email protected] [~]#

---------------------------------------------------------------------------------------------------------------------------

cat /usr/local/cpanel/version
11.62.0.8

The upcp didn't fix the problem.