Re: Performance degradation of inserts when database size grows

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