Hello all,
despite having the recommended indices on the database, deleting a user with rt-shredder is incredibly slow, it takes between 6:10 and 6:50 minutes per user. That’s a bit awkward, given the fact that shredding a ticket only takes a fraction of a second on my setup.
From looking at the database, the problem seems to be that the query “SELECT main.* FROM Attachments main WHERE (main.Creator = ‘XXXXX’) ORDER BY main.id ASC” spends an awful lot of time doing things.
Some debugging with an existing user ID:
Database changed
MariaDB [rt]> explain select * from Attachments where Creator = ‘183’;
±-----±------------±------------±-----±--------------±-----±--------±-----±--------±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±-----±------------±------------±-----±--------------±-----±--------±-----±--------±------------+
| 1 | SIMPLE | Attachments | ALL | NULL | NULL | NULL | NULL | 3086587 | Using where |
±-----±------------±------------±-----±--------------±-----±--------±-----±--------±------------+
1 row in set (0.001 sec)
In this case, no results are found but finding that out takes a LOT of time
MariaDB [rt]> select COUNT() from Attachments where Creator = ‘183’;
±---------+
| COUNT() |
±---------+
| 0 |
±---------+
1 row in set (2 min 46.374 sec)
I looked at the usual tips about adding indices and noticed that the Attachments table does not seem to have any indices set in the tips, but my instance of the table has the following indices:
MariaDB [rt]> show indexes from Attachments;
±------------±-----------±-------------±-------------±--------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
±------------±-----------±-------------±-------------±--------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| Attachments | 0 | PRIMARY | 1 | id | A | 3086587 | NULL | NULL | | BTREE | | |
| Attachments | 1 | Attachments2 | 1 | TransactionId | A | 3086587 | NULL | NULL | | BTREE | | |
| Attachments | 1 | Attachments3 | 1 | Parent | A | 771646 | NULL | NULL | | BTREE | | |
| Attachments | 1 | Attachments3 | 2 | TransactionId | A | 3086587 | NULL | NULL | | BTREE | | |
±------------±-----------±-------------±-------------±--------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
4 rows in set (0.001 sec)
Is there anything I can do to speed things up? I could put more RAM into the VM, or more CPUs, I just have to know what the limiting factor is.
I’m looking at upwards of 40K users to delete (we never shredded spammers etc.), so spending 6 minutes per user gives me a half-year worth of non-stop database shredding…