Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted June 9, 2016

Calculating MySQL Database, Table, and Column Sizes

By Rob Gravelle

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

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



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM