What’s the hold up?

The other day in the office we noticed that one of our servers was performing a little sluggishly. We rolled up our sleeves, fired up a terminal window and prepared to take a look under the hood.

There were a lot of database jobs backing up in the process list and this confused me – our connections are implemented as singleton classes and I was pretty sure that all of the queries had been examined with some EXPLAIN ANALYZE attention.

After some head-scratching I came up with this command line script that will list the five most CPU intensive SQL queries for the current user in the process list:

ps aux --sort=pcpu | grep "postgres: $USER" | tail -5 | awk '{ print $2 }' | xargs -I{} psql -c 'select procpid, current_query, query_start, backend_start from pg_stat_activity where procpid = {}'