Re: pgtune + configurations with 9.3

От: Mark Kirkwood
Тема: Re: pgtune + configurations with 9.3
Дата: ,
Msg-id: 5466898C.9070809@catalyst.net.nz
(см: обсуждение, исходный текст)
Ответ на: Re: pgtune + configurations with 9.3  (Shaun Thomas)
Ответы: Re: pgtune + configurations with 9.3  (Jim Nasby)
Re: pgtune + configurations with 9.3  (Stuart Bishop)
Список: pgsql-performance

Скрыть дерево обсуждения

pgtune + configurations with 9.3  (Tory M Blue, )
 Re: pgtune + configurations with 9.3  (Albe Laurenz, )
 Re: pgtune + configurations with 9.3  (Josh Berkus, )
  Re: pgtune + configurations with 9.3  (Shaun Thomas, )
   Re[2]: [PERFORM] pgtune + configurations with 9.3  (Alexey Vasiliev, )
    Re: pgtune + configurations with 9.3  (Shaun Thomas, )
     [PERFORM] pgtune + configurations with 9.3  (Alexey Vasiliev, )
      Re: pgtune + configurations with 9.3  (Shaun Thomas, )
       Re[2]: [PERFORM] pgtune + configurations with 9.3  (Alexey Vasiliev, )
        Re: Re[2]: [PERFORM] pgtune + configurations with 9.3  (Stuart Bishop, )
       Re: pgtune + configurations with 9.3  (Mark Kirkwood, )
        Re: pgtune + configurations with 9.3  (Jim Nasby, )
         Re: pgtune + configurations with 9.3  (Mark Kirkwood, )
        Re: pgtune + configurations with 9.3  (Stuart Bishop, )
         Re: pgtune + configurations with 9.3  (Johann Spies, )
 Re: pgtune + configurations with 9.3  (Johann Spies, )
  Re: pgtune + configurations with 9.3  (Johann Spies, )

On 15/11/14 06:06, Shaun Thomas wrote:
> Alexey,
>
> The issue is not that 8GB is the maximum. You *can* set it higher. What I'm saying, and I'm not alone in this, is
thatsetting it higher can actually decrease performance for various reasons. Setting it to 25% of memory on a system
with512GB of RAM for instance, would be tantamount to disaster. A checkpoint with a setting that high could overwhelm
prettymuch any disk controller and end up  completely ruining DB performance. And that's just *one* of the drawbacks. 
>

It is probably time to revisit this 8GB limit with some benchmarking. We
don't really have a hard and fast rule that is known to be correct, and
that makes Alexey's job really difficult. Informally folk (including
myself at times) have suggested:

min(ram/4, 8GB)

as the 'rule of thumb' for setting shared_buffers. However I was
recently benchmarking a machine with a lot of ram (1TB) and entirely SSD
storage [1], and that seemed quite happy with 50GB of shared buffers
(better performance than with 8GB). Now shared_buffers was not the
variable we were concentrating on so I didn't get too carried away and
try much bigger than about 100GB - but this seems like a good thing to
come out with some numbers for i.e pgbench read write and read only tps
vs shared_buffers 1 -> 100 GB in size.

Cheers

Mark

[1] I may be in a position to benchmark the machines these replaced at
some not to distant time. These are the previous generation (0.5TB ram,
32 cores and all SSD storage) but probably still good for this test.




В списке pgsql-performance по дате сообщения:

От: Mark Kirkwood
Дата:
Сообщение: Re: pgtune + configurations with 9.3
От: Yuri Kunde Schlesner
Дата:
Сообщение: Plan uses wrong index, preferring to scan pkey index instead