Slow RT

Hello all,

We are having trouble with slow queries for a long time already. We are
using RT 3.4.5 (but planning to upgrade to RT3.6).
Also we are using mysql 5.0.15.

Some of our tickets are rather long, and those can take up to a minute
to load.
The slow query option of sql is turned on and this under this mail is a
snippet of it. The first one out of it takes 24 seconds, the last one
even 237 seconds. It also happens a lot that the SELECT GET_LOCK takes
360 seconds (the timeout time).

If I do an explain on the first query, I get this result:

mysql> explain SELECT COUNT(DISTINCT main.id) FROM Tickets main ,
Transactions Transactions_1, Attachments Attachments_2 WHERE
((Transactions_1.ObjectType= ‘RT::Ticket’)) AND ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’))
AND ((main.Status = ‘resolved’)AND(main.Queue = ‘4’)AND(main.Subject
LIKE ‘%dsl exp%’)AND ( (Attachments_2.Content LIKE
’%cpe%’)AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id
= Transactions_1.ObjectId) ) );
| id | select_type | table | type |
possible_keys | key | key_len |
ref | rows | Extra |
| 1 | SIMPLE | main | ref |
PRIMARY,Tickets1,Tickets6,i_custom2 | Tickets1 | 17 |
const,const | 50436 | Using where |
| 1 | SIMPLE | Transactions_1 | ref |
PRIMARY,Transactions1 | Transactions1 | 70 |
const,rt3.main.EffectiveId | 1 | Using where; Using index |
| 1 | SIMPLE | Attachments_2 | ref |
Attachments2 | Attachments2 | 4 |
rt3.Transactions_1.id | 1 | Using where |
3 rows in set (0.00 sec)

If I do an explain on the last query, I get:

mysql> explain SELECT COUNT(DISTINCT main.id) FROM Tickets main ,
Transactions Transactions_1, Attachments Attachments_2 WHERE
((Transactions_1.ObjectType= ‘RT::Ticket’)) AND ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’))
AND ((main.Status = ‘resolved’)AND(main.Queue = ‘4’)AND(main.Subject
LIKE ‘%dsl exp%’)AND ( (Attachments_2.Content LIKE
’%cpe%’)AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id
= Transactions_1.ObjectId) ) );
| id | select_type | table | type |
possible_keys | key | key_len |
ref | rows | Extra |
| 1 | SIMPLE | main | ref |
PRIMARY,Tickets1,Tickets6,i_custom2 | Tickets1 | 17 |
const,const | 50598 | Using where |
| 1 | SIMPLE | Transactions_1 | ref |
PRIMARY,Transactions1 | Transactions1 | 70 |
const,rt3.main.EffectiveId | 1 | Using where; Using index |
| 1 | SIMPLE | Attachments_2 | ref |
Attachments2 | Attachments2 | 4 |
rt3.Transactions_1.id | 1 | Using where |
3 rows in set (0.00 sec)

I guess these explain results are ok, or am I mistaken?

Is there somewhere else that I can maybe have a look?

Thanks,

Alain

Query_time: 24 Lock_time: 0 Rows_sent: 1 Rows_examined: 35113

SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE
((Transactions_1.ObjectType= ‘RT::Ticket’)) AND ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’))
AND ((main.Status = ‘resolved’)AND(main.Queue = ‘4’)AND(main.Subject
LIKE ‘%dsl exp%’)AND ( (Attachments_2.Content LIKE
’%cpe%’)AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id
= Transactions_1.ObjectId) ) );

Time: 070315 11:42:37

User@Host: rt_user[rt_user] @ rtdb.vianetworks.nl [10.0.15.58]

Query_time: 12 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SELECT GET_LOCK(‘Apache-Session-c1f1d783667e9ebb9e878498a22514a0’, 3600);

Time: 070315 11:43:11

User@Host: rt_user[rt_user] @ rtdb.vianetworks.nl [10.0.15.58]

Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 691650

SELECT COUNT(DISTINCT main.id) FROM Groups Groups_3, CachedGroupMembers
CachedGroupMembers_2, Principals Principals_1, ACL ACL_4, 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.PrincipalType = ‘User’)) AND ((Principals_1.id != ‘1’))
AND ((main.id = Principals_1.id)) AND ((ACL_4.ObjectType = 'RT::Ticket’
AND ACL_4.ObjectId = 108309) OR (ACL_4.ObjectType = ‘RT::Queue’ AND
ACL_4.ObjectId = 13) OR (ACL_4.ObjectType = ‘RT::System’)) AND
((Groups_3.Domain = ‘RT::Ticket-Role’ AND Groups_3.Instance = ‘108309’)
OR (Groups_3.Domain = ‘RT::Queue-Role’ AND Groups_3.Instance = ‘13’) OR
(Groups_3.Domain = ‘RT::System-Role’));

Time: 070315 11:43:50

User@Host: rt_user[rt_user] @ rtdb.vianetworks.nl [10.0.15.58]

Query_time: 3 Lock_time: 0 Rows_sent: 1 Rows_examined: 691650

SELECT COUNT(DISTINCT main.id) FROM Groups Groups_3, CachedGroupMembers
CachedGroupMembers_2, Principals Principals_1, ACL ACL_4, 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.PrincipalType = ‘User’)) AND ((Principals_1.id != ‘1’))
AND ((main.id = Principals_1.id)) AND ((ACL_4.ObjectType = 'RT::Ticket’
AND ACL_4.ObjectId = 108309) OR (ACL_4.ObjectType = ‘RT::Queue’ AND
ACL_4.ObjectId = 7) OR (ACL_4.ObjectType = ‘RT::System’)) AND
((Groups_3.Domain = ‘RT::Ticket-Role’ AND Groups_3.Instance = ‘108309’)
OR (Groups_3.Domain= ‘RT::Queue-Role’ AND Groups_3.Instance = ‘7’) OR
(Groups_3.Domain = ‘RT::System-Role’));

Time: 070315 11:45:04

User@Host: rt_user[rt_user] @ rtdb.vianetworks.nl [10.0.15.58]

Query_time: 237 Lock_time: 0 Rows_sent: 1 Rows_examined: 313160

SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE
((Transactions_1.ObjectType= ‘RT::Ticket’)) AND ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’))
AND ((main.Status = ‘resolved’)AND(main.Queue = ‘4’)AND (
(Attachments_2.Content LIKE ‘%cpe%’)AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) );

Time: 070315 11:45:23

Alain Sips wrote:

Hello all,

We are having trouble with slow queries for a long time already. We are
using RT 3.4.5 (but planning to upgrade to RT3.6).
Also we are using mysql 5.0.15.

Some of our tickets are rather long, and those can take up to a minute
to load.
The slow query option of sql is turned on and this under this mail is a
snippet of it. The first one out of it takes 24 seconds, the last one
even 237 seconds.
Thats not bad considering the fact that the database is scanning all of
the content column for the wildcard %cpe%. What is killing your
performance is the fact that there is a % infront of the cpe which means
the database can’t use any indices.
At our site I have disable this pre/post pending of wildcards. If
someone wants to search for %cpe% then he/she knows that he/she is in
for a (long) wait.
This behaviour can be switched off by patching searchbuilder.

Joop