Cause:
By default, we disable an option in WHM to include MySQL database sizes in disk usage calculations. The primary reason for this is due to the inefficiency and high resource usage of the script used to generate this usage calculation which consumes a lot of CPU, especially if you have many databases, or large databases.
KnownHost Recommendation:
We strongly recommend keeping this usage calculation turned off to save on resources. You're probably asking yourself something like "Why is this any less efficient than calculating the disk usage of my file structure?". The answer to that question is rather simple: Due to the fact that /var/lib/mysql and it's subdirectories (each database has a folder here) must be owned by the system's MySQL user and group (mysql:mysql), the system filesystem quotas cannot be used to calculate this usage like they are used to calculate it for your users home directory filesystem.
I still want to enable MySQL disk usage calculations. How do I do it?
There are a couple of ways this can be accomplished:
WHM Method:
Using WHM is definitely the easiest of the two methods for the novice user.
This method is for advanced users who are familiar with SSH.
By default, we disable an option in WHM to include MySQL database sizes in disk usage calculations. The primary reason for this is due to the inefficiency and high resource usage of the script used to generate this usage calculation which consumes a lot of CPU, especially if you have many databases, or large databases.
KnownHost Recommendation:
We strongly recommend keeping this usage calculation turned off to save on resources. You're probably asking yourself something like "Why is this any less efficient than calculating the disk usage of my file structure?". The answer to that question is rather simple: Due to the fact that /var/lib/mysql and it's subdirectories (each database has a folder here) must be owned by the system's MySQL user and group (mysql:mysql), the system filesystem quotas cannot be used to calculate this usage like they are used to calculate it for your users home directory filesystem.
I still want to enable MySQL disk usage calculations. How do I do it?
There are a couple of ways this can be accomplished:
WHM Method:
Using WHM is definitely the easiest of the two methods for the novice user.
- Once logged into WHM, proceed to the Tweak Settings link in the top of the navigation bar on the left hand side of the screen.
-
- Once on this page, type "include database" into the search box in the top right hand corner of the page. This will bring you to the option to enable/disable database usage in disk calculations.
- Select the option you desire, and click "Save".
- Your change has now taken effect and the next time quotas are run on your system, database sizes will be calculated.
This method is for advanced users who are familiar with SSH.
- Login to SSH.
- Using your preferred command-line text editor, modify the /var/cpanel/cpanel.config file. For this tutorial I'll be using vi.
- Locate the line starting with "disk_usage_include_sqldbs".
- Change this value as you desire to achieve the needed result. A value of "1" will enable the disk usage calculation for databases. A value of "0" will disable the disk usage calculation for databases.
- After you've made your modification, save the file and exit the text editor. We now need to run a command to put the change into effect.
- Run the command "/scripts/update_db_cache" if you'd like to force a database size calculation immediately - otherwise it will be calculated with the system quotas at their next run.
- We also need to run "/usr/local/cpanel/whostmgr/bin/whostmgr2 --updatetweaksettings" to enact the new setting change we made.