Skip to main content

Change open files limit mysql

zye
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

Comments

28 comments

Date Votes
  • ssfred
    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.
    0
  • zye
    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
    0
  • ssfred
    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.
    0
  • zye
    hi - i know that i have to restart to get new config loaded :-) thx though
    0
  • zye
    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?
    0
  • zye
    opend ticket Your support request ID: 7550513
    0
  • zye
    solved Solution: the limitation is being set by the systemd service file: ==== [17:37:01 hostname root@7550513 ~]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
    0
  • cPanelMichael
    Hello, I'm happy to see you were able to address the issue. Thank you for updating us with the outcome.
    0
  • zye
    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!!!
    0
  • zye
    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.
    0
  • cPanelMichael
    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:
    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.
    0
  • wizzy420
    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.
    0
  • cPanelMichael
    Hello, The resolution associated with internal case CPANEL-7882 is scheduled for inclusion with cPanel version 60. Thank you.
    0
  • IPXVIII
    Hello, I have the same issue, my is not changing. I'm running: [LIST]
  • 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:
    [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
    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
    Max open files 10000 10000 files
    mysqladmin variables|grep open_files_l
    | open_files_limit | 10000
    Is there something I'm missing?
    • 0
  • IPXVIII
    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
    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
    ... max-connections = 10000 table-open-cache = 124288 ... open-files-limit = 5310700 innodb-open-files = 55536 ...
    /etc/systemd/system/mysql.service
    [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
    [Service] LimitNOFILE=5310700 LimitMEMLOCK=5310700
    MySQL/mariadb is running as
    /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
    [Warning] Could not increase number of max_open_files to more than 65536 (request: 258587)
    0
  • IPXVIII
    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.
    0
  • cPanelMichael
    Hello, I'm happy to see our Support team was able to help answer your question. Thank you for updating us with the outcome.
    0
  • Jaison V John
    @cPanelMucheal I just increased the limit to 5310700 in mysql.service, ran systemctl daemon-reload, but the problem still persists. Any other ideas? --------------------------------------------------------------------------------------------------------------------------- root@ds4 [~]# cat /etc/systemd/system/mysql.service | grep -i Limit LimitNOFILE=5310700 root@ds4 [~]# systemctl daemon-reload root@ds4 [~]# grep -i open_files_limi /etc/my.cnf open_files_limit = 50000 root@ds4 [~]# mysqladmin variables|grep open_files_l | open_files_limit | 12000 | root@ds4 [~]# 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 root@ds4 [~]# ---------------------------------------------------------------------------------------------------------------------------
    0
  • Jaison V John
    @cPanelMicheal I just increased the limit to 5310700 in mysql.service, ran systemctl daemon-reload, but the problem still persists. Any other ideas? --------------------------------------------------------------------------------------------------------------------------- root@ds4 [~]# cat /etc/systemd/system/mysql.service | grep -i Limit LimitNOFILE=5310700 root@ds4 [~]# systemctl daemon-reload root@ds4 [~]# grep -i open_files_limi /etc/my.cnf open_files_limit = 50000 root@ds4 [~]# mysqladmin variables|grep open_files_l | open_files_limit | 12000 | root@ds4 [~]# 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 root@ds4 [~]# ---------------------------------------------------------------------------------------------------------------------------

    cat /usr/local/cpanel/version 11.62.0.8 The upcp didn't fix the problem.
    0
  • cPanelMichael
    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.
    0
  • Jaison V John
    Hi @cPanelMichael Sorry, forget to update the fix here. Modifying the "LimitNOFILE" entry in the /etc/systemd/system/mysql.service.d/limits.conf file sorted out the problem. :)
    0
  • WizardOfYonder
    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 ;)
    0
  • Del Drago
    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.
    0
  • cPanelMichael
    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.
    0
  • cPanelMichael
    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.
    0
  • lukekenny
    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."
    0
  • cPanelMichael
    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.
    0
  • xata11
    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
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post