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 = {}'
0 Responses to “What’s the hold up?”