Some bugs and spring pilgrimage to Percona Live Santa Clara 2018

I am now in an airport, waiting for one of the four flights that will bring me to Percona Live Santa Clara 2018.  This is a good time to write some details about my tutorial on parallel replication.  But before talking about Percona Live, I will share thoughts on MySQL/MariaDB bugs that caught my attention in the last weeks/months (Valeriy: you clearly have an influence on me).

Next week in Brussels: Parallel Replication at the MySQL Pre-FOSDEM Day

FOSDEM is next weekend and I am talking about Parallel Replication on Friday, February 2nd at the MySQL Pre-FOSDEM Day (there might be tickets left in case of cancellation, attendance is free of charge).  During this talk, I will show benchmark results of MySQL 8.0 parallel replication on Booking.com real production environments.  I thought I could share a few things before the talk so here it is.

More Write Set in MySQL: Group Replication Certification

This is the third post in the series on Write Set in MySQL.  In the first post, we explore how Write Set allows to get better parallel replication in MySQL 8.0.  In the second post, we saw how the MySQL 8.0 improvement is an extension of the work done in MySQL 5.7 to avoid replication delay/lag in Group Replication.  In this post, we will see how Write Set is used in Group Replication to detect conflicts in multi-writer mode during certification.  We will also see the impacts, on conflict detection, of the Write Set bug that I presented in the first post.

Write Set in MySQL 5.7: Group Replication

In my previous post, I write that Write Set is not only in MySQL 8.0 but also in MySQL 5.7 though a little hidden.  In this post, I describe Write Set in 5.7 and this will bring us in the inner-working of Group Replication.  I am also using this opportunity to explain and show why members of a group can replicate faster than a standard slave.  We will also see the impacts, on Group Replication, of the Write Set bug that I presented in my last post.

An update on Write Set (parallel replication) bug fix in MySQL 8.0

In my MySQL Parallel Replication session at Percona Live Santa Clara 2017, I talked about a bug in Write Set tracking for parallel replication (Bug#86078).  At the time, I did not fully understand what was going wrong but since then, we (Engineers at Oracle and me) understood what happened and the bug is supposed to be fixed in MySQL 8.0.4.  This journey thought me interesting MySQL behavior and bug reporting practices.  In this post, I am sharing both in addition to some insight on Write Set tracking for parallel replication.

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.

Why we still need MyISAM (for read-only tables)

TL;DR: we still need MyISAM and myisampack because it uses less space on disk (half of compressed InnoDB) !

In the previous post, I shared my experience with InnoDB table compression on a read-only dataset.  In it, I claimed, without giving much detail, that using MyISAM and myisampack would result is a more compact storage on disk.  In this post, I will share more details about this claim.

An Adventure in InnoDB Table Compression (for read-only tables)

In my last post about big MySQL deployments, I am quickly mentioning that InnoDB compression is allowing dividing disk usage by about 4.3 on a 200+ TiB dataset.  In this post, I will give more information about this specific use case of InnoDB table compression and I will share some statistics and learnings on this system and subject.  Note that I am not covering InnoDB page compression which is a new feature of MySQL 5.7 (also known as hole punching).

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 ?

InnoDB Basics - Compaction: when and when not

This is old news for MySQL/MariaDB expert but people that are starting using InnoDB do not always know that disk space is not automatically released when deleting data from a table.  To explain and demonstrate that, I will take two real-world examples: table1 and table2.

Fun with InnoDB Persistent Statistics

Something interesting happened to me in the last days, and it is worth sharing.  I was upgrading MariaDB (MySQL also impacted) to a new major version and mysql_upgrade showed something like this:

[...]
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1062 (23000) at line 586: Duplicate entry 'schema-table_name#P#partition_name_truncated' for key 'PRIMARY'
ERROR 1062 (23000) at line 590: Duplicate entry 'schema-table_name#P#partition_name_truncated' for key 'PRIMARY'
ERROR 1062 (23000) at line 593: Duplicate entry 'schema-table_name#P#partition_name_truncated' for key 'PRIMARY'
FATAL ERROR: Upgrade failed

Better Replication when running both InnoDB and MyRocks (or other Storage-Engines)

Kristian Nielsen is working on a new feature for MariaDB 10.3 and he published very interesting results.  This feature is MDEV-12179: Per-engine mysql.gtid_slave_pos tables.  He writes about replicating twice as fast in the worst case when using two storage engines (InnoDB and MariaRocks in his tests, but could also be InnoDB and TokuDB or TokuDB and MyRocks).  I will let you read all the details on his blog about Improving replication with multiple storage engines.

Why am I posting this here ?  Mostly because I want to share with you that:

• I am also involved in this project,
• I am working closely with Kristian on this feature,
• and that Booking.com is financing Kristian's time on this development.

If you are also interested in this, feel free to comment in the JIRA MDEV, to leave a comment below, or on Kristian's post.

My two parallel replication talks at Percona Live Santa Clara 2017

Yes, another post about my talks at Percona Live Santa Clara: I obviously still have things to share.  This time, I will focus on my parallel replication talks by giving a short preview.

My talks at Percona Live Santa Clara 2017

In a previous post, I listed all the Booking.com talks at Percona Live.  In this post, I will give more details about my talks.

As a reminder, the list of my talks is the following:

• Monitoring Booking.com without looking at MySQL (Thursday keynote)
• The two little bugs that almost brought down Booking.com (Tuesday Lightning Talk)
• MySQL/MariaDB Parallel Replication: inventory, use cases and limitations (Wednesday talk)
• MySQL Parallel Replication (LOGICAL_CLOCK): all the 5.7 (and some of the 8.0) details (Thursday talk)

I will quickly describe each of them below.

Booking.com talks at Percona Live Santa Clara 2017

In a week, me and some Booking.com colleagues will be in Santa Clara for Percona Live.

Booking.com is sponsoring the conference and we will be present at the Monday Evening Reception.  You do not need a tutorial pass to attend the dinner (even if it is on the tutorial day): any valid pass will do.  If you do not have your ticket yet, it is time to register (you can use the discount code “SeeMeSpeak” for a 10% discount on the registration fees).

Many thanks Oracle for implementing RESET MASTER TO

MySQL 8.0.1 is out and it includes an implementation of my feature request (Bug #77438).  This extension to RESET MASTER allows to simplify master promotion with Binlog Servers.  Let's see how it works:

# mysql -N <<< "SHOW MASTER STATUS"
binlog.027892   3006935
# mysql -N <<< "RESET MASTER TO 12345; DO sleep(rand()*10); SHOW MASTER STATUS"
binlog.012345   92773
# mysql -N <<< "RESET MASTER TO 12345678; DO sleep(rand()*10); SHOW MASTER STATUS"
binlog.12345678 24795
# mysql -N <<< "RESET MASTER TO 1234567890; DO sleep(rand()*10); SHOW MASTER STATUS"
binlog.1234567890       13987
# mysql -N <<< "RESET MASTER TO 12345678901; DO sleep(rand()*10); SHOW MASTER STATUS"
ERROR 3567 (HY000) at line 1: The requested value '12345678901' for the next
binary log index is out of range. Please use a value between '1' and '2147483647'.
# mysql -N <<< "RESET MASTER TO $RANDOM; DO sleep(rand()*10); SHOW MASTER STATUS"
binlog.013529   89880
# mysql -N <<< "RESET MASTER TO $RANDOM; DO sleep(rand()*10); SHOW MASTER STATUS"
binlog.000831   22961
# mysql -N <<< "RESET MASTER TO $RANDOM; DO sleep(rand()*10); SHOW MASTER STATUS"
binlog.023089   107764
# mysql -N <<< "RESET MASTER TO $RANDOM; DO sleep(rand()*10); SHOW MASTER STATUS"
binlog.003433   67903

Many thanks Oracle for implementing my feature request, and a special mention to Daniël van Eeden for providing a patch in the bug report.

Busy April 2017: MariaDB Dev Meeting (no-slave-left-behind, MyRocks, ...) and Percona Live

In a few days, I will start my yearly travel to North America which will bring me at Percona Live at the end of the month.  But I will first stop in New York to attend the MariaDB Developer Meeting.  Let's see what will happen there.