Calculating MySQL Database, Table, and Column Sizes

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;

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;

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";

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;

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