This blog post will discuss the newest feature available in MySQL 8.0.30: dynamic redo log sizing. After the InnoDB buffer pool size, we can say that having a proper size for the redo logs is crucial for MySQL performance. There are numerous blog posts about how to calculate a good redo log size. One of our classic blog posts is this one from Baron: How to calculate a good InnoDB log file size (That blog post is from 2008 and is still a valid formula to use.)

Now, which problem is this feature trying to solve?

In MySQL, the buffer pool and redo log size are settings that do not change frequently. Usually, these settings are set during database installation; after that, they are forgotten until an issue arises. The problem is that they used to be static, which means that you had to restart MySQL so the changes …

In this blog, I will cover important aspects which you need to test when benchmarking replication setup. MySQL has great tools that could be used to test its performance. They include:

sysbench – https://github.com/akopytov/sysbench

BMK-kit – http://dimitrik.free.fr/blog/posts/mysql-perf-bmk-kit.html

mysqlslap – https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html

LinkBench – https://github.com/facebookarchive/linkbench

I will not describe how to use them here, as you can find instructions on the provided links or in the Percona blog by browsing tags …

MySQL 8.0 is now over four years old and Oracle released the latest quarterly offering today with MySQL 8.0.30, which comes with lots of bug fixes and some interesting additions.  This is my take on the release notes for those who do not have the time to wade through them, comments in italics are my comments and reflect the views of only me. 

This is an interesting release with a good many bug fixes and I urge those who need fixes to upgrade as soon as they can. For everyone else, the TL;DR is that unless you are on the Oracle Cloud Infrastructure or one of the fixed bugs is causing you problems, then upgrade at your leisure. 

Deprecation and removals

Setting the  replica_parallel_workers system variable to 0 is now deprecated and to use single threading set replica_parallel_workers=1 instead.  …

When we run a SELECT in a replica server and it returns a different result to the one we would have obtained had we queried the source server instead, due to changes in the dataset that have not yet been replicated or synchronized to the replica, we get what is known as a stale read. My colleague Marcelo has blogged about Stale Reads and How to Fix Them before.

The need to avoid stale reads is a constraint that leads people into migrating to Percona XtraDB Cluster (PXC) / Galera Cluster or, more recently, Group Replication. I am focusing on PXC and Galera in this short blog post, which, similar to Group Replication, implements not exactly a synchronous replication model but what is commonly referred to as a …

In this blog, I explain how to recover from a replication failure caused by a corrupted relay log file.

MySQL replica stores data received from its source binary log in the relay log file. This file could be corrupted due to various reasons, mostly hardware failures. If this happens, replication will stop working, and the error log on the replica will have entries similar to:

2022-05-12T12:32:07.282374Z 2 [ERROR] Error in Log_event::read_log_event(): 'Event too small', data_len: 0, event_type: 0
2022-05-12T12:32:07.282386Z 2 [ERROR] Error reading relay log event for channel '': slave SQL thread aborted because of I/O
...
2022-05-12T12:32:07.282396Z 2 [ERROR] Slave SQL for channel '': Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running …

Oracle’s MySQL 8.0.29 release extended the support for ALTER TABLE … ALGORITHM=INSTANT to 1) allow users to instantly add columns in any position of the table, and 2) instantly drop columns. As part of this work, the InnoDB redo log format has changed for all DML operations on the server. This new redo log format introduced a design flaw that can cause data corruption for tables with INSTANT ADD/DROP COLUMNS.

The corruption happens when InnoDB crash recovery takes place. InnoDB applies redo logs at startup. Percona XtraBackup copies the redo log during backup and applies it as part of the –prepare step to bring the backup to a consistent state.

Percona fixed the corruption issue and several other issues with the INSTANT ADD/DROP column …

In this blog post, I will show how to install the MySQL-compatible Percona XtraDB Cluster (PXC) Operator on Minikube as well as perform some basic actions.   I am by no means a Kubernetes expert and this blog post is the result of my explorations preparing for a local MySQL Meetup, so if you have some comments or suggestions on how to do things better, please comment away!

For my experiments, I used Minikube version 1.26 with the docker driver in the most basic installation on Ubuntu 22.04 LTS, though it should work with other combinations, too. You also can find the official “Running Percona XtraDB Cluster on Minikube” documentation …

As a principal architect at Percona, one of my duties is to tune MySQL database servers for our customers. The tuning effort looks at every aspect of the database service like the operating system, the MySQL configuration, the schema, the queries, etc. We have well-defined processes to tune the operating system and the MySQL configuration. However, tuning the schema and the queries using it can be anywhere from trivial to extremely challenging.

The challenge with the schema and the queries is mostly with the indexes. The most common types of indexes are based on b-trees or hash lists. InnoDB doesn’t support hash indexes, a bummer for equality conditions. B-tree indexes are more general-purpose, they are decent for equality conditions and very good for range conditions. They are however quite heavy and very dependent on the defined order of columns. They are also poor for double range conditions.

Double range conditions can be …

Imagine you need to design an online system for storing documents on a per-user basis where nobody, including database administrators, would be able to change the content of those documents without being noticed by document owners.

In Percona Server for MySQL 8.0.28-20, we added a new component called Encryption UDFs – an open-source alternative to MySQL Enterprise Encryption that allows users to access a number of low-level OpenSSL encryption primitives directly from MySQL. This includes calculating digests (with a great variety of hash functions), asymmetric key generation (RSA, …

As you might know, mysqldump is single-threaded and STDOUT is its default output. As MyDumper is multithreaded, it has to write on different files. Since version 0.11.3 was released in Nov 2021, we have the possibility to stream our backup in MyDumper. We thought for several months until we decided what was the simplest way to implement it and we also had to add support for compression. So, after fixing several bugs, and we now consider it is stable enough, we can explain how it works.

How Can You Stream if MyDumper is Multithreaded?

Receiving a stream is not a problem for myloader, it receives a file at a time and sends it to a thread to process it. However, each worker thread in mydumper is connected to the database, and as soon as it reads data, it should be sent to the stream, which might cause collisions with other worker threads that are reading data from the database. In order to avoid this issue, we ended up with the …