9

What is the most accurate way to estimate how big a database would be with the following characteristics:

  • MySQL
  • 1 Table with three columns:
    • id --> big int)
    • field1 --> varchar 32
    • field2 --> char 32
  • there is an index on field2

You can assume varchar 32 is fully populated (all 32 characters). How big would it be if each field is populated and there are:

  1. 1 Million rows
  2. 5 Million rows
  3. 1 Billion rows
  4. 5 Billion rows

My rough estimate works out to: 1 byte for id, 32 bits each for the other two fields. Making it roughly:

  1 + 32 + 32 = 65 * 1 000 000 = 65 million bytes for 1 million rows
= 62 Megabyte

Therefore:

  1. 62 Mb
  2. 310 Mb
  3. 310 000 Mb = +- 302Gb
  4. 1 550 000 Mb = 1513 Gb

Is this an accurate estimation?

rockstardev
  • 13,479
  • 39
  • 164
  • 296
  • http://stackoverflow.com/questions/7325090/mysql-database-size-estimation – goat Jul 13 '13 at 06:59
  • Check this out: http://lists.mysql.com/mysql/217296. According to this, the database size is not solely dependent on it's table rows – Milad.Nozari Jul 13 '13 at 07:00

3 Answers3

25

If you want to know the current size of a database you can try this:

SELECT table_schema "Database Name"
     , SUM(data_length + index_length) / (1024 * 1024) "Database Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema
Matteo Tassinari
  • 18,121
  • 8
  • 60
  • 81
12

My rough estimate works out to: 1 byte for id, 32 bits each for the other two fields.

You're way off. Please refer to the MySQL Data Type Storage Requirements documentation. In particular:

  • A BIGINT is 8 bytes, not 1.

  • The storage required for a CHAR or VARCHAR column will depend on the character set in use by your database (!), but will be at least 32 bytes (not bits!) for CHAR(32) and 33 for VARCHAR(32).

  • You have not accounted at all for the size of the index. The size of this will depend on the database engine, but it's definitely not zero. See the documentation on the InnoDB row structure for more information.

3

On the MySQL website you'll find quite comprehensive information about storage requirements: http://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html

It also depends if you use utf8 or not.

tosc
  • 759
  • 4
  • 16