mysql by 27 comments

How to calculate the MySQL database size

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.

  1. data_length – store the real data.
  2. 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

27 comments on “How to calculate the MySQL database size”

  2. 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

    Reply

  3. 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;

    Reply

    2. SELECT data_length , index_length ,round(((data_length + index_length) / 1024 / 1024), 2) Size in MB FROM information_schema.TABLES WHERE table_schema = “database name” AND table_name = “table name”

      Reply

  7. 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!!

    Reply

  9. 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!

    Reply

  11. 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:

    SELECT sum(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2)) "Size in MB"
FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = "your_db_name";

    Thanks!.

    Reply

      1. 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;

        Reply

  13. 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

    Reply

    2. 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.

      Reply

      1. We could also do:

        du -sh /var/lib/mysql/*

        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. 🙂

        Reply

        1. 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 🙂

          Reply

Leave a Comment

Your email address will not be published. Required fields are marked *