The latest from the MySQL community

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

SST, by current definition, is a full backup of a Donor to Joiner.  The most popular method is Percona XtraBackup, so we’re talking about a donor node that must:

1. Run a full XtraBackup that reads its entire datadir
2. Keep up with Galera replication to it as much as possible (though laggy donors don’t send flow control)
3. Possibly still be serving application traffic if you don’t remove Donors from rotation.
…

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:

• I am not a GTID and log-slaves detractor, I am a simplicity lover.
• I actually like GTIDs and I have some use-cases for them; one of  them is described in the MariaDB Knowledge Base (search for last_gtid in that page).
• But I think that using GTIDs in the slave protocol is complex and that a …

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

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

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

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

http://blog.booking.com/evaluating_mysql_parallel_replication_2-slave_group_commit.html

Follow the link above to read my latest article on the Booking.com developer blog.  It is about MySQL Parallel Replication and a very nice side effect of the MariaDB implementation: Slave Group Commit.

This is also a good opportunity to remind you that I will speak at Percona Live Santa Clara 2015 about 

As a MySQL DBA/consultant, it is part of my job to decode the MySQL binary logs – and there are a number of reasons for doing that. In this post, I’ll explain how you can get the important information about your write workload using MySQL row-based binary logs and a simple awk script.

First, it is important to understand that row-based binary logs contain the actual changes done by a query. For example, if I run a delete query against a table, the binary log will contain the rows that were deleted. MySQL provides the mysqlbinlog utility to decode the events stored in MySQL binary logs. You can read more about mysqlbinlog in detail in the reference manual here.

The following example illustrates how mysqlbinlog displays row events that specify data modifications. These correspond to events with the WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, …

I used to think one should never look at max_binlog_size, however last year I had a couple of interesting cases which showed that sometimes it may be very important variable to tune properly. I meant to write about it earlier but never really had a chance to do it. I have it now!

One of our customers was complaining that the database would lock up at random times and then it would go back to normal in just a few seconds. This was MySQL 5.0 running MyISAM/InnoDB mix, not heavily loaded. We used pt-stalk (at that time it was aspersa stalk) trying to figure out what is happening, however all we found was a spike in writes, many queries piled up and looking at the system process list it was quite obvious that page flush daemon was acting out. I/O Pattern was rather awkward – here is an output from …

Replication enables data from one MySQL server to be replicated on one or more other MySQL servers. Replication is mostly used as scale-out solution. In such a solution, all writes and updates take place on the master server, while reads take place on one or more slaves. This model is actually known as master-slave replication and this is the kind of replication that I will be setting up in this post.