[rt-users] Slow MySQL after upgrade from 3.8.6 to 3.8.8
Hi, Since upgrading from 3.8.6 to 3.8.8 our RT system has been very slow to return any pages containing ticket data such as the home page, searches and tickets themselves. I enabled the MySQL slow running query log and this was at the top of the list: mysqldumpslow -t 10 /var/log/mysql/mysql-slow.log Reading mysql slow query log from /var/log/mysql/mysql-slow.log Count: 47 Time=10.12s (475s) Lock=0.00s (0s) Rows=1.0 (47), rtuser[rtus...@* SELECT GET_LOCK('S', N) Any ideas? Thanks. RT Training in Washington DC, USA on Oct 25 26 2010 Last one this year -- Learn how to get the most out of RT!
Re: [rt-users] Slow MySQL after upgrade from 3.8.6 to 3.8.8
I had the same problem and ended up dropping the Groups2 index and performance went back to normal Nicola -Original Message- From: rt-users-boun...@lists.bestpractical.com on behalf of Alex Young Sent: Wed 8/18/2010 6:07 AM To: rt-users@lists.bestpractical.com Subject: [rt-users] Slow MySQL after upgrade from 3.8.6 to 3.8.8 Hi, Since upgrading from 3.8.6 to 3.8.8 our RT system has been very slow to return any pages containing ticket data such as the home page, searches and tickets themselves. I enabled the MySQL slow running query log and this was at the top of the list: mysqldumpslow -t 10 /var/log/mysql/mysql-slow.log Reading mysql slow query log from /var/log/mysql/mysql-slow.log Count: 47 Time=10.12s (475s) Lock=0.00s (0s) Rows=1.0 (47), rtuser[rtus...@* SELECT GET_LOCK('S', N) Any ideas? Thanks. RT Training in Washington DC, USA on Oct 25 26 2010 Last one this year -- Learn how to get the most out of RT!
Re: [rt-users] Slow MySQL after upgrade from 3.8.6 to 3.8.8
Thanks Nicola, I have just tried this and it's not made an difference to the performance. I found your previous emails on this subject so tried the same EXPLAIN and it's pretty quick. mysql explain SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1 ON - ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON - ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3 ON ( - Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE (Principals_1.Disabled = - '0') AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND - ((Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = '3') OR - (Groups_3.Domain = 'RT::System-Role')) AND (Groups_3.Type = 'AdminCc') ORDER - BY main.Name ASC; ++-+--++--++-++--+---+ | id | select_type | table| type | possible_keys | key| key_len | ref| rows | Extra | ++-+--++--++-++--+---+ | 1 | SIMPLE | Groups_3 | range | PRIMARY,Groups1,Groups2 | Groups1| 139 | NULL | 5 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | CachedGroupMembers_2 | ref| DisGrouMem,CachedGroupMembers3,SHREDDER_CGM1 | DisGrouMem | 5 | rtdb.Groups_3.id |1 | Using where; Using index | | 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY | PRIMARY| 4 | rtdb.CachedGroupMembers_2.MemberId | 1 | Using where | | 1 | SIMPLE | main | eq_ref | PRIMARY | PRIMARY| 4 | rtdb.Principals_1.id | 1 | Using where | ++-+--++--++-++--+---+ 4 rows in set (0.00 sec) From: Foggi, Nicola [mailto:nfo...@depaul.edu] Sent: 18 August 2010 15:02 To: Alex Young; rt-users@lists.bestpractical.com Subject: RE: [rt-users] Slow MySQL after upgrade from 3.8.6 to 3.8.8 I had the same problem and ended up dropping the Groups2 index and performance went back to normal Nicola -Original Message- From: rt-users-boun...@lists.bestpractical.com on behalf of Alex Young Sent: Wed 8/18/2010 6:07 AM To: rt-users@lists.bestpractical.com Subject: [rt-users] Slow MySQL after upgrade from 3.8.6 to 3.8.8 Hi, Since upgrading from 3.8.6 to 3.8.8 our RT system has been very slow to return any pages containing ticket data such as the home page, searches and tickets themselves. I enabled the MySQL slow running query log and this was at the top of the list: mysqldumpslow -t 10 /var/log/mysql/mysql-slow.log Reading mysql slow query log from /var/log/mysql/mysql-slow.log Count: 47 Time=10.12s (475s) Lock=0.00s (0s) Rows=1.0 (47), rtuser[rtus...@* SELECT GET_LOCK('S', N) Any ideas? Thanks. RT Training in Washington DC, USA on Oct 25 26 2010 Last one this year -- Learn how to get the most out of RT!
Re: [rt-users] Slow MySQL after upgrade from 3.8.6 to 3.8.8
Still can't find what the problem is. Disabled all the plugins possible to see if one of them was having an effect on 3.8.8 and causing the problem. I cant disable RTIR without it throwing an error, anyone know how RTIR can be disabled? Thanks. -Original Message- From: rt-users-boun...@lists.bestpractical.com [mailto:rt-users-boun...@lists.bestpractical.com] On Behalf Of Alex Young Sent: 18 August 2010 16:19 To: rt-users@lists.bestpractical.com Subject: Re: [rt-users] Slow MySQL after upgrade from 3.8.6 to 3.8.8 Thanks Nicola, I have just tried this and it's not made an difference to the performance. I found your previous emails on this subject so tried the same EXPLAIN and it's pretty quick. mysql explain SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1 ON - ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON - ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3 ON ( - Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE (Principals_1.Disabled = - '0') AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND - ((Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = '3') OR - (Groups_3.Domain = 'RT::System-Role')) AND (Groups_3.Type = 'AdminCc') ORDER - BY main.Name ASC; ++-+--++--++-++--+---+ | id | select_type | table| type | possible_keys | key| key_len | ref| rows | Extra | ++-+--++--++-++--+---+ | 1 | SIMPLE | Groups_3 | range | PRIMARY,Groups1,Groups2 | Groups1| 139 | NULL | 5 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | CachedGroupMembers_2 | ref| DisGrouMem,CachedGroupMembers3,SHREDDER_CGM1 | DisGrouMem | 5 | rtdb.Groups_3.id |1 | Using where; Using index | | 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY | PRIMARY| 4 | rtdb.CachedGroupMembers_2.MemberId | 1 | Using where | | 1 | SIMPLE | main | eq_ref | PRIMARY | PRIMARY| 4 | rtdb.Principals_1.id | 1 | Using where | ++-+--++--++-++--+---+ 4 rows in set (0.00 sec) From: Foggi, Nicola [mailto:nfo...@depaul.edu] Sent: 18 August 2010 15:02 To: Alex Young; rt-users@lists.bestpractical.com Subject: RE: [rt-users] Slow MySQL after upgrade from 3.8.6 to 3.8.8 I had the same problem and ended up dropping the Groups2 index and performance went back to normal Nicola -Original Message- From: rt-users-boun...@lists.bestpractical.com on behalf of Alex Young Sent: Wed 8/18/2010 6:07 AM To: rt-users@lists.bestpractical.com Subject: [rt-users] Slow MySQL after upgrade from 3.8.6 to 3.8.8 Hi, Since upgrading from 3.8.6 to 3.8.8 our RT system has been very slow to return any pages containing ticket data such as the home page, searches and tickets themselves. I enabled the MySQL slow running query log and this was at the top of the list: mysqldumpslow -t 10 /var/log/mysql/mysql-slow.log Reading mysql slow query log from /var/log/mysql/mysql-slow.log Count: 47 Time=10.12s (475s) Lock=0.00s (0s) Rows=1.0 (47), rtuser[rtus...@* SELECT GET_LOCK('S', N) Any ideas? Thanks. RT Training in Washington DC, USA on Oct 25 26 2010 Last one this year -- Learn how to get the most out of RT! RT Training in Washington DC, USA on Oct 25 26 2010 Last one this year -- Learn how to get the most out of RT!