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

[Jesse Vincent]

> 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.

Indeed. Before you dig deeply into what's going on with this version of
mysql, coming up to a current 5.0.x probably makes sense.

>
> 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?

An excellent question. I have a sneaking suspicion there's some DB
corruption in there somewhere :/ But maybe it's just a database bug.



-- 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 194 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090217/d49964b0/attachment.sig>