[rt-users] Severe performance problems suddenly in RT 3.8.2

[Jesse Vincent]

On Tue 17.Feb'09 at 17:47:43 +0000, Dave Holland wrote:
> Tim's just got into his car to go home, but I can provide some
> answers...
> 
> On Tue, Feb 17, 2009 at 12:29:10PM -0500, Jesse Vincent wrote:
> > On Tue 17.Feb'09 at 16:51:57 +0000, Tim Cutts wrote:
> > > We migrated our RT server to RT 3.8.2 a week ago, and all went well  
> > > until today, 
> > From what version?
> 
> 3.4.2

And just to be sure, you ran the commands in UPGRADING.mysql?

> 
> > Anything in mysql's error logs?
> 
> No, only the messages in the slow log, e.g:
> 
> # Query_time: 240  Lock_time: 0  Rows_sent: 4  Rows_examined: 27
> SELECT DISTINCT main.Id AS id, main.Filename AS filename,
> main.ContentType AS contenttype, main.Headers AS headers, main.Subject
> AS subject, main.Parent AS parent, main.ContentEncoding AS
> contentencoding, main.ContentType AS contenttype, main.TransactionId AS
> transactionid, main.Created AS created FROM Attachments main JOIN
> Transactions Transactions_1  ON ( Transactions_1.id = main.TransactionId
> ) JOIN Tickets Tickets_2 ON ( Tickets_2.id = Transactions_1.ObjectId )
> WHERE (Tickets_2.EffectiveId = '97814') AND (Transactions_1.ObjectType =
> 'RT::Ticket')  ORDER BY main.id ASC;


Can you give me an 'EXPLAIN' on that query?
> 
> and
> 
> # Query_time: 275  Lock_time: 0  Rows_sent: 1  Rows_examined: 806085
> select count(id) from Attachments;
> 
> > What does EXPLAIN SELECT COUNT(id) FROM Attachments say?
> 
> | id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
> +----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+
> |  1 | SIMPLE      | Attachments | index | NULL          | PRIMARY | 4       | NULL | 2698295 | Using index | 
> 
> > MySQL versions > 4.1 and < 5.0.45 are known to have a crippling query
> > optimizer bug that WILL hurt you on RT, but I'm not sure this is that.
> 
> Any pointers, please? I couldn't see anything in the RT wiki, sorry.

"institutional wisdom", sadly. But I'm assured "That issue isn't this
one"

Can you easily optimize your tables? Perhaps first, it's worth running
mysqltuner.pl (http://mysqltuner.pl) and posting the output.

> This is on Debian Etch so I expect we can upgrade to 5.0.51 from
> backports quite easily if need be.
> 
> thanks,
> Dave
> -- 
> ** Dave Holland ** Systems Support -- Infrastructure Management **
> ** 01223 496923 ** The Sanger Institute, Hinxton, Cambridge, UK **
> "An infinite number of mathematicians walk into a bar..."
> 
> 
> -- 
>  The Wellcome Trust Sanger Institute is operated by Genome Research 
>  Limited, a charity registered in England with number 1021457 and a 
>  company registered in England with number 2742969, whose registered 
>  office is 215 Euston Road, London, NW1 2BE. 
> 

-- 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 194 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090217/8ad1211f/attachment.sig>