Wednesday, November 30, 2022

Tail Latencies in Percona Server because of InnoDB Stalls on Empty Free List

If, in Percona Server, you are observing tail latencies on queries that should be fast, this might be a side effect of Percona's improved InnoDB Empty Free List Algorithm.  When using this algorithm (the default in 5.6 and 5.7 and optional configuration in 8.0), a query needing a free page while none are available waits until the LRU Manager Thread refills the free list.  Because this thread is waking-up only at regular intervals, the query is blocked (which explains the latency) until the LRU Manager Thread is done sleeping and completes its work.  These latencies / stalls might last up to one second in the worse case.  All the details are in this post, starting with InnoDB Free Pages.

Tuesday, November 15, 2022

Bad Optimizer Plan on Queries Combining WHERE, ORDER BY and LIMIT

Sometimes, the MySQL Optimizer chooses a wrong plan, and a query that should execute in less than 0.1 second ends-up running for 12 minutes !  This is not a new problem: bugs about this can be traced back to 2014, and a blog post on the subject dates of 2015.  But even if this is old news, because this problem recently came to my attention, it is a problem worth writing on.