File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes How to calculate the database size and database free space? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to calculate the database size and database free space?" Watch "How to calculate the database size and database free space?" New topic
Author

How to calculate the database size and database free space?

Amit Gupta Gsits
Greenhorn

Joined: Jun 21, 2010
Posts: 1
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.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

Welcome to JavaRanch, Amit.


OCUP UML fundamental and ITIL foundation
youtube channel
 
 
subject: How to calculate the database size and database free space?
 
Similar Threads
Restore problem
Cheap and best webhosting plan
MySql: ResultSet clarification
Capturing result of SUM query
Comparing large BIGINT fields in MySQL on Windows