Sunday, January 31, 2016

Why log_slave_updates is a bad default

In a recent post the MySQL product managers asked the community for feedback about proposed new defaults. One of the proposals is to make log-slave-updates on by default.

There are other important options that require some debate. They all look reasonable to me. This one, instead, which implies funnelling the replication events in a slave to its binary log, is questionable.

Let's start for the reason why it is a good idea. The scenario in which it makes sense is when you want a slave to be a master of one or more slaves. This is a common scenario in many cases where you need to support many servers in production. It is not, however always useful. I would like to show, here, a few cases when log-slaves-update is a problem rather than a solution.

Regular Master/Slave deployments

I would say that this case, rather than the hierarchical topology, is the most common situation. When you have a master/slave topology, you want the slave to be ready to replace the master, and for this you don't need log-slave-updates. Also the manual recommends having a slave without log-slave-updates when your goal is to switch roles after a failover.

When using multi-source replication

In multi-source replication, you can have more than one topology. Unlike the ancient circular replication, which was the only way of implementing multi source replication prior to MySQL 5.7, and has proven to be inefficient, the new methods for multi source don't need to enable log-slave-update, unless you are using complex and hybrid topologies like a star or a composite topology. Having used and tested such methods with MySQL and Tungsten replication for years, I can tell confidently that having a direct node-to-node replication stream is always preferable to using an intermediate node: there is better performance, less risk of data muddling, more certainty about the data origin.

Whenever you need performance in the slave

If we are not dealing with a hierarchical topology, having log-slave-updates means that the slave has a penalty performance for no good reason.

Suggesting a better improvement than the intended default

Rather than making log-slave-update the default, what I would like to see in the next MySQL version is a mechanism for log servers, i.e. an intermediate node that receives binary logs from a master and serves them to the slaves without need of applying anything. If hierarchical topologies are so important, this should be the way to go, rather than stretching an obsolete technology beyond its capabilities.

Thursday, January 21, 2016

Frictionless MySQL installation

I saw an interesting post about the ability of installing MySQL 5.7 without changing existing tools and procedures. The post is a plea to make MySQL installation frictionless.

That post was followed by a conversation on Twitter, where the recent security enhancements are blamed for getting in the way of existing practices and need a rewrite of installation tools.

I know the problem very well, as I have faced the installation change in MySQL Sandbox. SO I can sympathize with the ones who have to change deployment tools that rely on mysql_install_db, which was a Perl script up to version 5.6, then it was replaced with a C++ program in 5.7 and deprecated in the same version.

It occurred to me that, in order to keep the existing tools working and at the same time having a recommended installation, a DBA could just quickly replace the existing mysql_install_db with the following shell script:

exec_dir=$(dirname $0);

if [ ! -x $exec_dir/mysqld ]
    echo "$exec_dir/mysqld not found"
    exit 1

$exec_dir/mysqld --initialize-insecure --explicit_defaults_for_timestamp $@

This is Unix only solution. A corresponding script for Windows should be easy to come by.

It is not the optimal way, but it could alleviate the work of a DBA that wants to use tools that would be too cumbersome to adapt to the new requirements.