Thursday, November 15, 2018

MySQL Master Replication Crash Safety Part #2: lagging slaves

This is Part #2 of the MySQL Master Replication Crash Safety series.  In the previous post, we explored the consequence of reducing durability on masters with slaves using legacy file+position replication.  The consequences are data inconsistencies with a clear warning sign: the slaves stop replicating and report an error.  In this post, we extend our understanding of the impact of running a master with sync_binlog != 1 by considering lagging slaves that are using file+position replication.  This introduces a new inconsistency scenario without replication breakage, which is trickier to detect.  But let's start with some reminders.

Tuesday, November 13, 2018

How to install Percona Server 5.7 on Debian/Ubuntu without a root password prompt

In the last few months, I had to install Percona Server 5.7 (PS5.7) on Debian a few times.  I was regularly annoyed by apt-get -y install percona-server-server-5.7 prompting me for a password.  But that annoyance did not push me to investigate the subject in detail: it was always a quick manual fix and Googling did not lead to a straightforward solution.  However in the last days, I had to install PS5.7 on many servers, so it was worth finding how to do this.  I am writing this post in the hope that it will help some people...

Tuesday, October 30, 2018

On the consequences of sync_binlog != 1 (part #1)

A well-known performance booster in MySQL is to set sync_binlog to 0.  However, this configuration alone comes with serious consequences on consistency and on durability (the C and D of ACID); I explore those in this series.  In this post, I give some background on the sync_binlog parameter and I explain part of the problem with setting it to 0 (or to a value different from 1).  The other problems — including the behaviour with GTIDs (I am limiting the current scope to legacy file + position replication) — and some solutions are the subject of the upcoming posts.

Tuesday, September 11, 2018

Unforeseen use case of my GTID work: replicating from AWS Aurora to Google CloudSQL

A colleague brought an article to my attention.  I did not see it on Planet MySQL where I get most of the MySQL news (or it did not catch my eye there).  As it is interesting replication stuff, I think it is important to bring it to the attention of the MySQL Community, so I am writing this short post.

The surprising part for me is that it uses my 4-year-old work for online migration to GTID with MySQL 5.6.  This is a completely unforeseen use case of my work as I never thought that my hack would be useful after Oracle include an online migration path to GTID in MySQL 5.7 (Percona did something similar for MySQL 5.6).

Monday, August 27, 2018

Thursday, June 28, 2018

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:
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.

Thursday, April 19, 2018

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).

Saturday, January 27, 2018

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.

Thursday, January 11, 2018

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.

Monday, January 8, 2018

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.

Wednesday, January 3, 2018

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.

Tuesday, November 28, 2017

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.

Thursday, October 19, 2017

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.

Wednesday, August 16, 2017

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.

Monday, August 14, 2017

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.

Thursday, August 10, 2017

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.

Monday, August 7, 2017

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).

Wednesday, July 19, 2017

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.