[rt-users] Huge Attachments table in 3.4.4
Luke Vanderfluit
lvanderf at internode.com.au
Wed Oct 26 20:00:41 EDT 2005
Hi.
Jesse Vincent wrote:
>
>On Tue, Oct 25, 2005 at 04:14:46PM +0930, Luke Vanderfluit wrote:
>
>
>>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?
>>
>>
>
>You might want to start with Jeremy Zawodney's 'mytop' tool.
>
>
>
Thanks. I have installed this and it's great :-).
I have found by using mysqladmin 'processlist' and by logging
slowqueries that the query responsible for the slowness is this:
/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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;
\________________________________
Any ideas on how to increase the speed of this query?
I'm currently running RT 3.4.4 upgraded from 3.0.11 on a relatively slow
box.
If I run the query after a fresh restart of mysql, it takes 150 seconds
(yes it's a large database :-)
If I then run the following command
'optimize table Users, Principals, ACL, Groups, CachedGroupMembers;'
the query takes roughly 90 seconds to complete.
This is an improvement but hasn't addressed the root cause of why the
query takes so long.
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.
Here are the existing indexes on the tables involved in this query:
/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> show index from Users;
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Users | 0 | PRIMARY | 1 | id |
A | 99402 | NULL | NULL | | BTREE | |
| Users | 0 | Users1 | 1 | Name |
A | 99402 | NULL | NULL | | BTREE | |
| Users | 1 | Users2 | 1 | Name |
A | 99402 | NULL | NULL | | BTREE | |
| Users | 1 | Users3 | 1 | id |
A | 99402 | NULL | NULL | | BTREE | |
| Users | 1 | Users3 | 2 | EmailAddress |
A | 99402 | NULL | NULL | YES | BTREE | |
| Users | 1 | Users4 | 1 | EmailAddress |
A | 99402 | NULL | NULL | YES | BTREE | |
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> show index from Principals;
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Principals | 0 | PRIMARY | 1 | id |
A | 1212037 | NULL | NULL | | BTREE | |
| Principals | 1 | Principals2 | 1 | ObjectId |
A | 1212037 | NULL | NULL | YES | BTREE | |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> show index from ACL;
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| ACL | 0 | PRIMARY | 1 | id |
A | 77 | NULL | NULL | | BTREE | |
| ACL | 1 | ACL1 | 1 | RightName |
A | 77 | NULL | NULL | | BTREE | |
| ACL | 1 | ACL1 | 2 | ObjectType |
A | 77 | NULL | NULL | | BTREE | |
| ACL | 1 | ACL1 | 3 | ObjectId |
A | 77 | NULL | NULL | | BTREE | |
| ACL | 1 | ACL1 | 4 | PrincipalType |
A | 77 | NULL | NULL | | BTREE | |
| ACL | 1 | ACL1 | 5 | PrincipalId |
A | 77 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> show index from Groups;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Groups | 0 | PRIMARY | 1 | id |
A | 1094066 | NULL | NULL | | BTREE | |
| Groups | 1 | Groups2 | 1 | Type |
A | 17 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Groups2 | 2 | Instance |
A | 1094066 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Groups2 | 3 | Domain |
A | 1094066 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Domain | 1 | Domain |
A | 17 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Instance | 1 | Instance |
A | 1094066 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Domain_3 | 1 | Domain |
A | 17 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Domain_3 | 2 | Instance |
A | 547033 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Domain_3 | 3 | Type |
A | 1094066 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Domain_3 | 4 | id |
A | 1094066 | NULL | NULL | | BTREE | |
| Groups | 1 | Groups1 | 1 | Domain |
A | 17 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Groups1 | 2 | Instance |
A | 1094066 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Groups1 | 3 | Type |
A | 1094066 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Groups1 | 4 | id |
A | 1094066 | NULL | NULL | | BTREE | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> show index from CachedGroupMembers;
+--------------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment |
+--------------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| CachedGroupMembers | 0 | PRIMARY | 1 |
id | A | 2491767 | NULL | NULL | |
BTREE | |
| CachedGroupMembers | 1 | DisGrouMem | 1 |
GroupId | A | 2491767 | NULL | NULL | YES |
BTREE | |
| CachedGroupMembers | 1 | DisGrouMem | 2 |
MemberId | A | 2491767 | NULL | NULL | YES |
BTREE | |
| CachedGroupMembers | 1 | DisGrouMem | 3 |
Disabled | A | 2491767 | NULL | NULL | |
BTREE | |
| CachedGroupMembers | 1 | GrouMem | 1 |
GroupId | A | 2491767 | NULL | NULL | YES |
BTREE | |
| CachedGroupMembers | 1 | GrouMem | 2 |
MemberId | A | 2491767 | NULL | NULL | YES |
BTREE | |
| CachedGroupMembers | 1 | MemberId | 1 |
MemberId | A | 2491767 | NULL | NULL | YES |
BTREE | |
| CachedGroupMembers | 1 | ImmediateParentId | 1 |
ImmediateParentId | A | 2491767 | NULL | NULL | YES |
BTREE | |
| CachedGroupMembers | 1 | ImmediateParentId | 2 |
MemberId | A | 2491767 | NULL | NULL | YES |
BTREE | |
+--------------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
\________________________________________________
Any tips or suggestions on how to improve performance of this
application would be highly appreciated :-)
Kind regards.
Luke.
--
Luke
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20051027/5802d5b8/attachment.htm>
More information about the rt-users
mailing list