Searching on requestor in large databases

Heya.

using RT 2.0.8
I’ve just spent the night finishing off an import of 250,000 Tickets from
rt1 to rt2, everything is nice, except one major problem, searching on
requestor takes AGES, if you search on one requestor it takes about 15
seconds to return the list, if you accendently search on two different
requestors (which is pretty easy to do) it litrally bring mysql to its knees
(99% cpu usage and no end to the query in site), you can’t even press stop
on the browser to fix it up either, as mysql just keeps chugging away at the
query.

Now this wouldn’t be a big problem, except we use this function all the
time to find previous tickets by a given requestor, for each and every
incoming call infact.

I’ve had a look at the way requestor is handled, and its pretty messy, it no
wonder it takes so long to do the query… I’ve tried various things like
adding indexes to owner on the watchers table (this wasn’t in the default
schema for mysql, but it is for pgsql, odd) , and in a last ditch effort i’m
trying pgsql to see if its any better at handling this complex query…

Any other ideas on how to improve performace for this one, short of
restructing how requestor is stored I can’t think of any. Personally I don’t
see why requestor isn’t stored as a field in ticket (except it means there
can only be one requestor, but thats ok)

Configuration is as follows:
mysql 3.23
rt 2.0.8
searchbuilder .48
apache + mod_perl

Regards
Matthew Watson
Development, Netspace Online Systems
mwatson@netspace.net.au

you might give 2.0.11 a shot. Also note that a bunch of new indices were
added in 2.0.9.

you might also want to look at the exact queries going through mysql
and get mysql to explain just how it deals with them so you can see
what indices might help.

how much ram and cpu do you have in that box?On Sat, Jan 05, 2002 at 06:56:59AM +1100, Matthew Watson wrote:

Heya.

using RT 2.0.8
I’ve just spent the night finishing off an import of 250,000 Tickets from
rt1 to rt2, everything is nice, except one major problem, searching on
requestor takes AGES, if you search on one requestor it takes about 15
seconds to return the list, if you accendently search on two different
requestors (which is pretty easy to do) it litrally bring mysql to its knees
(99% cpu usage and no end to the query in site), you can’t even press stop
on the browser to fix it up either, as mysql just keeps chugging away at the
query.

Now this wouldn’t be a big problem, except we use this function all the
time to find previous tickets by a given requestor, for each and every
incoming call infact.

I’ve had a look at the way requestor is handled, and its pretty messy, it no
wonder it takes so long to do the query… I’ve tried various things like
adding indexes to owner on the watchers table (this wasn’t in the default
schema for mysql, but it is for pgsql, odd) , and in a last ditch effort i’m
trying pgsql to see if its any better at handling this complex query…

Any other ideas on how to improve performace for this one, short of
restructing how requestor is stored I can’t think of any. Personally I don’t
see why requestor isn’t stored as a field in ticket (except it means there
can only be one requestor, but thats ok)

Configuration is as follows:
mysql 3.23
rt 2.0.8
searchbuilder .48
apache + mod_perl

Regards

Matthew Watson
Development, Netspace Online Systems
mwatson@netspace.net.au


rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.

ok, i’ve track it down a little further…

the query it really dies on is

SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1 LEFT JOIN
Users as Users_2 ON Watchers_1.Owner = Users_2.id WHERE ((main.Status =
‘open’)OR(main.Status = ‘new’)) AND ((Watchers_1.Scope = ‘Ticket’)) AND
((Watchers_1.Type = ‘Requestor’)) AND ((Watchers_1.Email =
‘user@email.com’)OR(Users_2.EmailAddress = ‘user@email.com’)) AND main.id =
Watchers_1.Value ORDER BY main.Priority DESC LIMIT 25

I tried adding some indexes on various things, and that gave it a little
boost, but it still takes about 10-15 seconds just to display a ticket, and
about 20 seconds to display a listing of all emails by that given user…

Anyone have suggestions for speeding this up?

Mat.-----Original Message-----
From: rt-users-admin@lists.fsck.com
[mailto:rt-users-admin@lists.fsck.com]On Behalf Of Matthew Watson
Sent: Saturday, 5 January 2002 6:57 AM
To: rt-users@lists.fsck.com
Subject: [rt-users] Searching on requestor in large databases

Heya.

using RT 2.0.8
I’ve just spent the night finishing off an import of 250,000 Tickets from
rt1 to rt2, everything is nice, except one major problem, searching on
requestor takes AGES, if you search on one requestor it takes about 15
seconds to return the list, if you accendently search on two different
requestors (which is pretty easy to do) it litrally bring mysql to its knees
(99% cpu usage and no end to the query in site), you can’t even press stop
on the browser to fix it up either, as mysql just keeps chugging away at the
query.

Now this wouldn’t be a big problem, except we use this function all the
time to find previous tickets by a given requestor, for each and every
incoming call infact.

I’ve had a look at the way requestor is handled, and its pretty messy, it no
wonder it takes so long to do the query… I’ve tried various things like
adding indexes to owner on the watchers table (this wasn’t in the default
schema for mysql, but it is for pgsql, odd) , and in a last ditch effort i’m
trying pgsql to see if its any better at handling this complex query…

Any other ideas on how to improve performace for this one, short of
restructing how requestor is stored I can’t think of any. Personally I don’t
see why requestor isn’t stored as a field in ticket (except it means there
can only be one requestor, but thats ok)

Configuration is as follows:
mysql 3.23
rt 2.0.8
searchbuilder .48
apache + mod_perl

Regards
Matthew Watson
Development, Netspace Online Systems
mwatson@netspace.net.au

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Update on this…

After doing various things with indexes, I got our dba to have a looky at
it, and smartly he checked out the mysql cnf… and rightly so, it was setup
pretty bad. After tweeking my.cnf and apache/mod_perl we have this system
running much better. Although it still barks a bit sometimes when a big
query has a lock on a table (so heaps of other queries bottle up behind it)

Mat.-----Original Message-----
From: rt-users-admin@lists.fsck.com
[mailto:rt-users-admin@lists.fsck.com]On Behalf Of Matthew Watson
Sent: Monday, 7 January 2002 11:29 AM
To: rt-users@lists.fsck.com
Subject: RE: [rt-users] Searching on requestor in large databases

ok, i’ve track it down a little further…

the query it really dies on is

SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1 LEFT JOIN
Users as Users_2 ON Watchers_1.Owner = Users_2.id WHERE ((main.Status =
‘open’)OR(main.Status = ‘new’)) AND ((Watchers_1.Scope = ‘Ticket’)) AND
((Watchers_1.Type = ‘Requestor’)) AND ((Watchers_1.Email =
‘user@email.com’)OR(Users_2.EmailAddress = ‘user@email.com’)) AND main.id =
Watchers_1.Value ORDER BY main.Priority DESC LIMIT 25

I tried adding some indexes on various things, and that gave it a little
boost, but it still takes about 10-15 seconds just to display a ticket, and
about 20 seconds to display a listing of all emails by that given user…

Anyone have suggestions for speeding this up?

Mat.

-----Original Message-----
From: rt-users-admin@lists.fsck.com
[mailto:rt-users-admin@lists.fsck.com]On Behalf Of Matthew Watson
Sent: Saturday, 5 January 2002 6:57 AM
To: rt-users@lists.fsck.com
Subject: [rt-users] Searching on requestor in large databases

Heya.

using RT 2.0.8
I’ve just spent the night finishing off an import of 250,000 Tickets from
rt1 to rt2, everything is nice, except one major problem, searching on
requestor takes AGES, if you search on one requestor it takes about 15
seconds to return the list, if you accendently search on two different
requestors (which is pretty easy to do) it litrally bring mysql to its knees
(99% cpu usage and no end to the query in site), you can’t even press stop
on the browser to fix it up either, as mysql just keeps chugging away at the
query.

Now this wouldn’t be a big problem, except we use this function all the
time to find previous tickets by a given requestor, for each and every
incoming call infact.

I’ve had a look at the way requestor is handled, and its pretty messy, it no
wonder it takes so long to do the query… I’ve tried various things like
adding indexes to owner on the watchers table (this wasn’t in the default
schema for mysql, but it is for pgsql, odd) , and in a last ditch effort i’m
trying pgsql to see if its any better at handling this complex query…

Any other ideas on how to improve performace for this one, short of
restructing how requestor is stored I can’t think of any. Personally I don’t
see why requestor isn’t stored as a field in ticket (except it means there
can only be one requestor, but thats ok)

Configuration is as follows:
mysql 3.23
rt 2.0.8
searchbuilder .48
apache + mod_perl

Regards
Matthew Watson
Development, Netspace Online Systems
mwatson@netspace.net.au

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Mat,

Could you tell us what was poorly set up and what changes were made to
have it work well?

Best,
Blair

Matthew Watson wrote:

Update on this…

After doing various things with indexes, I got our dba to have a looky at
it, and smartly he checked out the mysql cnf… and rightly so, it was setup
pretty bad. After tweeking my.cnf and apache/mod_perl we have this system
running much better. Although it still barks a bit sometimes when a big
query has a lock on a table (so heaps of other queries bottle up behind it)

Mat.

Blair Zajac blair@orcaware.com
Web and OS performance plots - Orca Home Page - OrcaWare Technologies

The main things changed in the my.cnf file (mysqls config file) are

set-variable = key_buffer=128M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=64M

originally I have key_buffer at something like 32mb, which meant mysql had
to continuelly copy the query results out to disk before it could get more
results. Massively slowing things down.

I think we could also play with the locking settings to speed up RT majorly,
as one of the big speed issues we are having now is queries waiting for
locks to be released before running… But I’d rather not have to do that.

Regarding apache conf, I think that the issue with apache getting really big
is due to a bug in either rt2 or apache/mod_perl (or both) when tickets are
moved to a queue the user cant read, but thats in a different thread :slight_smile:

Mat.

Oh yeah, before anyone just up and does this…

The machine this is running on has 512mb of ram, and it is only running rt2,
eg

apache/mod_perl
sendmail
mysql
rt2

At first we allocated too much memory to mysql and found the machine
starting to eat into swap space… so becareful of that.

Mat.