Forum OpenACS Development: Re: acs_object__new slowness...

Collapse
Posted by Don Baccus on
Jeff ... good call on the parameters.

You've correctly identified the bottleneck as being the creation of tree_sortkey.  The general fix is to add an additional integer column called (say) "max_leaf" to each node , which can be used directly, avoiding the need to traverse all the children of a node when adding a new child.

This needs to be done for all types which carry tree_sortkeys, and should be done for 5.0.

There are still concurrency issues which AFAIK are unsolvable in today's PG because locking within PL/pgSQL doesn't work right (which I define as "the way Oracle does it" in this case).  Locking has to be done at the application level to work the way one expects and this is impractical for a system like OpenACS.  Fortunately in practice it's not clear anyone's actually run into the concurrency bug ...

Collapse
Posted by Jeff Davis on
It looks like for things that have context_id null using a sequence to generate the root tree_sortkey gives roughly an order of magnitude speedup (and for context_id -2 I found a similiar result).

You have to add a sequence as well as a "max_leaf" column so that you can generate the root tree_sortkey when context_id is null; that or create a root object that would always exist -- which I guess is what security_context_root is.

The question is is it preferable to force everything to be a child of security_context_root or add a sequence?

Adding the sequence and extra column are straightforward (and if anything substantially improve the locking issues since the resulting queries are so much faster). here was the new acs_objects_insert_tr that I used with a sequence to generate the root context tree_sortkey.

create sequence acs_object_root_sortkey;
select setval('acs_object_root_sortkey',
  (select max(tree_leaf_key_to_int(tree_sortkey)) 
   from acs_objects 
   where context_id is null));

create or replace function acs_objects_insert_tr () returns opaque as '
declare
        v_parent_sk     varbit default null;
        v_max_value     integer;
begin
        if new.context_id is null or new.context_id = -2 then 
	    select nextval(''acs_object_root_sortkey'') into v_max_value from dual;
        else 
            select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value 
              from acs_objects 
             where context_id = new.context_id;
            select tree_sortkey into v_parent_sk 
              from acs_objects 
             where object_id = new.context_id;
        end if;

        new.tree_sortkey := tree_next_key(v_parent_sk, v_max_value);

        return new;
end;' language 'plpgsql';

(the context_id = -2 bit is a hack but is safe and I wanted to see how faster user creation would be with it since that is where I originally started from -- the answer is user creation on a system with 100k users takes 175ms as opposed to 3750ms w/o this change).

I am willing to fix it for 5.0 but I think it needs OCT approval and will write a TIP for it...

Oh, and some information on where the speedups will show up. Malte generated this from aiesec (which has ~5M objects and 850k with null context_id) and you can see the ones with null context_id:

  COUNT(*) OBJECT_TYPE
---------- ---------------------
    152201 acs_activity

    126381 apm_parameter_value
     11079 cal_item
     48268 calendar
      8138 mailbox
      4654 notification
    205921 notification_request
     45549 portal
    146999 portal_page
      5097 rel_segment
     23760 site_node
      3866 static_portal_content
    113891 user
So creation of users, site nodes, packages, notification requests, calendar items and portal pages should all be significantly improved.
Collapse
Posted by Andrei Popov on
<blockquote> The general fix is to add an additional integer column
called (say) "max_leaf" to each node , which can be
used directly, avoiding the need to traverse all the
children of a node when adding a new child.
</blockquote>

But wouldn't it require additional time to update it (albeit this may still be better than current state)?

Maybe Oracle-like CONNECT BY for Pg should be given a try?