Thread: Setting Shared-Buffers

Setting Shared-Buffers

From
Rafael Domiciano
Date:
Hello People,

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)
(1 row)


Thnks,

Rafael Domiciano

Re: Setting Shared-Buffers

From
Tino Schwarze
Date:
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

Re: Setting Shared-Buffers

From
Scott Mead
Date:

On Thu, Jul 9, 2009 at 6:18 PM, Rafael Domiciano <rafael.domiciano@gmail.com> wrote:
Hello People,

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.

   What's your workload?  Is this db primarily for reporting or OLTP?

    If you have an OLTP style workload, I wouldn't recommend going much over 2.5 - 4 GB (depending on your specific workload).  Just set your 'effective_cache_size' higher.  This tells postgres how much memory that the OS has for caching and the database will perform better.
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)
(1 row)

  32 bit pg can't address that much memory.  You'd need to recompile or download the 64 bit packages.  I believe you'd need to dump / reload as well, but I may be off about that one.


--Scott

Re: Setting Shared-Buffers

From
Rafael Domiciano
Date:
Thnks for the replyies.

It's a slony slave db, for reporting.

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

On Thu, Jul 9, 2009 at 9:05 PM, Scott Mead <scott.lists@enterprisedb.com> wrote:

On Thu, Jul 9, 2009 at 6:18 PM, Rafael Domiciano <rafael.domiciano@gmail.com> wrote:
Hello People,

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.

   What's your workload?  Is this db primarily for reporting or OLTP?

    If you have an OLTP style workload, I wouldn't recommend going much over 2.5 - 4 GB (depending on your specific workload).  Just set your 'effective_cache_size' higher.  This tells postgres how much memory that the OS has for caching and the database will perform better.
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)
(1 row)

  32 bit pg can't address that much memory.  You'd need to recompile or download the 64 bit packages.  I believe you'd need to dump / reload as well, but I may be off about that one.


--Scott


Re: Setting Shared-Buffers

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

Re: Setting Shared-Buffers

From
Tino Schwarze
Date:
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

Re: Setting Shared-Buffers

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

Re: Setting Shared-Buffers

From
Anj Adu
Date:
You can use upto 64G of RAM on a 32 bit RHEL 5/ Fedora 8 OS using the kernel PAE extension.

On Thu, Jul 9, 2009 at 3:29 PM, Tino Schwarze <postgresql@tisc.de> wrote:
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

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

Re: Setting Shared-Buffers

From
Scott Marlowe
Date:
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.

Re: Setting Shared-Buffers

From
Scott Marlowe
Date:
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.

Re: Setting Shared-Buffers

From
Scott Marlowe
Date:
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.

Re: Setting Shared-Buffers

From
Iñigo Martinez Lasala
Date:
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.

Re: Setting Shared-Buffers

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






Re: Setting Shared-Buffers

From
Rafael Domiciano
Date:
Hello People,

I upgraded to a 64 bits System. Now, everything is OK. Thnk you guys.

On Sat, Jul 11, 2009 at 2:19 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
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 (especially multiple 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 queries of 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.

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