Your queries are slow, but memory/cpu usage is low – part II

On the previous article Your queries are slow, but memory/cpu usage is low I mentioned that one of the reasons where you can observe slowness in an app on AWS that is using RDS is IOPS budgeting. The main symptom you’ve already gathered from the title and that’s why you are reading this: slow queries, but you can actually see whether the issue is IOPS budget, not because AWS made it easier to keep track of it (they haven’t), but because the effect is visible in the metrics: you’ll notice a flatline in the maximum Read/Write IOPS.

To see the metrics:

  • Go to https://console.aws.amazon.com/rds/ .
  • Click instances in the left side bar.
  • Click the instance you’re having issues with.
  • Under cloudwatch there’s a search box, write IOPS there.

If you’re seeing a flatline at a number that is 3 times the storage capacity (eg: for a 10GB hard disk (EBS volume), that would be 30 IOPS) then you’ve run out of budget. If you see a bunch of peaks that are under and over that maximum, you have budget to spare.

If you see the flatline of slowness, you have two choices:

  1. If you just want an increase to less than 1000 IOPS, you can increase the storage you are using. You probably won’t need the space, but you’re just doing it for the IOPS. If you have a multi-AZ RDS instance the downtime will be minimal (under a minute in my latest experience).
  2. If you want more than 1000 IOPS then you can add Provisioned IOPS to your instance:
  • On the same screen as previously, click instance actions on the top right corner.
  • Under Storage type, choose: Provisioned IOPS (SSD).
  • Now a new option will appear for choosing how much capacity you want to provision:

Screenshot showing the Provisioned IOPS field under Storage Type on the AWS console

Note: Remember that these changes will make you database slower during the conversion, so please do it at a time where your users don’t need it.

Want to get python/AWS tips on your email? Just subscribe!

Your queries are slow, but memory/cpu usage is low – part I

You’re noticing timeouts on queries running on your PostgreSQL database? Or just slower response to queries? And when you look at RDS monitoring the cpu and memory look like the machine is doing nothing?

There are two main causes I’ve found for this: lock contention and IOPS limits. Today I’m talking about the most likely of them: lock contention.

So, first things first, how to identify if you have lock contention? There are two queries that will help you identify what’s going on:

SELECT COUNT(*) FROM pg_locks

This will show you how many locks are in your system at a point in time. If the database is not being used, the number should be zero. If it’s being used, I’d expect the locks to go up and sometimes fall down to zero (we’re talking about low load scenarios). If that doesn’t happen (or rarely happens) you might have slow transactions helding locks for a long time and making everything else wait for them.

You can confirm if that’s the case with this query:

SELECT * FROM pg_stat_activity WHERE state <> 'idle'

You’ll be looking at some states of idle in transaction and/or with wait_type lock.

If you see these signs, there are 2 strategies you can use to speed things up:

  • Optimise the queries that are causing the locks. The quickest return will probably come up from adding some indexes, I’d recommend talking a look at PostgresSQL’s wiki index usage query for where you’ll get the biggest return.
  • Reduce locks in your application (usually be eliminating queries). Remember those less than clear queries you were thinking of cleaning up another day? It might be the right time.

And that’s how you can make your app faster.

Want to get python/AWS tips on your email? Just subscribe!