How to calculate the MySQL database size
Posted on , Last modified : December 8, 2011
By mkyong
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.
- data_length – store the real data.
- 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
Tags : mysql
Here are some of my recommended Books
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!
thanks, you rock, this really helped, made my task a cinch.
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:
Thanks!.
Thanks for your extra input.
Your select statements for displaying table- and database-sizes are great. Exactly what I was looking for.
Thanks,
Bernd
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
Hi Mickey, thank for your tip :)
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.
We could also do:
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. :)
I want to calculate real data size per row of table not table
May i know why you need calculate per row? row data may vary in different row. Please share your answer if you manage to find it out.
ibdata1 is the sum of all databases that you have on the installation and that can be more than one database.
thanks for the information, then the script version will be more accurate at the calculation of the MySQL database size