If you’ve ever tried to calculate how much disk space your MySQL data entities – databases, tables, and columns – are taking up, you may have been left scratching your head for an answer. You’ll be happy to know that it can be done. In today’s article, I’ll show you a few sure fire ways to get at the information you’re looking for.
Calculate the Database Size
Want to know how much disk space is being used by your database(s)? Here are a couple of ways to figure it out.
In MySQL, the information_schema.TABLES table contains everything you need. Specifically, the sum of the data_length and index_length columns gives us the total table size.
Here is a query that groups each schema by database name and lists their size in both bytes and megabytes:
SELECT table_schema as 'Database Name',
sum( data_length + index_length ) as 'Size in Bytes',
round((sum(data_length + index_length) / 1024 / 1024), 4) as 'Size in MB'
FROM information_schema.TABLES
GROUP BY table_schema;
Database Name
|
Size in Bytes
|
Size in MB
|
information_schema
|
10240
|
0.0098
|
mysql
|
1158310
|
1.1047
|
performance_schema
|
0
|
0.0000
|
sakila
|
6895444
|
6.5760
|
sys
|
NULL
|
NULL
|
test
|
2506752
|
2.3906
|
The megabyte figures are calculated by dividing the total number of bytes by 1024 twice; the first division yields the number of kilobytes, while the second gives us the number of megabytes. Finally, that figure is passed through the round() function to limit the number of decimal places to four.
A Better Query
There are a few aspects of the above query that could be improved upon. The first is that you may want to filter out system tables. That is easily achieved using a NOT IN() clause:
WHERE table_schema not in ('information_schema', 'performance_schema', 'mysql', 'sys')
Another improvement might be to format sizes by the unit closest to their sizes, i.e. bytes, KBs, MBs, etc. The following user function accepts a FLOAT value and returns a formatted VARCHAR of 20 characters:
CREATE DEFINER=`root`@`localhost` FUNCTION `format_filesize`(filesize FLOAT)
RETURNS varchar(20) CHARSET utf8
BEGIN
SET @log = IFNULL(TRUNCATE(LOG(1024, filesize), 0),0);
return CONCAT(ROUND(filesize / POW(1024, @log), 2), ' ',
ELT(@log + 1, 'Bytes', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB', 'BB'));
END
Here is the updated query with both modifications, as well as sorting by size in descending order:
SELECT table_schema as 'Database Name',
sum( data_length + index_length ) as 'Size in Bytes',
round((sum(data_length + index_length) / 1024 / 1024), 4) as 'Formatted'
FROM information_schema.TABLES
WHERE table_schema not in ('information_schema', 'performance_schema', 'mysql', 'sys')
GROUP BY table_schema
ORDER BY ByteSize DESC;
Database Name
|
ByteSize
|
Formatted
|
sakila
|
6895444
|
6.58 MB
|
test
|
2506752
|
2.39 MB
|
The ibdata1 File
If you use the InnoDB storage engine for most of your tables, another approach is to locate the ibdata1 file, which holds almost all the InnoDB data for a MySQL installation. Its location will vary depending on your operating system: in Debian/Ubuntu it’s under “/var/lib/mysql”, while in Windows it’s in the “data” folder under your MySQL installation directory.
Once you’ve found it, check its size. I think you’ll find that it’s fairly close to the results of the first query above.

Check the ibdata1 file size
In Linux, the ls –lh command will give you the file size:
rob@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
Listing Database and Table Sizes
Here’s a query that does double duty. Not only does it provide valuable information about each table – number of rows, data length, index length, and size - but it also includes summary information in the last row, including the database size in the bottom-right cell.
The totals are appended to the result set using the UNION statement. The query that follows employs our format_filesize() function to format all of the totals:
SELECT table_name,
table_rows,
data_length,
index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size"
FROM information_schema.TABLES
WHERE table_schema = "sakila"
UNION
SELECT 'TOTALS:',
format_filesize( sum(table_rows) ),
format_filesize( sum(data_length) ),
format_filesize( sum(index_length) ),
format_filesize( sum(data_length + index_length) )
FROM information_schema.TABLES
WHERE table_schema = "sakila";
table_name
|
table_rows
|
data_length
|
index_length
|
size
|
actor
|
200
|
16384
|
16384
|
0.03
|
actor_info
|
NULL
|
NULL
|
NULL
|
NULL
|
address
|
603
|
81920
|
16384
|
0.09
|
category
|
16
|
16384
|
0
|
0.02
|
city
|
600
|
49152
|
16384
|
0.06
|
country
|
109
|
16384
|
0
|
0.02
|
customer
|
599
|
81920
|
49152
|
0.13
|
customer_list
|
NULL
|
NULL
|
NULL
|
NULL
|
film
|
1000
|
196608
|
81920
|
0.27
|
film_actor
|
5462
|
196608
|
81920
|
0.27
|
film_category
|
1000
|
65536
|
16384
|
0.08
|
film_list
|
NULL
|
NULL
|
NULL
|
NULL
|
film_text
|
1000
|
119636
|
205824
|
0.31
|
high_priced_rentals
|
336
|
16384
|
0
|
0.02
|
inventory
|
4581
|
180224
|
196608
|
0.36
|
language
|
6
|
16384
|
0
|
0.02
|
nicer_but_slower_film_list
|
NULL
|
NULL
|
NULL
|
NULL
|
payment
|
16086
|
1589248
|
638976
|
2.13
|
rental
|
16008
|
1589248
|
1196032
|
2.66
|
sales_by_film_category
|
NULL
|
NULL
|
NULL
|
NULL
|
sales_by_store
|
NULL
|
NULL
|
NULL
|
NULL
|
staff
|
2
|
65536
|
32768
|
0.09
|
staff_list
|
NULL
|
NULL
|
NULL
|
NULL
|
store
|
2
|
16384
|
32768
|
0.05
|
TOTALS:
|
46.49 KB
|
4.11 MB
|
2.46 MB
|
6.58 MB
|
Determining Column Sizes
You might think that you could just take the number of rows and multiply it by a field’s data type size to determine how many bytes it takes up on disk. For instance, if a column’s type is numeric, you could just get a count on the rows, and multiply it by the size, such as 4 for INTEGER, or 8 for DOUBLE, etc…
Sadly, that won't reflect the actual size taken up on disk, because of many factors, including the storage engine type, row format, fragmentation, and a whole host of other factors. Thus, estimating a column’s storage requirements is difficult, though certainly not impossible, to get at.
That being said, if you’re just looking for a general estimation, you can always use the OCTET_LENGTH() function. A synonym for LENGTH(), OCTET_LENGTH() returns the length of a field in bytes. A multi-byte string character counts as multiple bytes, so for a string containing five 2-byte characters, OCTET_LENGTH() returns 10, whereas CHAR_LENGTH() would return 5.
With that in mind, here is a query that measures the size of two fields of the sakila film table:
select title,
octet_length(title) 'title_length',
length 'movie_length',
octet_length(length) 'movie_length_length'
from film
limit 10;
title
|
title_length
|
movie_length
|
movie_length_length
|
ACADEMY DINOSAUR
|
16
|
86
|
2
|
ACE GOLDFINGER
|
14
|
48
|
2
|
ADAPTATION HOLES
|
16
|
50
|
2
|
AFFAIR PREJUDICE
|
16
|
117
|
3
|
AFRICAN EGG
|
11
|
130
|
3
|
AGENT TRUMAN
|
12
|
169
|
3
|
AIRPLANE SIERRA
|
15
|
62
|
2
|
AIRPORT POLLOCK
|
15
|
54
|
2
|
ALABAMA DEVIL
|
13
|
114
|
3
|
ALADDIN CALENDAR
|
16
|
63
|
2
|
Conclusion
In today’s article, we learned a few ways to obtain the size of our MySQL databases, tables, and columns, formatted in bytes, KBs, and MBs. In most cases, the above techniques will yield adequate results, but precision may vary depending on the storage engines employed.
See all articles by Rob Gravelle