Re: Scaling concerns - Mailing list pgsql-performance

On Sun, 17 Dec 2006, tsuraan wrote:

> Since my application is constantly adding to the database (far more is
> written than is ever read), it would be nice to have a multiple-write,
> single reader solution, if such a thing exists.

You seem to be working from the assumption that you have a scaling issue,
and that therefore you should be researching how to scale your app to more
machines.  I'm not so sure you do; I would suggest that you drop that
entire idea for now, spend some time doing basic performance tuning for
Postgres instead, and only after then consider adding more machines.  It
does little good to add more incorrectly setup servers to the mix, and
solving the multiple-write problem is hard.  Let's take a quick tour
through your earlier messages:

> My postgres settings are entirely default with the exception of
> shared_buffers being set to 40,000 and max_connections set to 400. I'm
> not sure what the meaning of most of the other settings are, so I
> haven't touched them.

http://www.powerpostgresql.com/Downloads/annotated_conf_80.html is a good
intro to the various parameters you might set, with some valuable hints on
the effective range you should be considering.  I'd suggest you use that
to identify the most likely things to increase, then read the manuals at
http://www.postgresql.org/docs/8.2/interactive/runtime-config.html for
more detail on what you're actually adjusting.  To get you started,
consider increasing effective_cache_size, checkpoint_segments, and
work_mem; those are three whose defaults are very low for your
application, relative to your hardware.  The thought of how your poor
database is suffering when trying to manage a heavy write load with the
default checkpoint_segments in particular makes me sad, especially when we
add:

> The machines running the database servers are my home desktop (a
> dual-core athlon 3200+ with 2GB RAM and a 120GB SATA II drive), and a
> production server with two dual-core Intel chips, 4 GB RAM, and a RAID 5
> array of SATA II drives on a 3Ware 9550 controller.

One big RAID 5 volume is probably the worst setup available for what
you're doing.  Luke already gave you a suggestion for testing write speed;
you should run that test, but I wouldn't expect happy numbers there.  You
might be able to get by with the main database running like that, but
think about what you'd need to do to add more disks (or reorganize the
ones you have) so that you could dedicate a pair to a RAID-1 volume for
holding the WAL.  If you're limited by write performance, I think you'd
find adding a separate WAL drive set a dramatically more productive
upgrade than trying to split the app to another machine.  Try it on your
home machine first; that's a cheap upgrade, to add another SATA drive to
there, and you should see a marked improvement (especially once you get
the server parameters set to more appropriate values).

I'd also suggest that you'd probably be able to get more help from people
here if you posted a snippet of output from vmstat and iostat -x with a
low interval (say 5 seconds) during a period where the machine was busy;
that's helpful for figuring out where the bottleneck on your machine
really is.

> Trying to do "INSERT INTO Messages(path, msgid) SELECT (path, msgid)
> FROM tmpMessages" took a really long time before psql died with an
> out-of-memory error.

Do you have the exact text of the error?  I suspect you're falling victim
to the default parameters being far too low here as well, but without the
error it's hard to know exactly which.

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