Re: [rt-users] mis-use of indexes on the Attachments table (RT 3.8.2)
On Tue, May 26, 2009 at 05:16:28PM -0400, Elijah Wright wrote: > Folks, > > We're in the midst of migrating from RT 3.6.4 to RT 3.8.2, and having some > "interesting" issues with the Attachments table. What does mysqltuner say on each system? What mysql version are you running on each? What's the configuration of each system? Give us a bit to work with ;) ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] mis-use of indexes on the Attachments table (RT 3.8.2)
I believe you're on mysql 5.1.x, it's know issue with some 5.1.2x. I can not say if it's been fixed in recent release of 5.1. You should file bug into mysql bug tracker. On Wed, May 27, 2009 at 1:16 AM, Elijah Wright wrote: > Folks, > We're in the midst of migrating from RT 3.6.4 to RT 3.8.2, and having some > "interesting" issues with the Attachments table. > Here's what queries against Attachments looked like on our old RT instance: > previous RT install (3.6.4): > mysql> explain SELECT main.* FROM Attachments main WHERE (main.Content IS > NOT NULL AND main.Content != '') AND (main.Parent = '1208717') AND > (main.ContentType = 'text/plain') ORDER BY main.id ASC; > ++-+---+--+---+--+-+---+--+-+ > | id | select_type | table | type | possible_keys | key | key_len | > ref | rows | Extra | > ++-+---+--+---+--+-+---+--+-+ > | 1 | SIMPLE | main | ref | Attachments3 | Attachments3 | 4 | > const | 2 | Using where; Using filesort | > ++-+---+--+---+--+-+---+--+-+ > 1 row in set (0.00 sec) > mysql> > > Now, here's what (what should be the self-same queries...) is being produced > on our new RT host, running 3.8.2: > new RT host: > mysql> explain SELECT main.* FROM Attachments main WHERE (main.Content IS > NOT NULL AND main.Content != '') AND (main.Parent = '1208717') AND > (main.ContentType = 'text/plain') ORDER BY main.id ASC; > ++-+---+---+---+-+-+--+-+-+ > | id | select_type | table | type | possible_keys | key | key_len | ref > | rows | Extra | > ++-+---+---+---+-+-+--+-+-+ > | 1 | SIMPLE | main | index | Attachments3 | PRIMARY | 4 | > NULL | 2199950 | Using where | > ++-+---+---+---+-+-+--+-+-+ > 1 row in set (0.11 sec) > mysql> > Obviously, on the new host, it isn't using the index on the Attachments > table - and that's REALLY making things go slowly for some bits of RT. > Anybody got a clue how to fix this, or an idea of what we can do to coerce > it to use the proper index? > [We have quite a lot of tickets, and a fairly scary amount of spam has > leaked into our RT instance - I'm going to need to run the shredder on a > large number of deleted tickets, but the Attachments table is currently so > big that doing so is a bit daunting...] > This is on a new host, and we imported via a mysqldump and re-import, so the > data in the table should be defragmented... but the end result is clearly > problematic. > thanks in advance, > --elijah > > ___ > http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users > > Community help: http://wiki.bestpractical.com > Commercial support: sa...@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. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] mis-use of indexes on the Attachments table (RT 3.8.2)
http://bugs.mysql.com/bug.php?id=37680 - here is bug report, you can help debug issue. On Wed, May 27, 2009 at 1:32 AM, Ruslan Zakirov wrote: > I believe you're on mysql 5.1.x, it's know issue with some 5.1.2x. I > can not say if it's been fixed in recent release of 5.1. You should > file bug into mysql bug tracker. > > On Wed, May 27, 2009 at 1:16 AM, Elijah Wright wrote: >> Folks, >> We're in the midst of migrating from RT 3.6.4 to RT 3.8.2, and having some >> "interesting" issues with the Attachments table. >> Here's what queries against Attachments looked like on our old RT instance: >> previous RT install (3.6.4): >> mysql> explain SELECT main.* FROM Attachments main WHERE (main.Content IS >> NOT NULL AND main.Content != '') AND (main.Parent = '1208717') AND >> (main.ContentType = 'text/plain') ORDER BY main.id ASC; >> ++-+---+--+---+--+-+---+--+-+ >> | id | select_type | table | type | possible_keys | key | key_len | >> ref | rows | Extra | >> ++-+---+--+---+--+-+---+--+-+ >> | 1 | SIMPLE | main | ref | Attachments3 | Attachments3 | 4 | >> const | 2 | Using where; Using filesort | >> ++-+---+--+---+--+-+---+--+-+ >> 1 row in set (0.00 sec) >> mysql> >> >> Now, here's what (what should be the self-same queries...) is being produced >> on our new RT host, running 3.8.2: >> new RT host: >> mysql> explain SELECT main.* FROM Attachments main WHERE (main.Content IS >> NOT NULL AND main.Content != '') AND (main.Parent = '1208717') AND >> (main.ContentType = 'text/plain') ORDER BY main.id ASC; >> ++-+---+---+---+-+-+--+-+-+ >> | id | select_type | table | type | possible_keys | key | key_len | ref >> | rows | Extra | >> ++-+---+---+---+-+-+--+-+-+ >> | 1 | SIMPLE | main | index | Attachments3 | PRIMARY | 4 | >> NULL | 2199950 | Using where | >> ++-+---+---+---+-+-+--+-+-+ >> 1 row in set (0.11 sec) >> mysql> >> Obviously, on the new host, it isn't using the index on the Attachments >> table - and that's REALLY making things go slowly for some bits of RT. >> Anybody got a clue how to fix this, or an idea of what we can do to coerce >> it to use the proper index? >> [We have quite a lot of tickets, and a fairly scary amount of spam has >> leaked into our RT instance - I'm going to need to run the shredder on a >> large number of deleted tickets, but the Attachments table is currently so >> big that doing so is a bit daunting...] >> This is on a new host, and we imported via a mysqldump and re-import, so the >> data in the table should be defragmented... but the end result is clearly >> problematic. >> thanks in advance, >> --elijah >> >> ___ >> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users >> >> Community help: http://wiki.bestpractical.com >> Commercial support: sa...@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. > -- Best regards, Ruslan. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] mis-use of indexes on the Attachments table (RT 3.8.2)
> This is on a new host, and we imported via a mysqldump and re-import, so the > data in the table should be defragmented... but the end result is clearly > problematic. Wait a minute. Did you install 3.8.2 and load a dump into it from the older version? Oy vey. The schemas don't really line up. You'd need to install the old version of RT on the new host, and then go through the upgrade process. Otherwise you're in for a lot of manual discovery & cajoling to get things to work properly. -- -- Tom Lahti BIT Statement LLC (425)251-0833 x 117 http://www.bitstatement.net/ -- ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
