unindexed delete

Question

unindexed delete

Closed this issue 6 years ago · 6 comments

I was looking at the slow query log on the read replica and this query shows up as taking an average of 0.75 seconds and scanning the table:
DELETE FROM signinCodes WHERE uid = X'...'

There aren't an egregious number of rows in that table (compared to others), but perhaps it needs an index (either on the table, or if there's a way to use one of the indices that already exist).

For fun, I think adding an index for uid on the slave only would be interesting

Answer 1 · 2018-10-09T21:47:35.000Z

Also note: with mysql replication (non-parallel), this will block the queue while it executes, although, at this point, I don't see enough of these to explain the general lagginess.

Answer 2 · 2018-10-09T22:21:03.000Z

It looks like this query is only done when deleting an account, which means it thankfully shouldn't be too frequent. I don't see a good way to do this query using existing indexes, so adding one makes sense to me.

Answer 3 · 2018-10-10T02:05:40.000Z

I added the index on the slave only but, while those queries are now longer showing in the slow log, it wasn't the "fix" for the replication lag.

Answer 4 · 2018-10-10T02:08:33.000Z

Also, for current slow query log I don't see anything else that's bad (and that's at 10ms threshold). This may just be the effect of serial replication.

Answer 5 · 2018-10-10T03:53:52.000Z

Actually, the root cause may be due some long-running transactions in the replication stream, and I have some things to try to isolate what those transactions are, but not tonight.

Answer 6 · 2018-10-10T04:07:05.000Z

I'll put further comments in a different issue and leave this one as the original problem 'unindexed delete' to be addressed specifically in this issue.