[Rt-devel] Why are Groups cachedmembers of themselves?

[Joby Walker]

Yes things are going along swimmingly.  The change resulted in a
significant drop in database usage from ~50-65% idle (with peaks to 20%
idle) to 65-75% idle (with peaks to 40% idle).

Joby Walker
C&C SSG, University of Washington


Jesse Vincent wrote:
> Is this change still happy in production for you?
> 
> best,
> jesse
> 
> ------- Original message -------
> From: Joby Walker <joby at u.washington.edu>
> Cc: jesse at bestpractical.com, rt-devel at lists.bestpractical.com
> Sent: 11/2,  11:45
> 
>> Jesse,
>>
>> I've pushed the below changes to our dev system and purged the duplicate 
>> cachedgroupmember records -- we haven't come across a problem yet. 
>> Doing so dropped the size of the cachedgroupmember table by more than 
>> 2/3!  In our production system (currently) this will mean a drop of 65% 
>> (833,989 records to 288,706).
>>
>> Joby Walker
>> C&C SSG, University of Washington
>>
>>
>> Joby Walker wrote:
>>> Jesse Vincent wrote:
>>>>> So why is this necessary?  Is it for ACL checks on User Defined groups?
>>>> It's for ~all group ACL and groupmember checks.
>>>>
>>> Ok, it took me a while to see where the cachedgroupmember record is 
>>> necessary: When you make a check to see if a group has the ACL to do X 
>>> (exactly what you said).
>>>
>>> The impact of adding hundreds of thousands of additional records seems 
>>> like a very high cost to be able to do these group queries just like 
>>> user permission queries.
>>>
>>> I grabbed a fresh rt-3.4.4 (what we currently use) and Modified the 
>>> following:
>>>
>>> RT::Group_Overlay->_Create()
>>>
>>> comment out line 511,512 where the group is added as a cached member of 
>>> itself.
>>>
>>> RT::Principal_Overlay->HasRight()
>>>
>>> Add on line 401 (just before the "Build that honkin-big SQL query") a 
>>> check that if the Principal is a Group to determine if that group has a 
>>> specific ACL.
>>>
>>>     if ($self->IsGroup) {
>>>     my $qb = 'SELECT ACL.id from ACL, Principals WHERE'
>>>             ." ( ACL.RightName = 'SuperUser' OR ACL.RightName = '$right' 
>>> ) "
>>>         ."AND Principals.Disabled = 0 "
>>>         ."AND Principals.id = " . $self->Id . " "
>>>         ."AND Principals.id = ACL.principalid "
>>>         ."AND ( " . join( ' OR ', @look_at_objects ). ") ";
>>>         $self->_Handle->ApplyLimits( \$qb, 1 );
>>>         my $hitcount = $self->_Handle->FetchResult($qb);
>>>     if ($hitcount) {
>>>             return (1);
>>>         }
>>>     }
>>>
>>>
>>> With these two changes all of the regression tests pass.
>>>
>>> Joby Walker
>>> C&C SSG, University of Washington
>>> _______________________________________________
>>> List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
>