On Wed, Feb 18, 2009 at 06:13:08PM +0000, 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 just a database bug. > > > > After chasing some red herrings we found something "interesting": > > > > mysql> select count(*) from Attachments; > > +----------+ > > | count(*) | > > +----------+ > > | 807113 | > > +----------+ > > 1 row in set (1 min 47.01 sec) > > > > mysql> select count(*) from Attachments ignore index(PRIMARY); > > +----------+ > > | count(*) | > > +----------+ > > | 807113 | > > +----------+ > > 1 row in set (0.66 sec) > > > > So I did a quick "alter table Attachments drop primary key, add > > primary > > key (id);" and now we are seeing much more sensible behaviour: > > > > mysql> select count(*) from Attachments; > > +----------+ > > | count(*) | > > +----------+ > > | 807568 | > > +----------+ > > 1 row in set (0.00 sec) > > > > There is still an occasional hiccup when it goes slower - possibly > > when > > an Attachment has been added - but by and large it's working as > > expected > > now. > > > > Thanks everyone for your help, I've learned rather a lot about MySQL > > in > > the last day or so! > > Should add that any form of full text search of Content is still > taking so long that the web interface times out, and in some browsers > this can then have a nasty effect on the session and you can't open > any more RT windows without shutting down your browser (behaviour seen > on both IE and Firefox) > > Tim >
Tim, That is a consequence of the I/O needed versus the performance of your I/O subsystem. If full text search of content is needed I would recommend using a backend database with that support. Currently there are two recipes on the wiki for adding that support to Oracle and PostgreSQL. I am biased, but the PostgreSQL recipe is extremely easy to set up and preforms wonderfully -- but I am biased. :) Cheers, Ken _______________________________________________ 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