Re: exponential performance decrease in ISD transaction

On 09/03/2012 01:27 PM, Jeff Janes wrote:
> In any case, the behavior you report is exactly would would be
> expected if autovacuum is not running. The config file you posted
> shows autovac is turned on, but I suspect that is not the config file
> actually being used by the running server.

It's also important to note that:

1) autovacuum doesn't kick in until a moderate number of changes have
been made. Having it turned on doesn't mean it runs continuously. The
table can accumulate a lot of dead junk before autovacuum decides to
clean things up.

2) When autovacuum *does* start, that can be a source of slowdowns itself.

I suspect that some level of table cleanup issue is here. I would also
bet that the performance seen initially is inflated because Linux's
write cache is absorbing writes at the beginning. The first few hundred
megabytes or possibly more you write to the database don't wait for
physical I/O at all. Once that cache fills, though, performance drops
hard. Most benchmarks like this will start out really fast, then drop
off dramatically once the write cache is full, and real-world disk
performance limits progress.

In those cases, the slower performance after things have been running a
while is actually the real sustainable speed of the server. The much
faster ones may only be possible when the write cache is relatively
empty, which makes them representative more of burst performance.

A look at the "Dirty:" line in /proc/meminfo as the test runs will give
you an idea if write cache filling is actually an issue here. If that
number just keeps going up and speeds keep on dropping, that's at least
one cause here. This could easily be both that and an autovacuum
related too though.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com