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.
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:
- Number of connections/sessions
- 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.
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
. 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.