Home / server / Questions / 137818
Accepted
bobinabottle
Asked: 2010-05-02 21:41:11 +0800 CST

cluster live postgres 8.3 server

  • 772

Our web application is getting more and more traffic, which is making our poor pg8.3 database server have a little trouble keeping up. I've had a look into using pgpool II for clustering the db to relieve a little strain, and I was wondering how this should be done to minimise downtime considering I would be clustering a live database.

Has anyone had experience with this or know of any guides to follow? Cheers :)

database cluster postgresql

1 Answers

  1. Best Answer

    First, are you sure clustering is the answer to your performance problem? Have you considered:

    1. Caching. If you have "public" pages that are heavily used and hit the database, frontend caching is very simple to setup and will probably do wonders (think httpd + mod_cache)
    2. Optimize your application. I'm going to assume that you already know your bottleneck is Postgres. Run pgFouine on a day's worth of logs, find the top queries, index them or try to avoid them.
    3. Improve hardware on the Postgres server. If you are CPU bound (rare case), maybe a better CPU or more cores will help. You can always add more RAM so that more stuff is cached (and make sure you use it! Postgres default memory settings are very conservative- this might be your problem, check that you are using all your RAM). Also, a big enough RAID10 array will do wonders...

    If you want to setup clustering anyway- and in Postgres land I'm inclined to think it should be more for availability purposes than for performance- pgpool provides some tricks to replicate the databases with very little downtime (http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#online-recovery).

    • 1