Change open files limit mysql

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,226
463
Hello @Jaison V John,

Could you verify if the /etc/systemd/system/mysql.service.d/limits.conf or /etc/systemd/system/mariadb.service.d/limits.conf files exist on this system?

Thank you.
 
Jul 19, 2015
5
1
3
nirvana
cPanel Access Level
Root Administrator
Why is this error still happening such a long time after it was
reported the first time, even now in version 64.0 (build 36)?

This should have been solved, and much more systematically than here
in this thread before. Even after reading all posts before, only lots
of trial and error brought some deeper understanding, and hence this
write-up, in the hope that cPanel will implement the solution, and
others who face the error and cannot configure as they want find help.

It is important to note,
that cPanel's own (re)start script (/scripts/restartsrv_mysql)
does not help to solve this problem (as it doesn't reload the daemon,
and hence new settings are not loaded)
as the error is shown still even though it might be resolved
already.
Only restarting mysql with this sequence will help:
# systemctl daemon-reload
# systemctl restart mysql
and then checking with
# systemctl status mysql
and the aforementioned:
# cat /proc/$(pidof mysqld)/limits |grep files
or
# mysqladmin variables|grep open_files_limit
will confirm the state of affairs (still w/ or finally w/o error).

By commenting out subsequently from modified files,
tested for order of precedence as found:
#1
/etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf
#2
/etc/systemd/system/mariadb.service.d/limits.conf
(#2 is needed, as upcp overwrites #3)
#3
/etc/systemd/system/mysql.service
#4
even though one might expect it to take still existing settings of
/etc/systemd/system/mysql.service.d/limits.conf
it does not and goes to a (minimum?) of 1024.

This would have been clear if cPanel would use the
# systemctl status mysql
which shows:
the drop-in (ie .conf files), ie the key hint toward the solution:

# systemctl status mysql
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─limits.conf, migrated-from-my.cnf-settings.conf
Active: active (running) since ... ago
Process: 21400 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 21217 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 21215 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 21361 (mysqld)
Status: "Taking your SQL requests now..."
CGroup: /system.slice/mariadb.service
└─21361 /usr/sbin/mysqld

... systemd[1]: Starting MariaDB database server...
... mysqld[21361]: ... [Note] /usr/sbin/mysqld (mysqld 10.1.25-MariaDB) starting as process 21361 ...
... mysqld[21361]: ... [Warning] Could not increase number of max_open_files to more than 10000 (request: 41011)
... systemd[1]: Started MariaDB database server.
#


Can you guys at cPanel please make sure there is more testing and
error avoidance, especially re databases and mail? Thanks!
:)


PS: Just reread this and thought, oh my, they must think I hate them. But that is not the case, so to make sure there is no confusion: Love you guys, you produce the best control panel there is! Just want to encourage you to systematize and test more, and attend to important errors earlier ;)
 
Last edited:
  • Like
Reactions: Del Drago

Del Drago

Member
Mar 2, 2012
12
1
53
cPanel Access Level
Root Administrator
By commenting out subsequently from modified files,
tested for order of precedence as found:
#1
/etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf
#2
/etc/systemd/system/mariadb.service.d/limits.conf
(#2 is needed, as upcp overwrites #3)
#3
/etc/systemd/system/mysql.service
#4
even though one might expect it to take still existing settings of
/etc/systemd/system/mysql.service.d/limits.conf
it does not and goes to a (minimum?) of 1024.
Thank you @WizardOfYonder!!! After hours of searching (and trial and error), your solution worked.

I sincerely hope that the cPanel folks take your advice, and resolve this issue.
 
  • Like
Reactions: WizardOfYonder

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,226
463
Hello @WizardOfYonder,

We may already have a case open to address the issue you are describing (CPANEL-11264), however I'd like to reproduce this issue on a test system to verify I correctly understand the scenario you have described. Could you provide some more information about what initially leads to you make a change to the open_files limit, and the steps you take to make the initial change? Is "Allow cPanel & WHM to determine the best value for your MySQL open_files_limit configuration?" enabled under the "SQL" tab in "WHM >> Tweak Settings" on the affected system?

Thank you.
 

cPanelMichael

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

To update, the following case is included with cPanel version 70:

Fixed case CPANEL-11264: Fixed systemd limits of MySQL and MariaDB.

Thank you.
 
  • Like
Reactions: eva2000

lukekenny

Member
Jan 24, 2018
18
3
3
Melbourne, Australia
cPanel Access Level
Root Administrator
Running 68.0.33 I am getting the same error. Checked LimitNOFILE and it was set to 10000, checked open_files_limit and it was set to 50000, so I raised LimitNOFILE to 50000 as well. The error still comes up after restarting the service. systemctl status mysql does not give the "[Warning] Could not increase number of max_open_files to more than 10000" mentioned above, there is no warning at all except for "Warning: mariadb.service changed on disk. Run 'systemctl daemon-reload' to reload units."
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,226
463
Running 68.0.33 I am getting the same error. Checked LimitNOFILE and it was set to 10000, checked open_files_limit and it was set to 50000, so I raised LimitNOFILE to 50000 as well. The error still comes up after restarting the service. systemctl status mysql does not give the "[Warning] Could not increase number of max_open_files to more than 10000" mentioned above, there is no warning at all except for "Warning: mariadb.service changed on disk. Run 'systemctl daemon-reload' to reload units."
I believe this is fixed in cPanel & WHM version 70 with the case referenced in my last response. However, could you provide more details about the specific commands you are running and the specific output you see so we can verify that's the case?

Thank you.
 

xata11

Member
Oct 17, 2018
15
0
1
Port harcourt
cPanel Access Level
Root Administrator
Why is this error still happening such a long time after it was
reported the first time, even now in version 64.0 (build 36)?

This should have been solved, and much more systematically than here
in this thread before. Even after reading all posts before, only lots
of trial and error brought some deeper understanding, and hence this
write-up, in the hope that cPanel will implement the solution, and
others who face the error and cannot configure as they want find help.

It is important to note,
that cPanel's own (re)start script (/scripts/restartsrv_mysql)
does not help to solve this problem (as it doesn't reload the daemon,
and hence new settings are not loaded)
as the error is shown still even though it might be resolved
already.
Only restarting mysql with this sequence will help:
# systemctl daemon-reload
# systemctl restart mysql
and then checking with
# systemctl status mysql
and the aforementioned:
# cat /proc/$(pidof mysqld)/limits |grep files
or
# mysqladmin variables|grep open_files_limit
will confirm the state of affairs (still w/ or finally w/o error).

By commenting out subsequently from modified files,
tested for order of precedence as found:
#1
/etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf
#2
/etc/systemd/system/mariadb.service.d/limits.conf
(#2 is needed, as upcp overwrites #3)
#3
/etc/systemd/system/mysql.service
#4
even though one might expect it to take still existing settings of
/etc/systemd/system/mysql.service.d/limits.conf
it does not and goes to a (minimum?) of 1024.

This would have been clear if cPanel would use the
# systemctl status mysql
which shows:
the drop-in (ie .conf files), ie the key hint toward the solution:

# systemctl status mysql
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─limits.conf, migrated-from-my.cnf-settings.conf
Active: active (running) since ... ago
Process: 21400 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 21217 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 21215 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 21361 (mysqld)
Status: "Taking your SQL requests now..."
CGroup: /system.slice/mariadb.service
└─21361 /usr/sbin/mysqld

... systemd[1]: Starting MariaDB database server...
... mysqld[21361]: ... [Note] /usr/sbin/mysqld (mysqld 10.1.25-MariaDB) starting as process 21361 ...
... mysqld[21361]: ... [Warning] Could not increase number of max_open_files to more than 10000 (request: 41011)
... systemd[1]: Started MariaDB database server.
#


Can you guys at cPanel please make sure there is more testing and
error avoidance, especially re databases and mail? Thanks!
:)


PS: Just reread this and thought, oh my, they must think I hate them. But that is not the case, so to make sure there is no confusion: Love you guys, you produce the best control panel there is! Just want to encourage you to systematize and test more, and attend to important errors earlier ;)
Thanks alot, you saved me alot, In my case i did not comment out the LimitNOFILE from /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf

because it still uses the server limit instead of the one i set on /etc/systemd/system/mysql.service

rather I placed the same value on the two of theme