Re: shredding users
Christian Loos <[email protected]>
| Newsgroups | gmane.comp.bug-tracking.request-tracker.devel |
|---|---|
| Message-ID | <[email protected]> |
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 <[email protected]> >> 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 -- RT Training - Boston, September 9-10 http://bestpractical.com/training
rt-shredder-attachments-index.txt
(text/plain, 1.8 KB)
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)