From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: exponential performance decrease in ISD transaction
Date: Wed, 05 Sep 2012 23:55:21 -0400
Message-ID: <50481EA9.6060904@2ndQuadrant.com> (raw)
In-Reply-To: <CAMkU=1wjE0wKBnwOhtLO9g_Hcffct8Gqpis7t6SQ=SOgV=k2GQ@mail.gmail.com>
References: <CA+rSYkSe0oZbaagQeXrg7xP=7tsUn=mkc8vbQ7vYk8noZ7uKKA@mail.gmail.com>
<CAMkU=1y5hHuWrrUuP+a_oxZv4NBGUGA2wfy+HU4Rq8jewo=mPg@mail.gmail.com>
<CA+rSYkR6+4X0Uqtv37rRFkAOuyR1NmxFEa_2yiNkH_r0rAz74g@mail.gmail.com>
<CAMkU=1wjE0wKBnwOhtLO9g_Hcffct8Gqpis7t6SQ=SOgV=k2GQ@mail.gmail.com>
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@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndQuadrant.com
Subject: Re: exponential performance decrease in ISD transaction
In-Reply-To: <50481EA9.6060904@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox