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

2009-02-18 Thread Tim Cutts
On 18 Feb 2009, at 12:48 am, Ruslan Zakirov wrote: First time I see something like that. May be it's better to ask on mysql lists. And sure you should keep digging into a reason. My practice shows that there is no such thing as suddenly in IT. There is always some change that turn world up

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

2009-02-18 Thread Dave Holland
On Tue, Feb 17, 2009 at 05:52:23PM -0500, Jesse Vincent wrote: An excellent question. I have a sneaking suspicion there's some DB corruption in there somewhere :/ But maybe it's just a database bug. After chasing some red herrings we found something interesting: mysql select count(*) from

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

2009-02-18 Thread Kenneth Marshall
On Wed, Feb 18, 2009 at 06:13:08PM +, Tim Cutts wrote: On 18 Feb 2009, at 5:54 pm, Dave Holland wrote: On Tue, Feb 17, 2009 at 05:52:23PM -0500, Jesse Vincent wrote: An excellent question. I have a sneaking suspicion there's some DB corruption in there somewhere :/ But maybe it's

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

2009-02-17 Thread Tim Cutts
We migrated our RT server to RT 3.8.2 a week ago, and all went well until today, when suddenly we've been hit by a massive performance problem. It seems that almost any query which touches the Attachments table takes an absolute age to complete. For example, I've just logged into the

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

2009-02-17 Thread Jesse Vincent
On Tue 17.Feb'09 at 16:51:57 +, 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? when suddenly we've been hit by a massive performance problem. It seems that almost any query which touches the Attachments

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

2009-02-17 Thread Jesse Vincent
On Tue 17.Feb'09 at 17:47:43 +, 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 +, Tim Cutts wrote: We migrated our RT server to RT 3.8.2 a

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

2009-02-17 Thread Dave Holland
On Tue, Feb 17, 2009 at 12:59:17PM -0500, Jesse Vincent wrote: And just to be sure, you ran the commands in UPGRADING.mysql? I believe so (Tim did it). # Query_time: 240 Lock_time: 0 Rows_sent: 4 Rows_examined: 27 SELECT DISTINCT main.Id AS id, main.Filename AS filename,

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

2009-02-17 Thread Jesse Vincent
I believe so (Tim did it). # Query_time: 240 Lock_time: 0 Rows_sent: 4 Rows_examined: 27 That just looks...crazy. Can you try an OPTIMIZE TABLE Attachments; ? (It may take ... a while.) pgpUvNTAP2WCd.pgp Description: PGP signature ___

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

2009-02-17 Thread Dave Holland
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 +, Tim Cutts wrote: We migrated our RT server to RT 3.8.2 a week ago, and all went well until today, From what

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

2009-02-17 Thread Ruslan Zakirov
On Tue, Feb 17, 2009 at 8:47 PM, Dave Holland d...@sanger.ac.uk 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 +, Tim Cutts wrote: We migrated our RT server to

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

2009-02-17 Thread Tim Cutts
On 17 Feb 2009, at 6:09 pm, Dave Holland wrote: On Tue, Feb 17, 2009 at 12:59:17PM -0500, Jesse Vincent wrote: And just to be sure, you ran the commands in UPGRADING.mysql? I believe so (Tim did it). Indeed. All done pukka according to the upgrade docs. Tim -- The Wellcome Trust

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

2009-02-17 Thread Tim Cutts
On 17 Feb 2009, at 6:16 pm, Jesse Vincent wrote: I believe so (Tim did it). # Query_time: 240 Lock_time: 0 Rows_sent: 4 Rows_examined: 27 That just looks...crazy. Can you try an OPTIMIZE TABLE Attachments; ? (It may take ... a while.) Might as well do that now, since we're out of

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

2009-02-17 Thread Tim Cutts
On 17 Feb 2009, at 6:16 pm, Jesse Vincent wrote: I believe so (Tim did it). # Query_time: 240 Lock_time: 0 Rows_sent: 4 Rows_examined: 27 That just looks...crazy. Can you try an OPTIMIZE TABLE Attachments; ? (It may take ... a while.) OK, OPTIMIZE TABLE done, but performance on

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

2009-02-17 Thread Tom Lahti
Now, I may be being silly here, but how can a table with only 806180 id's in its primary key have more than 3 million rows? And counting them takes three minutes, which really isn't many rows a second... Is this stored in InnoDB by chance? If it were me, I would mysqldump all databases,

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

2009-02-17 Thread Jesse Vincent
OK, OPTIMIZE TABLE done, but performance on SELECT COUNT(id) FROM Attachments is still terrible: This does sort of smell like an optimiser bug - it says it's using indices, but the level of I/O being done by the machine suggests it's actually performing a full table scan. Indeed.

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

2009-02-17 Thread Tim Cutts
On 17 Feb 2009, at 6:39 pm, Ruslan Zakirov wrote: I hope you see that mysql examined only 27 rows and spend 240 seconds to do that. I suspect that it's related to very high IO activity that were going parallel with this query and blocked mysql's IO requests. That was my worry. I've shuffled

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

2009-02-17 Thread Tim Cutts
On 17 Feb 2009, at 10:52 pm, Jesse Vincent wrote: OK, OPTIMIZE TABLE done, but performance on SELECT COUNT(id) FROM Attachments is still terrible: This does sort of smell like an optimiser bug - it says it's using indices, but the level of I/O being done by the machine suggests it's

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

2009-02-17 Thread Ruslan Zakirov
First time I see something like that. May be it's better to ask on mysql lists. And sure you should keep digging into a reason. My practice shows that there is no such thing as suddenly in IT. There is always some change that turn world up side down. optimize/analyze on InnoDB don't do the same

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

2009-02-17 Thread Tim Cutts
On 18 Feb 2009, at 12:48 am, Ruslan Zakirov wrote: First time I see something like that. May be it's better to ask on mysql lists. And sure you should keep digging into a reason. My practice shows that there is no such thing as suddenly in IT. There is always some change that turn world up