Thread: effective_cache_size on 32-bits postgres

effective_cache_size on 32-bits postgres

From
Rodrigo Barboza
Date:
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?

Re: effective_cache_size on 32-bits postgres

From
Rob Wultsch
Date:
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


Re: effective_cache_size on 32-bits postgres

From
Pavel Stehule
Date:
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


Re: effective_cache_size on 32-bits postgres

From
Rodrigo Barboza
Date:
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.
> 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

Re: effective_cache_size on 32-bits postgres

From
Pavel Stehule
Date:
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


Re: effective_cache_size on 32-bits postgres

From
Rodrigo Barboza
Date:
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>:
> 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

Re: effective_cache_size on 32-bits postgres

From
Pavel Stehule
Date:
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
>
>


Re: effective_cache_size on 32-bits postgres

From
Kevin Grittner
Date:
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


Re: effective_cache_size on 32-bits postgres

From
Claudio Freire
Date:
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.


Re: effective_cache_size on 32-bits postgres

From
Pavel Stehule
Date:
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


Re: effective_cache_size on 32-bits postgres

From
Rodrigo Barboza
Date:
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:
> 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.