Slow Query Log

I’m using RT 3.8 and Mysql 5.0.51a on Fedora Core 9.

I’ve been noticing some slowness using the interface. Most of the time it is when updating tickets. Reviewing the slow query log I found the following.

Out of a total of 53133 log entries there where
14369 entries for SELECT count(main.id) FROM ObjectCustomF
5540 entries for SELECT * FROM Templates WHERE Queue = '
18967 entries for SELECT main.* FROM CustomFields main JOI
9397 entries for SELECT main.* FROM Tickets main WHERE (

Oddly enough, I don’t use CustomFields.

Any suggestions on how to speed things up?

I’m using RT 3.8 and Mysql 5.0.51a on Fedora Core 9.

I’ve been noticing some slowness using the interface. Most of the
time it is when updating tickets. Reviewing the slow query log I
found the following.

It’d help if you could:

  • send the complete queries
  • send the actual metainformation about how slow is slow
  • run ‘explains’ for these queries
  • tell us about your RT server hardware configuration
  • tell us a bit about what you’ve already done to tune your database

Best,
Jesse

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@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@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@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)

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 :wink:

I noticed that also. Was wondering if I was going to walk away with my foot in my mouth. System is still very slow updating tickets and was hoping the slow query log was telling me something useful. I’ll go back and continue looking around.

Your mysql is configured to log every query that is not using indexes.
Turn it off and you’ll see all queries that take more than one second.On Sat, Aug 9, 2008 at 12:17 AM, zbigniew@starpower.net wrote:

I noticed that also. Was wondering if I was going to walk away with my foot in my mouth. System is still very slow updating tickets and was hoping the slow query log was telling me something useful. I’ll go back and continue looking around.

Best regards, Ruslan.