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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
