Author - Jay Janssen

Quick start MySQL testing using Docker (on a Mac!)

In this post, we’ll discuss how you can quick start MySQL testing using Docker, specifically in a Mac environment.
Like a lot of people, I’m hearing a lot about Docker and it’s got me curious. The Docker ecosystem seems to be moving quickly, however, and simple “getting started” or “how-to” type articles that are easy […]

Read more

Webinar: Writing Application Code for MySQL High Availability Followup Questions

Thanks to all who attended my webinar last week on Writing Application Code for MySQL High Availability.  This blog is for me to address the extra questions I didn’t have time to answer on the stream.
What do you think about using Galera Cluster but writing to a single Node with LVS ?
Whatever HA strategy you […]

Read more

High-load clusters and desynchronized nodes on Percona XtraDB Cluster

There can be a lot of confusion and lack of planning in Percona XtraDB Clusters in regards to nodes becoming desynchronized for various reasons.  This can happen a few ways:

An IST or SST joining node catching up after a state transfer (Joined/Joining state)
Using wsrep_desync for something like a backup
Executing a rolling-schema-upgrade using using wsrep_OSU_method=RSU

When […]

Read more

Optimizing PXC Xtrabackup State Snapshot Transfer

State Snapshot Transfer (SST) at a glance
PXC uses a protocol called State Snapshot Transfer to provision a node joining an existing cluster with all the data it needs to synchronize.  This is analogous to cloning a slave in asynchronous replication:  you take a full backup of one node and copy it to the new […]

Read more

PXC – Incremental State transfers in detail

IST Basics
State transfers in Galera remain a mystery to most people.  Incremental State transfers (as opposed to full State Snapshot transfers) are used under the following conditions:

The Joiner node reports Galera a valid Galera GTID to the cluster
The Donor node selected contains all the transactions the Joiner needs to catch up to the rest […]

Read more

Bypassing SST in Percona XtraDB Cluster with binary logs

In my previous post, I used incremental backups in Percona XtraBackup as a method for rebuilding a Percona XtraDB Cluster (PXC) node without triggering an actual SST. Practically this reproduces the SST steps, but it can be handy if you already had backups available to use.
In this post, I want to present another methodology […]

Read more

Bypassing SST in Percona XtraDB Cluster with incremental backups

Beware the SST
In Percona XtraDB Cluster (PXC) I often run across users who are fearful of SSTs on their clusters. I’ve always maintained that if you can’t cope with a SST, PXC may not be right for you, but that doesn’t change the fact that SSTs with multiple Terabytes of data can be quite […]

Read more

TOI wsrep_RSU_method in PXC 5.6.24 and up

I noticed that in the latest release of Percona XtraDB Cluster (PXC), the behavior of wsrep_OSU_method changed somewhat.  Prior to this release, the variable was GLOBAL only, meaning to use it you would:

Shell

mysql> set GLOBAL wsrep_OSU_method=’RSU’;
mysql> ALTER TABLE …
mysql> set GLOBAL wsrep_OSU_method=’TOI’;

123

mysql> set GLOBAL wsrep_OSU_method=’RSU’;mysql> ALTER TABLE …mysql> set GLOBAL wsrep_OSU_method=’TOI’;

This had the (possibly […]

Read more

Is 80% of RAM how you should tune your innodb_buffer_pool_size?

It seems these days if anyone knows anything about tuning InnoDB, it’s that you MUST tune your innodb_buffer_pool_size to 80% of your physical memory. This is such prolific tuning advice, it seems engrained in many a DBA’s mind.  The MySQL manual to this day refers to this rule, so who can blame the DBA? […]

Read more

Testing backup locks during Xtrabackup SST on Percona XtraDB Cluster

Background on Backup Locks
I was very excited to see Backup locks support in release notes for the latest Percona XtraDB Cluster 5.6.21 release. For those who are not aware, backup locks offer an alternative to FLUSH TABLES WITH READ LOCK (FTWRL) in Xtrabackup. While Xtrabackup can hot-copy Innodb, everything else in MySQL must be locked (usually […]

Read more