Monday, October 7, 2019

The dark side of super_read_only

There is an interesting feature in MySQL 5.7+: the global variable super_read_only.  MySQL 5.6 and before only had the read_only global variable which is not preventing a user with SUPER privilege to write to the database.  With super_read_only, those users cannot write anymore.  But this feature comes with a surprise.

Tuesday, July 16, 2019

MySQL Master Replication Crash Safety Part #5a: making things faster without reducing durability - using better hardware

This is a follow-up post in the MySQL Master Replication Crash Safety series.  In the previous posts, we explored the consequences of reducing durability on masters (different data inconsistencies after an OS crash depending on replication type) and the performance boost associated with this configuration (benchmark results done on Google Cloud Platform / GCP).  The consequences are summarised in the introduction of Part #4, and the tests are the subject of this last post.  Also in this last post, I mentioned that my results for high durability are limited by the sync latencies of GCP persistent disks.  As I found a system with better latencies, I am able to present new results.  And this system is a vm in Amazon Web Services (AWS) with local SSD.

MySQL Master Replication Crash Safety Part #5: faster without reducing durability (under the hood)

This post is a sister post to MySQL Master Replication Crash Safety Part #5: making things faster without reducing durability.  There is no introduction or conclusion to this post, only landing sections: reading this post without its context is not not recommended. You should start with the main post and come back here for more details.

Tuesday, July 9, 2019

MySQL Master Replication Crash Safety Part #4: benchmarks of high and low durability

This is a follow-up post in the MySQL Master Replication Crash Safety series.  In the three previous posts, we explored the consequence of reducing durability on masters (including setting sync_binlog to a value different from 1).  But so far, I only quickly presented why a DBA would run MySQL with such configuration.  In this post, I present actual benchmark results.  I also present a fundamental difference between on-premise servers and cloud virtual machines as my tests are done in Google Cloud Platform (GCP).  But before going further, let's summarise the previous posts.

MySQL Master Replication Crash Safety part #4: benchmarks (under the hood)

This post is a sister post to MySQL Master Replication Crash Safety Part #4: benchmarks of high and low durability.  There are no introduction or conclusion to this post, only landing sections: reading this post without its context is not recommended. You should start with the main post and come back here for more details.

Tuesday, June 25, 2019

Getting past 2400 threads with sysbench and dbdeployer for benchmarking MySQL 5.7.26

I am currently running tests with sysbench and dbdeployer on a Google Cloud Platform Debian 9 instance.  I ran into an interesting limit and lifting it was not straightforward.  I hope that by sharing this, you can avoid losing too much time.

Wednesday, May 8, 2019

Not enforcing SSL on CloudSQL, really !

When creating a MySQL CloudSQL instance, SSL connections are not enforced by default and you get below in the Connections tab of the Google Cloud Platform console.  Is this a problem ?  Some people might think no, but I do not agree with them.  And if I am writing this post, you can probably guess that there is a lot to say about this subject.  Read on for the details.

Wednesday, May 1, 2019

Tuesday, February 26, 2019

MySQL Master High Availability and Failover: more thoughts

Some months ago, Shlomi Noach published a series about Service Discovery.  In his posts, Shlomi describes many ways for an application to find the master.  He also gives detail on how these solutions cope with failover to a slave, including their integration with Orchestrator.

This is a great series, and I recommend its reading for everybody implementing master failover, with or without Orchestrator, and even if you are not fully automating the process yet.  Taking a step back, I realized that service discovery is only one of the five parts of a full MySQL Master Failover Strategy, and this post is about these five parts.  In some follow-up posts, I might analyze some deployments using the framework presented in this post.

Tuesday, February 12, 2019

MySQL Master Replication Crash Safety Part #3: GTID

This is a follow-up post in the MySQL Master Replication Crash Safety series.  In the two previous posts, we explored the consequence of reducing durability on masters (including setting sync_binlog to a value different than 1) when slaves are using legacy file+position replication.  In this post, we cover GTID replication.  This introduces a new inconsistency scenario with a potential replication breakage that depends on transaction execution on the master and timing on the slave.  Before discussing this violation of ACID, we start with some reminders about the last posts and with some explanations about GTIDs.

Tuesday, January 8, 2019

Care when changing MASTER_DELAY with CHANGE MASTER TO (delayed replication)

A few days ago, I stepped into a trap !  This made me lose time for fixing things (and even more for writing this post...).  In the hope that you will avoid my mistake, I am sharing this war story.  I also obviously opened a bug, more about this below.

TL&DR: be careful when using CHANGE MASTER TO MASTER_DELAY = N: it might wipe your relay logs !