Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

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:
    108
    Likes Received:
    2
    Trophy Points:
    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
     
    sandra lynn likes this.
  2. ssfred

    ssfred Well-Known Member

    Joined:
    Jan 6, 2012
    Messages:
    64
    Likes Received:
    4
    Trophy Points:
    58
    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.
     
    sandra lynn likes this.
  3. zye

    zye Well-Known Member

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

    ssfred Well-Known Member

    Joined:
    Jan 6, 2012
    Messages:
    64
    Likes Received:
    4
    Trophy Points:
    58
    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:
    108
    Likes Received:
    2
    Trophy Points:
    168
    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:
    108
    Likes Received:
    2
    Trophy Points:
    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?
     
  7. zye

    zye Well-Known Member

    Joined:
    Dec 6, 2002
    Messages:
    108
    Likes Received:
    2
    Trophy Points:
    168
    opend ticket

    Your support request ID: 7550513
     
  8. zye

    zye Well-Known Member

    Joined:
    Dec 6, 2002
    Messages:
    108
    Likes Received:
    2
    Trophy Points:
    168
    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:
    38,658
    Likes Received:
    1,425
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

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

    zye Well-Known Member

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

    zye Well-Known Member

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

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,658
    Likes Received:
    1,425
    Trophy Points:
    363
    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:
    127
    Likes Received:
    2
    Trophy Points:
    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.
     
  14. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,658
    Likes Received:
    1,425
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

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

    Thank you.
     
  15. IPXVIII

    IPXVIII Registered

    Joined:
    Nov 19, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    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?
     
  16. IPXVIII

    IPXVIII Registered

    Joined:
    Nov 19, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    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)
     
  17. IPXVIII

    IPXVIII Registered

    Joined:
    Nov 19, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    The cPanel support got the issue solved:
    I could set the value successfully to 1000000, everything above puts the limit to 65536.
     
  18. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,658
    Likes Received:
    1,425
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

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

    Jaison V John Member

    Joined:
    Apr 15, 2016
    Messages:
    17
    Likes Received:
    3
    Trophy Points:
    3
    Location:
    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?

    ---------------------------------------------------------------------------------------------------------------------------
    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 [~]#

    ---------------------------------------------------------------------------------------------------------------------------
     
  20. Jaison V John

    Jaison V John Member

    Joined:
    Apr 15, 2016
    Messages:
    17
    Likes Received:
    3
    Trophy Points:
    3
    Location:
    India
    cPanel Access Level:
    Root Administrator

    cat /usr/local/cpanel/version
    11.62.0.8

    The upcp didn't fix the problem.
     
Loading...

Share This Page