On May 2, 2008, at 1:22 PM, Greg Smith wrote:
> On Fri, 2 May 2008, Alexy Khrabrov wrote:
>
>> I have an UPDATE query updating a 100 million row table, and
>> allocate enough memory via shared_buffers=1500MB.
>
> In addition to reducing that as you've been advised, you'll probably
> need to increase checkpoint_segments significantly from the default
> (3) in order to get good performance on an update that large.
> Something like 30 would be a reasonable starting point.
>
> I'd suggest doing those two things, seeing how things go, and
> reporting back if you still think performance is unacceptable. We'd
> need to know your PostgreSQL version in order to really target
> future suggestions.
PostgreSQL 8.3.1, compiled from source on Mac OSX 10.5.2 (Leopard).
Saw the checkpoint_segments warning every ~20sec and increased it to
100 already. Will see what 512 MB buys me, but 128 MB was paging
miserably.
Cheers,
Alexy