Ok so I have a postgres database at work that we use to store application information and this database is shared across some large web applications. We recently started getting the above error in the postgres logs, and occasional failures in the web apps. Let me state for the record I am not a postgres expert, I can install it, get it running and use it, beyond that is the extent of my postgres knowledge. Anyway back to my story. So we started having this wierd failures and not knowing how to diagnose it I started googling.

The first thing I find is how to see how many processes I have running:

ps auxww | grep ^postgres | wc -l

ps auxww | grep ^postgres

The first shows you how many process are running, and the second gives you more detail. So I now I had a little bit better understanding of what was going on. I could see how many postgres process I had at any one time. But that did not help me figure out which queries and what app was causing the problems.

I googled some more and figured out how to query the active queries.

So I run this query against the stats database:

SELECT count(*) as cnt, usename, current_query FROM pg_stat_activity GROUP BY usename,current_query ORDER BY cnt DESC;

But I get “<command string not enabled>”; so more googling and I found I need to change a setting in the postgresql.conf and set the stats_command_string = true.

Ok with this done I was able to run the query above and find 1 single query that was getting repeated over and over again hundreds of times and was taking 8-10 minutes to finish, and even longer as the queries stacked up.

On further inspection, this one query is supposed to be memcached, its hit on every page of a site and should cache once and never hit the database again, but its not. Some times it does and things run great, other times it continues to not find it in cache and hit the database.

We could not figure out why this particular query out of the hundreds we store in memcache is not being cached properly. So we cache it the old fashion way in file, till we can work out why it won’t cache in memcache. Once its cached again properly, ir improperly in this case, Postgres starts working properly again. No more high connections and no more app failures.

The end, or is it? Now we have to figure out why it won’t cache in Memcache properly.