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.

MySQL Quota remote MySQL server and load

Discussion in 'Database Discussions' started by nzhook, Jan 27, 2015.

  1. nzhook

    nzhook Registered

    Joined:
    Jan 26, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,

    We have a number of CPanel servers which use the remote MySQL feature of CPanel and connect to a single server. This allows us to utilize the MYSQL resource.

    However there is an issue we have recently noticed on some servers (possibly all servers) where the load on the MySQL server spikes while CPanel does the quota check as part of /usr/local/cpanel/scripts/update_db_cache every 4 hours.

    The cause of the spike is that all the servers are running the queries on the information_schema database at the same time and as each query can run for a number of minutes it pushes the load as the disk usage is determined for all the databases on the server (some of which don't apply to the CPanel server running the query)

    I tried to look at the update_db_cache script to see if it can be optimized to run a query on an already populated database table (done regularly by the MySQL server itself) however the script is compiled perl which rules this option out.

    Does anyone know a solution which would allow us to reduce the load of all the servers running the check at the same time while still including the MySQL quota for a customers site.
    I am happy to write my own script to collect the data and store it somewhere for CPanel to read, but just need to know where this should be stored.

    Any help would be appreciated,
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    You could disable the following option under the "SQL" tab in "WHM Home » Server Configuration » Tweak Settings":

    "Use INFORMATION_SCHEMA to acquire MySQL disk usage"

    Per it's description:

    Using INFORMATION_SCHEMA ensures that disk usage by MySQL tables is included in totals. However, enabling this option may cause a significant drop in performance as MySQL may become unresponsive until data collection is complete. Disabling this option causes the system to query the filesystem directly, potentially excluding disk space used by some database tables. Note: If you use a remote MySQL server, you must turn this setting On in order to calculate MySQL disk usage.

    It won't calculate MySQL disk usage since you are using a remote MySQL server, however it might be a good idea to temporarily disable this option to verify it's the cause of the issue.

    Thank you.
     
  3. nzhook

    nzhook Registered

    Joined:
    Jan 26, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    We have done that on a few servers and it does make a difference.
    We have also noted that the processes which are causing the load are the information schema ones, killing the processes reduces the load drastically (and customers sites return to normal)

    What would be of great help would be if we could collect the information and populate a file or even a different table in MYSQL, so the information is already there when the quota check runs. This was my plan before I found out that the script was compiled and I could not change the table tname.

    Thanks
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    MySQL disk usage calculation is not currently supported when "Use INFORMATION_SCHEMA to acquire MySQL disk usage" is disabled. Feel free to open a feature request for this via:

    Submit A Feature Request

    Thank you.
     
Loading...

Share This Page