1

We found 3 different size of a MySQL Database Instance as follows:

  1. Mysqldump output
  2. Data directory size (even if we exclude size of bin-logs,error-logs)
  3. SUM (data_length + index_length ) calculated from information_schema.tables

Why we faced these differences and what could be used as my Instance size?

Please help.

  • For migration purpose data directory size can be used – Koustuv Chatterjee Feb 5 '16 at 12:41
1
  1. Dump output is raw SQL statements, numbers and everything else is represented as text so the size is in no way relevant (one int is 4 bytes, but as text 1 is probably 1 byte and maximal int value (signed) = 2147483647 is 10 bytes and minimal int value = -2147483648 is 11 bytes).
  2. I suppose those are all ibdataX, *.ibd, *.myi, *.myd, *.frm. Both MyISAM and InnoDB tables may contain some empty space (for example after deleting some rows) which will not be automatically reclaimed by the filesystem, InnoDB files contain not only actual row data but old versions of records, redo logs and other information needed for transactions.
  3. The free space mentioned before is not accounted for here, the same applies for redo logs probably.

In the end it depends on your needs for the size - do you need to know how much space to allocate for backups? #1 or #2 may be what you need, depending on your backup strategy and after accounting for compression. Do you need to know how big disks/cloud storage to pay for - #2 is probably the best bet.

I personally do not see much use for #3 in general, but you can use it to get some average storage needed for a record so you can project future grow of specific tables.

I am sure I missed some differences but hopefully nothing big.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.