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!

One Reply to “Your queries are slow, but memory/cpu usage is low – part I”

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax