Forum OpenACS Q&A: PostgreSQL vs Oracle
I have a few questions regarding OpenACS and PostgreSQL/Oracle :
1. Did anybody try to run a larger OpenACS installation on a cluster of 4CPU DB servers running PostgreSQL 7 or 8 ? If so, what were the results ?
2. Even better, did anybody try to run similar set-up with Oracle 10g ? What was the difference in the overall performance between the system running on PostgreSQL and Oracle ?
3. Do you consider PostgreSQL database to be a mature and reliable DB alternative to Oracle 10g - especially considering more complex setups - such as clustered DB servers ? Are there any serious drawbacks to consider when going with PostgreSQL (including performance-wise) ?
4. Does OpenACS 4.6 support PostgreSQL 8, or do you recommend to upgrade to 5.1 in one go ?
5. Is there a plan for OpenACS to add MySQL support in the next months/years ?
Even partial reply, or hint where to get more relevant information will help.
I have not tried it with 4 CPUs in production only 2, and probably having a lot of CPU is not the answer - instead look at just adding a lot of RAM such as 8GB or 16GB to your system.
After Postgres starts up and has been running for a while, if properly configured there will not be a lot of disk access unless you are adding data into the database (e.g. uploading, blogs, etc.) because the most commonly requested tuples will already be in RAM.
What is the reason to go to clustering? Is it for scalability, or for avoiding hardware caused downtime, or something else?
I think you need to explain a little more what you hope a clustered database will do for you.
The only part of MySQL I can see being supported in the future is the MAXdb (I think it is called), which is the open-source version of the SAPDB database, and which does have the needed things like subselects and stored procedures that OpenACS uses. Of course a database driver would need to be written for it.
We hope to have some performance data within the next month from some very large sites that are running .LRN / OpenACS with PostreSQL. One of things we need to sort out first is to agree on a common metric. It would also be nice if measurement tools were incorporated into releases and each installation would "phone home" automatically with performance data.
I will let the technical folks speak about MySQL but my two-cents: a) don't think there is a plan; b) and not worth the effort.
I've run OpenACS on both Oracle and Postgres. The Oracle install is a 2 CPU box, performs adequately. The PG installs are smaller configurations. I definitely consider PG a "mature and reliable" alternative to Oracle. The thing you get with PG over Oracle is easier administration and faster recovery in case of crash. Oracle can be a bear to administer, ESPECIALLY in a clustered (RAC) configuration. Note that PG clustering is new (as of 8? Someone please correct me), and Oracle took 3 revisions to get it "right" (some people will argue successfully they dont even have it right in 10g).
I've been using PG 8 for all my development servers since OACS 5.1. I haven't experienced any compatibility issues.
Do you seriously anticipate a need for such clustering at AIESEC, or are you just asking for background knowledge? What does your current RDBMS dataset, usage, hardware, and load look like?
I have not tried out Oracle 10g's clustering features at all yet. I will sometime, but for its remote offsite backup and failover capabilities, not in an attempt to increase performance. My (limited) understanding is that this is what all of Oracle's "clustering" functionality is primarily aimed at - data integrity and business continuity, not increased throughput. In fact I believe using Oracle clustering tends to slow down your RDBMS slightly vs. the single box case, not speed it up. (You may, of course, get a speed up if you use the slave nodes for large slow read-only queries, thus unloading those from the master node.)
PostgreSQL does have extensive replication and failover capabilities now, but I'm not particularly familiar with them, nor how they compare to what Oracle 10g has. That said, I think PostgreSQL does not yet have multi-master (writes allowed on multiple nodes) capability (Oracle does), but they're working on it.
University of Vienna is running on PostgreSQL and they are happy with it, serving up to 1000 users at a time (if I remember the statistics correctly), without clustering.
The technical side apart there is growing consent that supporting two databases officially is already delaying our release cycles. Therefore I'm pretty sure we will not add any other database to the officially supported list. If someone wants to maintain a port though, he is most definitely welcome to do so.