[Jim C. Nasby - Thu at 10:28:31AM -0500]
> I think it'd be much better to experiment with using much larger
> shared_buffers settings. The conventional wisdom there is from 7.x days
> when you really didn't want a large buffer, but that doesn't really
> apply with the new buffer management we got in 8.0. I know of one site
> that doubled their performance by setting shared_buffers to 50% of
> memory.
Oh, that's interessting. I will give it a shot. Our config is
"inheritated" from the 7.x-days, so we have a fairly low setting
compared to available memory. From the 7.x-days the logic was that "a
lot of careful thought has been given when designing the OS cache/buffer
subsystem, we don't really want to reinvent the wheel" or something like
that.
Sadly it's not easy to measure the overall performance impact of such
tunings in a production environment, so such a setting tends to be tuned
by religion rather than science :-)
> Something else to consider is that many people will put pg_xlog on the
> same drives as the OS (and swap). It's pretty important that those
> drives not have much activity other than pg_xlog, so any swap activity
> would have an even larger than normal impact on performance.
Hm ... that's actually our current setting, we placed the postgres
database itself on a separate disk, not the xlog. So we should have
done it the other way around? No wonder the performance is badly
affected by backups etc ...
What else, I gave the swappiness a second thought, compared to our
actual memory usage statistics ... turning down the swappiness would
have no significant effect since we're only using 2M of swap (hardly
significant) and our total memory usage by applications (including the
pg shared buffers) is less than 400M out of 6G. Maybe we could have
moved some 50M of this to swap, but that's not really significant
compared to our 6G of memory.
