Re: 1 or 2 servers for large DB scenario.

On Fri, 25 Jan 2008, David Brain wrote:

> The hardware storing this DB (a software RAID6) array seems to be very 
> IO bound for writes and this is restricting our insert performance to 
> ~50TPS.

If you're seeing <100TPS you should consider if it's because you're 
limited by how fast WAL commits can make it to disk.  If you really want 
good insert performance, there is no substitute for getting a disk 
controller with a good battery-backed cache to work around that.  You 
could just put the WAL xlog directory on a RAID-1 pair of disks to 
accelerate that, you don't have to move the whole database to a new 
controller.

> Also are there any settings that might boost our TPS on the existing hardware 
> (sync=off isn't an option..

Having a good write cache lets you run almost as fast as when fsync is 
off.

> Verson of  Postgresql is 8.2.3.

I'm hearing an echo here...8.2 versions before 8.2.4 have a bug related to 
statistics that can limit performance in several situations.  You should 
consider an upgrade just to remove that as a potential contributor to your 
issues.

To do a quick check on whether this is impacting things, run top, press 
"c" to show the full process lines, and note whether the statistics 
collector process is taking up a significant amount of CPU time.  If it 
is, you're definately being nailed by the bug, and you really need that 
upgrade.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD