Slow ticket search page becoming a problem

Philip_Kime · April 26, 2007, 3:27am

I know I asked this before but I’ve been swamped and lost track of there
the discussion got to.

RT 3.6.3, Mysql 5.0.27. The main ticket search page is really slow to
load, typically 45 seconds, sometimes longer. Problem query and explain
below. It’s starting to become a problem for us. Seems that the first
row of the explain output is the guilty one. Rows_examined is absurdly
high.

PK

Query_time: 45 Lock_time: 0 Rows_sent: 290 Rows_examined: 65256162

SELECT DISTINCT main.* 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.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’) OR (Groups_3.Domain = ‘RT::System-Role’)) ORDER BY
main.RealName ASC;

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: main
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 673
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: Principals_1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: rt3.main.id
rows: 1
Extra: Using where; Distinct
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: CachedGroupMembers_2
type: ref
possible_keys: DisGrouMem,SHRD_CGM1
key: SHRD_CGM1
key_len: 5
ref: rt3.main.id
rows: 1
Extra: Using where; Using index; Distinct
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: ACL_4
type: range
possible_keys: ACL1
key: ACL1
key_len: 54
ref: NULL
rows: 77
Extra: Using where; Using index; Distinct
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: Groups_3
type: eq_ref
possible_keys: PRIMARY,Groups1,Groups2
key: PRIMARY
key_len: 4
ref: rt3.CachedGroupMembers_2.GroupId
rows: 1
Extra: Using where; Distinct

Philip Kime
NOPS Systems Architect
310 401 0407

Jesse_Vincent · April 26, 2007, 12:59pm

That looks suspiciously like the problem I’d been talking to you
about before, Todd.On Apr 25, 2007, at 11:27 PM, Philip Kime wrote:

I know I asked this before but I’ve been swamped and lost track of
there the discussion got to.

RT 3.6.3, Mysql 5.0.27. The main ticket search page is really slow
to load, typically 45 seconds, sometimes longer. Problem query and
explain below. It’s starting to become a problem for us. Seems that
the first row of the explain output is the guilty one.
Rows_examined is absurdly high.

PK

Query_time: 45 Lock_time: 0 Rows_sent: 290 Rows_examined:

65256162
SELECT DISTINCT main.* 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.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’) OR (Groups_3.Domain = ‘RT::System-Role’)) ORDER
BY main.RealName ASC;

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: main
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 673
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: Principals_1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: rt3.main.id
rows: 1
Extra: Using where; Distinct
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: CachedGroupMembers_2
type: ref
possible_keys: DisGrouMem,SHRD_CGM1
key: SHRD_CGM1
key_len: 5
ref: rt3.main.id
rows: 1
Extra: Using where; Using index; Distinct
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: ACL_4
type: range
possible_keys: ACL1
key: ACL1
key_len: 54
ref: NULL
rows: 77
Extra: Using where; Using index; Distinct
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: Groups_3
type: eq_ref
possible_keys: PRIMARY,Groups1,Groups2
key: PRIMARY
key_len: 4
ref: rt3.CachedGroupMembers_2.GroupId
rows: 1
Extra: Using where; Distinct

–
Philip Kime
NOPS Systems Architect
310 401 0407

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

PGP.sig (186 Bytes)

Ruslan_Zakirov · April 26, 2007, 1:46pm

as far as I can see estimation of “rows” is very wrong I do think it’s
mysql bug, I suggest you rebuild all indexes and run analyze on
tables.On 4/26/07, Jesse Vincent jesse@bestpractical.com wrote:

That looks suspiciously like the problem I’d been talking to you about
before, Todd.

On Apr 25, 2007, at 11:27 PM, Philip Kime wrote:

I know I asked this before but I’ve been swamped and lost track of there the
discussion got to.

RT 3.6.3, Mysql 5.0.27. The main ticket search page is really slow to load,
typically 45 seconds, sometimes longer. Problem query and explain below.
It’s starting to become a problem for us. Seems that the first row of the
explain output is the guilty one. Rows_examined is absurdly high.

PK

Query_time: 45 Lock_time: 0 Rows_sent: 290 Rows_examined: 65256162

SELECT DISTINCT main.* 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.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’) OR (Groups_3.Domain = ‘RT::System-Role’)) ORDER BY
main.RealName ASC;

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: main
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 673
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: Principals_1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: rt3.main.id
rows: 1
Extra: Using where; Distinct
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: CachedGroupMembers_2
type: ref
possible_keys: DisGrouMem,SHRD_CGM1
key: SHRD_CGM1
key_len: 5
ref: rt3.main.id
rows: 1
Extra: Using where; Using index; Distinct
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: ACL_4
type: range
possible_keys: ACL1
key: ACL1
key_len: 54
ref: NULL
rows: 77
Extra: Using where; Using index; Distinct
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: Groups_3
type: eq_ref
possible_keys: PRIMARY,Groups1,Groups2
key: PRIMARY
key_len: 4
ref: rt3.CachedGroupMembers_2.GroupId
rows: 1
Extra: Using where; Distinct

–
Philip Kime
NOPS Systems Architect
310 401 0407

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

Philip_Kime · April 26, 2007, 10:38pm

The problem seems to be caused by the ORDER BY clause. If I remove this,
it’s very fast.

PKFrom: Jesse Vincent [mailto:jesse@bestpractical.com]
Sent: Thursday, April 26, 2007 6:00 AM
To: Todd Chapman
Cc: Philip Kime; RT Users
Subject: Re: [rt-users] Slow ticket search page becoming a problem

That looks suspiciously like the problem I’d been talking to you about
before, Todd.

Philip_Kime · April 27, 2007, 12:19am

Ok, the issue is that MYSQL 5 won’t use the index on main.Name by
default (possible keys list PRIMARY only, which is useless for this
ORDER BY clause), which it really needs to do with an ORDER BY clause
for main.Name (or main.RealName as in my example as I have modified the
display code). It is fixed if you force the index use:

mysql> SELECT DISTINCT main.* FROM Principals Principals_1,
CachedGroupMembers C
achedGroupMembers_2, Groups Groups_3, ACL ACL_4, Users main FORCE
INDEX(Users1) WHERE ((ACL_4.PrincipalType = Groups_3.Type)) AND
((ACL_4.RightName = ‘Own
Ticket’)) AND ((CachedGroupMembers_2.MemberId = Principals_1.id)) AND
((Groups_3
.id = CachedGroupMembers_2.GroupId)) AND ((Principals_1.Disabled = ‘0’))
AND ((P
rincipals_1.PrincipalType = ‘User’)) AND ((Principals_1.id != ‘1’)) AND
((main.i
d = Principals_1.id)) AND ((ACL_4.ObjectType = ‘RT::Queue’) OR
(ACL_4.ObjectType
= ‘RT::System’)) AND ((Groups_3.Domain = ‘RT::Queue-Role’) OR
(Groups_3.Domain
= ‘RT::System-Role’)) ORDER BY main.Name ASC;

Then it’s nice and fast again. The explain shows that it’s still a
filesort/temp query but it does a indexed table scan instead of an
unindexed range scan.

I assume that this would need a SearchBuilder mod to force the use of
the index related to the ORDER BY clause?

PKFrom: Jesse Vincent [mailto:jesse@bestpractical.com]
Sent: Thursday, April 26, 2007 6:00 AM
To: Todd Chapman
Cc: Philip Kime; RT Users
Subject: Re: [rt-users] Slow ticket search page becoming a problem

That looks suspiciously like the problem I’d been talking to you about
before, Todd.

Ruslan_Zakirov · April 27, 2007, 12:34am

Philip, please try the following query and send us times and EXPLAIN:

SELECT STRAIGHT_JOIN 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’) OR (Groups_3.Domain =
‘RT::System-Role’))
ORDER BY main.RealName ASC;

It’s the same query but with forced order of joins, I do believe that
this is the ideal plan for joins in this situation for all setups.

Ok, the issue is that MYSQL 5 won’t use the index on main.Name by default
(possible keys list PRIMARY only, which is useless for this ORDER BY
clause), which it really needs to do with an ORDER BY clause for main.Name
(or main.RealName as in my example as I have modified the display code). It
is fixed if you force the index use:

[snip]

Then it’s nice and fast again. The explain shows that it’s still a
filesort/temp query but it does a indexed table scan instead of an unindexed
range scan.

I assume that this would need a SearchBuilder mod to force the use of the
index related to the ORDER BY clause?

PK

Best regards, Ruslan.

Philip_Kime · April 27, 2007, 12:56am

You’re clearly a better DBA than me Yes, that Join orering is very
nice and executes in about 0.8 seconds as opposed to 45-90 seconds.
Explain is:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ACL_4
type: range
possible_keys: ACL1
key: ACL1
key_len: 54
ref: NULL
rows: 77
Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: Groups_3
type: ref
possible_keys: PRIMARY,Groups1,Groups2
key: Groups2
key_len: 67
ref: rt3.ACL_4.PrincipalType
rows: 26460
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: CachedGroupMembers_2
type: ref
possible_keys: DisGrouMem,SHRD_CGM1
key: DisGrouMem
key_len: 5
ref: rt3.Groups_3.id
rows: 1
Extra: Using where; Using index
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: Principals_1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: rt3.CachedGroupMembers_2.MemberId
rows: 1
Extra: Using where
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: main
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: rt3.CachedGroupMembers_2.MemberId
rows: 1
Extra:
5 rows in set (0.00 sec)

PKFrom: Ruslan Zakirov [mailto:ruslan.zakirov@gmail.com]
Sent: Thursday, April 26, 2007 5:34 PM
To: Philip Kime
Cc: Jesse Vincent; Todd Chapman; RT Users
Subject: Re: [rt-users] Slow ticket search page becoming a problem

Philip, please try the following query and send us times and EXPLAIN:

SELECT STRAIGHT_JOIN 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’) OR (Groups_3.Domain =
‘RT::System-Role’))
ORDER BY main.RealName ASC;

It’s the same query but with forced order of joins, I do believe that
this is the ideal plan for joins in this situation for all setups.

Ok, the issue is that MYSQL 5 won’t use the index on main.Name by
default (possible keys list PRIMARY only, which is useless for this
ORDER BY clause), which it really needs to do with an ORDER BY clause
for main.Name (or main.RealName as in my example as I have modified
the display code). It is fixed if you force the index use:

[snip]

Then it’s nice and fast again. The explain shows that it’s still a
filesort/temp query but it does a indexed table scan instead of an
unindexed range scan.

I assume that this would need a SearchBuilder mod to force the use of
the index related to the ORDER BY clause?

PK

Best regards, Ruslan.

Ruslan_Zakirov · June 28, 2007, 11:42am

Hi, guys.

The explain is bad I mean gives us nothing good. I think we need an
index on the Groups table to make it better. Run the following query
to create an index. This may take some time, but I’m not sure how
much, depends on size of the table.

CREATE INDEX RUZ_G1 ON Groups(Type, Domain);

And then redo explain for the query. If mysql will start search from
Users table then I have no ideas anymore and we must file a bug into
mysql’s tracker.On 6/27/07, Philip Kime pkime@shopzilla.com wrote:

mysql> EXPLAIN SELECT DISTINCT main.* FROM ACL ACL_4, Groups Groups_3,
CachedGroupMembers CachedGroupMembers_2, Principals Principals_1, Users ma
in WHERE ((ACL_4.PrincipalType = Groups_3.Type)) AND ((ACL_4.RightName = ‘Own
Ticket’)) AND ((CachedGroupMembers_2.MemberId = Principals_1.id)) AND ((Gr
oups_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\G
[snip explain]

Best regards, Ruslan.

Ruslan_Zakirov · November 18, 2007, 1:54am

Heya, guys.

Finally I found some time to test this problem on mysql 5.0 and even
has writen a script to reproduce it, but it showed that mysql 5.0.45
has no this bug. My script and doogles helped me to prove that it
really works as expected. So I really recommend upgrade to 5.0.45 at
least for users of 5.0.x.

Best regards, Ruslan.

Jesse_Vincent · November 18, 2007, 3:02am

Heya, guys.

Finally I found some time to test this problem on mysql 5.0 and even
has writen a script to reproduce it, but it showed that mysql 5.0.45
has no this bug. My script and doogles helped me to prove that it
really works as expected. So I really recommend upgrade to 5.0.45 at
least for users of 5.0.x.

that’s very cool. Can you publish the script so we can test older
releases to see what release it was fixed in?

-j

Wojciech_Jawor · November 18, 2007, 6:35pm

That’s great news - will upgrade our dev instance on Monday and let you
know how it works.

Thanks,
Wojtek-----Original Message-----
From: Ruslan Zakirov [mailto:ruslan.zakirov@gmail.com]
Sent: Saturday, November 17, 2007 5:54 PM
To: Wojciech Jawor; Philip Kime; RT users
Subject: Re: [rt-users] Slow ticket search page becoming a problem

Heya, guys.

Finally I found some time to test this problem on mysql 5.0 and even
has writen a script to reproduce it, but it showed that mysql 5.0.45
has no this bug. My script and doogles helped me to prove that it
really works as expected. So I really recommend upgrade to 5.0.45 at
least for users of 5.0.x.

Best regards, Ruslan.

Ruslan_Zakirov · November 18, 2007, 7:17pm

Script creates schema and data. The following query must use range
search for the second table instead of ‘ref’.

SELECT STRAIGHT_JOIN g.id FROM acl a, grp g WHERE
g.type = a.type
AND (
( g.domain = ‘RT::Ticket-Role’ AND g.instance = 10 )
OR ( g.domain = ‘RT::Queue-Role’ AND g.instance = 3 )
OR ( g.domain = ‘RT::System-Role’ AND g.instance = 1 )
);On Nov 18, 2007 6:02 AM, Jesse Vincent jesse@bestpractical.com wrote:

On Sun, Nov 18, 2007 at 04:54:03AM +0300, Ruslan Zakirov wrote:

Heya, guys.

Finally I found some time to test this problem on mysql 5.0 and even
has writen a script to reproduce it, but it showed that mysql 5.0.45
has no this bug. My script and doogles helped me to prove that it
really works as expected. So I really recommend upgrade to 5.0.45 at
least for users of 5.0.x.

that’s very cool. Can you publish the script so we can test older
releases to see what release it was fixed in?

-j

Best regards, Ruslan.

create_data.pl (945 Bytes)

Balint_Bekeny1 · November 18, 2007, 10:01pm

Hi All,

I have upgraded our RT’s heavily used MySQL database engine from 5.0.27
to 5.0.45 (on FreeBSD 6.2) and the Search page has become multiple times
quicker.

Thank you Ruslan!
Bekény

Wojciech Jawor wrote, On 2007.11.18. 19:35:

Dirk_Pape · November 21, 2007, 8:54am

Hello,

–Am 18. November 2007 22:17:42 +0300 schrieb Ruslan Zakirov
ruslan.zakirov@gmail.com:

Script creates schema and data. The following query must use range
search for the second table instead of ‘ref’.

so this is from our site:

"
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 897
Server version: 5.0.32-Debian_7etch1-log Debian etch distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>
"

As far as I understood this meens that “Server version:
5.0.32-Debian_7etch1-log Debian etch distribution” is ok, isn’t it?

Regards, Dirk.