Boosting MySQL Replication

This post is my first one after the blog got a new design! Yayyy! I hope you guys enjoy it and remember: feedback is always welcome!

One of the most common questions that I receive is how to improve MySQL replication performance. Or to fix a replica (old slave nomenclature) falling behind the source (old master nomenclature).

So, let’s start with some solutions and consider their advantages and disadvantages.

Source Parameters

binlog_transaction_dependency_tracking: The default value is COMMIT_ORDER and means that the dependency information is generated from the source’s commit timestamps. Changing this parameter to WRITESET allows MySQL to parallelize tuples that are not updating the same value.

If you are running MySQL 5.7, before setting the binlog_transaction_dependency_tracking, you need to set the transaction_write_set_extraction parameter.

Drawbacks: NONE

binlog_group_commit_sync_delay: Controls how many microseconds the binary log commit waits before synchronizing the binary log file to disk. In other words, increasing this parameter allows MySQL to write more transactions in the same commit group, increasing the level of parallelism.

Drawbacks: Increasing this value too much will make the application write slower

Recommended Master Settings

Do not forget to add the settings to your my.cnf file to survive restarts.

Replica Parameters

sync_binlog: MySQL server synchronizes its binary log to disk (using fsync()) before the commit of every transaction. This option is the safest choice but brings a significant drop in performance.

Drawbacks: If the replica crashes, its binary log might lose some transactions.

innodb_flush_log_at_trx_commit: This is probably the most well know parameter by MySQL DBAs. Setting the innodb_flush_log_at_trx_commit to 0 or 2 disables the strict ACID compliance. The value of 2 instructs InnoDB to write into log files after transaction commit, but don’t fsync. Instead, it fsyncs log files every second. This way, you can lose 1 second of updates in case if OS crashes. With modern hardware that supports up to 1M inserts per second, this can be quite a huge number. Version 0 is even worse: you may lose up to 1 second of transactions even if mysqld crashes, not only the operating system.

Drawbacks: If MySQL or the OS crashes, data can be lost.

slave_parallel_type: Transactions that are part of the same binary log group commit on a source are applied in parallel on a replica. (Hence the importance of setting binlog_group_commit_sync_delay in the master). The default option (DATABASE) only parallelizes transactions that come from different databases.

Drawbacks: NONE

slave_parallel_workers: We need to do some math to calculate this number. That’s because it depends on the workload that the replica is handling. To estimate the potential parallelism execute the following on the REPLICA:

And create the view:

Finally, you need to run the query to evaluate the parallelism:

Now, let’s see an example and analyze the results:

From the result above, we can observe that two slave threads are performing 72% of the replication. Furthermore, one of the threads is applying only 2.9% of the effort. So, if we analyze the numbers, it is interesting to set slave_parallel_workers value to 4.

Drawbacks: Setting a high value can cause high CPU usage and be ineffective; on the other hand, setting to a low value might cap the replication performance.

Recommended Replica Settings

Do not forget to add the settings to your my.cnf file to survive restarts.

Final Considerations

I recommend having GTID enabled for some known issues. I recommend reading this blog post:

https://www.percona.com/blog/2015/01/29/multi-threaded-replication-with-mysql-5-6-use-gtids/

As you could observe, most of the decisions involve a balance between performance and the risk of losing some data on the replica. Some DBAs to avoid this, they create multiple replicas while others assume the risk, and in case of a crash, they recreate the slave from the master.

I hope it helped! See you!