Forum OpenACS Development: ACS 4.x won't scale (I hope I am wrong)

Even Philip says 4.x is incomplete and sucks, though of course he claims it is all the fault of the VCs and their lackeys, and that he bears no responsibility for this product.  I don't even want to go there...

Perms need a severe re-do, this is clear.  I've been thinking that this and the APM are two areas I would spend time on as the port progresses.  Not alone, necessarily, but its crucial enough so I'm going to want to have my thumbs stuck deep into it.

I don't think this needs to stand in the way of the porting effort, which after all is more than anything else about building a framework that can support multiple databases.  Yes, this seems a no-brainer but  after the Oracle uber alles attitude held by aD for so long it seems that a proof-of-concept goal must be utmost in our minds.

I guess I mean it's not going to be *worse* than the aD-delivered version when we get to the end of this first step.  After all, we'll be running their code.

I hope we'll have time to make it better before we declare victory (i.e. our first release), of course, but proving the feasibility of the multi-db approach is #1 on my priority list.  That's largely because I'm very confident we can fix these weaknesses once we've got the basic toolkit framework in place.  Again, hopefully that comes early enough in the process so we can redo the most gross aspects of 4.x before releasing.

And this is certainly one of them (the lack of meaningful admin stuff is another, I love the aD employee's comment in another thread about loading places and getting a list of 90,000 objects on his admin objects page!)

Right now I have to have this fixed (or at least 1 order of magnitude less f'd up) by Monday morning, so you will get your fix earlier than you think. This is my paying gig so I can't put it off for a later revision.

This also means that I can't do as much on consolidating the acs-messaging et al until this is fixed. Maybe I will get lucky and Malte will have a partial fix that can hold my client over.

Although I haven't played around much with ACS 4 much, I have spent the last 3 years working with Oracle in a bank, designing and implementing a risk management system. A big problem is things like which trade belongs to wich department, and departments are part of other departments etc.

I checked out some random bits I found at google ( just searcing for the name of the view actually turned up quite a few hits. )

http://www.google.com/search?q=cache:acs40.arsdigita.com/doc/sql/display-sql%3Furl%3Dacs-permissions-create.sql%26package_key%3Dacs-kernel+acs_privilege_descendant_map+&hl=da

From the small time I spend on it, it looks like that the view

create or replace view acs_privilege_descendant_map as select p1.privilege, p2.privilege as descendant from acs_privileges p1, acs_privileges p2 where p2.privilege in (select child_privilege from acs_privilege_hierarchy start with privilege = p1.privilege connect by prior child_privilege = privilege) or p2.privilege = p1.privilege;

is used alot, through the acs_object_grantee_priv_map. Is that also the case in the OpenACS distribution ? Anyway, on Oracle 7.3 which I was working on, views on views on views.. etc generally was very difficult to get working. The Oracle optimizer generally didn't do that great.

For what its worth I would suggest two possible solutions that could be explored.

1. Merge the views by hand. That is, take the view acs_object_party_privilege_map and copy paste the underlying views into this. Of course this makes the code less reusable but you don't have to break the structure of the OpenACS system. It is much easier to optimize this by hints, than on a series of dependent views.

2. The connect by view above, could also be part of the problem. A solution we used to speed performance was to create a table, with the contents of our department structure in. That way we could build indexes on the relevant keys.

Again, I haven't spend time on OpenACS 4 ( only 3.24 ) so the above may be nonsense, but I thought the problems described here where similar to some I had experienced earlier.

Probably, as was pointed out by others, the nesting of views is causing problems with optimizing the queries, though I'm not sure that postgresql has the same problem in this respect. It would to see how the two db's compare.

It seems to me that the aD design goal for the permissioning system was to be able to reach into one table or view and pull out a yes or no answer: permission to read, or not, or 'Can object A perform action B on object C'. Essentially this is done by counting at least one row in acs_object_party_privilege_map. This view is a join of two other views (acs_object_grantee_priv_map and group_member_map) unioned with one of the views (acs_object_grantee_priv_map). acs_object_grantee_priv_map is a join of two other views, etc...

Permissioning is either a difficult problem, or one that isn't solved very well in ACS. Although many times aD points out the state of the art for certain problems, I haven't seen a real discussion on this one.

The other day I setup an OpenLDAP server to work with the Netscape Roaming profile schema. I had to set one permission record in the startup file. This one record serves to configure access for all clients!

I don't really know if ldap is an answer, certainly not a simple answer. I just wonder how permissioning is done so easily in this application, and so painfully in the RDBMS world.

Looking at the requirements doc, one sees that the system answers more than "Can A do B?".  Answering "Can A do B?" seems to be where most permissioning systems stop; I like that this one makes it possible to answer other questions (from http://acs40.arsdigita.com/doc/acs-kernel/permissions/requirements.html ):

"Which parties may perform operation O on target T?"

"Which operations may party P perform on target T?"

"Upon which targets may party P perform operation O?"

These questions occur less often, and so thus could afford to be slower than the primary question, but they're still important and shouldn't be forgotten, and are awfully hard to answer without a table in the RDBMS to query against (which is also a requirement for writing queries that only return appropriate objects - basically any system that isn't in the RDBMS would suck because a lot of filtering of query result sets would be required).

ADSI doesn't seem to be an answer because it looks like LDAP - "Give me info about Bob, please" and doesn't seem to answer things like "Who can read my document?" or really be meant for controlling access to items on a finer scale than a bunch of items with a few groups each (as opposed to a zillion items and a zillion groups).  If I'm missing something, please point it out, I've only read their interface whitepaper (and fail to see what it does beyond replace LDAP).

The permissions system has several serious problems as has been pointed out in this thread.

I worked on tuning the permissions system when it was first released. The system (after some tuning) performed very well for the common cases, i.e. "Can user X perform action Y?" and was also able to answer other questions with very reasonable performance.

Since then, more functionality was added to the core, causing the permissions system to have some of these problems. This is our fault because we did not perform any more tuning and performance testing on the system after the changes.

I think both Finn and Henry have some good ideas for optimizations that can be performed right away to speed the system up.

First I would build all views on top of base tables instead of other views. Oracle will not optimize through views (I don't know about other databases). Once the views are rebuilt, I would tune each of them individually.

One thing I see often is people using the acs_permission.permission_p in where clauses. Oracle does not optimize this either (I also don't know about other databases). This is very bad; if you must have this in a where clause wrap the rest of the query in a view on the fly with this call on the outside (to minimize the number of rows you have to execute this on).

Henry is right, the permission system is not light weight. Developers should use it at a coarser level, e.g. permission forums not individual messages, etc.

I have seen the permission system, in an earlier stage, perform exceptionally, with some work it can again.

I appreciate the constructive suggestions being posted here, and to echo what Yon said, we are committed to making permissions scale. Overall, there seems to be support for the requirements and the API. If anyone thinks the requirements or API are missing something, please post the specifics. Likewise, if you think it is overengineered or does more than it should at the cost to other parts of the system.

The problem appears to be in the current ACS 4.2 implementation. If we can find common patterns that lead to improved Oracle and Postgres data models, I'll be ecstatic.

The big slowdown is acs_permission.permission_p (which calls that ugly view). Since it is already a pl/sql proc it might make sense to simply query the base tables one at a time and if the select count(*) returns > 0 then there is no need to query the next table(s).

This was a quick thought so I may be missing something, but we have a procedural language there is really no need to do it all in sql.

Here is his email to me: ---

It looks like the permissions data model isn't completely dead. At least the question, "Does party A have permission P on object O?"

I rewrote acs_permission.permission_p and got rid of the unions. That didn't help at all performance wise, but it did allow me to rewrite the selects invidually, and after a while it was clear that only the first select was the hog. I rewrote the first one using oracle exist, and that fixed it. I guess the exist short circuts the huge selects like you would expect. I did the same for the other 4 selects in the union for good measure.

btw, I'm not sure I know what some of those selects are doing, especially the one that puts a 0 in the party column.

----

I put the file on http://jongriffin.com/static/openacs/permissions/ for public scrutiny.

If anyone else would like to check it out please feel free to do so. I will commit this into OpenACS in a few days to give it a little testing period.

FYI, here's what my acs_permission.permission_p (Oracle) looks like right now. As you can see, I have not ditched the acs_object_party_privilege_map view yet as you have, despite Michael B.'s comment in the source suggesting it...

  function permission_p (
    object_id	 acs_objects.object_id%TYPE,
    party_id	 parties.party_id%TYPE,
    privilege	 acs_privileges.privilege%TYPE
  ) return char
  as
    exists_p char(1);
  begin
    -- We should question whether we really want to use the
    -- acs_object_party_privilege_map since it unions the
    -- difference queries. UNION ALL would be more efficient.
    -- Also, we may want to test replacing the decode with
    --  select count(*) from dual where exists ...
    -- 1/12/2001, mbryzek

    -- I've gone ahead and replaced it the "select decode(count(*),0,0,1)
    -- from ..." with the superior "select count(*) from dual where exists
    -- (select 1 from ... )".  However, mbryzek's comments re. union
    -- vs. union all, above, are still quite cogent...
    -- --atp@arsdigita.com, 2001/04/29 05:29 EDT

    select decode(count(*), 0,'f', 't') into exists_p
    from dual where exists (select 1
      from acs_object_party_privilege_map
     where object_id = permission_p.object_id
       and party_id = permission_p.party_id
       and privilege = permission_p.privilege
    );
    return exists_p;
  end;

And  yea, decode doesn't buy you much when you are alreadly using a procedural language.

This is in the acs-permissions-create.sql file.

I encourage everyone to update.

The sad results which i managed to *achieve* with testing scalability was:
1) using functions in sql (which procces number of rewos > 1)
2) usning null as criteria in where caluse (oracle diesn't include
null in indexes)
3) lame design of acs_messages and bboard

The permission datamodel design wasn't showstopper. the problem was
usage of it.

WBR

We were excited to find, and implement your bugfix ...from /web/openacs-4/packages/acs-kernel/sql/oracle from the nightly-2001-07-03.tar.tgz
<<acs-permissions-create.sql>>

Unfortunately this has not helped , and each attempt to open mailboxes causes the subsequent calls to permission_p which result in VERY dismal performance.

I read that your environment became very usable after the bugfix... do you have any suggestions as to why we dont see the same improvement ?

we are running RH 6.2 with Oracle 8.1.7 on a PIII 700 with 1G Ram
with 580 registered users, it takes roughly 10 to 15 seconds per page request... regardless of which page, or module...

Thanks in advance

I've suspected this would be the case for some time.  While I hate the  notion of burying dependencies on the permissions datamodel into client code, we may have to bite the bullet and do as Aram suggests, at least for crucial permissions checks that are executed very frequently.

I am working with an ACS4.x system that has over 200,000 users, 2 million acs_objects, 400K rows in acs_permissions, 6 million rows in acs_permissions_all, 13 million rows in acs_object_grantee_priv_map, and an apparently uncountable number of rows in acs_object_party_privilege_map (I gave up after 20 minutes).

I have no problems with permission_p slowing things down. Am I just lucky? Maybe the pages make too many calls to permission_p?

This along with the patch makes things extremely fast.

I modified Jon's upgraded acs_permission.permission_p() code and got some significant performance improvements by making two changes. First, I ditched using DECODE and WHERE EXISTS, in favor of simply using COUNT() and using sub-selects instead of joins in the group related sections. Secondly, I changed the order of precedence in the permissions check to Public, direct, group, and group_rels.

create or replace package body acs_permission 
as 
  procedure grant_permission ( 
    object_id    acs_permissions.object_id%TYPE, 
    grantee_id   acs_permissions.grantee_id%TYPE, 
    privilege    acs_permissions.privilege%TYPE 
  ) 
  as 
  begin 
    insert into acs_permissions 
      (object_id, grantee_id, privilege) 
    values 
      (object_id, grantee_id, privilege); 
  exception 
    when dup_val_on_index then 
      return; 
  end grant_permission; 
  -- 
  procedure revoke_permission ( 
    object_id    acs_permissions.object_id%TYPE, 
    grantee_id   acs_permissions.grantee_id%TYPE, 
    privilege    acs_permissions.privilege%TYPE 
  ) 
  as 
  begin 
    delete from acs_permissions 
    where object_id = revoke_permission.object_id 
    and grantee_id = revoke_permission.grantee_id 
    and privilege = revoke_permission.privilege; 
  end revoke_permission; 
  -- 
  function permission_p ( 
    object_id    acs_objects.object_id%TYPE, 
    party_id     parties.party_id%TYPE, 
    privilege    acs_privileges.privilege%TYPE 
  ) return char 
  as 
    v_count     INTEGER; 
  begin 
    -- Check public permissions 
    SELECT COUNT(*) INTO v_count 
    FROM   acs_object_grantee_priv_map 
    WHERE object_id = permission_p.object_id 
    AND   privilege = permission_p.privilege 
    AND   grantee_id = -1; 
    if v_count > 0 then 
        return 't'; 
    end if; 
    -- 
    -- Check direct permissions 
    SELECT COUNT(*) INTO v_count 
    FROM   acs_object_grantee_priv_map 
    WHERE object_id = permission_p.object_id 
    AND   privilege = permission_p.privilege 
    AND   grantee_id = permission_p.party_id; 
    if v_count > 0 then 
        return 't'; 
    end if; 
    -- 
    -- Check group permmissions 
    SELECT COUNT(*) INTO v_count
    FROM acs_object_grantee_priv_map 
    WHERE object_id = permission_p.object_id 
    AND   privilege = permission_p.privilege 
    AND   grantee_id IN (SELECT DISTINCT group_id 
                         FROM   group_approved_member_map 
                         WHERE  member_id = permission_p.party_id); 
    if v_count > 0 then 
        return 't'; 
    end if; 
    -- 
    -- relational segment approved group 
    SELECT COUNT(*) INTO v_count 
    FROM   acs_object_grantee_priv_map 
    WHERE object_id = permission_p.object_id 
    AND   privilege = permission_p.privilege 
    AND   grantee_id IN (SELECT DISTINCT segment_id 
                         FROM   rel_seg_approved_member_map 
                         WHERE  member_id = permission_p.party_id); 
    if v_count > 0 then 
        return 't'; 
    end if; 
    -- 
    return 'f'; 
  end; 
  -- 
end acs_permission; 
/ 
show errors 

The big bottleneck was the performance of the group and group-rels related permission checks, which is now much better. Everything seems to work great now, but am I missing something?

Andrew,

I'm running the ACS 4.2 tarball from the ACS Repository, not OpenACS. To verify, I did a diff on my acs-permissions-create.sql and the one from the stock tarball, and they were identical.

I don't really understand how the permissions via relational segments is supposed to work. I'm not sure how to get rel_seg_approved_member_map.segment_id to get inserted as acs_object_grantee_priv_map.grantee_id so I can't run a similar query to what you have. Everything I do to try to create a relational segment just ends up populating group_approved_member_map instead.

I haven't run analyze or dbms_stats yet. I need to learn how to do that next. In terms of performance, the difference between IN and JOIN is over a second on my system, per call to acs_permission.permission_p. For a bboard thread of 10 messages, it takes over 10 seconds to return the page. Like you said, I don't have much data yet, but my site had gotten progressively worse as what little load I had increased.

I know the cartesian join is the problem. The data model is kind of hairy, but I think the problem lies somewhere in the fact that objects, privileges and grantees (ie. parties) all have a heirarchical structure. We already have to compare a privilege and all its super-privileges to an object and all the contexts that contain it, and when we throw groups into the mix (grantee and all its group memberships) using JOIN, we end up with a cartesian join, but by using IN, we avoid it by explicitly splitting the query in a way that the optimizer is not choosing on its own. That's just my guess, anyway.

The only performance analysis that will help us measure scalability is that performed on installations with meaningful amounts of data (I think you pass this test) with statistics properly gathered as will be done for a production site.

Both DBs will generate horrible plans if tables aren't analyzed.  Both will do so occassionally even if they *are* properly analyzed but not nearly as often.

Both of these plans look like good ones to me.  As to which runs faster you either need to find a slower computer (since they both report "CPU 0.00") or insert a lot more data ...

Either way works.

Andrew: I'm wondering the same thing i.e. why does the permission_p
function check the group and rel segment perms before the direct perms. Shouldn't the checks (from first to last: group, relseg, direct, public, public-like) be ordered from most common to least common?

Reviving the dead again -- in calendar code, query get_viewable_calendar called in /calendar/admin/calendar-preferences is a killer (aside from a strange state in which calendar code is, as there are some unported queries, etc.). It uses calendar__readable_p finction that is basically this:

CREATE FUNCTION calendar__readable_p(
        integer,
        integer
)
RETURNS boolean
AS 'declare
        readable_p__calendar_id         alias for $1;
        readable_p__party_id            alias for $1;
        v_readable_p                    boolean;

    begin
        select (case count(*)
                when 1 then true
                        else false
                ) into v_readable_p
        from    acs_object_party_privilege_map
                where   party_id = readable_p__party_id
                and     object_id = readable_p__calendar_id
                and     privilege = ''calendar_read'';

        return v_readable_p;
end;'
LANGUAGE 'plpgsql';

First of all, readable_p__party_id seems better be aliased to $2, not $1. case must be re-written, as this syntax is incorrect. After my wimpy changes it looks thusly:

CREATE FUNCTION calendar__readable_p (
	integer,
	integer
)
RETURNS boolean AS '
DECLARE
	readable_p__calendar_id		alias for $1;
        readable_p__party_id            alias for $2;
	v_readable_p			boolean;
BEGIN

        /*
         *
         * Using acs_object_party_privilege_map is EVIL -- it takes AGES!
         * We only need objects that are labeled as cal_item, hence...
         *
         */

        select case 
                when count(*) = 1 then ''t''
                else ''f''
               end into v_readable_p
        from dual where exists (
                select  1
                from    acs_object_party_privilege_map 
                where   party_id = readable_p__party_id
                        and object_id = readable_p__calendar_id 
                        and privilege = ''calendar_read''
        );

	RETURN v_readable_p;
END;
' LANGUAGE 'plpgsql';

...and kills OACS outright. A simple select calendar__readable_p(o.object_id, 4683) as readable_p from acs_objects o; (where 4683 is my id) has been running for some 10-20 minutes already and there are only 400 or so objects in acs_objects. FWIW, analyze has just been run...

I'l try to re-write acs_object_party_privilege_map to not use a cascade of UNIONs on views on views on views, and see whether this helps...

We've also talked about specifying a special directory for static files that don't need to be checked, but that won't happen until 4.7.