Re: SSI slows down over time

Ryan Johnson <ryan(dot)johnson(at)cs(dot)utoronto(dot)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