[rt-users] any page showing a user drop down in a ticket is loading slow

[Kevin Falcone]

On Wed, Dec 18, 2013 at 12:29:05PM -0500, Adam Hobaugh wrote:
> >The slow query logs are most helpful when you then run EXPLAIN on the
> >query so you know why the database is having trouble.
> Here is the EXPLAIN for the two queries.
> 
> mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL
> ACL_3 JOIN Principals Principals_1  ON ( Principals_1.id = main.id )
> JOIN CachedGroupMembers CachedGroupMembers_2  ON (
> CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN
> CachedGroupMembers CachedGroupMembers_4  ON (
> CachedGroupMembers_4.MemberId = Principals_1.id )  WHERE
> ((ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId   = 3) OR
> (ACL_3.ObjectType = 'RT::System') OR (ACL_3.ObjectType = 'RT::Queue'
> AND ACL_3.ObjectId   = 3) OR (ACL_3.ObjectType = 'RT::System')) AND
> (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND
> (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket'
> OR ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled
> = '0') AND (CachedGroupMembers_2.GroupId = '4') AND
> (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled =
> '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id
> != '1')  ORDER BY main.Name ASC;
> +----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
> | id | select_type | table                | type   | possible_keys
> | key                 | key_len | ref
> | rows | Extra                                                     |
> +----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
> |  1 | SIMPLE      | CachedGroupMembers_2 | range  |
> DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | DisGrouMem
> | 10      | NULL                                  |  264 | Using
> where; Using index; Using temporary; Using filesort |
> |  1 | SIMPLE      | main                 | eq_ref | PRIMARY
> | PRIMARY             | 4       |
> rt4_2_1.CachedGroupMembers_2.MemberId |    1 |
> |
> |  1 | SIMPLE      | Principals_1         | eq_ref | PRIMARY
> | PRIMARY             | 4       | rt4_2_1.main.id
> |    1 | Using where; Distinct                                     |
> |  1 | SIMPLE      | CachedGroupMembers_4 | ref    |
> DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 |
> CachedGroupMembers3 | 5       |
> rt4_2_1.CachedGroupMembers_2.MemberId |    1 | Using where; Distinct
> |
> |  1 | SIMPLE      | ACL_3                | range  | ACL1
> | ACL1                | 85      | NULL
> |   13 | Using where; Using index; Distinct                        |
> +----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
> 5 rows in set (0.01 sec)

Your query here says that MySQL is looking at a pretty tiny dataset
and running the same query on multiple databases here shows a wildly
different explain and very quick results.  This usually points to you
having some enormous tables (although the explain doesn't indicate
that) or a badly tuned mysql.

Can you download and run http://mysqltuner.pl on your database.

It would also be interesting to see
    SELECT TABLE_ROWS, TABLE_NAME
    FROM information_schema.tables
    WHERE TABLE_SCHEMA = 'rt4';
changing your database name where needed.

-kevin
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 235 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20131218/1571efee/attachment.sig>