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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD