Forum OpenACS Development: Compatibility Matrix

Hi all,

Has there been any progress in using versions of OpenACS components beyond the ones in the Compatibility Matrix ( https://openacs.org/xowiki/openacs-compatibility-matrix )?

Ubuntu 11.10 installs Postgresql 9.2 by default.

Are any known issues working with pg 9 on OpenACS (besides the pg8 config changes)?

Correction, ubuntu 11.10 installs pg 9.1 currently.

Barebones recipe:

http://sql-info.de/postgresql/notes/converting-tsearch2-to-8.3.html

Not sure how this affects OpenACS search (or other) package code..

Hi Ryan,

I see that it's included, but the transition doesn't appear trivial. Does the tsearch2-search package automatically adjust to the changes?

Eduardo,

You are right about the changes in PostgreSQL, which became worse for largish OpenACS/DotLRN sites after 8.3; for smallish sites, 8.4+ is ok. Therefore that's not a question about compatibility, but about recommended versions depending on the site's needs.

Starting from PostgreSQL 8.4, the postgres optimizer prefers in more cases sequential scans (at least with pretty standard pg-config files). This is especially bad for permissions (and search) in large sites with dotlrn. For this reason, we kept our production site a long time in PostgreSQL 8.3.*, and we hoped that the postgres optimizer in newer versions would behave better for these cases.

During this summer, we lost our patience and changed in our main installations the permission management to recursive queries [1] and switched to PostgreSQL 9.0.*. The change involved as well changes in the basic data-model (the huge materialized transitive tables). The work was done mostly by Victor Guerra. As a result, permission queries are now for our site significantly faster (often 4 times) than with 8.3. I think, there is still some need for cleanup in our installation (Victor is currently on vacation), but we will commit our changes back to CVS when ready.

-gustaf neumann

[1] http://www.postgresql.org/docs/9.0/static/queries-with.html

Hi Dave,

As Gustaf mentioned, we made some modifications in our installation in order to have a better performance when moving from PG 8.3 to PG 9.0. The changes mostly affect permissions.

Basically one has to run a couple of scripts and change some xql files. So the code is not in reality much. Everything is packaged in its own package so applying the changes should not be hard for a stand alone openacs installation; the only thing is that steps for upgrading need to be well stablished so people dont get lost. So, Ill try to dedicate a couple of hours this and next week and do some clean up and docu ( and some testing ;) ).

Best,

Hi,

I have applied Victors changes and it works fine except when trying to update the context hierarchy or set permissions inheritance since it then fires the triggers to maintain the tables.

The code I got from Victor did not include any data model changes or changes to the triggers so I am not sure where to go from here.

I know Victor is on vacation but if someone from Vienna has some information, or anyone else, and I am leaving this message as a record of my progress.

Thanks
Dave

Here is where I am experiencing some issues

2.5 seconds:
update acs_objects
set security_inherit_p = 'f'
where object_id = '1290509'

1.6 seconds
select acs_permission__grant_permission(
'182879',
'27310',
'read'
);

So it appears that these are probably updating acs_object_party_privilege_map and related tables via triggers with this which will get really slow if you need to update more than 1 object. Its already pretty slow, several seconds to change permissions on 1 object.

Very interesting results.

If you try to change the access policy on a bugtracker instance it calls set_inherit and grant_permission on every bug.

This can be hundreds of objects. The first ones took 1-2 seconds per query as the page runs by the end the last objects take 1 ms.

It appears Postgresql is loading the tables/indexes into memory and as that happens to seems to help.

Maybe this is not a signifigant issue unless you try to issue hundreds of small queries in one submission.

Try reducing random_page_cost to something lower than the default value 4, and raising the value of effective_cache_size to the size of your shared buffers + something reasonably large for the OS filesystem cache (and remember, modern disk drives cache too!).

We were having problems with unwanted sequential scans where indexed scans were obviously 50x faster in a production system I'm working on (NOT OpenACS/dotLRN) and by fiddling with these parameters I was able to persuade the planner to use indexed scans where it seemed to me it should.

I set random_page_cost to 1.0 ...

I can also help with testing so please let me know how I can help.

One hopes that sequential scans include an interpolation process on sorted keys to help reduce the scan range.

Hi Don,

Good to know. Thank you.

It seems to me that smart (whatever it's called) interpolation of an ordered set could be faster than sequencing once the count reaches some massive number.. By "smart" I'm referring to a modified conventional mathematical interpolation, where the next best guess uses the last two data points to suggest an intersecting line and intersection point to jump to --providing the next best guess.

Assuming 69 million rows, ordered, non-linear values, one could probably cut to search range with a very small count within just a few steps.. Would be fun to play with.

Anyway, I've successfully used a technique like this to reduce the interpolation loops of some business math functions to something reasonable for dynamic web pages.. About 12 iterations on projects with millions of values, non-linear. I'd be glad to share, if you know someone that want to pursue this.

cheers,

Hi Don,

Yeah, hair brain idea. I get it. I just passed the idea on to someone that could do something about it if there was anything to it; They didn't see it applicable to DB scans either.

Before we decided in our installation to head toward recursive queries, we were fiddling as well with the page costs. We could persuade in some queries pg to use the index, in some queries we had to turn off the optimizer, and we had to rearrange queries, etc. Improving the results from some queries often worsen the results on others, some queries were still not effected, the results were not really encouraging; furthermore, for packaging openacs in distros, recommended configuration changes make life more complicated.

From our experience getting rid of the huge materialized tree tables (having many duplicate keys), and move towards recursive queries looks much more promising. As effect, e.g. permission queries are now several times faster with 9.0 then they were with 8.3 (we are using the recursive queries in production since summer). However, this path requires some data model changes. Victor has the packaging of the changes in a more or less non-destructive way mostly done, just a matter of days. The package will be made available to the brave (i.e. early adapters). Depending on the results we can decide via TIP, whether that should go into the main branches.