Обсуждение: effective_cache_size on 32-bits postgres
Hi guys, I am worried about the effective_cache_size.
I run a 32-bits postgres installation on a machine with 64 bits kernel.
Should I limit effective_cache_size to a maximum of 2.5gb?
On Mon, Mar 18, 2013 at 10:53 AM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote: > Hi guys, I am worried about the effective_cache_size. > I run a 32-bits postgres installation on a machine with 64 bits kernel. > Should I limit effective_cache_size to a maximum of 2.5gb? That variables refers to fs cache, so 32 bit pg should not matter. Shared buffers and similar variables will be another matter. Why the heck are you running 32 bit pg on a 64 bit system? You are almost certainly doing it wrong. -- Rob Wultsch wultsch@gmail.com
Hello 2013/3/18 Rodrigo Barboza <rodrigombufrj@gmail.com>: > Hi guys, I am worried about the effective_cache_size. > I run a 32-bits postgres installation on a machine with 64 bits kernel. > Should I limit effective_cache_size to a maximum of 2.5gb? sure and probably little bit less Regards Pavel
Ok, now I'm lost, who is right about the limit? Rob or Pavel?
Rob, I know it should be a 64 bit, and it will be soon, but there are good reasons for this scenario and it's ok for now.
On Mon, Mar 18, 2013 at 3:15 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
2013/3/18 Rodrigo Barboza <rodrigombufrj@gmail.com>:> Hi guys, I am worried about the effective_cache_size.sure and probably little bit less
> I run a 32-bits postgres installation on a machine with 64 bits kernel.
> Should I limit effective_cache_size to a maximum of 2.5gb?
Regards
Pavel
2013/3/18 Pavel Stehule <pavel.stehule@gmail.com>: > Hello > > 2013/3/18 Rodrigo Barboza <rodrigombufrj@gmail.com>: >> Hi guys, I am worried about the effective_cache_size. >> I run a 32-bits postgres installation on a machine with 64 bits kernel. >> Should I limit effective_cache_size to a maximum of 2.5gb? > > sure and probably little bit less wrong reply - Rob has true Pavel > > Regards > > Pavel
So setting this as half of ram, as suggested in postgres tuning webpage should be safe?
It says it is a conservative value...
On Mon, Mar 18, 2013 at 3:18 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2013/3/18 Pavel Stehule <pavel.stehule@gmail.com>:wrong reply - Rob has true> Hello
>
> 2013/3/18 Rodrigo Barboza <rodrigombufrj@gmail.com>:
>> Hi guys, I am worried about the effective_cache_size.
>> I run a 32-bits postgres installation on a machine with 64 bits kernel.
>> Should I limit effective_cache_size to a maximum of 2.5gb?
>
> sure and probably little bit less
Pavel
>
> Regards
>
> Pavel
2013/3/18 Rodrigo Barboza <rodrigombufrj@gmail.com>: > So setting this as half of ram, as suggested in postgres tuning webpage > should be safe? > It says it is a conservative value... depends how much memory is used as cache ?? it can be a shared_buffers + file system cache Regards Pavel Stehule > > > On Mon, Mar 18, 2013 at 3:18 PM, Pavel Stehule <pavel.stehule@gmail.com> > wrote: >> >> 2013/3/18 Pavel Stehule <pavel.stehule@gmail.com>: >> > Hello >> > >> > 2013/3/18 Rodrigo Barboza <rodrigombufrj@gmail.com>: >> >> Hi guys, I am worried about the effective_cache_size. >> >> I run a 32-bits postgres installation on a machine with 64 bits kernel. >> >> Should I limit effective_cache_size to a maximum of 2.5gb? >> > >> > sure and probably little bit less >> >> wrong reply - Rob has true >> >> Pavel >> >> > >> > Regards >> > >> > Pavel > >
Rodrigo Barboza <rodrigombufrj@gmail.com> wrote: > So setting this as half of ram, as suggested in postgres tuning > webpage should be safe? Half of RAM is likely to be a very bad setting for any work load. It will tend to result in the highest possible number of pages duplicated in PostgreSQL and OS caches, reducing the cache hit ratio. More commonly given advice is to start at 25% of RAM, limited to 2GB on Windows or 32-bit systems or 8GB otherwise. Try incremental adjustments from that point using your actual workload on you actual hardware to find the "sweet spot". Some DW environments report better performance assigning over 50% of RAM to shared_buffers; OLTP loads often need to reduce this to prevent periodic episodes of high latency. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Mar 18, 2013 at 3:47 PM, Kevin Grittner <kgrittn@ymail.com> wrote: > Rodrigo Barboza <rodrigombufrj@gmail.com> wrote: > >> So setting this as half of ram, as suggested in postgres tuning >> webpage should be safe? > > Half of RAM is likely to be a very bad setting for any work load. > It will tend to result in the highest possible number of pages > duplicated in PostgreSQL and OS caches, reducing the cache hit > ratio. More commonly given advice is to start at 25% of RAM, > limited to 2GB on Windows or 32-bit systems or 8GB otherwise. Try > incremental adjustments from that point using your actual workload > on you actual hardware to find the "sweet spot". Some DW > environments report better performance assigning over 50% of RAM to > shared_buffers; OLTP loads often need to reduce this to prevent > periodic episodes of high latency. He's asking about effective_cache_size. You seem to be talking about shared_buffers. Real question behind this all, is whether the e_c_s GUC is 32-bit on 32-bit systems. Because if so, it ought to be limited too. If not... not.
2013/3/18 Kevin Grittner <kgrittn@ymail.com>: > Rodrigo Barboza <rodrigombufrj@gmail.com> wrote: > >> So setting this as half of ram, as suggested in postgres tuning >> webpage should be safe? > > Half of RAM is likely to be a very bad setting for any work load. > It will tend to result in the highest possible number of pages > duplicated in PostgreSQL and OS caches, reducing the cache hit > ratio. More commonly given advice is to start at 25% of RAM, > limited to 2GB on Windows or 32-bit systems or 8GB otherwise. Try > incremental adjustments from that point using your actual workload > on you actual hardware to find the "sweet spot". Some DW > environments report better performance assigning over 50% of RAM to > shared_buffers; OLTP loads often need to reduce this to prevent > periodic episodes of high latency. you are speaking about shared_buffers now. Pavel > > -- > Kevin Grittner > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company
Yes, Claudio. You got it.
But Rob seems to have already answered the confusion between 32 and 64 bits for effective_cache_size.
Actually I am creating generic configuration based on physical memory.
So I wanna be conservative about effective_cache_size. That's why I'm following postgres tuning website instructions. If it says it is conservative, that's good for me.
On Mon, Mar 18, 2013 at 3:51 PM, Claudio Freire <klaussfreire@gmail.com> wrote:
On Mon, Mar 18, 2013 at 3:47 PM, Kevin Grittner <kgrittn@ymail.com> wrote:He's asking about effective_cache_size. You seem to be talking about
> Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:
>
>> So setting this as half of ram, as suggested in postgres tuning
>> webpage should be safe?
>
> Half of RAM is likely to be a very bad setting for any work load.
> It will tend to result in the highest possible number of pages
> duplicated in PostgreSQL and OS caches, reducing the cache hit
> ratio. More commonly given advice is to start at 25% of RAM,
> limited to 2GB on Windows or 32-bit systems or 8GB otherwise. Try
> incremental adjustments from that point using your actual workload
> on you actual hardware to find the "sweet spot". Some DW
> environments report better performance assigning over 50% of RAM to
> shared_buffers; OLTP loads often need to reduce this to prevent
> periodic episodes of high latency.
shared_buffers.
Real question behind this all, is whether the e_c_s GUC is 32-bit on
32-bit systems. Because if so, it ought to be limited too. If not...
not.