Disk Space Usage doesn't include MySQL databases size

[sehh]

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?

 

[cPanelDavidG]

Related post/thread:

http://forums.cpanel.net/showpost.php?p=307650&postcount=41

 

[sehh]

Indeed, its currently very hard to get proper sizes from MySQL.

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

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:

+-------------+------------+-----------+-----------+----------+--------------+
| 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]

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]

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]

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.

 

[cPanelKenneth]

/scripts/updatemysqlquota should resolve that.

 

[sehh]

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]

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]

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.

 

[Danny_T]

Thanks for the info.

At this point i wished that mysql had not invented InnoDB. And i hope cPanel developers are giving us a sollution in a new release or whatever.
Its a big problem that can fill up diskspaces and you dont know why.

 

[cPanelKenneth]

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:

[mysqld]
innodb_file_per_table

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

 

[sehh]

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.

 

[cPanelKenneth]

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.

 

[NNNils]

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]

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

 

[ahlul_b4n9_s]

Just Use This

/scripts/updatemysqlquota

Its worked for me.

 

[Kent Brockman]

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

 

[cPanelKenneth]

Let us know when having news about the issue, so we can abandon this cron job

I'm subscribed to this thread. Once I have further information to convey I will pass it along.

 

[ffeingol]

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?