On 06/04/2014 10:55 AM, Tom Lane wrote:
The effect disappears completely if I run under SI instead of SSI, though. That makes me suspect strongly that the problem lurks in SSI-specific infrastrucure.Ryan Johnson <ryan.johnson@xxxxxxxxxxxxxx> 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. However, I did notice that the SLRU buffer that holds "old" SSI transactions sometimes spikes from hundreds to millions of entries (by annotating the source to ereport a warning whenever the difference between head and tail is at least 10% higher than the previous record). Not sure if that's related, though: I'm pretty sure SSI never scans SLRU, it's only used for random lookups. 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. Here are the offenders (100-second run, 24 clients, ~2.2ktps): lwlock 7 shacq 0 exacq 7002810 blk 896235 spindelay 213 The above aggregates the per-lock stats from all processes, filters out locks with fewer than 10000 accesses (shared+exclusive) or with zero blk, then sorts by highest blk first. According to [1], locks {28, 29, 30} are {SerializableXactHashLock, SerializableFinishedListLock, SerializablePredicateLockListLock}, all SSI-related; locks 65-80 are the sixteen PredicateLockMgrLocks that the post mentions. Looking in lwlock.h, lock 7 (which tops the list) is the WALInsertLock. That lock was *not* mentioned in the pgsql-hackers post. Re-running the same analysis for SI instead of SSI gives 4.6ktps and a much shorter list: lwlock 7 shacq 0 exacq 14050121 blk 3429384 spindelay 347 However, all of this only confirms that SSI has a lock bottleneck; it doesn't say why the bottleneck gets worse over time. [1] http://www.postgresql.org/message-id/CA+TgmoYAiSM2jWEndReY5PL0sKbhgg7dbDH6r=oXKYzi9B7KJA@xxxxxxxxxxxxxx Thoughts? Ryan |