[rt-devel] shredding users

[Christian Loos]

Am 17.07.2014 17:43, schrieb Alex Vandiver:
> On 07/17/2014 09:23 AM, Wallace Reis wrote:
>> On Jul 17, 2014, at 10:07, Christian Loos <cloos at netcologne.de> 
>> wrote:
>>> Hi,
>>>
>>> shredding users, the longest database part was this query:
>>> SELECT main.* FROM Attachments main  WHERE (main.Creator = '123')
>>> ORDER BY main.id ASC
>>>
>>> Beside maybe mentioning an index on Attachments.Creator for 
>>> shredder and maybe omit the sorting, wouldn't it be better to 
>>> "SELECT main.id FROM Attachments ..." which wouldn't send the 
>>> Content column?
>>
>> Hi,
>>
>> It depends on the RT instance you have. Have you analysed the
>> query plan of original query? How about the proposed solutions?
>> Without looking at that, it sounds like that the first two things
>> you suggested about the index and sorting would provide way more
>> gain than just removing columns from the retrieval list if it’s for
>> a RT instance where attachments are basically small (about a few
>> hundreds of KB).
> 
> There's no reason to _not_ limit the set of columns down.  The index
> will probably help the most, I agree -- removing the sort is unlikely
> to make much difference, as sorting by the primary key is pretty cheap
> in most databases.
>  - Alex
> 

Hi,

attached some details on query times and index creation.

Without this index shred the user with the WebUI results in killing
mysqld in my case. Shred the user with sbin/rt-shredder takes constantly
30 minutes due to the Attachments query.
After creating the index shred a user with the WebUI takes 1 minute.

I think you should highly recommend the index in the shredder
documentation or even think about adding this index for all users.

Chris
-------------- next part --------------
mysql> SELECT COUNT(*) FROM Attachments;
+----------+
| COUNT(*) |
+----------+
|  1316179 |
+----------+
1 row in set (1.50 sec)

mysql> EXPLAIN SELECT main.* FROM Attachments main WHERE (main.Creator = '123')  ORDER BY main.id ASC;
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | main  | index | NULL          | PRIMARY | 4       | NULL | 621453 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+

mysql> SELECT main.* FROM Attachments main  WHERE (main.Creator = '123')  ORDER BY main.id ASC;
Empty set (31 min 58.99 sec)

mysql> CREATE INDEX SHREDDER_ATTACHMENTS1 ON rt4.Attachments (Creator);
Query OK, 0 rows affected (13 min 32.98 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT main.* FROM Attachments main WHERE (main.Creator = '123')  ORDER BY main.id ASC;
+----+-------------+-------+------+-----------------------+-----------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys         | key                   | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------------+-----------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | main  | ref  | SHREDDER_ATTACHMENTS1 | SHREDDER_ATTACHMENTS1 | 4       | const |    1 | Using where |
+----+-------------+-------+------+-----------------------+-----------------------+---------+-------+------+-------------+

mysql> SELECT main.* FROM Attachments main  WHERE (main.Creator = '123')  ORDER BY main.id ASC;
Empty set (0.00 sec)