Re: SSI slows down over time - Mailing list pgsql-performance

From Tom Lane
Subject Re: SSI slows down over time
Date
Msg-id 13824.1396796137@sss.pgh.pa.us
Whole thread Raw
In response to SSI slows down over time  (Ryan Johnson <ryan.johnson@cs.utoronto.ca>)
Responses Re: SSI slows down over time  (Ryan Johnson <ryan.johnson@cs.utoronto.ca>)
Re: SSI slows down over time  (Ryan Johnson <scovich@gmail.com>)
List pgsql-performance
Ryan Johnson <ryan.johnson@cs.utoronto.ca> writes:
> I get a strange behavior across repeated runs: each 100-second run is a
> bit slower than the one preceding it, when run with SSI (SERIALIZABLE).
> ... So the question: what should I look for to diagnose/triage this problem?

In the past I've seen behaviors like this that traced to the range of
"interesting" transaction IDs getting wider as time went on, so that
more pages of pg_clog were hot, leading to more I/O traffic in the
clog SLRU buffers.  Possibly this is some effect like that.

> I've tried linux perf, but all it says is that lots of time is going to
> LWLock (but callgraph tracing doesn't work in my not-bleeding-edge
> kernel).

You could recompile with -DLWLOCK_STATS to enable gathering stats on
which LWLocks are heavily contended.  That would be a starting point
for localizing the cause.

            regards, tom lane


pgsql-performance by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: SSI slows down over time
Next
From: Jeff Janes
Date:
Subject: Slow Count-Distinct Query