Message-ID: <4DDAAB8F.5030508@fuzzy.cz>
Date: Mon, 23 May 2011 20:46:39 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.13) Gecko/20101219 Lightning/1.0b3pre Thunderbird/3.1.7
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Performance degradation of inserts when database size
 grows
References: <BANLkTi=VKBmRLVLDjy8qxpWx_6-rmbUaXg@mail.gmail.com>
	<4DDA617A.6090105@peak6.com>
In-Reply-To: <4DDA617A.6090105@peak6.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Dne 23.5.2011 15:30, Shaun Thomas napsal(a):
> On 05/17/2011 07:45 AM, Andrey Vorobiev wrote:
> 
>> 2011-05-17 18:55:51 NOVST LOG: checkpoint starting: xlog
>> 2011-05-17 18:57:20 NOVST LOG: checkpoint complete: wrote 62861 buffers
>> (24.0%); 0 transaction log file(s) added, 0 removed, 0 recycled;
>> write=89.196 s, sync=0.029 s, total=89.242 s
> 
> Increase your checkpoint_segments. If you see "checkpoint starting:
> xlog" instead of "checkpoint starting: time", you don't have enough
> checkpoint segments to handle your writes. Checkpoints *will* degrade
> your throughput.
> 

Really? He already has 64 checkpoint segments, which is about 1GB of
xlog data. The real problem is that the amount of buffers to write is
constantly growing. At the beginning there's 62861 buffers (500MB) and
at the end there's 137657 buffers (1GB).

IMHO increasing the number of checkpoint segments would make this
disruption even worse.

What I don't understand is that the checkpoint time does not increase
with the amount of data to write. Writing the

   62861 buffers      total=89.242 s    ( 5 MB/s)
   83747 buffers      total=75.061 s    ( 9 MB/s)
   97341 buffers      total=60.479 s    (13 MB/s)
  110149 buffers      total=52.379 s    (17 MB/s)
  120003 buffers      total=46.864 s    (20 MB/s)
  122296 buffers      total=57.867 s    (17 MB/s)
  128165 buffers      total=55.188 s    (18 MB/s)
  138508 buffers      total=58.068 s    (19 MB/s)
  132485 buffers      total=73.047 s    (14 MB/s)
  139542 buffers      total=68.319 s    (16 MB/s)
  137657 buffers      total=84.640 s    (13 MB/s)

Maybe this depends on what sections of the files are modified
(contiguous vs. not contiguous), but I doubt it.

In 9.1 there's a feature that spreads checkpoint writes, but with 8.4
that's not possible. I think think this might be tuned using background
writer, just make it more aggressive.

- bgwriter_delay (decrease)
- bgwriter_lru_maxpages (increase)
- bgwriter_lru_multiplier (increase)

regards
Tomas