Re: Huge spikes in number of connections doing "PARSE"

On Mon, Mar 14, 2011 at 07:49:46PM +0100, hubert depesz lubaczewski wrote:
> I wrote a script, that every 15 seconds, checks system for Pg backends in
> "PARSE" state. If there are more than 100 of them, script randombly chooses
> 10 of them, and runs "gdb -batch -quiet -ex=bt /usr/bin/postgres PID" on
> them.
>
> Over the weekend I got 2125 such stack traces logged, but only 60 of them
> happened when we had such huge unexpected spikes (this db server is quite
> busy), with over 400 parsing backends.
>
> These 60 were summarized, and output is available here:
> http://www.depesz.com/various/locks.summary.txt

Thanks; that's exactly what I wanted.

> Any ideas based on the stack traces in the file ( the file itself is 20kB, so I
> didn't want to put it in email )

The common theme is contention over updating the shared lock table, with the
lockers targeting system catalogs. Such requests normally get satisfied from
backend-local caches, greatly reducing the amount of lock activity. That isn't
happening, indicating that either your backend lifespan is very short or your
caches are getting reset. More likely the latter.

What is a typical lifespan for a backend in this system? What sort of
connection pooling are you using, if any?

Do you create temporary objects or perform other DDL regularly? In PostgreSQL
8.3, when any one backend-local local cache fell too far out of date, all
backends would take a full cache reset. That seems like the most credible
explanation for your symptoms. PostgreSQL 8.4 brought major changes to this
area (commit fad153ec45299bd4d4f29dec8d9e04e2f1c08148), so upgrading might
eliminate the spikes you're seeing.

Regardless of PostgreSQL version, constraining the number of concurrent backends
at the connection pool level will make these events less frequent and costly.
You'll almost always be better off running 200 backends and letting 200
additional queries wait than running 400 backends.

nm