Forum OpenACS Development: improving permission queries

Request notifications

Collapse
Posted by Gustaf Neumann on
On large installations, permission checking might become a performance bottleneck. We experienced this some time ago with 1000+ concurrent users in learn@wu. The following index helped us to speed permission queries by more than a factor of 10.

create index acs_permissions_object_id_idx on acs_permissions(object_id);

i have added this to CVS head (sql creation procs, and update for oracle and postgres).

Collapse
Posted by Malte Sussdorff on
And I thought it was there all along. Thanks for tracking this down.

In my opinion this should be getting into the 5.3.1 release of OpenACS core, as I would say a speedup of permission queries by a factor of 10 translates into a bug in the previous version.

Collapse
Posted by Gustaf Neumann on
there are couple of indices already on the various permission related tables, but not that one. When the server is getting to its limits, db-queries that take under "sunshine conditions" 2ms will take sometimes 2 or more seconds. I would think that for most rather low traffic installations, this index does not matter. For us it did, so i reported it back.

It is not a bug, the functional behavior is exactly the same like before. Every trained oacs admin can feed the single line into her database. But be aware that the database upgrade script will barf on upgrading upgrade-5.3.0b1-5.3.0b2.sql
I found no simple and proven way to create indices conditionally on postgres and oracle.

-gustaf neumann
PS: the actual upgrade scripts were missing at the time of my original post two days ago.

Collapse
Posted by Dave Bauer on
This seems to work on Postgresql reliably. I can run it multiple times without an error.

Tested on PG 8.0.7. I am not aware of any change in the system catalogs that would make this incompatible with PG 8.1 or 8.2.

create or replace function inline_0() returns integer as '
declare v_exists integer;
begin
select into v_exists count(*) from pg_class where relname = ''acs_permissions_object_id_idx'';
if v_exists = 0 then
create index acs_permissions_object_id_idx on acs_permissions(object_id);
end if;
return null;
end;' language 'plpgsql';
select inline_0();
drop function inline_0();

Collapse
Posted by Dave Bauer on
Here is the oracle version, tested on 10g.
declare v_exists integer;

begin
select count(*) into v_exists from user_indexes where lower(index_name)='acs_permissions_object_id_idx';
if v_exists = 0 then
    execute immediate 'create index acs_permissions_object_id_idx on acs_permissions(object_id)';
end if;

end;
/
show errors
Collapse
Posted by Don Baccus on
Please add this to the 5.3 branch if you have time before the weekend final release deadline. I'm amazed I missed this when I rewrote most of the rest of the code in the permissions system, clearly that index is needed.
Collapse
Posted by Gustaf Neumann on
done