[rt-users] Slow Query Log

[Jesse Vincent]

On Fri, Aug 08, 2008 at 02:43:24PM -0400, zbigniew at starpower.net wrote:
> RT is the only thing running on a Fedora Core 9 VMWare ESX VM.
> It has an Intel Xeon 1.6Ghz

From what you just sent, all your slow queries are 

# Query_time: 0 

Which isn't all that slow ;)


> 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)
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com
> 

--