Free Page Consumption by InnoDB Table Compression (Percona backoff Algo. part #2)

In my previous post about InnoDB Stalls on Empty Free List, I used a test environment that might look a little artificial : a table larger than the InnoDB Buffer Pool but fitting in the Linux Page Cache.  This configuration allows serving very quickly what MySQL thinks are IOs because these are hit in the filesystem cache.  In this post, I explain why this environment is not totally artificial and can be a good representation of another more complex environment.  I also explain how an Empty Free List backoff stall can occur when no IO is done.  You have probably already guessed what this post is covering from the title, so let's dive in.

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.

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.

Triggering Replication Lag for Testing a Script

I am currently working on a script to auto-enable parallel replication / multi-threaded replication (MTR) when there is replication lag.  For testing this script, I need to trigger replication lag that would disappear after enabling MTR.  I came-up with a simple solution for that, and I thought it could be useful to more people, so I am writing this blog post about it.  Read-on for the details.

Rows Examined not Trustworthy because of Index Condition Pushdown

When Index Condition Pushdown is used, you cannot trust rows examined as reported by the slow query log (nor in some Performance Schema tables) and you cannot trust the global status Innodb_rows_read (nor the InnoDB Metrics dml_reads).  These can lead to an incorrect query complexity analysis, to a mistaken estimation of the total load of a MySQL server, and to a lot of wasted time.  As we go through the details in this post, we will understand why a SELECT * is sometimes faster than only selecting the columns from an index (we would normally expect the opposite) and we will visit inner workings of the MySQL Storage Engine Architecture.  But first, let's start by exposing the problem.

Crashing MySQL with Malicious Intent and a lot of Determination

A year ago, I blogged about An Unprivileged User can crash your MySQL Server.  At the time, I explained how to protect yourself against this problem.  A few weeks ago, I revisited this vulnerability in a follow-up post in which I explained the fix, claimed that the MySQL 5.7 default configuration for Group Replication is still problematic, and explained a tuning to avoid the vulnerability.  In this last post in the series, I explain how to exploit this vulnerability to crash older version of MySQL (or untuned Group Replication in 5.7), but this needs a lot of determination.