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

Dave Crooke wrote:

> 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?

The PostgreSQL model presumes that it's going to be cooperating with the
operating system cache. In a default config, all reads and writes go
through the OS cache. You can get the WAL writes to be written in a way
that bypasses the OS cache, but even that isn't the default. This makes
PostgreSQL's effective cache size equal to shared_buffers *plus* the OS
cache. This is why Windows can perform OK even without having a giant
amount of dedicated RAM; it just leans on the OS more heavily instead.
That's not as efficient, because you're shuffling more things between
shared_buffers and the OS than you would on a UNIX system, but it's
still way faster than going all the way to disk for something. On, say,
a system with 16GB of RAM, you can setup Windows to use 256MB of
shared_buffers, and expect that you'll find at least another 14GB or so
of data cached by the OS.

The reasons why Windows is particularly unappreciative of being
allocated memory directly isn't well understood. But the basic property
that shared_buffers is not the only source, or even the largest source,
of caching is not unique to that platform.

> 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.

The way data goes in and out of Oracle's SGA is often via direct I/O
instead of even touching the OS read/white cache. That's why the
situation is so different there. If you're on an Oracle system, and you
need to re-read a block that was recently evicted from the SGA, it's
probably going to be read from disk. In the same situation with
PostgreSQL, it's likely you'll find it's still in the OS cache.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com