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

[Jesse Vincent]

I


On Sat, Jan 27, 2007 at 09:29:17AM +0100, Dirk Pape wrote:
> 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?

I haven't seen it. It _does_ look like htat query is qgetting built
wrong.  I wonder if changing the search to be Watcher = rather than what
it is now would make it all better.

> 
> 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.
> 
> 
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com
> 

--