[rt-users] RT2 Slowness
Matthew Watson
mwatson at office.netspace.net.au
Thu Feb 21 04:53:04 EST 2002
As a follow up to this, I was sent a few suggestions on various indexes to
try(thankyou to everyones suggestions), none of which resolved the problem
though.
I'm wondering if its something weird with my system or if everyone has
massive slowness with this query.
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).
But of cause this may break my system for upgrades, which I really dont
want.
Any thoughts or comments on this?
> -----Original Message-----
> From: rt-users-admin at lists.fsck.com
> [mailto:rt-users-admin at lists.fsck.com]On Behalf Of Matthew Watson
> Sent: Thursday, 21 February 2002 5:32 PM
> To: rt-users at lists.fsck.com
> Subject: [rt-users] RT2 Slowness
>
>
>
> Hi,
>
> I've asked this question again, but didn't really get any good answers so
> I'll try again..
>
> Using RT2.0.11
> mysql 3.23.43-log
>
> system -
> AMD 1200
> 512mb ram
> Ultra Wide SCSI.
>
> 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.
>
> The problem I think is mainly due to the query below, which is
> run to find
> all the tickets requested by user "blabla at domain.com",
> by default the query tries to use the typeindex index on the Watchers_1
> table, which seemingly does nothing as it has to search ALL rows in that
> table, i've tried forcing it to use the other indexes, but they still
> require searching all rows...
>
> Anyone encounted this problem, have a solution? Below I have a
> heap of info
> for any mysql junkies who may be able to help me with this horrid issue.
>
>
> SELECT DISTINCT main.*
> FROM Tickets main, Watchers Watchers_1 LEFT JOIN Users as Users_2 ON
> Watchers_1.Owner = Users_2.id
> WHERE ((main.EffectiveId = main.id))
> AND ((Watchers_1.Scope = 'Ticket'))
> AND ((Watchers_1.Type = 'Requestor'))
> AND ((Watchers_1.Email =
> 'mwatson at netspace.net.au')OR(Users_2.EmailAddress =
> 'mwatson at netspace.net.au'))
> AND main.id = Watchers_1.Value
> ORDER BY main.id DESC LIMIT 50
>
>
>
> +------------+--------+-------------------------------------------
> ---------+
> -----------+---------+------------------+--------+----------------
> ----------
> -------------------+
> | table | type | possible_keys
> |
> key | key_len | ref | rows | Extra
> |
> +------------+--------+-------------------------------------------
> ---------+
> -----------+---------+------------------+--------+----------------
> ----------
> -------------------+
> | Watchers_1 | range |
> Watchers1,Watchers2,typeindex,watchers_index_matty |
> typeindex | 17 | NULL | 291618 | where used; Using
> temporary; Using filesort |
> | main | eq_ref | PRIMARY,Tickets4
> |
> PRIMARY | 4 | Watchers_1.Value | 1 | where used
> |
> | Users_2 | eq_ref | PRIMARY
> |
> PRIMARY | 4 | Watchers_1.Owner | 1 | where used; Distinct
> |
> +------------+--------+-------------------------------------------
> ---------+
> -----------+---------+------------------+--------+----------------
> ----------
> -------------------+
>
>
> mysql> show index from Watchers;
> +----------+------------+----------------------+--------------+---
> ----------
> +-----------+-------------+----------+--------+---------+
> | Table | Non_unique | Key_name | Seq_in_index |
> Column_name
> | Collation | Cardinality | Sub_part | Packed | Comment |
> +----------+------------+----------------------+--------------+---
> ----------
> +-----------+-------------+----------+--------+---------+
> | Watchers | 0 | PRIMARY | 1 | id
> | A | 291618 | NULL | NULL | |
> | Watchers | 1 | Watchers1 | 1 | Scope
> | A | 2 | NULL | NULL | |
> | Watchers | 1 | Watchers1 | 2 | Value
> | A | 291618 | NULL | NULL | |
> | Watchers | 1 | Watchers1 | 3 | Type
> | A | 291618 | NULL | NULL | |
> | Watchers | 1 | Watchers1 | 4 | Owner
> | A | 291618 | NULL | NULL | |
> | Watchers | 1 | Watchers2 | 1 | Email
> | A | 72904 | NULL | NULL | |
> | Watchers | 1 | ownerindex | 1 | Owner
> | A | 20829 | NULL | NULL | |
> | Watchers | 1 | typeindex | 1 | Type
> | A | 2 | NULL | NULL | |
> | Watchers | 1 | watchers_index_matty | 1 | Scope
> | A | 2 | NULL | NULL | |
> | Watchers | 1 | watchers_index_matty | 2 | Type
> | A | 2 | NULL | NULL | |
> | Watchers | 1 | watchers_index_matty | 3 | Email
> | A | 72904 | NULL | NULL | |
> +----------+------------+----------------------+--------------+---
> ----------
> +-----------+-------------+----------+--------+---------+
> 11 rows in set (0.10 sec)
>
> mysql> show index from Users;
> +-------+------------+----------+--------------+--------------+---
> --------+-
> ------------+----------+--------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
> Collation |
> Cardinality | Sub_part | Packed | Comment |
> +-------+------------+----------+--------------+--------------+---
> --------+-
> ------------+----------+--------+---------+
> | Users | 0 | PRIMARY | 1 | id | A
> |
> 47714 | NULL | NULL | |
> | Users | 0 | Users1 | 1 | Name | A
> |
> 47714 | NULL | NULL | |
> | Users | 1 | Users2 | 1 | EmailAddress | A
> |
> 47714 | NULL | NULL | |
> +-------+------------+----------+--------------+--------------+---
> --------+-
> ------------+----------+--------+---------+
> 3 rows in set (0.01 sec)
> ----------------------------------------------
> Matthew Watson
> Development, Netspace Online Systems
> mwatson at netspace.net.au
>
>
> _______________________________________________
> rt-users mailing list
> rt-users at lists.fsck.com
> http://lists.fsck.com/mailman/listinfo/rt-users
More information about the rt-users
mailing list