Message-ID: <50481EA9.6060904@2ndQuadrant.com>
Date: Wed, 05 Sep 2012 23:55:21 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.24) Gecko/20111120 Icedove/3.1.16
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: exponential performance decrease in ISD transaction
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>
In-Reply-To: 
 <CAMkU=1wjE0wKBnwOhtLO9g_Hcffct8Gqpis7t6SQ=SOgV=k2GQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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