MySQL 8.0.17 GTID Crash Safety Improvement

I have known for some times that there is an interesting improvement in MySQL 8.0.17 regarding GTID Crash Safety, but I have not had the time nor the need to look into it before.  When writing my last post (Understanding MySQL Replication "fatal error 1236": [...]), I saw something interesting related to this, and it is now time to cover this on my blog. From my point of view, this change is very interesting, but the documentation about it should be improved (and more), so there will be bug reports.  Let's explore all this.

Symlinks are Unsafe since MySQL 8.0.39 (and maybe even before)

You read this right, symbolic links (symlinks) are unsafe in MySQL since at least 8.0.39.  As always, it is a little more complicated than that, but if you are using symbolic links and in certain conditions, you risk a crash.  I think it is important to raise awareness on this, hence this post.

Row Deletion Jobs Done Right

I am continuing my blog post series on using indexes — or tables — as queues.  In this post, I cover Row Deletion Jobs (I do not call these purge jobs, to avoid confusion with the InnoDB Purge).  Such jobs are tempting to implement using an index, but this might be a wrong / suboptimal way.  I write about the right / better / cheaper way below, with cheaper meaning potentially significant savings on a Cloud bill !

Mind the InnoDB Purge on Queue / Row Deletion Job (else slow queries)

I am starting a blog post series on using indexes — or tables — as queues.  I had this series in the back of my mind for some time.  This started a few years back when I worked on optimizing a row deletion job (I do not call this a purge job, to avoid confusion with the InnoDB Purge).  Such jobs can be generalized to using indexes (or tables) as queues (this is fairly cryptic, I come back to this).  In this post, I explain why queries, which are expected to be fast, might become slow, and as the title of this post implies, it is related to the InnoDB Purge.

More than Flushing (also Caching) for innodb_flush_method, and Missing Release Candidate

Something changed in MySQL 8.4 related to caching, and it is easy to miss, so it deserves a post.  And a subject adjacent to this is the missing Release Candidate for MySQL 8.4 LTS, with my hope that the next LTS will have a Release Candidate, so I also cover this topic below.

Undo Log Truncation Bug in 8.0 leads to Data Corruption

I am upset about this one : I have a hard time not seeing this as negligence, and it starts to become a pattern...  So please forgive me if this post is not my most diplomatic, because I really think someone deserves a kick in the butt !  But what is all this about...

There is a MySQL bug, which can lead to data corruption, opened for 8.0 in September 2023, fixed in MySQL 8.4.0 (released in February 2024), AND STILL NOT FIXED IN MySQL 8.0.44 !  Luckily for some, it is fixed in Percona Server 8.0.39 (released in October 2024).  As usual, I start this post with some context before diving in the nitty-gritty details.

Interesting Troubleshooting of a MySQL Crash : filling then freeing the disk

I recently troubleshoot an interesting MySQL crash, and I think it is worth sharing (with the related bugs).  MySQL crashed when the disk was full, you can see the free disk graph below.  The Y-axis is in the tens of GiB scale and the X-axis is in the hour scale.  Can you guess what happened ?

Performance Regression in MySQL 8.0, Fixed in 8.4, Easy Workaround (innodb_doublewrite_pages)

While doing benchmarks on 5.7 and 8.0, I came across a performance regression in MySQL 8.0 over 5.7 and opened a bug (Bug #111353 : 3x Performance Regression from 5.7 to 8.0 on ALTER TABLE FORCE).  There has been recent activity on this bug, showing an easy workaround.  This, even if it is known since 16 July 2024, has not been talked about much, so this deserves a blog post.

The Light MySQL Startup Optimization on EBS Volumes

In the last weeks / months, I have been working on understanding / improving MySQL startup with many tables.  I already wrote four posts on the subject, they are listed below.  In this post, I use the system analysis of the previous post to revisit the light optimization on EBS volumes.  With this analysis, I am able to determine why the previous tests did not show improvements, and I am able to provide an example of a faster startup.

Understanding InnoDB Tablespace Duplicate Check (MySQL Startup with Many Tables)

This post is a little long, please bear with me as after the intro, you can skip to the essential.

In the last weeks / months, I have been working on understanding / improving MySQL startup with many tables.  I already wrote three posts on the subject, they are listed below.  In this post, I focus on describing the startup phase InnoDB Tablespace Duplicate Check (Duplicate Check for short).  This allows to better understand the optimization I already submitted, and find another optimization that could be leveraged.

Long and Silent / Stressful MySQL Startup with Many Tables

In the last weeks / months, I have been working on understanding / improving MySQL startup with many tables.  I already wrote two posts on the subject, the links are below.  So far, I did not share what brought my attention to this, and it is the subject of this post.  Also, and because it is related, I come back to the optimization / contribution I already made on the subject, looking at it with new information from this post.

Impressed by Disabling InnoDB Redo Logging for Creating Tables as Fast as Possible

In the last weeks / months, I have been working on understanding / improving MySQL startup with many tables.  I already wrote a post about my work (Faster MySQL Startup with Many Tables).  In that post, I link to a bug report (Bug #115988 : Too Much Disk Read on Startup, penalizing deployments with many tables).  In that bug report, I write, without much details, that the quickest way to create tables in 8.0+ is by reducing durability and disabling redo logging.  We will explore this statement in this post.  The TLDR is that I was very impressed by the speedup introduced by disabling Redo Logging, and I think there might be other situations where this is useful.

Faster MySQL Startup with Many Tables (1M+)

I have been scratching my head about MySQL startup for some time.  There is much to say about this, and many other posts will probably follow.  For now, it is enough to know that with many tables (millions) the startup of MySQL 8.0+ (including 8.0, 8.4 and 9.0) is suboptimal (to say the least).  With very little changes, I was able to speed it up, from 2:39 to 1:09 (1 minute and 9 seconds).  This result is obtained with 1 million tables on a m6id.xlarge AWS instance (4 vcpu and local SSD).  It does not translate directly to EBS volumes, even though there are still things I think can be done there.  I describe all the details of my optimizations in the rest of this post.

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.

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.

Here is the CREATE TABLE of death

In a previous post, I talked about the existence of a CREATE TABLE that is crashing MySQL up to versions 5.5.58, 5.6.38 and 5.7.20, and MariaDB up to version 5.5.57, 10.0.32, 10.1.26 and 10.2.7.  I hope you upgraded (or can mitigate this problem in another way) as I am now publishing the CREATE TABLE of death.

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.

The danger of no Primary Key when replicating in RBR (and a partial protection with MariaDB 10.1)

TL;DR: unless you know what you are doing, you should always have a primary key on your tables when replicating in RBR (and maybe even all the time).

TL;DR2: MariaDB 10.1 has an interesting way to protect against missing a primary key (innodb_force_primary_key) but it could be improved.

A few weeks ago, I was called off hours because replication delay on all the slaves from a replication chain was high and growing.  It was not the first time this happened on that chain, so I thought right away that this was probably an UPDATE or DELETE of many rows on a table without a primary key.  Let's see what is the problem with this and to understand that, we have to talk about binary log formats.

More Details about InnoDB Compression Levels (innodb_compression_level)

In one of my previous posts, I shared InnoDB table compression statistics for a read-only dataset using the default value of innodb_compression_level (6).  In it, I claimed, without giving much detail, that using the maximum value for the compression level (9) would not make a big difference.  In this post, I will share more details about this claim.

TL;DR: tuning innodb_compression_level is not very useful for my dataset.