Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to calculate the database size and database free space?

 
Amit Gupta Gsits
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2597
12
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to JavaRanch, Amit.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic