[rt-users] [repost] Performance-Bug in SelfService when updated from 3.6.1 to 3.6.3

[Dirk Pape]

Hello,

does anybody have the same performance degration on 3.6.3 
SelfService/index.html or do I have to dig into our special configuration??

If the latter, does anybody have a hint where to dig into?

Dirk.

--Am 23. Januar 2007 08:39:38 +0100 schrieb Dirk Pape 
<pape-rt at inf.fu-berlin.de>:

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