Hi,
I am using mysql 5.1 and 5.0. I would like to calculate Database Disk Usage (Percentage of database disk used) and Database Disk Free Space (Actual free database volume left in MB).
I tried following SQL queries:
For Database Disk Usage:-
SELECT table_schema "Data Base Name",
sum( data_length + index_length ) * 100 /sum(data_length + index_length + data_free)
"Data Base Size in Percentage"
FROM information_schema.TABLES
where table_schema = DATABASE();
For Database Disk Free Space:-
SELECT table_schema "Data Base Name",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
where table_schema = DATABASE();
On Red Hat Linux (with MYSQL 5.0) above queries showing values, Database Disk Usage = 98.8601, Database Disk Free Space = 0.00643539.
On windows XP SP3 (with MYSQL 5.1) above queries showing values, Database Disk Usage = 1.7802, Database Disk Free Space = 1500.00000000.
Since, I am getting different values for the same database so the queries that I am using seem incorrect.
I tried one more query with windows XP SP3 and MYSQL 5.1 since some values are negative so result not seems correct.
Here is the query and result
Query:--
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 = DATABASE();
Result:-
schema_name = abc
total_size = 27.20Mb
data_used = -1472.80Mb
data_free = 1500.00Mb
pct_used = -5414.07
total_tables = 150
Please suggest approach using
java API or any other approach to calculate the Database Disk Usage and Database Disk Free Space.
Thanks in advance.