[rt-users] Re: CachedGroupMembers table size

[Ruslan Zakirov]

Make sure you have an index on CachedGroupMembers.MemberId.

On 5/18/06, Alexey G Misyurenko <mag at caravan.ru> wrote:
> 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
>


-- 
Best regards, Ruslan.