Saturday, October 3, 2009

Implementing wait_timeout in PostgreSQL

In MySQL, the wait_timeout setting can be used to automatically close connections that have been inactive for a certain amount of time.  The default is 8 hours.

Unfortunately, there is no equivalent in PostgreSQL (as of version 8.3).  However, it's easy to implement.  You just need to use the query_start column (of the pg_stat_activity view) to determine when a connection was last used.

Here is an example using Bash:

#!/bin/bash
WAIT_TIMEOUT_MINUTES="$1"

/usr/bin/psql -U postgres -t -c "SELECT procpid FROM pg_stat_activity WHERE (NOW() - query_start) > INTERVAL '$WAIT_TIMEOUT_MINUTES MINUTES' AND current_query = '<IDLE>'" | xargs kill

This script takes the timeout as a command-line parameter, and then kills off any connections that have been sitting idle for too long.

Add this to a cron job and you're all set.

If you're using PostgreSQL 8.4, then you can do this entirely using SQL, thanks to the new terminate_backend() function:

SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE (NOW() - query_start) > INTERVAL 60 MINUTES' AND current_query = '<IDLE>'

This is especially useful if you're working in a Windows environment where there's no equivalent of xargs (that I know of), or if you don't have access to a command line (i.e. you're making a remote connection using psql) and therefore can't use kill.

Thanks to the anonymous commenter who pointed out an error in the original script.

10 comments:

Anonymous said...

Wouldn't this kill off long running updates or queries too?

Matt said...

That's a very good point. I wonder if the "waiting" column could be used to detect this. I'll try it out and see what I find.

Matt said...

What am I thinking? The "current_query" column says whether there's an ongoing query or not. I'll update the code. Thanks for pointing this out!

thommy said...

NB:
"xargs" for windows (and other fine Unix commandline tools) can be found at http://unxutils.sourceforge.net/

pesatd said...
This comment has been removed by a blog administrator.
Magnus Hagander said...

There's a race condition in this - if a session was idle when you ran the psql query but then starts doing something, it will be killed. And the information in pg_stat_activity only updates once every half second...

Anonymous said...
This comment has been removed by a blog administrator.
Unknown said...

With 8.4 you could actually do this in an organized way by calling pg_terminate_backend(). Sending SIGTERM directly to a server process is kind of not recommended.

Matt said...

@Magnus, great point, and definitely something to keep in mind. Thanks for bringing it up.

In my case, this doesn't matter because we're using a timeout of 60 minutes, along with a client-side connection pool that recycles idle connections after 5-10 minutes. Anything that's around for 60 minutes is *definitely* an "abandoned" connection that we want to kill off.

Matt said...

@Peter, I did in fact refer to pg_terminate_backend ;-).

If you're using 8.3 or earlier, however, using SIGTERM can be preferable to leaving connections around indefinitely.

It all depends on your setup. In our case, the idle connections were being left around by abnormally-terminating clients. So killing them off is absolutely the right thing to do.