RT Query builder slow

RT Users
1

Hi

Our RT system is generally very fast, except for when you click on anything
that displays the Query Builder screen. Once Query Builder has loaded and
you click “Add and Search”, the search itself is fast to return the results
and display subsequent pages.

Some details of our system

Ubuntu-server 6.06 LTS
RT 3.6.3 compiled from source.
Intel Xeon 3.0ghz.
using the latest DBI::SearchBuilder.

I can point you to our server usage stats if required but will need your IP
addresses to grant access.

Thanks

Neil Turner

NetBanx Disclaimer

Confidentiality: This e-mail and its attachments are intended for the above
named only and may be confidential. If they have come to you in error you
must take no action based on them, nor must you copy or show them to anyone;
please reply to this e-mail and highlight the error.

Security Warning: Please note that this e-mail has been created in the
knowledge that Internet e-mail is not a 100% secure communications medium.
We advise that you understand and observe this lack of security when
e-mailing us.

Viruses: Although we have taken steps to ensure that this e-mail and
attachments are free from any virus, we advise that in keeping with good
computing practice the recipient should ensure they are actually virus free.

2

you’re using mysql 5.x or you granted OwnTicket right to too many
users. Which one?On 6/27/07, Neil Turner neil.turner@netbanx.com wrote:

Hi

Our RT system is generally very fast, except for when you click on anything
that displays the Query Builder screen. Once Query Builder has loaded and
you click “Add and Search”, the search itself is fast to return the results
and display subsequent pages.

Some details of our system

Ubuntu-server 6.06 LTS
RT 3.6.3 compiled from source.
Intel Xeon 3.0ghz.
using the latest DBI::SearchBuilder.

I can point you to our server usage stats if required but will need your IP
addresses to grant access.

Thanks

Neil Turner

Best regards, Ruslan.

3

Or possibly both :slight_smile:

Which mysql version do you recommend and
How many users is too many for OwnTicket?

Thanks for your reply

NeilFrom: ruslan.zakirov@gmail.com [mailto:ruslan.zakirov@gmail.com] On Behalf
Of Ruslan Zakirov
Sent: Wednesday, June 27, 2007 3:34 PM
To: neil.turner@netbanx.com
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] RT Query builder slow

you’re using mysql 5.x or you granted OwnTicket right to too many users.
Which one?

4

We have mysql 5.022 - can you let us know why that is a problem?

All users have OwnTicket but I think the right may be repeated due
to queues having that right as well as a general rule for privileged
users - could this be it?

NeilFrom: ruslan.zakirov@gmail.com [mailto:ruslan.zakirov@gmail.com] On Behalf
Of Ruslan Zakirov
Sent: Wednesday, June 27, 2007 3:34 PM
To: neil.turner@netbanx.com
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] RT Query builder slow

you’re using mysql 5.x or you granted OwnTicket right to too many users.
Which one?

5

We have mysql 5.022 - can you let us know why that is a problem?
Mysql 5.x uses wrong order of joins for a query. We have no solution
on RT side. I tried to fix this problem several times, but now I think
we have to file bug into mysql bug tracker.

All users have OwnTicket but I think the right may be repeated due
to queues having that right as well as a general rule for privileged
users - could this be it?
How many users you’re seeing in the select box? 1-100 is ok, 1000 is not.

Neil

-----Original Message-----
From: ruslan.zakirov@gmail.com [mailto:ruslan.zakirov@gmail.com] On Behalf
Of Ruslan Zakirov
Sent: Wednesday, June 27, 2007 3:34 PM
To: neil.turner@netbanx.com
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] RT Query builder slow

you’re using mysql 5.x or you granted OwnTicket right to too many users.
Which one?

Best regards, Ruslan.

6

We have mysql 5.022 - can you let us know why that is a problem?
Mysql 5.x uses wrong order of joins for a query. We have no solution
on RT side. I tried to fix this problem >several times, but now I
think we have to file bug into mysql bug tracker.

OK, thanks for the info, however see below

How many users you’re seeing in the select box? 1-100 is ok, 1000 is not.

We got to the bottom of this, one particular queue had
privileges set such that Everyone could be an owner of
a ticket. The QueryBuilder was attempting to populate
the Owner dropdown list with everyone who had emailed
RT (tens of thousands!)

Disabling this feature fixed this problem - QueryBuilder
now displays in under 3 seconds now.

Thanks

Neil

7

We have mysql 5.022 - can you let us know why that is a problem?
Mysql 5.x uses wrong order of joins for a query. We have no solution
on RT side. I tried to fix this problem >several times, but now I
think we have to file bug into mysql bug tracker.

OK, thanks for the info, however see below
Can you send me explain of the following query? I’m collecting debug
info to make things better.
EXPLAIN SELECT DISTINCT main.* FROM
ACL ACL_4,
Groups Groups_3,
CachedGroupMembers CachedGroupMembers_2,
Principals Principals_1,
Users main
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.Disabled = ‘0’))
AND ((Principals_1.PrincipalType = ‘User’))
AND ((Principals_1.id != ‘1’))
AND ((main.id = Principals_1.id))
AND ( ACL_4.ObjectType = ‘RT::Queue’ OR ACL_4.ObjectType = ‘RT::System’ )
AND (Groups_3.Domain = ‘RT::Queue-Role’)
ORDER BY main.RealName ASC;

How many users you’re seeing in the select box? 1-100 is ok, 1000 is not.

We got to the bottom of this, one particular queue had
privileges set such that Everyone could be an owner of
a ticket. The QueryBuilder was attempting to populate
the Owner dropdown list with everyone who had emailed
RT (tens of thousands!)
that’s what I was talking about. RT is not designed for such setups,
may be we’ll deny granting OwnTicket right to Everyone and
Unprivileged groups.

Disabling this feature fixed this problem - QueryBuilder
now displays in under 3 seconds now.
much better

Thanks

Neil

Best regards, Ruslan.