[rt-users] Performance Issue - RT 3.xx

[Asif Iqbal]

I applied these indexes and upgraded RT3.0.3 to RT3.0.4 with mysql 4.0.13 and
Apache+modperl1 . It definitely improved a lot over RT3.03 but still as fast as
my good old RT2.0.13. Any other suggestions to improve the performance. My users
wants the old RT back due to its slowness

Thanks

On Tue, 15 Jul 2003, Greg Kuhnert wrote:

> I've just received a message from the guy that found the initial query
> problem .... I am forwarding a copy here for the benefit of others in
> the list:
>
> -----Original Message-----
> From: Jim Crumpler
> Sent: Tuesday, July 15, 2003 1:28 PM
> To: Greg Kuhnert
> Subject: query speed..
>
>
> I've made the display ticket about 4 times faster by adding two more
> indices to the CachedGroupMembers table.. The number of linear scanned
> rows scanned has dropped from 167,000 to about 30 for that query I sent
> through before.
>
> This may slow down the ticket update a little.. If its too bad, then
> drop the indices called group1 and member1 in CachedGroupMembers.
>
> The queries don't seem to be optimised for tables this large.
>
> Jim..
>
> ---
> (the two commands I typed.)
>
> mysql> create index group1 on CachedGroupMembers (GroupId);
> Query OK, 167064 rows affected (9.46 sec)
> Records: 167064  Duplicates: 0  Warnings: 0
>
> mysql> create index member1 on CachedGroupMembers (MemberId);
> Query OK, 167064 rows affected (10.67 sec)
> Records: 167064  Duplicates: 0  Warnings: 0
>
>
> ------------------------------------------------------------------------
> --------
>
> mysql> describe
>     ->
>     -> SELECT DISTINCT main.*
>     -> FROM Tickets main
>     -> ,Groups Groups_1
>     -> ,Principals Principals_2
>     -> ,CachedGroupMembers CachedGroupMembers_3
>     -> ,Principals Principals_4
>     -> ,Users Users_5
>     ->
>     -> WHERE ((main.EffectiveId = main.id))
>     -> AND ((main.Type = 'ticket'))
>     -> AND ( (  ( (Users_5.EmailAddress = 'anthony at comindico.com.au')
> AND (Groups_1.Domain = 'RT::Ticket-Role') AND (Groups_1.Type =
> 'Requestor') AND (Principals_2.PrincipalType = 'Group') )  )
>     -> AND ( (main.Status = 'new') OR (main.Status = 'open') ) )
>     -> AND Groups_1.Instance = main.id
>     -> AND Groups_1.id = Principals_2.ObjectId
>     -> AND Principals_2.id = CachedGroupMembers_3.GroupId
>     -> AND CachedGroupMembers_3.MemberId = Principals_4.id
>     -> AND Principals_4.ObjectId = Users_5.id
>     ->
>     -> ORDER BY main.Priority DESC LIMIT 10;
> +----------------------+--------+-----------------------------------+---
> ----------+---------+------------------------------+------+-------------
> --------------------------------+
> | table                | type   | possible_keys                     |
> key         | key_len | ref                          | rows | Extra
> |
> +----------------------+--------+-----------------------------------+---
> ----------+---------+------------------------------+------+-------------
> --------------------------------+
> | Users_5              | ref    | PRIMARY,Users3,Users4             |
> Users4      |     121 | const                        |    1 | where
> used; Using temporary; Using filesort |
> | Principals_4         | ref    | PRIMARY,Principals2               |
> Principals2 |       5 | Users_5.id                   |   15 |
> |
> | CachedGroupMembers_3 | ref    | DisGrouMem,GrouMem,group1,member1 |
> member1     |       5 | Principals_4.id              |    2 |
> |
> | Principals_2         | eq_ref | PRIMARY,Principals2               |
> PRIMARY     |       4 | CachedGroupMembers_3.GroupId |    1 | where used
> |
> | Groups_1             | eq_ref | PRIMARY,Groups1,Groups2           |
> PRIMARY     |       4 | Principals_2.ObjectId        |    1 | where used
> |
> | main                 | eq_ref | PRIMARY,Tickets4,Tickets5         |
> PRIMARY     |       4 | Groups_1.Instance            |    1 | where used
> |
> +----------------------+--------+-----------------------------------+---
> ----------+---------+------------------------------+------+-------------
> --------------------------------+
> 6 rows in set (0.00 sec)
> _______________________________________________
> rt-users mailing list
> rt-users at lists.fsck.com
> http://lists.fsck.com/mailman/listinfo/rt-users
>
> Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm
>

-- 
Asif Iqbal
http://pgpkeys.mit.edu:11371/pks/lookup?op=get&search=0x8B686E08
There's no place like 127.0.0.1