The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Change open files limit mysql

Discussion in 'Database Discussions' started by zye, May 15, 2016.

Tags:
  1. zye

    zye Well-Known Member

    Joined:
    Dec 6, 2002
    Messages:
    96
    Likes Received:
    1
    Trophy Points:
    8
    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
     
  2. ssfred

    ssfred Well-Known Member

    Joined:
    Jan 6, 2012
    Messages:
    62
    Likes Received:
    2
    Trophy Points:
    8
    Location:
    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.
     
  3. zye

    zye Well-Known Member

    Joined:
    Dec 6, 2002
    Messages:
    96
    Likes Received:
    1
    Trophy Points:
    8
    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
     
    #3 zye, May 17, 2016
    Last edited: May 17, 2016
  4. ssfred

    ssfred Well-Known Member

    Joined:
    Jan 6, 2012
    Messages:
    62
    Likes Received:
    2
    Trophy Points:
    8
    Location:
    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.
     
  5. zye

    zye Well-Known Member

    Joined:
    Dec 6, 2002
    Messages:
    96
    Likes Received:
    1
    Trophy Points:
    8
    hi - i know that i have to restart to get new config loaded :) thx though
     
  6. zye

    zye Well-Known Member

    Joined:
    Dec 6, 2002
    Messages:
    96
    Likes Received:
    1
    Trophy Points:
    8
    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?
     
  7. zye

    zye Well-Known Member

    Joined:
    Dec 6, 2002
    Messages:
    96
    Likes Received:
    1
    Trophy Points:
    8
    opend ticket

    Your support request ID: 7550513
     
  8. zye

    zye Well-Known Member

    Joined:
    Dec 6, 2002
    Messages:
    96
    Likes Received:
    1
    Trophy Points:
    8
    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
     
    ssfred likes this.
  9. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    653
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  10. zye

    zye Well-Known Member

    Joined:
    Dec 6, 2002
    Messages:
    96
    Likes Received:
    1
    Trophy Points:
    8
    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!!!
     
  11. zye

    zye Well-Known Member

    Joined:
    Dec 6, 2002
    Messages:
    96
    Likes Received:
    1
    Trophy Points:
    8
    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.
     
  12. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    653
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
    eva2000 likes this.
  13. wizzy420

    wizzy420 Well-Known Member

    Joined:
    Nov 13, 2007
    Messages:
    125
    Likes Received:
    2
    Trophy Points:
    18
    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.
     
  14. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    653
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page