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.

Better InnoDB Crash Recovery in MariaDB 10.1

Recently, I had to go through crash recovery of a large MariaDB 10.1.21 instance.  After starting MariaDB, I started tailing the error logs expecting to wait many minutes while InnoDB was scanning ibd files.  I was surprised (and actually delighted) with this:

A Metric for Tuning Parallel Replication in MySQL 5.7

MySQL 5.7 introduced the LOGICAL_CLOCK type of multi-threaded slave (MTS).  When using this type of parallel replication (and when slave_parallel_workers is greater than zero), slaves use information from the binary logs (written by the master) to run transactions in parallel.  However, enabling parallel replication on slaves might not be enough to get a higher replication throughput (VividCortex blogged about such a situation recently in Solving MySQL Replication Lag with LOGICAL_CLOCK and Calibrated Delay).  To get a faster slave with parallel replication, some tuning is needed on the master.

FOSDEM talks

I will be heading to Brussels on Friday for FOSDEM.

How upgrading MariaDB Server failed because 50M warnings were ignored

This post is part of the series "please do not ignore warnings in MySQL/MariaDB".  The previous post of the series can be found here.

In this post, I will present why ignoring warnings made me lose time in upgrading MariaDB Server.  I think this war story is entertaining to read and it is also worth presenting to people claiming that ignoring warnings is no big deal.

Why I wrote "please do not ignore warnings" and "to always investigate/fix warnings" (in MySQL/MariaDB)

In a last post, I wrote the two following sentences:

• please do not ignore warnings
• always investigate/fix warnings

I realized that without context, this might be hard to understand.  In this post, I want to give more background about these two sentences.

Oracle MySQL and the funny replication breakage of Friday, January 13

In my previous post, I talked about a funny replication breakage that I experienced with MariaDB.  So what about different versions of MySQL...

Funny replication breakage of Friday, January 13

A funny replication breakage kept me at the office longer than expected today (Friday 13 is not kind with me).

So question of the day: can you guess what the below UPDATE statement does (or what is wrong with it) ?

Last Details about the Percona Live Amsterdam Community Dinner 2016

Here are the last details about the Percona Live 2016 Community Dinner hosted at Booking.com:

• you can still register
• the menu is below
• the program of the lightning talks is on Giuseppe blog

Booking.com will also do a short talk.  The subject is a surprise !

Percona Live Amsterdam and MariaDB Developer Meeting 2016: Tip to Stay Dry

Or should I say "to avoid getting soaked"...

The Amsterdam weather forecasts for next week is out and even if it looks good for the Percona Live MySQL and (No)SQL Conference (Monday to Wednesday) and for the MariaDB Developer Meeting (Thursday to Saturday), it could still change (or you might suffer the rain in the week-end):

Parallel Replication: Post on Booking.com Blog, Talk at Percona Live Amsterdam 2016, and more

I have a new blog post on blog.booking.com describing MariaDB 10.1 Optimistic Parallel Replication (with benchmark results):

• Evaluating MySQL Parallel Replication Part 4: More Benchmarks in Production
  http://blog.booking.com/evaluating_mysql_parallel_replication_4-more_benchmarks_in_production.html

Booking.com talks at Oracle Open World 2016

If you are in San Francisco for Oracle Open World, here is the list of talks by Booking.com:

• MySQL’s State of the Dolphin and Customer Experiences:
  Experience from Nicolai Plum, Booking.com
• MySQL System Design and Operations at Booking.com
• MySQL X Protocol: Talking to MySQL Directly over the Wire
• MySQL Architecture Cookbook

I am not there myself, but feel free to go meet my colleagues and ask them questions you have about MySQL and Booking.com.

And hopefully see you in 2 weeks at Percona Live Amsterdam.

Please register to the Percona Live Amsterdam Community Dinner 2016

Percona Live Amsterdam is in 3 weeks, and on the evening of the second day of the conference (Tuesday October 4th 2016), there is the traditional Community Dinner.

As last year, Booking.com is hosting the event and as last year, canal boats will bring attendees from the conference venue to Booking.com headquarters.

This event involves some planning: Percona needs to arrange for canal boats and Booking.com needs to order food and drinks and plan for catering staff.  In both cases, too much or not enough is bad for obvious reasons, and this is why we ask you to register.

The normal sales end soon.  After that, commitments need to be made and we will have less flexibility for accommodating more people.

So please help us make the event a success by registering to the Percona Live Community Dinner.

And as a reminder, you can see a picture from last year below, more pictures from the event and boat trip on Percona facebook album.

A discussion about sync-master-info and other replication parameters

Some time ago, feedback was requested on new replication default after MySQL 5.7.  Some of the suggested default are:

• relay-log-info-repository = TABLE (previous default FILE)
• relay-log-recovery = ON (previous default OFF)
• master-info-repository = TABLE (previous default FILE)
• sync-master-info = 1,000 (previous default 10,000)
• sync-relay-log = 1,000 (previous default 10,000)

I agree on the suggestions for relay-log-info-repository and relay-log-recovery: they are needed for crash safe replication, and having crash safe replication enabled by default is a good thing.  I have doubts about master-info-repository: I do not see what benefits are introduced by this change.  I have much bigger doubts about sync-master-info and sync-relay-log: those changes bring an illusion of safety, which is bad.  Let's dive in more details about those parameters.

Understanding Bulk Index Creation in InnoDB (and innodb_sort_buffer_size)

In a previous post, I presented an Unexpected Memory Consumption for Bulk Index Creation in InnoDB.  This was triggered by an increased innodb_sort_buffer_size and as stated in another post: "the sorting algorithm does not scale well with large sort buffers".  In this post, I will present why it does not scale well and I will suggest solutions.

Let's meet at Percona Live Amsterdam

I am very happy that my talk, MySQL Parallel Replication: inventory, use-cases and limitations, is included in the Sneak Peek of Percona Live Amsterdam.  As a member of the Conference Committee, I knew this was being discussed, but I refrained from commenting on discussion about my talk and the submissions of my colleagues from Booking.com.

MySQL Parallel Replication and more Booking.com talks at Percona Live (April 2016)

In a few days, I will be flying to San Francisco and then making my way to Santa Clara to attend the Percona Live Conference.  On the last day of the conference (Thursday), I will speak about MySQL Parallel Replication.  I hope to see you there and I will he happy to answer questions you might have (on this subject and others):

• Thursday at 12:50 pm: MySQL Parallel Replication: inventory, use-case and limitations

Replication crash safety with MTS in MySQL 5.6 and 5.7: reality or illusion?

Reminder: MTS = Multi-Threaded Slave.

Update 2017-04-17: since the publication of this post, many things happened:

• the procedure for fixing a crashed slave has been automated (Bug#77496)
• Bug#80103 as been closed at the same time as Bug#77496
• but I still think there are unfixed things, see Bug#81840

Update 2018-08-20: it was brought to my attention that MySQL 5.6 with GTID enabled and with sync_binlog != 1 is not replication crash safe, even with single threaded replication.  This is reported in Bug#70659 - Make crash safe slave work with gtid + less durable settings.  Thanks Valeriy for mentioning, in your last blog post, the bug from Sveta Smirnova (Bug#90997) which pointed me to Yoshinori Matsunobu's bug.  To my knowledge, MySQL 5.7 and 8.0 are also affected by this bug, even if this should not be the case.  I will soon open a new bug report about this and will put the bug number below.

End of updates.

I will be talking about parallel replication at FOSDEM in Brussel on January 30th and at Percona Live Santa Clara in April (link to the talk descriptions here and here).  Come to one (or both) of those talks to learn more about this subject.

JFG proposed sessions for Percona Live Santa Clara (and Community voting)

This year, Percona introduced Community Voting for Percona Live submission.  This is what you can read on the conference website:

In an effort to involve the larger community in the selection of speaking sessions for the 2016 Percona Live Data Performance Conference, we’ve implemented a community voting process. After a speaker submits a proposal we encourage sharing to the community and social networks for a vote. The more highly ranked proposals will continue onto the next phase of the voting process with the conference committee.

The annoyance of the day, brought to you live from the MySQL Ecosystem

And by MySQL Ecosystem here, I do not only include Oracle but also MariaDB.

Binlog Servers for Simplifying Point in Time Recovery

A common way to implement point in time recovery capability is:

1. to regularly do a full backup of a database,
2. and to save the binary logs of that database (or from its master if doing backups on a slave).

When point in time recovery is required you need to:

1. restore a backup,
2. and apply the binary logs up to the point of recovery.

(Step # 2 and # b above are the ones that will be simplified by using Binlog Servers.)

Do not run those commands with MariaDB GTIDs - part # 2

Update 2016-01-30: restarting the IO_THREAD might be considered useful in some situations (avoiding MDEV-9138).  Look for "in contrast, if the IO thread was also stopped first" in MDEV-6589 for more information.

In a previous post, I listed some sequences of commands that you should not run on a MariaDB slave that is lagging and which is using the GTID protocol.  Those are the following (do not run them, it's a trap):

• "STOP SLAVE; START SLAVE UNTIL ...;",
• or "STOP SLAVE; START SLAVE;" (to remove an UNTIL condition as an example),
• or "STOP SLAVE; SET GLOBAL slave_parallel_threads=...; START SLAVE;",
• and maybe others.

Do not run those commands with MariaDB GTIDs - part # 1

In the spirit of sharing war stories and avoiding others to do the same mistakes as I did, here are some sequences of commands that you should avoid to run on a MariaDB slave that is lagging and which is using the GTID protocol.  Remember, do not run those because...

Percona Live Amsterdam 2015: Tip to Stay Dry

Or should I say to: "not to get soaked"...

The Amsterdam weather forecasts for next week is out  and it does not look so good during Percona Live MySQL and NoSQL Conference:

Abstracting Binlog Servers and MySQL Master Promotion without Reconfiguring all Slaves

http://blog.booking.com/abstracting_binlog_servers_and_mysql_master_promotion_wo_reconfiguring_slaves.html

Follow the link above to read my latest article on the Booking.com Developer Blog.  It is about Binlog Servers and how to promote a slave as the new master without reconfiguring all slaves.

This is also a good opportunity to remind you of my next talks:

• I’ll be giving a talk about Binlog Servers and another talk about Binary Logs in general at Percona Live Amsterdam. Feel free to grab me after the talk, catch me at Booking.com booth (# 205) or share a drink with me at the Community Dinner to exchange thoughts about Binlog Servers of other MySQL subjects. 
• I'll also be giving a talk about Binlog Servers at Oracle Open World in San Francisco at the end of October.
• Finally, I'll also be animating a  Birds-of-a-Feather (BoF) session at Oracle Open World: Riding the Binary Logs: Forthcoming Evolution in the Replication Stream.

Looking forward to seeing you in Amsterdam and/or in San Francisco.

Unexpected Memory Consumption for Bulk Index Creation in InnoDB (MySQL)

In my last Booking.com Hackathon, I worked on MyISAM vs InnoDB for data loading (LOAD DATA IN FILE) and bulk index creation.  My motivation was the following: knowing that some are still using MyISAM for this particular use-case, I wanted to verify/understand if/why InnoDB is slower than MyISAM.  I do not yet have complete results on this specific subject but I found some interesting things that are worth sharing.

MariaDB 10.0 Parallel Replication Benchmark Results (and PLAMS and OOW).

My latest post is online on the Booking.com blog: Evaluating MySQL Parallel Replication Part 3: Benchmarks in Production.  In this post, I present benchmark results on MariaDB 10.0 parallel replication on four Booking.com production workloads.

This post is also the opportunity to promote my two talks at Percona Live Europe, taking place in Amsterdam from September 21 to 23:

• Binlog Servers at Booking.com
• Riding the Binlog: an in Deep Dissection of the Replication Stream

I will also be at Oracle Open World in San Francisco (from October 25 to 29) giving a talk and animating a Birds-of-a-Feather on similar subjects:

• Binlog Servers at Booking.com (talk)
• Riding the Binary Logs: Forthcoming Evolution in the Replication Stream (BoF)

Looking forward to see you in Amsterdam and/or in San Francisco.

Self-Critic and Slides of my PLMCE Talks

The link to the slides of my talks can be found at the end of this post but first, let me share some thoughts about PLMCE.

Talking with people, I was surprised to be criticized of presenting only the good sides of my solution without giving credit to the good side of the alternative solutions.  More than surprised, I was also a little shocked as I want to be perceived as objective as possible.  Let me try to fix that:

MaxScale Binlog Server HOWTO: POC for Master Promotion without Touching any Slave

Note: DO NOT use this procedure in production, this is a proof of concept (POC).  MaxScale 1.1.0 does not yet fully support that procedure and things could go wrong in some situations (see at the end of the post for the details).

In my talk at PLMCE 2015, I presented an architecture to promote a slave as a new master without touching any other slave and I claimed that I tested it.  This HOWTO will show you how I did my test so you are able to reproduce my results.

MaxScale Binlog Server HOWTO: Operations (including Chaining)

In the Install and Configure HOWTO, we learned how to install and configure a MaxScale Binlog Server.  In this HOWTO, I will present the common operations that you might need to perform when using this software.  Those operations include:

• Purging Binary Logs,
• Chaining Binlog Servers,
• Saving Binary Log Files in the Non-Default Directory,
• Downloading Binary Logs other than First,
• Listing Connected Slaves,
• Disconnecting one or all Slaves,
• Differentiating a MaxScale from a MySQL Server,
• Getting More Information about Slaves (and more),
• Recovering After a Crash.

MaxScale Binlog Server HOWTO: Install and Configure

Updated 2015-04-25: add the link to the slides of my PLMCE talk and a link to a bug number.

MaxScale 1.1.0 is out and includes the new Binlog Server module.  This is the first post in s series of three.  The two others are about Operations and High Availability.  The links to the 2 other posts are at the end of this page.

In this post, I present how to install and configure MaxScale as a Binlog Server using the Binlog Router plugin.

Even Easier Master Promotion (and High Availability) for MySQL (no need to touch any slave)

Dealing with the failure of a MySQL master is not simple.  The most common solution is to promote a slave as the new master but in an environment where you have many slaves, the asynchronous implementation of replication gets in your way.  The problem is that each slave might be in a different state:

• some could be very close to the dead master,
• some could be missing the latest transactions,
• and some could be far behind (lagging, delayed slaves, or slaves in maintenance).