Re: [rt-users] [repost] Performance-Bug in SelfService when updated from 3.6.1 to 3.6.3
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 ;) Good luck, -- Gilmar Santos Jr Torsten Brumm escreveu: 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 Torsten 2007/1/28, Gilmar Santos Jr [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]: 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: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 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 ___ 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
Re: [rt-users] [repost] Performance-Bug in SelfService when updated from 3.6.1 to 3.6.3
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 Torsten 2007/1/28, Gilmar Santos Jr [EMAIL PROTECTED]: 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: [EMAIL PROTECTED] 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 ___ 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
Re: [rt-users] [repost] Performance-Bug in SelfService when updated from 3.6.1 to 3.6.3
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 [EMAIL PROTECTED]: 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:
Re: [rt-users] [repost] Performance-Bug in SelfService when updated from 3.6.1 to 3.6.3
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 [EMAIL PROTECTED]: 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 |
Re: [rt-users] [repost] Performance-Bug in SelfService when updated from 3.6.1 to 3.6.3
Hi Jesse, --Am 27. Januar 2007 03:36:24 -0500 schrieb Jesse Vincent [EMAIL PROTECTED]: 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. ___ 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
Re: [rt-users] [repost] Performance-Bug in SelfService when updated from 3.6.1 to 3.6.3
On 1/28/07, Dirk Pape [EMAIL PROTECTED] wrote: Hi Jesse, --Am 27. Januar 2007 03:36:24 -0500 schrieb Jesse Vincent [EMAIL PROTECTED]: 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: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com -- Best regards, Ruslan. ___ 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
Re: [rt-users] [repost] Performance-Bug in SelfService when updated from 3.6.1 to 3.6.3
Hello, --Am 27. Januar 2007 22:18:04 +0100 schrieb Dirk Pape [EMAIL PROTECTED]: 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 Description: Binary data ___ 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
Re: [rt-users] [repost] Performance-Bug in SelfService when updated from 3.6.1 to 3.6.3
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: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
