Forum OpenACS Q&A: PostgreSQL vs Oracle

Collapse
Posted by Ludek Houska on
Hi,

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.

Regards,

Ludek Houska
AIESEC International

Collapse
2: Re: PostgreSQL vs Oracle (response to 1)
Posted by Patrick Giagnocavo on
Here are my answers based on what is now several years of hosting for my customers (and being the one asked to track down any issues with the database):

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.

Collapse
3: Re: PostgreSQL vs Oracle (response to 1)
Posted by Alfred Essa on
Ludek,

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.

Collapse
4: Re: PostgreSQL vs Oracle (response to 1)
Posted by C. R. Oldham on
Ludek,

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).

Collapse
5: Re: PostgreSQL vs Oracle (response to 1)
Posted by Nick Carroll on
Ludek,

I've been using PG 8 for all my development servers since OACS 5.1. I haven't experienced any compatibility issues.

Cheers,
Nick.

Collapse
Posted by Andrew Piskorski on
Ludek, AFAICT a cluster of multiple quad CPU machines is huge, massive overkill for all but a minuscule fraction of RDBMS backed websites, even if you are also integrating substantial amounts of data that is not directly web-related. (One modern dual CPU box with 16 or 32 GB of RAM can do a lot...) So you aren't likely to find all that much real interest in such RDBMS clustering in the OpenACS community.

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.

For some related links see these older threads: one, two, three.

Collapse
7: Re: PostgreSQL vs Oracle (response to 1)
Posted by Malte Sussdorff on
OpenACS 4.6 does not support PostgreSQL 8 (to my knowledge) as some names in procedures had to be changed for it to work on 5.2. But the knowledge what has to be changed is in the community, so you might just as well as when you do the upgrade. But yes, definitely upgrade to 5.1 (or even 5.2, depending on when you want to switch).

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.