Fix slow write queries

I found this when checking slow write query logs of toolsdb:

# Time: 220506 13:58:07
# User@Host: s52421[s52421] @  [172.16.1.20]
# Thread_id: 45106096  Schema: s52421__commonsdelinquent_p  QC_hit: No
# Query_time: 306.319265  Lock_time: 46.930841  Rows_sent: 0  Rows_examined: 14449986
# Rows_affected: 0
use s52421__commonsdelinquent_p;
SET timestamp=1651845487;
update `event` set done=0,note='' where note like '%rate limit%' and done=2;

‌

This is going through 14M rows and locks the table for 46 seconds. It puts a heavy burden on the database, slows down replication making the upgrade almost impossibly hard. If we implement a write query killer, it would be killed right away.

It’s coming from this: https://bitbucket.org/magnusmanske/commons-delinquent/src/368b4c892edc7c9030b90d78a16d6248af7b6203/demon.php?at=master#lines-450

You can try to do a SELECT query on replica first and then run it if needed. You definitely should reduce the time interval it’s happening (to maybe once a day?).

Comments (1)