Hello,
I recently upgraded our test system from 3.6.1 to 3.6.3 and I observed a
performance problem in SelfService which is new. SelfService/index.html
takes more than 15 minutes to load on our server where RT.
I tracked this issue down to a change in SelfService/Elements/MyRequest.
In 3.6.1 and before SelfService only searched for Tickets of which the User
is requestor.
In 3.6.3 it searches for Tickets where user ist Requestor, AdminCc or Cc of.
This search is not well performed by my mysql db server (Ver 12.22 Distrib
4.0.24, for pc-linux-gnu (i386)) as reported by me in other tickets before.
I changed SelfService/Elements/MyRequest only to include Tickets with
requestor and the page is performing well again.
All indexes are as specified by the default installation. I tested some
additional indexes whithout any success.
Is this a known issue only applying to mysql? Only to mysql 4? Or why does
no others report this error?
Here is the query and the "explaination":
# Query_time: 805 Lock_time: 0 Rows_sent: 1 Rows_examined: 204821648
SELECT COUNT(DISTINCT main.id) FROM (((((((((Tickets main JOIN Groups
Groups_4 ON ( Groups_4.Instance = main.id)) JOIN Groups Groups_7 ON (
Groups_7.Instance = main.id)) LEFT JOIN CachedGroupMembers
CachedGroupMembers_5 ON ((CachedGroupMembers_5.GroupId !=
CachedGroupMembers_5.MemberId)) AND ( CachedGroupMembers_5.GroupId =
Groups_4.id)) JOIN Groups Groups_1 ON ( Groups_1.Instance = main.id))
LEFT JOIN CachedGroupMembers CachedGroupMembers_8 ON (
CachedGroupMembers_8.GroupId = Groups_7.id) AND (
(CachedGroupMembers_8.GroupId != CachedGroupMembers_8.MemberId))) LEFT
JOIN Users Users_6 ON ( Users_6.id = CachedGroupMembers_5.MemberId)) LEFT
JOIN Users Users_9 ON ( Users_9.id = CachedGroupMembers_8.MemberId)) LEFT
JOIN CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.GroupId = Groups_1.id) AND (
(CachedGroupMembers_2.GroupId != CachedGroupMembers_2.MemberId))) LEFT
JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId))
WHERE ((Groups_1.Domain = 'RT::Ticket-Role')) AND ((Groups_1.Type =
'Requestor')) AND ((Groups_4.Domain = 'RT::Ticket-Role')) AND
((Groups_4.Type = 'Cc')) AND ((Groups_7.Domain = 'RT::Ticket-Role')) AND
((Groups_7.Type = 'AdminCc')) AND ((main.EffectiveId = main.id)) AND
((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( (
(Users_3.EmailAddress = '') ) OR ( (Users_6.EmailAddress = '') ) OR (
(Users_9.EmailAddress = '') ) ) AND ( (main.Status = 'open') OR
(main.Status = 'new') OR (main.Status = 'stalled') ) );
+----------------------+--------+-----------------+------------+---------+-------------------------------+-------+--------------------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----------------------+--------+-----------------+------------+---------+-------------------------------+-------+--------------------------+
| Groups_1 | ref | Groups1,Groups2 | Groups2 | 65 |
const | 51330 | Using where |
| main | eq_ref | PRIMARY | PRIMARY | 4 |
Groups_1.Instance | 1 | Using where |
| Groups_7 | ref | Groups1,Groups2 | Groups1 | 65 |
const | 72594 | Using where; Using index |
| CachedGroupMembers_8 | ref | DisGrouMem | DisGrouMem | 5 |
Groups_7.id | 1 | Using index |
| Groups_4 | ref | Groups1,Groups2 | Groups1 | 65 |
const | 72594 | Using where; Using index |
| CachedGroupMembers_5 | ref | DisGrouMem | DisGrouMem | 5 |
Groups_4.id | 1 | Using index |
| Users_6 | eq_ref | PRIMARY | PRIMARY | 4 |
CachedGroupMembers_5.MemberId | 1 | |
| Users_9 | eq_ref | PRIMARY | PRIMARY | 4 |
CachedGroupMembers_8.MemberId | 1 | |
| CachedGroupMembers_2 | ref | DisGrouMem | DisGrouMem | 5 |
Groups_1.id | 1 | Using index |
| Users_3 | eq_ref | PRIMARY | PRIMARY | 4 |
CachedGroupMembers_2.MemberId | 1 | Using where |
+----------------------+--------+-----------------+------------+---------+-------------------------------+-------+--------------------------+
10 rows in set (0.05 sec)
Regards,
Dirk.
--
Dr. Dirk Pape (eAS - Projektleitung Campus Management)
Freie Universitaet Berlin
Grunewaldstr. 34a, 12165 Berlin
Tel. +49 (0)30 838 75143, Fax. +49 (0)30 838 54654
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
