0

I want to estimate the disk space for 1 million rows.

enter image description here

Currently, I have 8527959 rows, Data Length of 744 MB and Index Length 989.4 MB.

Please let me know if my calculations are correct.

Total Data Length for 1 million rows

Data of 744MB consists of 46500 pages of 16KB blocks (InnoDB page size)

If 46500 pages consist of 8527959 rows, 1 page consists an average of 183 rows.

So, 1 million rows need (1,000,000/183) pages= 5465 pages of 16KB

So, 1 million rows of data need 87.4MB.

Total Index Length for 1 million rows

Indexes of of 989.4MB consists of 61837 pages of 16KB blocks (InnoDB page size)

If 61837 pages consist of 8527959 rows, 1 page consists an average of 138 rows.

So, 1 million rows need (1,000,000/138) pages= 7247 pages of 16KB

So, 1 million rows of data need 115.9MB.

Total disk space (approximate) for 1 million rows

Total data length+Total Index Length = 87.4MB + 115.9 MB = 203.3 MB

Is this correct?

PyRookie
  • 31
  • 1
  • 1
  • 2
  • 3
    Create a table with exactly same schema and copy 1M rows into it. Then you can see if your calculation has been correct. – Paul Spiegel Feb 06 '18 at 23:20
  • Please don't cross post, that just creates headaches for moderators – Tom V Feb 07 '18 at 12:11
  • @PaulSpiegel Is there way to get an approximate number assuming that I am unable to create a table to check it? – PyRookie Feb 07 '18 at 20:02
  • Sizes are roughly proportional. Several hiccups can occur: Allocation starts with 16KB blocks (a 1-row table will take 16KB for data), then moves to 8MB "extents" (or something like that). But when you are talking about millions of rows, the hiccups are more "random". – Rick James Feb 14 '18 at 02:04

0 Answers0