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

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

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Dave Crooke <dcrooke(at)gmail(dot)com>
Cc: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>, 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 02:09:24
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Greg SmithDate: 2010-01-16 02:25:43
Subject: Re: a heavy duty operation on an "unused" table kills my server
Previous:From: Tom LaneDate: 2010-01-16 02:05:20
Subject: Re: New server to improve performance on our large and busy DB - advice? (v2)

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