MySQL Quota remote MySQL server and load

nzhook

• January 27, 2015 21:49

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,

• 

  cPanelMichael

  • January 28, 2015 13:34

  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.

  0
• 

  nzhook

  • January 29, 2015 20:40

  ]Hello :) You could disable the following option under the "SQL" tab in "WHM Home " Server Configuration " Tweak Settings": 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.

  
  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

  0
• 

  cPanelMichael

  • January 30, 2015 19:00

  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.

  0

Please sign in to leave a comment.