Forum OpenACS Development: Performance boost for older installations

1: Performance boost for older installations

Posted by Gustaf Neumann on 01/12/07 11:24 PM

A side effect of the the forums query of eduardo santoshttp://openacs.org/forums/message-view?message_id=553128 was a positive surprise:

After sourcing ...../postgresql/content-revision.sql in older OpenACS installations, the performance of queries involving the content repository can improve drastically. This is due to the fact that probably an update script was omitted ages ago in the history of OpenACS. Note that the stored procedures executed in the database are not necessarily the same as in the .sql files if one upgrades an old database. This situations is certainly a pain do debug. Apparently the old revision-specific stored procedures had some performance flaws.

For example, the query
select * from xowiki_pagei p, cr_items ci ,xowiki_tags tags where ci.parent_id =363021 and ci.item_id = p.item_id and ci.live_revision = p.page_id and tags.item_id = ci.item_id and tags.tag = 'COMET';
on the database of openacs.org was running after sourcing the actual stored procedures faster by a factor of 700 (before 2483.00 ms, after 3.20 ms)

The fix for this will be added to the upgrade scripts of 5.3. So if you are planing to upgrade to 5.3 soon, there is no action required.

2: Re: Performance boost for older installations (response to 1)

Posted by Claudio Pasolini on 01/13/07 02:03 PM

I have several installations running oacs-5-1 with PostrgreSQL 7.4 and right now I can't afford upgrading them to oacs-5.2.

I didn't understand if the performance boost is possible only after upgrading or if I can simply source a version of content-revision.sql taken from oacs-5-2 into the oacs-5-1 database.

3: Re: Performance boost for older installations (response to 2)

Posted by Gustaf Neumann on 01/13/07 04:15 PM

The fix will go into the upgrade scripts of the 5.3 series.

If you are running 5.2 and postgres, and you do not want to wait or you do not want to upgrade soon to 5.3 (for unknown reasons) do the following:

connect the the database via psql
\df content_revision__new
if you see 7 versions of content_revision__new, everything is fine, nothing to be done.
if you see 4 versions, you should source the mentioned .sql-file.

if you are running 5.1 (or something earlier) the fix might help or not. i have no such animal around. In general, sourcing the .sql stored procedures of the accompanying version should not harm. Any way, i would recommend to make a database dump before sourcing the .sql-file in case you destroy something.

Hope this answers your questions...

best regards
-gustaf neumann

4: Re: Performance boost for older installations (response to 3)

Posted by Brian Fenton on 01/15/07 05:03 PM

Hi Gustaf,

That's very interesting. Do you know if something similar could be done with Oracle or is it a Postgres-only improvement? I have some old OpenACS systems running on Oracle that would enjoy a boost like that!

thanks
Brian

5: Re: Performance boost for older installations (response to 4)

Posted by Gustaf Neumann on 01/15/07 06:47 PM

That case is purely postgres specific. In general, it is possible that similar cases lurk around at at other places as well.

A good exercise would be to load "old" data into a new schema. Postgres has a --schema-only and --data-only flag for dumping the database. In theory it should be possible to build a new schema based on the modules, save the schema and load the original data on top of this....

no idea, whether oracle supports this as well...