Home
The Toolkit for Online Communities
17514 Community Members, 2 members online, 2245 visitors today
Log In Register
OpenACS Home : Forums : OpenACS Q&A : prohibitive ACS permissioning slowness?

Forum OpenACS Q&A: prohibitive ACS permissioning slowness?

Icon of envelope Request notifications

Collapse
Posted by Peter Marklund on

I recently tried to add ACS permissioning to the folders in my Photo Archive application. Unfortunately, all SQL queries that I have tried so far to retrieve the sub-folders that the user has read permission on have been heineously slow. Here is an example of a query that I tried:

select path as full_path
  from par_folders pf1, acs_objects ao1
  where  ao1.object_id = pf1.folder_id
  and    ao1.context_id = '13667'
  and exists (select 1
              from all_object_party_privilege_map 
              where object_id = ao1.object_id
              and party_id = '2426'
              and privilege = 'read');

Here is the size of some of the tables and views involved:

marklundweb=# select count(*) from acs_objects;
 count 
-------
  9021
(1 row)

marklundweb=# select count(*) from parties;
 count 
-------
     6
(1 row)

marklundweb=# select count(*) from acs_privileges;
 count 
-------
    82
(1 row)

marklundweb=# select count(*) from all_object_party_privilege_map;
  count  
---------
 1455495
(1 row)

Is the ACS permissioning system still unscalable? Can my query somehow be optimized?

Collapse
Posted by Michael Bluett on
Shouldn't your query use numbers rather than strings when querying,  i.e. 13667 rather than '13667'?  [Not that I have OpenACS installed yet, just a SQL hunch.]
Collapse
Posted by Stephen . on
Use acs_permission.permission_p rather than the exists clause, you're only doing one permission check and this function is optimised for this.
Collapse
Posted by Stephen . on
Err sorry, your not just selecting one row, and I'm sure you started with the permission_p function anyway.  It was the morning after the night before and I shouldn't have been typing...

I would expect that even with the function call overhead permission_p would still perform better for small numbers of rows returned.  Did you notice a big difference?  It would be good to work out some guide lines for when to use the simple permission_p and when to go with inline SQL, and how.

Collapse
Posted by Andrew Grumet on
After experimenting with some of the more familiar approaches to permission_p scaling (such as restricting the number of rows that it is run on and avoiding placing it in a WHERE clause), we've started to look at its internals. A proposed change is mentioned in this thread: http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=0004rw&topic_id=12&topic=OpenACS%204%2e0%20Design. The thread did well for a short while and then sort of petered out (no pun intended). We have yet to carefully test the proposed changes but you might want to take a look at them.
Collapse
Posted by Peter Marklund on
<p>
Thanks for your replies! Unfortunately I gave up on using ACS permissions. I don't have time right now to look into how I could optimize ACS permissions and judging by the problems other people have had with the API I doubt that I would have much luck... Yes, I tried using acs_permission_p and I believe that was the slowest approach. I didn't do any timing though, how do I do that with Postgres? The fastest  query that I tried was to join with all_object_party_privilege_map (instead of using the sub-query in the posting above) but even that query was far too slow to be acceptable (I consider anything over 0.5 seconds to be too slow).
</p>

<p>
Instead of using ACS permissions I resorted to adding a public_p flag to my folders table. However, I then ran into the following query that was also slow:

<pre>
select path as full_path
  from par_folders pf1, acs_objects ao1
  where  ao1.object_id = pf1.folder_id
  and    ao1.context_id = :parent_id
  and (exists (select 1 from acs_objects ao2,
                        par_folders pf2
                  where  pf2.folder_id = ao2.object_id
                and    ao2.tree_sortkey between ao1.tree_sortkey
                and    tree_right(ao1.tree_sortkey)
                and    pf2.public_p = 't'
                  )
      )
</pre>

Now I would like to know what index that will best speed up my query. If I can't get that query fast I will simply resort to replacing the tree sub-query with a simple "and public_p = 't'" clause. That would at last give me a query that doesn't take forever...
</p>