• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to calculate the database size and database free space?

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Welcome to JavaRanch, Amit.
 
reply
    Bookmark Topic Watch Topic
  • New Topic