<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi.<br>
<br>
Jesse Vincent wrote:
<blockquote cite="mid20051026125303.GP14262@bestpractical.com"
type="cite">
<pre wrap="">
On Tue, Oct 25, 2005 at 04:14:46PM +0930, Luke Vanderfluit wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi.
I have upgraded RT from 3.0.11 to 3.4.4. Mysql 4.0.18, Apache 1.3.27
(with static modperl module).
The database we are using is quite large.
The main problem we are having is when a user needs to comment on a
ticket, the loading of a ticket can take quite a long time.
This is due to mysql churning through data.
I'd like to find out how to speed up this process.
How do I find out what tables mysql is checking through when RT is
loading the Update.html page?
</pre>
</blockquote>
<pre wrap=""><!---->
You might want to start with Jeremy Zawodney's 'mytop' tool.
</pre>
</blockquote>
Thanks. I have installed this and it's great :-).<br>
<br>
I have found by using mysqladmin 'processlist' and by logging
slowqueries that the query responsible for the slowness is this:<br>
/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br>
SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL
ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4 WHERE
((ACL_2.RightName = 'OwnTicket')) AND ((CachedGroupMembers_4.MemberId =
Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId))
AND ((Principals_1.Disabled = '0')or(Principals_1.Disabled = '0')) AND
((Principals_1.id != '1')) AND ((main.id = Principals_1.id)) AND (
( ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = 'Group'
AND ( Groups_3.Domain = 'SystemInternal' OR Groups_3.Domain =
'UserDefined' OR Groups_3.Domain = 'ACLEquivalence')) OR ( (
(Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = 23) OR (
Groups_3.Domain = 'RT::Ticket-Role' AND Groups_3.Instance = 238803) )
AND Groups_3.Type = ACL_2.PrincipalType) ) AND (ACL_2.ObjectType =
'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId =
23) ) ORDER BY main.Name ASC;<br>
\________________________________<br>
<br>
Any ideas on how to increase the speed of this query?<br>
I'm currently running RT 3.4.4 upgraded from 3.0.11 on a relatively
slow box.<br>
If I run the query after a fresh restart of mysql, it takes 150 seconds
(yes it's a large database :-)<br>
If I then run the following command <br>
'optimize table Users, Principals, ACL, Groups, CachedGroupMembers;' <br>
the query takes roughly 90 seconds to complete.<br>
This is an improvement but hasn't addressed the root cause of why the
query takes so long.<br>
<br>
There may be an index that I can add to the tables to improve
performance to normal speed which should be around 3-5 seconds on this
box.<br>
<br>
Here are the existing indexes on the tables involved in this query:<br>
/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br>
mysql> show index from Users;<br>
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+<br>
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |<br>
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+<br>
| Users | 0 | PRIMARY | 1 | id |
A | 99402 | NULL | NULL | | BTREE
| |<br>
| Users | 0 | Users1 | 1 | Name |
A | 99402 | NULL | NULL | | BTREE
| |<br>
| Users | 1 | Users2 | 1 | Name |
A | 99402 | NULL | NULL | | BTREE
| |<br>
| Users | 1 | Users3 | 1 | id |
A | 99402 | NULL | NULL | | BTREE
| |<br>
| Users | 1 | Users3 | 2 | EmailAddress |
A | 99402 | NULL | NULL | YES | BTREE
| |<br>
| Users | 1 | Users4 | 1 | EmailAddress |
A | 99402 | NULL | NULL | YES | BTREE
| |<br>
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+<br>
mysql> show index from Principals;<br>
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br>
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |<br>
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br>
| Principals | 0 | PRIMARY | 1 | id |
A | 1212037 | NULL | NULL | | BTREE
| |<br>
| Principals | 1 | Principals2 | 1 | ObjectId |
A | 1212037 | NULL | NULL | YES | BTREE
| |<br>
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br>
mysql> show index from ACL;<br>
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+<br>
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |<br>
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+<br>
| ACL | 0 | PRIMARY | 1 | id |
A | 77 | NULL | NULL | | BTREE
| |<br>
| ACL | 1 | ACL1 | 1 | RightName |
A | 77 | NULL | NULL | | BTREE
| |<br>
| ACL | 1 | ACL1 | 2 | ObjectType |
A | 77 | NULL | NULL | | BTREE
| |<br>
| ACL | 1 | ACL1 | 3 | ObjectId |
A | 77 | NULL | NULL | | BTREE
| |<br>
| ACL | 1 | ACL1 | 4 | PrincipalType |
A | 77 | NULL | NULL | | BTREE
| |<br>
| ACL | 1 | ACL1 | 5 | PrincipalId |
A | 77 | NULL | NULL | | BTREE
| |<br>
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+<br>
mysql> show index from Groups;<br>
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br>
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |<br>
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br>
| Groups | 0 | PRIMARY | 1 | id |
A | 1094066 | NULL | NULL | | BTREE
| |<br>
| Groups | 1 | Groups2 | 1 | Type |
A | 17 | NULL | NULL | YES | BTREE
| |<br>
| Groups | 1 | Groups2 | 2 | Instance |
A | 1094066 | NULL | NULL | YES | BTREE
| |<br>
| Groups | 1 | Groups2 | 3 | Domain |
A | 1094066 | NULL | NULL | YES | BTREE
| |<br>
| Groups | 1 | Domain | 1 | Domain |
A | 17 | NULL | NULL | YES | BTREE
| |<br>
| Groups | 1 | Instance | 1 | Instance |
A | 1094066 | NULL | NULL | YES | BTREE
| |<br>
| Groups | 1 | Domain_3 | 1 | Domain |
A | 17 | NULL | NULL | YES | BTREE
| |<br>
| Groups | 1 | Domain_3 | 2 | Instance |
A | 547033 | NULL | NULL | YES | BTREE
| |<br>
| Groups | 1 | Domain_3 | 3 | Type |
A | 1094066 | NULL | NULL | YES | BTREE
| |<br>
| Groups | 1 | Domain_3 | 4 | id |
A | 1094066 | NULL | NULL | | BTREE
| |<br>
| Groups | 1 | Groups1 | 1 | Domain |
A | 17 | NULL | NULL | YES | BTREE
| |<br>
| Groups | 1 | Groups1 | 2 | Instance |
A | 1094066 | NULL | NULL | YES | BTREE
| |<br>
| Groups | 1 | Groups1 | 3 | Type |
A | 1094066 | NULL | NULL | YES | BTREE
| |<br>
| Groups | 1 | Groups1 | 4 | id |
A | 1094066 | NULL | NULL | | BTREE
| |<br>
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br>
mysql> show index from CachedGroupMembers;<br>
+--------------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+<br>
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null
| Index_type | Comment |<br>
+--------------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+<br>
| CachedGroupMembers | 0 | PRIMARY | 1 |
id | A | 2491767 | NULL | NULL |
| BTREE | |<br>
| CachedGroupMembers | 1 | DisGrouMem | 1 |
GroupId | A | 2491767 | NULL | NULL | YES
| BTREE | |<br>
| CachedGroupMembers | 1 | DisGrouMem | 2 |
MemberId | A | 2491767 | NULL | NULL | YES
| BTREE | |<br>
| CachedGroupMembers | 1 | DisGrouMem | 3 |
Disabled | A | 2491767 | NULL | NULL |
| BTREE | |<br>
| CachedGroupMembers | 1 | GrouMem | 1 |
GroupId | A | 2491767 | NULL | NULL | YES
| BTREE | |<br>
| CachedGroupMembers | 1 | GrouMem | 2 |
MemberId | A | 2491767 | NULL | NULL | YES
| BTREE | |<br>
| CachedGroupMembers | 1 | MemberId | 1 |
MemberId | A | 2491767 | NULL | NULL | YES
| BTREE | |<br>
| CachedGroupMembers | 1 | ImmediateParentId | 1 |
ImmediateParentId | A | 2491767 | NULL | NULL | YES
| BTREE | |<br>
| CachedGroupMembers | 1 | ImmediateParentId | 2 |
MemberId | A | 2491767 | NULL | NULL | YES
| BTREE | |<br>
+--------------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+<br>
\________________________________________________<br>
<br>
<br>
Any tips or suggestions on how to improve performance of this
application would be highly appreciated :-)<br>
Kind regards.<br>
Luke.<br>
<br>
<blockquote cite="mid20051026125303.GP14262@bestpractical.com"
type="cite">
<pre wrap=""></pre>
</blockquote>
-- <br>
<pre class="moz-signature" cols="72">Luke
</pre>
</body>
</html>