[rt-users] Slow RT

[Alain Sips]

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 at 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 at 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 at 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 at 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