MySQL 5.7 introduced the LOGICAL_CLOCK type of multi-threaded slave (MTS). When using this type of parallel replication (and when slave_parallel_workers is greater than zero), slaves use information from the binary logs (written by the master) to run transactions in parallel. However, enabling parallel replication on slaves might not be enough to get a higher replication throughput (VividCortex
« 10 Newer Entries
I have a new blog post on blog.booking.com describing MariaDB 10.1 Optimistic Parallel Replication (with benchmark results):
Evaluating MySQL Parallel Replication Part 4: More Benchmarks in Productionhttp://blog.booking.com/evaluating_mysql_parallel_replication_4-more_benchmarks_in_production.html
If you want to know more about MySQL/MariaDB Parallel Replication and if you are attending
Reminder: MTS = Multi-Threaded Slave.
Update 2017-04-17: since the publication of this post, many things happened:
the procedure for fixing a crashed slave has been automated (Bug#77496) Bug#80103 as been closed at the same time as Bug#77496 but I still think there are unfixed things, see Bug#81840
End of update 2017-04-17.
I will be talking about parallel replication at FOSDEM in Brussel on
My webinar “Multi-threaded Replication in MySQL 5.6 and 5.7″ on February 25 generated several excellent questions following the presentation (available here for playback along with the slides). I didn’t have time to answer many of the questions during the session and so in this post I answer all of them. Thanks to everyone who attended!
Q: What do you expect from MTS with logical clock? Do you
think performance would be good as with per database?
A: MTS with 5.6 is not usable if you have a single database. I do not have numbers, but this is quite frequent. With 5.7 everyone should be able to benefit from multi-threaded replication.
Q: When MySQL 5.6 was released, performance of MTS was lower,
than in 5.5, for example. Is this addressed now?
A: I am not sure which …
In a previous post, titled “Multi-threaded replication with MySQL 5.6: Use GTIDs,” I explained that using GTID replication is almost a requirement when using MySQL 5.6 MTS. Let’s see now how to perform the day-to-day operations when MTS and GTIDs are both enabled. (I’ll also be presenting a related webinar next week titled “Multi-threaded Replication in MySQL 5.6 and 5.7″).
Seeing the execution gaps
If you have a look at
SHOW SLAVE STATUS while the
slave is running, you may not be expecting such an output:
[...] Executed_Gtid_Set: …[Read more]
MySQL 5.6 allows you to execute replicated events in parallel as
long as data is split across several databases. This feature is
named “Multi-Threaded Slave” (MTS) and it is easy to enable by
slave_parallel_workers to a > 1 value.
However if you decide to use MTS without GTIDs, you may run into
annoying issues. Let’s look at two of them.
Skipping replication errors
When replication stops with an error, a frequent approach is to
“ignore now and fix later.” This means you will run
GLOBAL sql_slave_skip_counter=1 to be able to restart
replication as quickly as possible and later use
pt-table-checksum/pt-table-sync to resync data on the slave.
Then the day when I hit:
mysql> show slave status; [...] Last_SQL_Error: Worker 0 failed executing transaction '' at master log mysql-bin.000017, end_log_pos 1216451; Error 'Duplicate entry '1001' for key 'PRIMARY'' on query. …[Read more]
Have you ever wondered exactly does log_warnings=2 log? Well, I have, and finally decided to check the code. (The manual used to mention setting this to 2 for diagnosing some connection-related problems, but I didn’t run into that comment in my most recent search.)
Basically, in recent 5.6 source code, we find “log_warnings > 1″ in 7 files. In 5.5 source, it is only in 5 files. Here are the 7 files in 5.6:
filesort.cc (line 460) log_event.cc (lines 4873, 10020, 11209) rpl_master.cc (line 912) rpl_rli_pdb.cc (lines 1538, 1596, 1735, 2066) rpl_slave.cc (lines 3585, 4684, 5405, 5436) sql_acl.cc (lines 9591, 9613, 11351) sql_connect.cc (line 791)
Long story short, the main (most common) ones are when a filesort fails (filesort.cc) or a failed login occurs (sql_acl.cc). Then there are some replication-specific instances where it logs extra info, such as master/slave/binlog info, “ignored” errors, and some summary …[Read more]
MySQL 5.7.2 features enhanced Multi-threaded slave which
can be used to apply transactions in parallel even within a
single database. Internal details of its working can be found in
an earlier post. In this post we will
see how we can configure our replication slave to use this
MySQL 5.7.2 has a new system variable --slave-parallel-type which is dynamic. It can be set to the following values:
1. DATABASE : (Default) Use the db partitioned MTS (1 worker per database)
2. LOGICAL_CLOCK: Use logical clock based parallelization mode.
Apart from this the original option of --slave-parallel-workers=N is still valid and it sets that number of workers that we need to spawn. Also since the slave leverages the group of transactions that have …
IntroductionRe-applying binary logs generated from highly
concurrent master on the slave has always been an area of focus.
It is important for various reasons. First, in real-time systems,
it becomes extremely important for the slave to keep up with the
master. This can only be guaranteed if the slaves’ performance in
reapplying the transactions from the binary log is similar (or
at-least comparable) to that of master, which is accepting
queries directly from multiple clients. Second, in synchronous
replication scenarios, having a fast slaves, aids in reducing the
response times as seen by the clients to the master. This can be
made possible by applying transactions from the binary log in
parallel. However if left uncontrolled, a simple round-robin
multi-threaded applying will lead to inconsistency and the slave
will no longer be the exact replica of the leader.
The infamous out of order commit problemThe Out of order execution of transaction …
« 10 Newer Entries