Debug low performance & high CPU usage in PostgreSQL

Our company has recently been looking into the performance of our SQL databases. We use AWS RDS with the PostgreSQL engine. We have been noticing some unexpected usage in one of the databases and suspected that it is executing some inefficient queries from our metrics data processors. Occasionally the CPU usage would hit 100%, signaling that something’s off. To figure out what was going on, we used the following strategy.

pgAdmin helps you monitor & analyze your database.

Observe

There is no point in rushing into fixing a problem if you don’t know what the problem is. First, look at your Database resources usage:

  • CPU
  • Number of connections/sessions
  • Storage
  • Number of read/write IOPS

Use pgAdmin and its Query Tool to investigate further. The pg_stat_activity table in the postgres database will be very useful for us.

First, let’s see what’s connected to our database server.

SELECT state, count(*) 
FROM pg_stat_activity 
GROUP BY state;

Now let’s look into the details of these queries.

SELECT state, now() - xact_start AS duration, datname,
  usename, client_addr, client_hostname, 
  application_name, query
FROM pg_stat_activity
ORDER BY duration DESC NULLS LAST;

This query will return all running queries sorted by how long they’ve been running (duration column). You can look up the query column to see the original request. The rest of the columns (datname, usename, client_addr, client_hostname, and application_name) will help you identify the sender of the query.

In our case, the troublesome queries came from an old processor that was calculating some aggregated values for our metrics 🤦.

Be better next time

The actions we took to investigate this issue should be performed regularly to ensure your database is in the best condition. The hardest part is defining what needs to be done; the rest is just pasting the SQL query into the pgAdmin Query Tool.

Point copy-paste notification
You can store your SQL query as a Point snippet.

To make the latter even faster, you can use Point to store the presented SQL query for the future. That way, next time you’ll be able to insert it into pgAdmin’s Query Tool in just a few keystrokes! Simply press Ctrl + . or click the icon and search for your snippet by name, label, or content 🔥.

I recommend that you save a link to this article as a snippet! In a couple of months, you probably won’t remember how the SQL snippet worked anymore and you’ll find it extremely useful to be able to quickly jump back to this article. I’m always storing both the solution and a link to its explanation (whether it is a blog post or Stack Overflow thread). Here you can read more on how to improve your workflow as a developer using Point snippets.

To try Point, simply go to the Chrome Extension Store and install it.

One thought on “Debug low performance & high CPU usage in PostgreSQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s