Forum OpenACS Development: Postgres9, OpenACS and load balancing

Hi, all!

Last week we upgraded our DB from Postgres 8.2.13 to
Postgres 9.0.3. The size of our OpenACS DB is over 60 GB.
We have touched a little code to run in this PG release
and get selects to better performance.

Now we have running the model one master DB (RW) and one slave DB (RO). If we have a crash, we can start with the RO DB changing a bit the confs file.

I am now playing a bit with how to use the RO instance. I have coded a pair of functions to select the RO dbn for pure SELECTs and round robbing that selects (-dbn flag). It is Ok, but I like a more generic aproximation, not change one to one all the selects in the code.
Perhaps doing the round robbing in the tcl driver? But many code is in PGPSQL functions ...

Any idea, please?

Regards,
Agustin

Collapse
Posted by Eduardo Santos on
Hi Jose,

I'v been trying to find a good set of PostgreSQL tools to provide load balancing for a while, and already used Warm Standby and Slony-I. Both have problems, and the best choice depends on how your system is configured. In a general way, you can see the following issues:

- Warm Standby is good for crash recovery, but you can't use the other DB's even for RO operations, so there's no load balancing possibility. The main advantage is that it's easy to configure and there's almost no maintenance effort.

- Slony-I has the advantage that the slave DB's can be used to RO operations, but it's extremely difficult to configure (you have to write a script for every sinlge table replication) and even more hard to keep working (maintenance work is just really big). Use it only if you don't use too many OpenACS packages and now Slony-I very well.

I'm still looking forward to test PostgreSQL 9.0 Hot Standby ( http://www.postgresql.org/docs/9.0/static/hot-standby.html ), wich is basically the same as Warm Standby with the possibility to use the slave node for RO operations. I guess it'll be the best choice for OpenACS installs, but I'm not currently using it and didn't test it yet.

Best regards

Collapse
Posted by Maurizio Martignano on
Dear Augustin,
I had a look at this table:
http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling
It seems pgpool could be (COULD BE) the way to go.
On top of that by using a middleware in between the client and the server(s) you don't need to change your client code.
I was just wondering if somebody had already used pgpool and with which results...
Thanks,
Maurizio
Collapse
Posted by Eduardo Santos on
Hi Maurizio,

I also use PgPool mre as a load balancer. I've never used it as a replication tool, because the opinions I got said it's not too much stable.

If you have Hot Standby with a lot of slave servers, I'm sure PgPool is the way to go to load balancing the connections.