[rt-users] Slow Query Log

[zbigniew at starpower.net]

RT is the only thing running on a Fedora Core 9 VMWare ESX VM.
It has an Intel Xeon 1.6Ghz

top>>>   Mem:    774768k total,   743096k used,    31672k free,    19836k buffers 
Swap:  1572856k total,   104668k used,  1468188k free,   179940k cached 

uptime>>>>   14:40:53 up 20 days,  2:39,  1 user,  load average: 0.16, 0.04, 0.02

Have done no tuning of the database other than installing the RT 3.8 schema updates.  Previous ran 3.6.3 with no problems for over a year.


# User at Host: rt_user[rt_user] @ localhost []
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
SELECT count(main.id) FROM ObjectCustomFieldValues main  WHERE (main.Disabled = '0') AND (main.ObjectType = 'RT::Transaction') AND (main.ObjectId = '92578');

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | main  | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.02 sec)





# Time: 080725 12:42:47
# User at Host: rt_user[rt_user] @ localhost []
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
SELECT main.* FROM CustomFields main JOIN ObjectCustomFields ObjectCustomFields_1  ON ( ObjectCustomFields_1.CustomField = main.id )  WHERE (main.Disabled = '0') AND (main.Name = NULL) AND (ObjectCustomFields_1.ObjectId = '1' OR ObjectCustomFields_1.ObjectId = '0') AND (main.LookupType = 'RT::Queue-RT::Ticket-RT::Transaction')  GROUP BY main.id  ORDER BY MAX(ObjectCustomFields_1.ObjectId) DESC, MIN(ObjectCustomFields_1.SortOrder) ASC;


+----+-------------+----------------------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table                | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+----------------------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | main                 | ALL  | PRIMARY       | NULL | NULL    | NULL |    1 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | ObjectCustomFields_1 | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where                                  | 
+----+-------------+----------------------+------+---------------+------+---------+------+------+----------------------------------------------+
2 rows in set (0.00 sec)




# Time: 080725 12:44:18
# User at Host: rt_user[rt_user] @ localhost []
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 4817
SELECT main.* FROM Tickets main  WHERE (main.Status != 'deleted') AND ( ( main.Owner = '10' OR main.Owner = '135' )  AND main.Type = 'reminder' AND  ( main.Status = 'new' OR main.Status = 'open' ) ) AND (main.EffectiveId = main.id)  ORDER BY main.Due DESC;

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | main  | ALL  | Tickets2      | NULL | NULL    | NULL | 5381 | Using where; Using filesort | 
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)