Mysql slowness

RT Users
1

So upon upgrading to 3.4 (latest debain release), we started to notice
extreme slowness (in excess of 2 minutes) upon loading the query builder
or sometimes when trying to create a new ticket. To try to troubleshoot
this, first I tried to move from apache1 and mod_perl to apache2 with
fastcgi. There was some improvement, but still taking minutes to load
sometimes and fastcgi needed apache2 to be restarted daily otherwise it
hung. When watching the processes via top while loading query builder or
doing any of the other �slow� tasks, the mysqld process would sometimes
use upwards of 80-90 percent of the CPU. Due to the different
combinations of apache, apache2, and modperl, fastcgi, and speedycgi, the
observation from top leads me to believe that the slowness is not due to
the webserver or perl processor, but due to mysql. We have close to
19,000 tickets and numerious users in our system and it�s never been super
fast, but with 3.4, it�s becoming unbearable.

I�m wondering if anyone has any ideas to try and speed mysql or everything
up in general. Has anyone else had this problem with so many tickets? Is
the solution to drop some of the historical tickets?

Any help is greatly appreciated. Thanks!

Nate Kroll

Milwaukee School of Engineering

2

I found a little more out about this problem we seem to be having. The
slowest part seems to be when loading the query builder page and this
mysql query is run:

SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL
ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4 WHERE
((ACL_2.RightName = ‘OwnTicket’)) AND ((CachedGroupMembers_4.MemberId =
Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) AND
((Principals_1.Disabled = ‘0’)or(Principals_1.Disabled = ‘0’)) AND
((Principals_1.id != ‘1’)) AND ((main.id = Principals_1.id)) AND ( (
ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = ‘Group’ AND (
Groups_3.Domain = ‘SystemInternal’ OR Groups_3.Domain = ‘UserDefined’ OR
Groups_3.Domain = ‘ACLEquivalence’)) OR ( ( (Groups_3.Domain =
‘RT::Queue-Role’ ) ) AND Groups_3.Type = ACL_2.PrincipalType) ) AND
(ACL_2.ObjectType = ‘RT::System’ OR (ACL_2.ObjectType = ‘RT::Queue’) )
ORDER BY main.Name ASC;

The query has taken up to 59 seconds and is the most frequent slow query
logged to mysql. The query returns about 2100 results. Is this normal
for people or is something fubar-ed on our setup?

Also, the CachedGroupMembers table has about 170000 rows in it. Is this
normal for a rt setup with about 19000 tickets and less than 30 users?

Thanks,

Nate-----Original Message-----

So upon upgrading to 3.4 (latest debian release), we started to notice
extreme slowness (in excess of 2 minutes) upon loading the query builder
or sometimes when trying to create a new ticket. To try to troubleshoot
this, first I tried to move from apache1 and mod_perl to apache2 with
fastcgi. There was some improvement, but still taking minutes to load
sometimes and fastcgi needed apache2 to be restarted daily otherwise it
hung. When watching the processes via top while loading query builder
or
doing any of the other “slow” tasks, the mysqld process would sometimes
use upwards of 80-90 percent of the CPU. Due to the different
combinations of apache, apache2, and modperl, fastcgi, and speedycgi,
the
observation from top leads me to believe that the slowness is not due to
the webserver or perl processor, but due to mysql. We have close to
19,000 tickets and numerious users in our system and it’s never been
super
fast, but with 3.4, it’s becoming unbearable.

I’m wondering if anyone has any ideas to try and speed mysql or
everything
up in general. Has anyone else had this problem with so many tickets?
Is
the solution to drop some of the historical tickets?

Any help is greatly appreciated. Thanks!

Nate Kroll

Milwaukee School of Engineering

http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

3

I found a little more out about this problem we seem to be having. The
slowest part seems to be when loading the query builder page and this
mysql query is run:

SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL
ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4 WHERE
((ACL_2.RightName = ‘OwnTicket’)) AND ((CachedGroupMembers_4.MemberId =
Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) AND
((Principals_1.Disabled = ‘0’)or(Principals_1.Disabled = ‘0’)) AND
((Principals_1.id != ‘1’)) AND ((main.id = Principals_1.id)) AND ( (
ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = ‘Group’ AND (
Groups_3.Domain = ‘SystemInternal’ OR Groups_3.Domain = ‘UserDefined’ OR
Groups_3.Domain = ‘ACLEquivalence’)) OR ( ( (Groups_3.Domain =
‘RT::Queue-Role’ ) ) AND Groups_3.Type = ACL_2.PrincipalType) ) AND
(ACL_2.ObjectType = ‘RT::System’ OR (ACL_2.ObjectType = ‘RT::Queue’) )
ORDER BY main.Name ASC;

The query has taken up to 59 seconds and is the most frequent slow query
logged to mysql. The query returns about 2100 results. Is this normal
for people or is something fubar-ed on our setup?

Perhaps you’ve granted “Everyone” the right to OwnTickets. Which isn’t
likely what you meant.

4

I checked the group rights and the only right Everyone has is
ModifyOwnMembership. Howver, on the query builder page, for the Owner
is drop down, there seems to be every email address we’ve ever entered
on a ticket for requestor. The requestor role isn’t allowed to
OwnTickets either though. Why would every contact show up as a possible
owner if you can’t select them as an owner when creating a ticket?

Thanks,
NateFrom: Jesse Vincent [mailto:jesse@bestpractical.com]
Sent: Wednesday, July 13, 2005 12:03 PM
To: Kroll, Nathan Allen
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] mysql slowness

I found a little more out about this problem we seem to be having. The
slowest part seems to be when loading the query builder page and this
mysql query is run:

SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL
ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4 WHERE
((ACL_2.RightName = ‘OwnTicket’)) AND ((CachedGroupMembers_4.MemberId
Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId))
AND
((Principals_1.Disabled = ‘0’)or(Principals_1.Disabled = ‘0’)) AND
((Principals_1.id != ‘1’)) AND ((main.id = Principals_1.id)) AND ( (
ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = ‘Group’ AND
(
Groups_3.Domain = ‘SystemInternal’ OR Groups_3.Domain = ‘UserDefined’
OR
Groups_3.Domain = ‘ACLEquivalence’)) OR ( ( (Groups_3.Domain =
‘RT::Queue-Role’ ) ) AND Groups_3.Type = ACL_2.PrincipalType) ) AND
(ACL_2.ObjectType = ‘RT::System’ OR (ACL_2.ObjectType = ‘RT::Queue’)
)
ORDER BY main.Name ASC;

The query has taken up to 59 seconds and is the most frequent slow
query
logged to mysql. The query returns about 2100 results. Is this
normal
for people or is something fubar-ed on our setup?

Perhaps you’ve granted “Everyone” the right to OwnTickets. Which isn’t
likely what you meant.

5

Nate,

We also see this problem with a PostgreSQL DB on the same query.
This is not surprising considering what the query is doing. I thought
that there was some work being done to either provide some sort of
cache or materialized view to reduce the average cost of this query.

KenOn Wed, Jul 13, 2005 at 10:57:07AM -0500, Kroll, Nathan Allen wrote:

I found a little more out about this problem we seem to be having. The
slowest part seems to be when loading the query builder page and this
mysql query is run:

SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL
ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4 WHERE
((ACL_2.RightName = ‘OwnTicket’)) AND ((CachedGroupMembers_4.MemberId =
Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) AND
((Principals_1.Disabled = ‘0’)or(Principals_1.Disabled = ‘0’)) AND
((Principals_1.id != ‘1’)) AND ((main.id = Principals_1.id)) AND ( (
ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = ‘Group’ AND (
Groups_3.Domain = ‘SystemInternal’ OR Groups_3.Domain = ‘UserDefined’ OR
Groups_3.Domain = ‘ACLEquivalence’)) OR ( ( (Groups_3.Domain =
‘RT::Queue-Role’ ) ) AND Groups_3.Type = ACL_2.PrincipalType) ) AND
(ACL_2.ObjectType = ‘RT::System’ OR (ACL_2.ObjectType = ‘RT::Queue’) )
ORDER BY main.Name ASC;

The query has taken up to 59 seconds and is the most frequent slow query
logged to mysql. The query returns about 2100 results. Is this normal
for people or is something fubar-ed on our setup?

Also, the CachedGroupMembers table has about 170000 rows in it. Is this
normal for a rt setup with about 19000 tickets and less than 30 users?

Thanks,

Nate

-----Original Message-----

So upon upgrading to 3.4 (latest debian release), we started to notice
extreme slowness (in excess of 2 minutes) upon loading the query builder
or sometimes when trying to create a new ticket. To try to troubleshoot
this, first I tried to move from apache1 and mod_perl to apache2 with
fastcgi. There was some improvement, but still taking minutes to load
sometimes and fastcgi needed apache2 to be restarted daily otherwise it
hung. When watching the processes via top while loading query builder
or
doing any of the other “slow” tasks, the mysqld process would sometimes
use upwards of 80-90 percent of the CPU. Due to the different
combinations of apache, apache2, and modperl, fastcgi, and speedycgi,
the
observation from top leads me to believe that the slowness is not due to
the webserver or perl processor, but due to mysql. We have close to
19,000 tickets and numerious users in our system and it’s never been
super
fast, but with 3.4, it’s becoming unbearable.

I’m wondering if anyone has any ideas to try and speed mysql or
everything
up in general. Has anyone else had this problem with so many tickets?
Is
the solution to drop some of the historical tickets?

Any help is greatly appreciated. Thanks!

Nate Kroll

Milwaukee School of Engineering

The rt-users Archives

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

The rt-users Archives

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

6

Kroll, Nathan Allen wrote:

I checked the group rights and the only right Everyone has is
ModifyOwnMembership. Howver, on the query builder page, for the Owner
is drop down, there seems to be every email address we’ve ever entered
on a ticket for requestor. The requestor role isn’t allowed to
OwnTickets either though. Why would every contact show up as a possible
owner if you can’t select them as an owner when creating a ticket?

Have you checked both per-queue /and/ global rights? What about
rights for Unprivileged?

7

I checked that none of those groups had OwnTicket rights.

Any other ideas?

NateFrom: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Phil
Homewood
Sent: Wednesday, July 13, 2005 10:26 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] mysql slowness

Kroll, Nathan Allen wrote:

I checked the group rights and the only right Everyone has is
ModifyOwnMembership. Howver, on the query builder page, for the Owner
is drop down, there seems to be every email address we’ve ever entered
on a ticket for requestor. The requestor role isn’t allowed to
OwnTickets either though. Why would every contact show up as a
possible
owner if you can’t select them as an owner when creating a ticket?

Have you checked both per-queue /and/ global rights? What about
rights for Unprivileged?

|<< Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
The rt-users Archives

Be sure to check out the RT Wiki at http://wiki.bestpractical.com