[rt-users] Severe performance problems suddenly in RT 3.8.2

[Tim Cutts]

On 17 Feb 2009, at 6:16 pm, Jesse Vincent wrote:

>
>
>> I believe so (Tim did it).
>>
>>>> # Query_time: 240  Lock_time: 0  Rows_sent: 4  Rows_examined: 27
>
> That just looks...crazy. Can you try an OPTIMIZE TABLE Attachments; ?
> (It may take ... a while.)

OK, OPTIMIZE TABLE done, but performance on SELECT COUNT(id) FROM  
Attachments is still terrible:

This does sort of smell like an optimiser bug - it says it's using  
indices, but the level of I/O being done by the machine suggests it's  
actually performing a full table scan.

mysql> optimize table Attachments;
+------------------+----------+----------+----------+
| Table            | Op       | Msg_type | Msg_text |
+------------------+----------+----------+----------+
| rtdb.Attachments | optimize | status   | OK       |
+------------------+----------+----------+----------+
1 row in set (23 min 11.29 sec)

mysql> select count(id) from Attachments;
+-----------+
| count(id) |
+-----------+
|    806180 |
+-----------+
1 row in set (3 min 27.42 sec)

mysql> explain select count(id) from Attachments;
+----+-------------+-------------+-------+---------------+--------- 
+---------+------+---------+-------------+
| id | select_type | table       | type  | possible_keys | key     |  
key_len | ref  | rows    | Extra       |
+----+-------------+-------------+-------+---------------+--------- 
+---------+------+---------+-------------+
|  1 | SIMPLE      | Attachments | index | NULL          | PRIMARY |  
4       | NULL | 3065551 | Using index |
+----+-------------+-------------+-------+---------------+--------- 
+---------+------+---------+-------------+
1 row in set (0.00 sec)

Now, I may be being silly here, but how can a table with only 806180  
id's in its primary key have more than 3 million rows?  And counting  
them takes three minutes, which really isn't many rows a second...

Tim


-- 
 The Wellcome Trust Sanger Institute is operated by Genome Research 
 Limited, a charity registered in England with number 1021457 and a 
 company registered in England with number 2742969, whose registered 
 office is 215 Euston Road, London, NW1 2BE.