Fix slow write queries

Create issue
Issue #90 new
Ladsgroup created an issue

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

# Time: 220506 13:58:07
# User@Host: s52421[s52421] @  []
# 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:

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)

  1. Ladsgroup reporter

    I just added index on done (MariaDB [s52421__commonsdelinquent_p]> CREATE INDEX done ON event (done);) to make it faster.

  2. Log in to comment