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
Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

  • Pingback: Looid()

  • Neha

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

  • http://www.23hq.com/stephaniesalitw/story/list printable coupon code

    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.

  • http://www.slideshare.net/ninap8838/yh-international-coupon-big-savings jennifer

    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!

  • rahul

    thanks, you rock, this really helped, made my task a cinch.

  • Pingback: how to return the size of mysql databases()

  • Pingback: MySQL’de DataBase boyutu sorgulama | Can Yapan()

  • http://www.juan-capristan.net/ Juan Caprist├ín

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

    • http://www.mkyong.com mkyong

      Thanks for your extra input.

  • Pingback: Keeping Puppet Dashboard’s Database small « Some Softwaremanagement()

  • http://berndadamowicz.wordpress.com/ Bernd Adamowicz

    Your select statements for displaying table- and database-sizes are great. Exactly what I was looking for.

    Thanks,
    Bernd

  • Pingback: MySql database size « Aspects()

  • http://coknown.com Mickey

    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

    • http://www.mkyong.com mkyong

      Hi Mickey, thank for your tip :)

    • http://tomakefast.com PJ Brunet

      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.

      • http://matt.ph Matt

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

        • Kai

          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 :)

  • Ravy

    I want to calculate real data size per row of table not table

    • mkyong

      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.

  • daniel

    ibdata1 is the sum of all databases that you have on the installation and that can be more than one database.

    • mkyong

      thanks for the information, then the script version will be more accurate at the calculation of the MySQL database size