[rt-devel] RE: [rt-users] RT2 Slowness

[Bruce Campbell]

On Thu, 21 Feb 2002, Matthew Watson wrote:

> >  I've got a db of about 250,000 - 300,000 tickets I'm finding that doing a
>  I'm wondering if its something weird with my system or if everyone has
> massive slowness with this query.

Ah, it would seem that I will have to revise a previous (private)
statement.  You appear to be the largest RT installation (300,000
tickets?), not my previous employer ;)

>  My one thought as to how to fix it would be to always store the email
> address in Watchers.Email, however this is pretty ugly as far as database
> design goes I believe (but at this point speed is much more important to me
> that db design).

Are you logging queries (or at least slow queries) ?

>  But of cause this may break my system for upgrades, which I really dont
> want.

> >  Using RT2.0.11
> >  mysql 3.23.43-log

With or without threading ?

> >  system -
> >   AMD 1200
> >   512mb ram

DB work always runs better with more memory, but a point to look out for
here would be how much swap you have configured, and how often is the swap
used?

> >  I've got a db of about 250,000 - 300,000 tickets I'm finding that doing a
> > search for all tickets by a given user is VERY slow (about 30-60 seconds
> > when no other
> > queries are running). This is causing alot of issues because I then get
> > locking issues with inserts and updates etc.. causing the system to daily
> > come to

> > grinding stop. I've tried playing around to the table locking sequence
> > (using low-priority-locks) but this seemed to have limited effect.

Quick easy suggestions:

	Add specific indexes on Watchers.Email, and Watchers.Type .
	( you've done this )

Slightly harder suggestions:

	USE INDEX		( requires changes in SearchBuilder, 3.23.12+ )
	IGNORE INDEX		( ditto )
	SQL_BUFFER_RESULT	( requires changes in SearchBuilder )
	INSERT DELAYED		( Aieeee, likely to break things )

Painful suggestions:

	Upgrade to MySQL 4x which uses a Query Cache (see MySQL docs)
	Use MySQL replication and direct insert/update queries to the
		master, selects to the slave ( requires much voodoo )

Regards,

-- 
                             Bruce Campbell                            RIPE
                   Systems/Network Engineer                             NCC
                 www.ripe.net - PGP562C8B1B                      Operations