Do we need Provisioned IOPS for RDS instance that's using 60 IOPS according to monitoring?

Question

We have PostgreSQL instance serving tens of r/w queries per second.

• Instance type: db.m3.2xlarge
• Instance Provisioned IOPS (SSD): 1000
• Instance storage size: 100GB , Database size is about 5-10GB.

It is serving 100s of simultaneous clients with read-write queries. Yet, when we look at Cloudwatch Monitoring it shows IOPS in range of 20-60.

And Read iOPS is around 0!

This can't be right with 100s of connections and clients performing read/write queries all the time? The Postgres configuration is standard, we did not turn off fsync.

Is the cache so effective that IOPS is not a factor with database size of 5GB? Or AWS monitoring console wrong?

Paying for 1000 IOPS cost extra $300 for this db instance. And minimum IOPS you can buy is 1000.

I am wondering if we can do without IOPS?

• Or AWS monitoring is not correct?
• Or 20 IOPS we're having now will kill the server performance if we have non-IOPS server?
• Or with 5GB database it mostly fits in cache and IOPS is not a factor?

Answer 1

@CraigRinger is correct. If your dataset is small enough to fit entirely in memory, you won't need provisioned IOPS since insert/update traffic and logs are the only consuming IOPS.

But in case someone finds this topic, here's what CloudWatch looks like when you've exhausted your GP2 credits. As you can see there the Read and Write IOPS charts don't tell us much, but the read/write latency charts show massive spikes.

For context, these are 2 weeks of a PostgreSQL read replica used for analytics. The switch from 100GB GP2 (300 Base IOPS, $11.50/mo) to 100GB io1 (1000 IOPS, $112.50/mo) happens about 2/3 way through these charts (no more latency spikes). The cheaper option would've been to just up the quantity of GP2 storage. Provisioned IOPS are outrageously overpriced, but predictable behavior during heavy workloads in this instance made sense.

Answer 2

Your DB is almost entirely cached in RAM. (You can confirm this with use of the pg_buffercache extension). Those IOPS numbers are entirely to be expected. I would expect this server to be just fine without provisioned IOPS.

If you restart the instance it'll be slow for a little while as it builds the cache back up, but 5GB isn't much for that. Also, having provisioned iops actually makes this worse, because as well as setting a minimum I/O rate, piops sets the maximum too. It's a target rate not a minimum.

By contrast, regular volumes can burst to much higher read rates than piops volumes, so they'll perform better when you're warming the cache back up after a restart.

BTW:

Restarting the database won't slow it much, as it only has to read data from the OS's disk cache back into shared_buffers. It's only if you restart the whole machine that you'll see a slowdown for a while. If you want to simulate this without a restart, you can use Linux's drop_caches feature:

  echo 1 | sudo tee -a /proc/sys/vm/drop_caches

This is actually worse than the situation after a restart because it evicts binaries and libraries from memory too. The system will chug very heavily at first, as it reads the very frequently accessed binaries and libraries it's executing back into RAM. Then you'll start to see cache recovery behaviour like you would after a restart.

Also, you have too many connections configured. Install pgbouncer, put it in front of the database, and reduce your max_connections. You'll get better performance.