[rt-users] 'strange' performance bottlenecks

[Ruslan Zakirov]

You wouldn't find anything special in RT logs. It's problem with MySQL
query optimizer/planner. Could you run EXPLAIN SELECT <this query>
from mysql shell several times and look if plan changes between.

Also you need to run:
ANALYZE TABLE ACL, CachedGroupMembers, GroupMembers, Groups, Principals, Users;
From MySQL shell to update server statistics for RT tables.

On 4/11/06, Luke Vanderfluit <lvanderf at internode.com.au> wrote:
> Hi.
>
> We have a large RT system, consisting of nearly 400000 tickets.
> Our set up is as follows:
>
> We have 2 machines:
>
> ticket1 (sun solaris 9)
> =======
> apache 1.3.27
> rt345
> mysql4 client
>
> ticket2 (sun X4100 solaris 10)
> =======
> mysql5
>
> The RT instance on ticket1 is talking successfully to ticket2 and
> producing mostly very good speeds.
> However we have a 'strange problem' occurring.
> Every so often, twice a day, the mysql instance (on ticket2) maxes out
> at 99% cpu use.
> Then RT starts running very slowly.
> This bottleneck can occur unpredictably and last for up to an hour.
>
> I am currently trying to track down this problem but not having a lot of
> success.
> When the congestion occurs the mysql processlist shows around 10 similar
> queries such as this one:
> /~~~~~~~~~~~~~~~~~~~~~~~~~~~
> SELECT COUNT(DISTINCT main.id) FROM Users main , Principals
> Principals_1, CachedGroupMembers CachedGroupMembers_2, Groups Groups_3,
> ACL ACL_4  WHERE ((ACL_4.PrincipalType = Groups_3.Type)) AND
> ((ACL_4.RightName = 'OwnTicket')) AND ((CachedGroupMembers_2.MemberId =
> Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_2.GroupId)) AND
> ((Principals_1.PrincipalType = 'User')) AND ((Principals_1.id != '1'))
> AND ((main.id = Principals_1.id)) AND ((ACL_4.ObjectType = 'RT::Ticket'
> AND ACL_4.ObjectId = 113141) OR (ACL_4.ObjectType = 'RT::Queue' AND
> ACL_4.ObjectId = 5) OR (ACL_4.ObjectType = 'RT::System')) AND
> ((Groups_3.Domain = 'RT::Ticket-Role' AND Groups_3.Instance = '113141')
> OR (Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = '5') OR
> (Groups_3.Domain = 'RT::System-Role'));
> \___________________________
>
> Each of these queries can take up to 100 or more seconds to complete
> during the bottleneck.
> If I run the query at 'normal' times, it returns almost immediately.
> It seems that the bulk of the these queries together, stifles RT and
> causes it to grind to a very slow pace. And maybe there is an underlying
> query that could cause it.
>
> My theory at present is that somewhere in the organisation someone is
> doing a task that includes a specific query or group of queries that
> causes RT to flounder.
>
> I am running rt in debug mode, but have no clues as yet as to what is
> causing the problem.
>
> Any help would be appreciated.
> Kind regards.
>
> --
> Luke
>
> _______________________________________________
> 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
>


--
Best regards, Ruslan.