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.

The dark side of super_read_only

There is an interesting feature in MySQL 5.7+: the global variable super_read_only.  MySQL 5.6 and before only had the read_only global variable which is not preventing a user with SUPER privilege to write to the database.  With super_read_only, those users cannot write anymore.  But this feature comes with a surprise.

Arg: relay_log_space_limit is (still) not dynamic !

This will be a short rant: why is relay_log_space_limit still not dynamic ?

Another Post on the Percona Community Blog, Bug Activities on Replication Crash Safety, and Percona Live Europe

I published another article on the Percona Community Blog.  This time, it is about Semi-Synchronous Replication.  You can read the post here:

• Question about Semi-Synchronous Replication: the Answer with all the Details

I previously wrote about my motivation to publish on the Percona Community Blog.  Things have not changed: I still believe it is a great community initiative that I want to encourage.  You can learn more about the Percona Community Blog in their Hello World and in the Story So Far posts.

JFG Posted on the Percona Community Blog - A Nice Feature in MariaDB 10.3: no InnoDB Buffer Pool in Core Dumps

I just posted an article on the Percona Community Blog.  You can access it following this link:

• A Nice Feature in MariaDB 10.3: no InnoDB Buffer Pool in Core Dumps

I do not know if I will stop publishing posts on my personal blog or use both, I will see how things go.  In the rest of this post, I will share why I published there and how things went in the process.

A crashing bug in MySQL: the CREATE TABLE of death (more fun with InnoDB Persistent Statistics)

I ended one of my last posts - Fun with InnoDB Persistent Statistics - with a cryptic sentence: there is more to say about this but I will stop here for now.  What I did not share at the time is the existence of a crashing bug somehow related to what I found.  But let's start with some context.

How far can you go with MySQL or MariaDB ?

MySQL theoretical limits are known and they can be found in the manual, they include:

• MyISAM permits data and index files to grow up to 256 TiB by default, but this limit can be changed up to the maximum permissible size of 65,536 TiB (256^7 − 1 bytes).
• The maximum tablespace size depends on the InnoDB page size: 64 TiB for 16 KiB pages.
  (The maximum tablespace size is also the maximum size for a [InnoDB] table.)

But what can be done in practice ?  How far can you go with MySQL or with MariaDB ?