Disk Space Usage doesn't include MySQL databases size

sehh

Well-Known Member
Feb 11, 2006
579
5
168
Europe
The "Disk Space Usage" bar on the left side of the cPanel, doesn't include the size of the SQL databases.

As a result, my users complain why they hit their over quota limit when the displayed usage is only showing 30% or so (user has 1gb quota with a 700mb database).

Is there a way to calculate and include the size of the database in the counter?
 

sehh

Well-Known Member
Feb 11, 2006
579
5
168
Europe
Indeed, its currently very hard to get proper sizes from MySQL.

After lots of research, i found the following reliable method:

Code:
SELECT s.schema_name, CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),"Mb") total_size, CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb") data_used, CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free,IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used, COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name = "mydatabasename" GROUP BY s.schema_name ORDER BY pct_used DESC;
Just change the "mydatabasename" to the name of the database you want to get data for, keep the double quotes around the name.

The above should print this:

Code:
+-------------+------------+-----------+-----------+----------+--------------+
| schema_name | total_size | data_used | data_free | pct_used | total_tables |
+-------------+------------+-----------+-----------+----------+--------------+
| mydb        | 100.87Mb   | 100.86Mb  | 0.00Mb    |   100.00 |           49 | 
+-------------+------------+-----------+-----------+----------+--------------+
the "size" is the REQUESTED size (but not used by data)

the "used" is the ACTUAL data size used in the db

unfortunately, it doesn't work on MySQL 4.1 that i tried, it only works on my systems with MySQL 5.x.

maybe not the solution cPanel developers are looking for, but worth taking a look.
 

sehh

Well-Known Member
Feb 11, 2006
579
5
168
Europe
Another idea, is to store the SQL database in the /home/user directory, so whatever happens it would be easy to just count the size of the home dir. Of course that may not give real-time numbers for InnoDB tables running on the live database, but its a close estimate that should make everyone happy with quotas and disk usage.
 

cPanelKenneth

cPanel Development
Staff member
Apr 7, 2006
4,608
77
308
cPanel Access Level
Root Administrator
Was the above done on an InnoDB table type? Also, did you run it over a period of time as the data stored changed to see how accurate the produced values are?

InnoDB tables can be split into separate files, similar to how MyISAM tables are, but MySQL doesn't do it by default. Hence on an existing system, those tables would need to go through some kind of /dump/drop/restore process for MySQL to create them in the proper location. Placing them in /home/user/mysql (for example), introduces other issues pertaining to permissions and quotas. Surmountable issues, but annoying.
 

sehh

Well-Known Member
Feb 11, 2006
579
5
168
Europe
The database contains tables of both InnoDB and MyIsam types. No i haven't tested the above over a prolonged period of time, just over a few hours and it seems "ok" to me.

Our problem is with cPanel 11. Since the upgrade from cPanel 10, many of our users have started complaining about disk usage quota limits.

For example, one client of ours has 500MB of disk usage and 800MB of SQL data (1.3GB total in 1.5GB quota).

The client before the upgrade would see about 80% disk usage and after the upgrade to cPanel 11 the user sees 30% disk usage.

He then tried to upload loads of stuff thinking that he had enough free space.

Now i've got over quota users who think they are not over quota and cPanel allows them to store unlimited SQL data.

Please help.
 
Last edited:

sehh

Well-Known Member
Feb 11, 2006
579
5
168
Europe
I run the script you mentioned and it worked, now space usage is back to normal.

Do i have to run that manually via a cron job or something? or is this a one-time thing that was only required to run after the upgrade?
 

Danny_T

Well-Known Member
Jul 19, 2005
181
0
166
Netherlands
I am reffered to here bu cpaneldavid.

user quouta 1Gb
user home 500mb
user innodb database 700mb
cpanel says: MySQL Disk Space 72.71/482.59 MB
mysql version 4.1.22

I need some sollution before it gets out of hand and other customers going to migrate too to innodb to avoid quota counts.

Is it possible to set somewhere in a config to not allow innodb?
Is there a script that can convert all found innodb back to myisam ?
 

sehh

Well-Known Member
Feb 11, 2006
579
5
168
Europe
We are having two kinds of problems:

1) cPanel/WHM does not accurately calculate the disk space used by user databases

2) We have to manually execute the "/scripts/updatemysqlquota" script via a cron job because cPanel/WHM fails to maintain disk usage on its own.

So far the developers seen to ignore the existance of Innodb databases which is a problem for us.


Danny_T, you can disable Innodb databases from the /etc/my.cnf but i wouldn't recommend that because you'll break many hosted sites. You can't convert a database from InnoDB to MyISAM because that would break the actual application that depends on the InnoDB features.
 

cPanelKenneth

cPanel Development
Staff member
Apr 7, 2006
4,608
77
308
cPanel Access Level
Root Administrator
InnoDB started life as a third party project (meaning, not developed by MySQL). In fact, the InnoDB technology itself is now owned by Oracle.

While you can disable the InnoDB table, it is not recommended. However, it is recommended that you configure that table type to create the tables similar to how MyISAM does, by creating a separate file in the database directory, rather than merging it into one central file named ibdata1. To do this, add the following to /etc/my.cnf:

Code:
[mysqld]
innodb_file_per_table

While it seems we are ignoring the overall database space issues, we are not.
 

sehh

Well-Known Member
Feb 11, 2006
579
5
168
Europe
What happens if we add "innodb_file_per_table" in an existing database? do the databases/tables get converted?

EDIT:

to answer my own question, existing databases are not affected, only newly created databases will go into separate files, which is a problem unless we have a way to convert existing databases.
 
Last edited:

NNNils

Well-Known Member
Sep 17, 2002
580
0
166
Since some time also these big files appear:

#sql_7dd2_0.MYD
#sql_7dd2_0.MYI
#sql_7dd2_1.MYD
#sql_7dd2_1.MYI

What are those for?
 

astopy

Well-Known Member
Apr 3, 2003
166
0
166
cPanel Access Level
Root Administrator
YOu can try using the ALTER TABLE statement (http://dev.mysql.com/doc/refman/5.0/en/alter-table.html) to change the table type, but more than likely you are better off with a dump/drop/restore method to switch.
The following advice from the MySQL web site seems to work:

1) add "innodb_file_per_table" option to my.cnf
2) restart mysqld
3) do an ALTER TABLE <table> ENGINE=InnoDB; on each table you want to move apart. This will recreate the table in its separate .ibd file.
and to generate all the alter table commands you'll need:

select concat('alter table ',TABLE_SCHEMA ,'.',table_name,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables
where table_type='BASE TABLE' and engine = 'InnoDB'
http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html
 

Kent Brockman

Well-Known Member
PartnerNOC
Jan 20, 2008
1,257
60
178
Buenos Aires, Argentina
cPanel Access Level
Root Administrator
I run the script you mentioned and it worked, now space usage is back to normal.

Do i have to run that manually via a cron job or something? or is this a one-time thing that was only required to run after the upgrade?
Hi, posibly setting a weekly cron job (or even daily if the hosted sites have a hard work) is the solution?


While it seems we are ignoring the overall database space issues, we are not.
Let us know when having news about the issue, so we can abandon this cron job
 

ffeingol

Well-Known Member
PartnerNOC
Nov 9, 2001
494
144
343
cPanel Access Level
DataCenter Provider
I have a question about the "/scripts/updatemysqlquota" script. In looking at the code it basically chown's the database files to be owned by the account user-id and group mysql. From memory chowning the db files to anything other than mysql caused a lot of problems. The basic issue (again from memory) was that when the cpanel account went over quota mysql could no longer write to the database. mysql then 'hung' trying to perform the database write. This then totally hung mysql until you either restarted mysql or gave the account more quota.

Has this been 'fixed' with the latest version of mysql or is this still a nightmare lurking out there?