Re: PG 8.3 and large shared buffer settings

Поиск
Список
Период
Сортировка
От Hannu Krosing
Тема Re: PG 8.3 and large shared buffer settings
Дата
Msg-id 1254160810.16062.12.camel@hvost1700
обсуждение исходный текст
Ответ на Re: PG 8.3 and large shared buffer settings  (Josh Berkus <josh@agliodbs.com>)
Список pgsql-performance
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



В списке pgsql-performance по дате отправления:

Предыдущее
От: "Sgarbossa Domenico"
Дата:
Сообщение: Performance problems with DISTINCT ON
Следующее
От: mange
Дата:
Сообщение: Performance RAID 0