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!

Change open files limit mysql

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

Tags:
  1. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,528
    Likes Received:
    2,180
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  2. 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
    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. :)
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    cPanelMichael likes this.
  3. WizardOfYonder

    WizardOfYonder Member

    Joined:
    Jul 19, 2015
    Messages:
    5
    Likes Received:
    1
    Trophy Points:
    3
    Location:
    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 ;)
     
    #23 WizardOfYonder, Aug 4, 2017
    Last edited: Aug 5, 2017
    Del Drago likes this.
  4. Del Drago

    Del Drago Member

    Joined:
    Mar 2, 2012
    Messages:
    12
    Likes Received:
    1
    Trophy Points:
    53
    cPanel Access Level:
    Root Administrator
    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.
     
    WizardOfYonder likes this.
  5. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,528
    Likes Received:
    2,180
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  6. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,528
    Likes Received:
    2,180
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    eva2000 likes this.
  7. lukekenny

    lukekenny Member

    Joined:
    Jan 24, 2018
    Messages:
    11
    Likes Received:
    3
    Trophy Points:
    3
    Location:
    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."
     
  8. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,528
    Likes Received:
    2,180
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice