Performance-Bug in SelfService when updated from 3.6.1 to 3.6.3

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

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@inf.fu-berlin.de:

I

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.

Hi Jesse,

–Am 27. Januar 2007 03:36:24 -0500 schrieb Jesse Vincent
jesse@bestpractical.com:

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.

you are right. Switching to Watcher instead of ( Requestor, Cc, AdminCc )
helps.

But I had the same problem with users that build queries with the search
form, that behave bad in the same manner.

Why is this search so badly translated into SQL?

Dirk.

Hi Jesse,

–Am 27. Januar 2007 03:36:24 -0500 schrieb Jesse Vincent
jesse@bestpractical.com:

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.

you are right. Switching to Watcher instead of ( Requestor, Cc, AdminCc )
helps.

But I had the same problem with users that build queries with the search
form, that behave bad in the same manner.

Why is this search so badly translated into SQL?
Because it’s not easy to write down complete translator. Just think about:
Requestor = X
Requestor != X
Requestor IS [NOT] NULL
Requestor = X AND Requestor = Y
Requestor = X OR Requestor = Y
Requestor = X AND Requestor != Y

That’s not that easy to build query with minimal number of joins and
get rid of left joins where it’s possible.

I have an idea how to do it, but that may require complete rework of
ticketsql subsystem.

Dirk.


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

Hello,

–Am 27. Januar 2007 22:18:04 +0100 schrieb Dirk Pape
pape-rt@inf.fu-berlin.de:

you are right. Switching to Watcher instead of ( Requestor, Cc, AdminCc )
helps.

thanks Jesse for help and Ruslan for explaination.

Here is the simple patch.

Dirk.

performance_of_selfservice.patch (425 Bytes)

Dirk Pape escreveu:

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

Try to increase “innodb_buffer_pool_size” in my.cnf. See [1], [2] and
[3]. Tunning some parameters like “join_buffer_size”,
“innodb_buffer_pool_size” and some others gave me a huge performance
improvement. Take care of available RAM to avoid swapping.

1 - http://dev.mysql.com/doc/refman/4.1/en/innodb-configuration.html
2 - http://dev.mysql.com/doc/refman/4.1/en/innodb-tuning.html
3 - http://wiki.bestpractical.com/index.cgi?PerformanceTuning

Gilmar Santos Jr

Hi Gilmar,

i’m playing around with this buffers since some months without finding a
good solution, can you give me a hint about yours??

We have 4GB RAM at the DB Server.

Thanks Torsten2007/1/28, Gilmar Santos Jr gilmarsantosjr@safernet.org.br:

Dirk Pape escreveu:

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

Try to increase “innodb_buffer_pool_size” in my.cnf. See [1], [2] and
[3]. Tunning some parameters like “join_buffer_size”,
“innodb_buffer_pool_size” and some others gave me a huge performance
improvement. Take care of available RAM to avoid swapping.

1 - http://dev.mysql.com/doc/refman/4.1/en/innodb-configuration.html
2 - http://dev.mysql.com/doc/refman/4.1/en/innodb-tuning.html
3 - http://wiki.bestpractical.com/index.cgi?PerformanceTuning


Gilmar Santos Jr


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

MFG

Torsten Brumm

http://www.torsten-brumm.de

Hi Torsten,

I think these parameter are most important:

key_buffer = 16M
join_buffer_size = 32M
innodb_buffer_pool_size = 256M
innodb_log_buffer_size = 32M
innodb_log_file_size = 128M

The last 2 affects the recovery time and write speed. Higher values provides
better writing performance and greater crash recovery times.

Maybe you will have problems if you modify “innodb_log_file_size”. I had to stop
the server, change the configuration, remove the ib_log* files and start the
server again. Sometimes, according to some docs, it doesn’t work…

You can try at least 2 GB to “innodb_buffer_pool_size”. MySQL save some useful
information about innodb. The less “page faults” the better performance :wink:

Good luck,

Gilmar Santos Jr

Torsten Brumm escreveu: