[rt-users] RT2 Slowness

[Matthew Watson]

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