Forum OpenACS Development: Forums and Notifications scalabililty ...

Since this topic arose in a thread below I thought I'd summarize what I know thus far ...

1. The Notifications sweeper has a query that takes a long time under PG but not under Oracle.  I ported that query (it was originally in correct under both Oracle and PG) but in an Oracle context (Sloan), and apparently my straightforward port to SQL 92 outer join syntax leads to a shitty plan (PG refuses to optimize SQL 92 joins which ticks me off but let's not go there!)

Thanks for Robert Locke to uncovering this with his personal clone of the openacs.org data and code.  This is the proc that causes system load to go through the roof at times with PG chewing up processor time for long, long periods.

He and I have discussed some ideas for optimizing the query and he's going to try some of them out in the next few days.  I may find time to help, too, if he needs it (which he probably won't)

2. The thread count implementation actually counts posts per thread, which is expensive.  It would be relatively easy to denormalize this by maintaining the thread count in each thread root.  If anyone wants to tackle this, e-mail me in private and I'll fill in the details (should be easy, a day or two of work to do it for both PG and Oracle).

3. Object insertion.  This appears to be an issue in PG.  We already know this, to be honest, and the forums package has a faster implementation for its tree structure (which suffers from a race condition, unfortunately.)  This can be cleaned up and used globally.

#1 and #3 have cropped up because openacs.org is perhaps our largest PG-based site using forums, notifications, and with a fair number of objects.  Most larger OpenACS sites still run Oracle.  It's clear we need to pay more attention to PG scalability ... fortunately openacs.org is big enough that it appears to be a good testbed.  See, slow is actually good! :)

Collapse
Posted by Lamar Owen on
Let me mention the obvious thing:  if OpenACS.org is currently on PG 7.2.x, then upgrading to 7.3.3 should produce some performance improvements.  Is this a possibility given the codebase?  Of course, there's other performance improvements, but seeing how it would all work on 7.3.3 would be educational if nothing else.