[rt-users] Re: CachedGroupMembers table size

[Alexey G Misyurenko]

Hello!

Some additional information. Oracle DBA inform that "Take" operation
make next one SQL Request

SELECT *
 FROM (SELECT limitquery.*, ROWNUM limitrownum
         FROM (SELECT acl.ID
                 FROM acl, GROUPS, principals, cachedgroupmembers
                WHERE (   acl.rightname = 'SuperUser'
                       OR acl.rightname = 'DelegateRights'
                      )
                  AND principals.disabled = 0
                  AND cachedgroupmembers.disabled = 0
                  AND principals.ID = GROUPS.ID
                  AND principals.ID = cachedgroupmembers.groupid
                  AND cachedgroupmembers.memberid = '10'
                  AND (   acl.objecttype = 'RT::System'
                       OR acl.objecttype = 'RT::System' AND acl.objectid 
= '1'
                      )
                  AND acl.principalid = principals.ID
                  AND acl.principaltype = 'Group'
                  AND (   GROUPS.domain = 'SystemInternal'
                       OR GROUPS.domain = 'UserDefined'
                       OR GROUPS.domain = 'ACLEquivalence'
                       OR GROUPS.domain = 'Personal'
                      )) limitquery
        WHERE ROWNUM <= 1)
WHERE limitrownum >= 1

This request is "havy" for any Database if

GROUPS contain 2'000'000 records, principals - 2'000'000 and 
cachedgroupmembers = 4'500'000


Alexey G Misyurenko wrote:
> Ruslan Zakirov wrote:
>> Yep, it's normal. I have code branch where I experiment with ideas on
>> how to get rid of some useless groups.
>>
> 
> Is any documents on the NET where I can read how to optimize  (compact)
> information on this tables or delete some information without risk?
> 
> I ask because we have I big speed degradation. 
> $RT::Handle->BeginTransaction() from SetOwner from Ticket_Overlay.pm
> take about 200 second to run.
> 
> DB & Apache allreade run on different servers.
> 
> 
> 
>> On 5/18/06, Alexey G Misyurenko <mag at caravan.ru> wrote:
>>> Alexey G Misyurenko wrote:
>>> > Hello!
>>> >
>>> > Is it normal that CachedGroupMembers table contain about 4'500'000 
>>> records?
>>> >
>>>
>>> Also is it normal that table Group & Principal contain 2'000'000 records
>>> (each one)?
>>>
>>> And is it normal that table Group containe record's
>>> where field Name is like 'User {digits}'?
>>>
>>>
>>> > rt:    3.4.4
>>> > about: 500'000 tickets
>>> > DB:    Oracle
>>> >
>>>
>>>
>>> -- 
>>> WBR,   Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
>>> CTO of Caravan ISP            http://www.caravan.ru
>>> Phone: +7 495 3632252         Cell:  +7 495 5082794
>>> _______________________________________________
>>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>>>
>>> Community help: http://wiki.bestpractical.com
>>> Commercial support: sales at bestpractical.com
>>>
>>>
>>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>>> Buy a copy at http://rtbook.bestpractical.com
>>>
>>>
>>> We're hiring! Come hack Perl for Best Practical: 
>>> http://bestpractical.com/about/jobs.html
>>>
>>
>>
> 
> 


-- 
WBR,   Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP            http://www.caravan.ru
Phone: +7 495 3632252         Cell:  +7 495 5082794