Re: PG 8.3 and large shared buffer settings

On Mon, 2009-09-28 at 10:36 -0700, Josh Berkus wrote:
> On 9/26/09 8:19 AM, Greg Smith wrote:
> > This means that the question you want an answer to is "if the OS cache
> > isn't really available, where does giving memory to shared_buffers
> > becomes less efficient than not caching things at all?" My guess is
> > that this number is much larger than 10GB, but I don't think anyone has
> > done any tests to try to quantify exactly where it is. Typically when
> > people are talking about systems as large as yours, they're dedicated
> > database servers at that point, so the OS cache gets considered at the
> > same time. If it's effectively out of the picture, the spot where
> > caching still helps even when it's somewhat inefficient due to buffer
> > contention isn't well explored.
>
> It also depends on the filesystem. In testing at Sun and on this list,
> people have found that very large s_b (60% of RAM) plus directIO was
> actually a win on Solaris UFS, partly because UFS isn't very agressive
> or smart about readahead and caching. On Linux/Ext3, however, it was
> never a win.

Again, it depends.

On my recent testing of a simple seqscan on 1-int table, that are
entirely in cache (either syscache or shared buffers), the shared
buffers only scan was 6% to 10% percent faster than when the relation
was entirely in system cache and each page had to be switched in via
syscall / context switch.

This was on Linux/Ext3 but I suspect this to be mostly independent of
file system.

Also, in ancient times, when I used Slony, and an early version of Slony
at that, which did not drop and recreate indexes around initial copy,
the copy time could be 2 to 3 _times_ slower for large tables with lots
of indexes when indexes were in system cache vs. when they were in
shared buffers (if I remember correctly, it was 1G shared buffers vs. 3G
on a 4G machine). It was probably due to all kinds of index page splits
etc which shuffled index pages back and forth a lot between userspace
and syscache. So this is not entirely read-only thing either.

--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training