Recap on the problem:
- A query takes a long time to complete.
- During this time it makes for a lot of I/O.
- Query’s I/O overloads the db, making for other queries run slow.
I introduce the notion of self-throttling queries: queries that go to sleep, by themselves, throughout the runtime. The sleep period means the query does not perform I/O at that time, which then means other queries can have their chance to execute.
I present two approaches:
- The naive approach: for every 1,000 rows, the query sleep for 1 second
- The factor approach: for every 1,000 rows, the query sleeps for the amount of time it took to iterate those 1,000 rows (effectively doubling the total runtime of the query). Continue reading » “Self throttling MySQL queries”