Change open files limit mysql

zye · May 15, 2016

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

ssfred · May 17, 2016

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.

zye · May 17, 2016

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

ssfred · May 17, 2016

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 · May 17, 2016

hi - i know that i have to restart to get new config loaded

thx though

zye · May 17, 2016

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 · May 17, 2016

opend ticket

Your support request ID: 7550513

zye · May 17, 2016

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

cPanelMichael · May 30, 2016

Hello,

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

zye · Aug 10, 2016

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 · Aug 10, 2016

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 · Aug 14, 2016

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.

wizzy420 · Aug 29, 2016

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 · Sep 1, 2016

Hello,

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

Thank you.

IPXVIII · Jan 27, 2017

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 · Jan 29, 2017

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 · Feb 1, 2017

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 · Feb 1, 2017

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 · Feb 9, 2017

@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 · Feb 9, 2017

Jaison V John said:
@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.

Thread starter	Similar threads	Forum	Replies	Date
N	Question: Change MySql collation from latin1 to ut8_general	Databases	5	Nov 9, 2021
G	SOLVED Will WHM or cPanel ever remove the my.cnf changes I have made?	Databases	4	Feb 24, 2021
P	Making changes to databases in phpmyadmin but they are not reflecting on my site	Databases	1	Feb 11, 2021
R	Change database date format	Databases	1	Nov 19, 2020
M	max_open_files cannot be changed	Databases	1	Jan 27, 2019

Change open files limit mysql

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Administrator

Well-Known Member

Well-Known Member

Administrator

Well-Known Member

Administrator

Registered

Registered

Registered

Administrator

Member

Member