Calculating database size?

Question

What is the most accurate way to gauge how large a database is with the following characteristics:

• MySQL
• 1 table with three columns:
  • id → big int)
  • field1 → varchar 32
  • field2 → char 32
• there is an index in the field2

You can assume that varchar 32 is completely full (all 32 characters). How big would it be if each field is completed, and is:

• 1 million rows
• 5 million rows
• 1 billion rows
• 5 billion rows

My rough estimate allows: 1 byte for id, 32 bits for the other two fields. About:

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

Thus:

• 62 Mb
• 310 Mb
• 310 000 Mb = + - 302Gb
• 1,550,000 MB = 1,513 GB

Is this an accurate estimate?

Answer 1

If you want to know the current database size, you can try the following:

 SELECT table_schema "Database Name" , SUM(data_length + index_length) / (1024 * 1024) "Database Size in MB" FROM information_schema.TABLES GROUP BY table_schema 

Answer 2

My rough estimate allows: 1 byte for id, 32 bits for the other two fields.

You've gone. Refer to the MySQL documentation for Data Warehouse Requirements . In particular:

• A BIGINT is 8 bytes, not 1.

• The storage required for a CHAR or VARCHAR column will depend on the character set used by your database (!), But will have at least 32 bytes (not a bit!) For CHAR(32) and 33 for VARCHAR(32) .

• You did not consider the size of the index. The size of this will depend on the database engine, but it is definitely not zero. See the documentation in the InnoDB row structure for more details.

Answer 3

On the MySQL website you will find 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.