Forum OpenACS Development: Re: OpenACS performance issues

Collapse
Posted by Gustaf Neumann on
> 0 ms gethandle gethandle (returned pool2)
> 389 ms 0or1row
> dbqd.acs-tcl.tcl.acs-permissions-procs.permission::permission_p_not_cached.select_permission_p: 0or1row nsdb0

permission handling seems very slow. maybe you have missed some updates adding indices; what is the output of the following postgres commands?

\d acs_permissions
select count(*) from acs_permissions;

\d acs_object_context_index
select count(*) from acs_object_context_index;

\d acs_privilege_descendant_map
select count(*) from acs_privilege_descendant_map;

Collapse
Posted by Matthew Coupe on
=# \d acs_permissions
Table "public.acs_permissions"
Column | Type | Modifiers
------------+------------------------+-----------
object_id | integer | not null
grantee_id | integer | not null
privilege | character varying(100) | not null
Indexes:
"acs_permissions_pk" PRIMARY KEY, btree (object_id, grantee_id, privilege)
"acs_permissions_grantee_idx" btree (grantee_id)
"acs_permissions_object_id_idx" btree (object_id)
"acs_permissions_privilege_idx" btree (privilege)
Foreign-key constraints:
"acs_permissions_grantee_id_fk" FOREIGN KEY (grantee_id) REFERENCES parties(party_id)
"acs_permissions_on_what_id_fk" FOREIGN KEY (object_id) REFERENCES acs_objects(object_id)
"acs_permissions_priv_fk" FOREIGN KEY (privilege) REFERENCES acs_privileges(privilege)

=# select count(*) from acs_permissions;
count
--------
193374
(1 row)

=# \d acs_object_context_index
Table "public.acs_object_context_index"
Column | Type | Modifiers
---------------+---------+-----------
object_id | integer | not null
ancestor_id | integer | not null
n_generations | integer | not null
Indexes:
"acs_object_context_index_pk" PRIMARY KEY, btree (object_id, ancestor_id)
"acs_obj_ctx_idx_ancestor_idx" btree (ancestor_id)
Check constraints:
"acs_obj_context_idx_n_gen_ck" CHECK (n_generations >= 0)
Foreign-key constraints:
"acs_obj_context_idx_anc_id_fk" FOREIGN KEY (ancestor_id) REFERENCES acs_objects(object_id)
"acs_obj_context_idx_obj_id_fk" FOREIGN KEY (object_id) REFERENCES acs_objects(object_id)

n=# select count(*) from acs_object_context_index;
count
--------
859795
(1 row)

=# \d acs_privilege_descendant_map
Table "public.acs_privilege_descendant_map"
Column | Type | Modifiers
------------+------------------------+-----------
privilege | character varying(100) | not null
descendant | character varying(100) | not null
Indexes:
"acs_priv_desc_map_idx" btree (descendant)
Foreign-key constraints:
"acs_priv_hier_child_priv_fk" FOREIGN KEY (descendant) REFERENCES acs_privileges(privilege)
"acs_priv_hier_priv_fk" FOREIGN KEY (privilege) REFERENCES acs_privileges(privilege)

=# select count(*) from acs_privilege_descendant_map;
count
-------
309
(1 row)

Collapse
Posted by Matthew Coupe on
On my dev box I changed the Master Template back to Zen and I was still getting some slow request times on the permission checks.

429 ms 0or1row dbqd.acs-tcl.tcl.acs-permissions-procs.permission::permission_p_not_cached.select_permission_p: 0or1row nsdb0

select 1 from dual
where exists
( select 1
from acs_object_party_privilege_map ppm
where ppm.object_id = '3551196' and ppm.party_id = '14554' and ppm.privilege = 'read' )

Collapse
Posted by Gustaf Neumann on
zen is no performance bottleneck, it won't affect the speed of permissions at all.

we have on our learn system more than 10 times the number of entries in the permission tables, and much better speed (most time below 1ms, with up to 2000 concurrently active users; but we have also a different hardware).

The indices you have are correct, these are the indices of the standard openacs installation.

We have on our productive environment two indices more:
Try to add these:

create index acs_privilege_descendant_map_privilege_idx on acs_privilege_descendant_map (privilege);
create index acs_object_context_index_object_id_idx on acs_object_context_index (object_id);
after creating the indices, do a "VACUUM" on both tables.

measure again. It is a while, since i have added these indices. They might be an artefact of our local modifications. if they don't not help, remove the indices again.

Collapse
Posted by Matthew Coupe on
> zen is no performance bottleneck, it won't affect the speed of permissions at all.

Sorry, I wasn't very clear. I was benchmarking against Zen to see if there are any extra expensive queries in my custom Zen template. Zen is a little faster but that is to be expected as our custom template is a little more complex.

I added the indices but there was little effect and some queries were still taking 240ms+.

Dave mentioned increasing Shared_Buffers from 32MB - I tried this but Postgresql wouldn't restart. What sort of value should I be going for here? Could this be the issue?

Collapse
Posted by Gustaf Neumann on
Seems twice as fast, but not really fast.
Have you set shmmax as indicated above? shmmax must be larger than the shared buffers, otherwise postgres won't start. note, that if you have multiple postgres servers running, you need memory for each server. What is the message, when postgres refuses to start?