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
is the above query result includes size of TEXT and BLOB columns?
Yes
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
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;
Can any one help me how to set database size limit.
How to get data_length index_length of specific table? Is it possible. please help
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
SELECT data_length , index_length ,round(((data_length + index_length) / 1024 / 1024), 2)
Size in MBFROM information_schema.TABLES WHERE table_schema = “database name” AND table_name = “table name”Issue: Checking file size and sum of result from the query yields two different values…
same with me
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!!
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.
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.
a similar one maybe:
SELECT TABLE_ROWS,round(((DATA_LENGTH+INDEX_LENGTH)/1024 /1024),2) as “Size in MB”,TABLE_SCHEMA,TABLE_NAME from information_schema.TABLES;
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. 🙂
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 🙂
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