How to calculate the MySQL database size

As i know, there are two ways to calculate MySQL database size.

1. SQL script

Sum up the data_length + index_length is equal to the total table size.

  1. data_length – store the real data.
  2. index_length – store the table index.

Here’s the SQL script to list out the entire databases size


SELECT table_schema "Data Base Name", sum( data_length + index_length) / 1024 / 1024 
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

Another SQL script to list out one database size, and each tables size in detail


SELECT table_name, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES where table_schema = "schema_name";

2. Locate the MySQL stored data

Access this article to find out where does MySQL database saved the data.

Windows
Locate the MySQL ibdata1 file, right click on the file and click the properties, see the size? :)

Linux
Locate the MySQL ibdata1 file


mkyong@myserver:/var/lib/mysql$ ls -lh
total 1.5G
drwx------ 2 mysql mysql 4.0K 2009-08-26 13:36 mydatabase
-rw-r--r-- 1 root  root     0 2009-08-19 09:39 debian-5.0.flag
-rw-rw---- 1 mysql mysql 1.5G 2009-08-27 17:32 ibdata1
-rw-rw---- 1 mysql mysql 5.0M 2009-08-27 17:32 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M 2009-08-27 17:32 ib_logfile1
drwxr-xr-x 2 mysql root  4.0K 2009-08-19 11:19 mysql
-rw------- 1 root  root     6 2009-08-19 09:39 mysql_upgrade_info

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
Vicky Khan
Guest
Vicky Khan

is the above query result includes size of TEXT and BLOB columns?

Vašek Kasal
Guest
Vašek Kasal

Thx for sharing.
One thing:
Your result looks like MiB, not MB unit – (data_length + index_length) / 1024 / 1024
MB should be calculated as – (data_length + index_length) / 1000 / 1000

Roy
Guest
Roy

This provides more data and better resembles the entire schema, where the original poster only returned one row.

select table_schema, table_name, table_rows, data_length, index_length,
round(sum(data_length + index_length)/1024/1024,2) AS “Size in MB”
from information_schema.TABLES where table_schema = “schema_name”
GROUP BY table_schema, table_name;

Bhavana kl
Guest
Bhavana kl

Can any one help me how to set database size limit.

Ramesh
Guest
Ramesh

How to get data_length index_length of specific table? Is it possible. please help

Avishek
Guest
Avishek

This can be done without SQL query as well, best explained here http://mysqldevs.blogspot.in/2014/04/mysql-database-size-stored-data.html?utm_source=BP_recent

Zain
Guest
Zain

SELECT data_length , index_length ,round(((data_length + index_length) / 1024 / 1024), 2) Size in MB FROM information_schema.TABLES WHERE table_schema = “database name” AND table_name = “table name”

NoName
Guest
NoName

Issue: Checking file size and sum of result from the query yields two different values…

Dheeraj
Guest
Dheeraj

same with me

Neha
Guest
Neha

This is exactly what I was looking for. Your blog always helps when I am looking to either understand a topic or when I am looking for a solution to an exact problem. Good job!!

printable coupon code
Guest
printable coupon code

It’s actually very complicated in this active life to listen news on Television, thus I just use world wide web for that reason, and obtain the newest news.

jennifer
Guest
jennifer

Hi there, just became alert to your blog through Google, and found that it’s truly informative. I am going to watch out for brussels. I’ll appreciate if you continue this
in future. Numerous people will be benefited from your writing.
Cheers!

rahul
Guest
rahul

thanks, you rock, this really helped, made my task a cinch.

trackback
how to return the size of mysql databases

[…] Source < Beginning mongo […]

trackback
MySQL’de DataBase boyutu sorgulama | Can Yapan

[…] için t?klay?n?z. Bu yaz? MySQL kategorisine Can Yapan taraf?ndan yaz?lm??t?r. yer imlerinize […]

Juan Capristán
Guest
Juan Capristán

Nice, just what I was looking for. It’s obvious from your post, but just for completeness, the query to get the total size of a particular DB:

SELECT sum(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2)) "Size in MB"
FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = "your_db_name";

Thanks!.

trackback
Keeping Puppet Dashboard’s Database small « Some Softwaremanagement

[…] Thanks to Mkyong’s blog post who provided the SQL statements for analysing the database and tables. And of course […]

Bernd Adamowicz
Guest
Bernd Adamowicz

Your select statements for displaying table- and database-sizes are great. Exactly what I was looking for.

Thanks,
Bernd

trackback
MySql database size « Aspects

[…] in http://www.mkyong.com/mysql/how-to-calculate-the-mysql-database-size/ GA_googleAddAttr("AdOpt", "1"); GA_googleAddAttr("Origin", "other"); […]

Mickey
Guest
Mickey

A simple trick with 1 line total;
Just sum up all of the files in the database path
this way you get a true measure of the HD storage.

ls /var/lib/mysql/ -lh|grep total

Total 21M

PJ Brunet
Guest
PJ Brunet

Thanks for the tip but this will always say 21M.

You need to specify the subdirectory or add “-R” if you want the full picture, for example:

ls -R /var/lib/mysql/ -lh|grep total
total 21M
total 4.2M
total 247M
total 68K
total 16M
total 984K
total 371M

In this case I have 6 databases in 6 subdirectories.

Matt
Guest
Matt

We could also do:

du -sh /var/lib/mysql/*

That should list the total size on disk per database.

I know this post is very old but I check back here every now and then. :)

Kai
Guest
Kai

Getting the size using file system utilities only works nice for databases using MyISAM or the innodb_file_per_table option. If the InnoDB data is stored within the same ibdata file(s) then there is no way of determining the amount stored for each database.

But the original SQL works like a charm :)

Ravy
Guest
Ravy

I want to calculate real data size per row of table not table

daniel
Guest
daniel

ibdata1 is the sum of all databases that you have on the installation and that can be more than one database.