4

I have a table in MySql at the moment, 7.3 million rows, 1.5GB in size if I run this query:

How to get the sizes of the tables of a mysql database?

I'm trying to get a handle on what a full table scan of that in AWS Aurora would cost me?

AWS lists it as:

I/O Rate - $0.200 per 1 million requests

But how do I possible translate that into "what will this cost me"?

Community
  • 1
  • 1
Kong
  • 8,792
  • 15
  • 68
  • 98

2 Answers2

0

See also https://stackoverflow.com/a/6927400/122441

As an example, a medium sized website database might be 100 GB in size and expect to average 100 I/Os per second over the course of a month. This would translate to $10 per month in storage costs (100 GB x $0.10/month), and approximately $26 per month in request costs (~2.6 million seconds/month x 100 I/O per second * $0.10 per million I/O).

Hendy Irawan
  • 20,498
  • 11
  • 103
  • 114
0

Here's a deep dive that might be helpful:

https://aws.amazon.com/blogs/database/planning-i-o-in-amazon-aurora/

Read operations in Aurora MySQL operate on 16 KB pages. So you can do a rough calculation of how many pages will be needed for the full-table scan.

But is each page entirely full or is there some empty space - that depends on the fill factor. So probably that rough calculation needs to be adjusted to take the fill factor into account.

The first full-table scan will take some number of I/Os, resulting in some corresponding cost. But then if you do another table scan, maybe the whole table is already in the buffer pool, so no I/Os are required (i.e. nothing has to be fetched from storage). For a busy cluster, it might be more economical to use a bigger instance class so all table and index data is kept in the buffer pool all the time. MySQL also has some optimizations like the "midpoint insertion strategy" to prevent occasional big queries from knocking actual frequently accessed data out of the buffer pool.

For realistic cost estimation for I/O, you'd want to measure your actual workload over time and extrapolate. The blog suggests monitoring in CloudWatch the metric '[Billed] Volume Read IOPS (Count)'.

Max Webster
  • 181
  • 6