6

I have an application database with a table for users (1kbyte of data per user based on counting fields * typelength), and about 100 things of the same size belonging to a user (0.5 kbyte per thing), and it is in a "user" table and a "thing" table.

That would seem to lead to about 51kbytes of data per user. However, I have heard that for MySQL, I should double it to cover index tables, which would get me to 102kbytes/user Is that true? Are there any other data expansion factors to consider for MySQL, or is 102 kbytes a good estimate?

Besides the indexing factor (which I think is 2) and the storage efficiency (which I also think is 2), are there any other multipliers for data storage in MySQL?

Toby Allen
  • 10,997
  • 11
  • 73
  • 124
Jay Godse
  • 15,163
  • 16
  • 84
  • 131
  • What database engine are you using? Do you want actual numbers or estimates? You can use TABLE STATUS to get the actual size of each table in bytes. You might be able to extrapolate from that and get realistic estimates of numbers. – Thomas Owens Sep 06 '11 at 19:31
  • I'm using innodb for storage. – Jay Godse Sep 06 '11 at 19:46
  • http://dev.mysql.com/doc/refman/5.0/en/innodb-disk-management.html discusses InnoDB disk utilization to some extent, but without knowing your purpose, I'm not sure how to best compute or estimate the disk usage. – Thomas Owens Sep 06 '11 at 19:49
  • Just a note: for instance varchar is variable length type. So if you have varchar(100) it doesn't mean it will use all 100 bytes. – Karolis Sep 06 '11 at 20:11
  • http://stackoverflow.com/a/17627863/84162 – MECU Nov 14 '14 at 15:28

1 Answers1

2

Short answer
Size increase 2-3x over MyISAM is common, 4x is rare.

Everything about the InnODB engine:
http://dev.mysql.com/doc/refman/5.1/en/innodb-storage-engine.html

InnoDB, MyISAM and disk space:
http://mysqlha.blogspot.com/2009/01/innodb-myisam-and-disk-space_16.html

MySQL engines space usage comparison:
Part1: http://marksverbiage.blogspot.com/2008/02/mysql-engines-and-space-usage.html
Part2: http://marksverbiage.blogspot.com/2008/04/mysql-engines-space-usage-comparison.html

Here's the physical row structure:
http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html

There are a lot of variables and issues:

  • Indexes, remember InnoDB includes the PK in every secondary index.
  • Are you packing keys (slow)?
  • Is the table redundant?
  • Don't forget about the logs (binary log, slow query log, error log ....)
  • Are rows declared as nullable, if so add an extra byte per nullable column per row.
  • What charset are you using?
Johan
  • 74,508
  • 24
  • 191
  • 319