Skip site navigation (1) Skip section navigation (2)

Re: New server to improve performance on our large and busy DB - advice? (v2)

From: Dave Crooke <dcrooke(at)gmail(dot)com>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)ca>, pgsql-performance(at)postgresql(dot)org
Subject: Re: New server to improve performance on our large and busy DB - advice? (v2)
Date: 2010-01-16 01:49:25
Message-ID: ca24673e1001151749q64636758gfcf2f4e8ac3fd71f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
This is the second time I've heard that "PG shared buffer on Windows doesn't
matter" ... I'd like to understand the reasoning behind that claim, and why
it differs from other DB servers.

.... though that's much less important for Pg than for most other things, as
> Pg uses a one-process-per-connection model and lets the OS handle much of
> the caching. So long as the OS can use all that RAM for caching, Pg will
> benefit, and it's unlikely you need >2GB for any given client connection or
> for the postmaster.
>

Any DB software would benefit from the OS buffer cache, but there is still
the overhead of copying that data into the shared buffer area, and as
necessary unpacking it into in-memory format.

Oracle uses a more or less identical process and memory model to PG, and for
sure you can't have too much SGA with it.

It's nice to have the flexibility to push up shared_buffers, and it'd be
> good to avoid any overheads in running 32-bit code on win64. However, it's
> not that unreasonable to run a 32-bit Pg on a 64-bit OS and expect good
> performance.
>

My reasoning goes like this:

a. there is a significant performance benefit to using a large proportion of
memory as in-process DB server cache instead of OS level block / filesystem
cache

b. the only way to do so on modern hardware (i.e. >>4GB) is with a 64-bit
binary

c. therefore, a 64-bit binary is essential


You're the second person that's said a. is only a "nice to have" with PG ...
what makes the difference?

Cheers
Dave

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2010-01-16 02:05:20
Subject: Re: New server to improve performance on our large and busy DB - advice? (v2)
Previous:From: Dave CrookeDate: 2010-01-16 01:37:50
Subject: Re: New server to improve performance on our large and busy DB - advice? (v2)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group