Обсуждение: Setting Shared-Buffers

Hi Rafael,

On Thu, Jul 09, 2009 at 07:18:55PM -0300, Rafael Domiciano wrote:

> Today, I've upgraded a dedicated postgres server, from 2 Gb to 10 Gb.
> Everything gone well.
>
> But, I would like shared buffers to use at least 5 Gb of the total memory.
> Setting kernel.shmmax with 6291456000 (6000 Mb) is not working properly, the
> server is changing the value to a small one.
> So I can't set the shared buffers to the value that I want.
>
> Now, just momently, the server is running with only 2 Gb of shared buffers,
> but I want to use all the capacity of the server/memory.
>
> Can anyone help you,
>
> Linux Fedora Core 9
> postgres=# select version();
>                                               version
> ---------------------------------------------------------------------------------------------------
>  PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.3.0
> 20080428 (Red Hat 4.3.0-8)

You cannot use more than 3.something GB of memory on a 32 bit system. A
single process is limited to IIRC 3.5 GB of address space on such
systems and shared memory is part of it's address space. If the server
supports such amounts of memory, it is likely that you may run a 64 bit
OS on it. (Note: You need a full dump/restore of the whole PostgreSQL DB
space if you switch to 64 bit.)

HTH,

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.craniosacralzentrum.de

Rafael Domiciano <rafael.domiciano@gmail.com> wrote:

> So, what's a good value to set to effective_cache_size with 10 Gb
> RAM?

It depends on how many other things you have on the system which are
using RAM, but probably something around 9GB would be appropriate.
Maybe even 9.5GB.

-Kevin

On Fri, Jul 10, 2009 at 08:49:40AM -0500, Kevin Grittner wrote:

> > So, what's a good value to set to effective_cache_size with 10 Gb
> > RAM?
>
> It depends on how many other things you have on the system which are
> using RAM, but probably something around 9GB would be appropriate.
> Maybe even 9.5GB.

I've seen PostgreSQL perform a lot worse after setting
effective_cache_size to 2 GB on a 8 GB dedicated database system. The
planner started ignoring indices and doing sequential scans. Lowering
effective_cache_size to 512 MB solved that.

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.craniosacralzentrum.de

Tino Schwarze <postgresql@tisc.de> wrote:

> I've seen PostgreSQL perform a lot worse after setting
> effective_cache_size to 2 GB on a 8 GB dedicated database system.
> The planner started ignoring indices and doing sequential scans.
> Lowering effective_cache_size to 512 MB solved that.

You'd probably get overall better plans by fixing that with other
configuration options.  If the active portion of your database is
pretty well cached, you might try setting both random_page_cost and
seq_page_cost to 0.1.  If your data isn't that well cached, you could
try leaving seq_page_cost at 1 but lower random_page_cost to somewhere
in the neighborhood of 2.

-Kevin

On Fri, Jul 10, 2009 at 3:26 PM, Anj Adu<fotographs@gmail.com> wrote:
> You can use upto 64G of RAM on a 32 bit RHEL 5/ Fedora 8 OS using the kernel
> PAE extension.

And it's about 15% slower, and pgsql itself can only access ~2 or 3G
shared and 2G per process.  I routinely set shared_buffers to well
over 3G on big machines, and have a few reporting queries that run
truly huge work_mem settings.  Really, there's not much reason to be
running postgresql on 32 bit unix anymore, unless you're stuck using
an ancient flavor or something.

However, I was referring to Windows, where things are even worse, as
the OS only sees 3Gigs total cause apparently it doesn't support PAE.

Oh wait, that was a different thread.  info still holds though.

On Fri, Jul 10, 2009 at 8:04 PM, Scott Marlowe<scott.marlowe@gmail.com> wrote:
> On Fri, Jul 10, 2009 at 3:26 PM, Anj Adu<fotographs@gmail.com> wrote:
>> You can use upto 64G of RAM on a 32 bit RHEL 5/ Fedora 8 OS using the kernel
>> PAE extension.
>
> And it's about 15% slower, and pgsql itself can only access ~2 or 3G
> shared and 2G per process.  I routinely set shared_buffers to well
> over 3G on big machines, and have a few reporting queries that run
> truly huge work_mem settings.  Really, there's not much reason to be
> running postgresql on 32 bit unix anymore, unless you're stuck using
> an ancient flavor or something.
>
> However, I was referring to Windows, where things are even worse, as
> the OS only sees 3Gigs total cause apparently it doesn't support PAE.
>



--
When fascism comes to America, it will be intolerance sold as diversity.

On Fri, Jul 10, 2009 at 10:27 PM, <sriram.dandapani@bt.com> wrote:
>
> 2G per process is plenty ...and useful if you have large data warehouse style queries which are long running
(especiallymultiple of those) 

For you, yes.  But not necessarily for others.

> We do benefit from the Linux memory caching model regardless of what Postgres uses right ?

Definitely.

> On a machine which we upgraded from 4G to 16G on a 32 bit PAE kernel...we saw a doubling of performance for most
queriesof a certain type.(mostly data warehouse type accessing several hundreds of thousands of records). 
>
> Postgres version that we use is 8.1.9.

I bet you'd see another big performance improvement with a 64bit OS
and pgsql AND an upgrade to 8.4.  But if it's fast enough, then stick
to 8.1.x  I would recommend an update to the latest 8.1 release
though.

Hi Scott.

It worse... only 2 GB, although if you modify boot.ini and include /3G flag it's possible to see 3GB per process (but some errors could emerge in certain windows apps).  

>However, I was referring to Windows, where things are even worse, as
>the OS only sees 3Gigs total cause apparently it doesn't support PAE.

2G per process is plenty ...and useful if you have large data warehouse style queries which are long running
(especiallymultiple of those) 

We do benefit from the Linux memory caching model regardless of what Postgres uses right ?

On a machine which we upgraded from 4G to 16G on a 32 bit PAE kernel...we saw a doubling of performance for most
queriesof a certain type.(mostly data warehouse type accessing several hundreds of thousands of records).  

Postgres version that we use is 8.1.9.

-----Original Message-----
From: pgsql-admin-owner@postgresql.org on behalf of Scott Marlowe
Sent: Sat 7/11/2009 3:04 AM
To: Anj Adu
Cc: Tino Schwarze; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Setting Shared-Buffers

On Fri, Jul 10, 2009 at 3:26 PM, Anj Adu<fotographs@gmail.com> wrote:
> You can use upto 64G of RAM on a 32 bit RHEL 5/ Fedora 8 OS using the kernel
> PAE extension.

And it's about 15% slower, and pgsql itself can only access ~2 or 3G
shared and 2G per process.  I routinely set shared_buffers to well
over 3G on big machines, and have a few reporting queries that run
truly huge work_mem settings.  Really, there's not much reason to be
running postgresql on 32 bit unix anymore, unless you're stuck using
an ancient flavor or something.

However, I was referring to Windows, where things are even worse, as
the OS only sees 3Gigs total cause apparently it doesn't support PAE.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin