Showing entries 1 to 8
Displaying posts with tag: MTS (reset)
Parallel Replication: Post on Blog, Talk at Percona Live Amsterdam 2016, and more

I have a new blog post on describing MariaDB 10.1 Optimistic Parallel Replication (with benchmark results):

Evaluating MySQL Parallel Replication Part 4: More Benchmarks in Production

If you want to know more about MySQL/MariaDB Parallel Replication and if you are attending Percona

Replication crash safety with MTS in MySQL 5.6 and 5.7: reality or illusion?

Reminder: MTS = Multi-Threaded Slave.

I will be talking about parallel replication at FOSDEM in Brussel on January 30th and at Percona Live Santa Clara in April (link to the talk descriptions here and here).  Come to one (or both) of those talks to learn more about this subject.

MySQL 5.6 introduced replication crash safety for single-threaded replication. I already blogged about this, more

Q&A: Multi-threaded Replication in MySQL 5.6 and MySQL 5.7

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 …

[Read more]
More on MySQL 5.6 multi-threaded replication and GTIDs (and Feb. 25 webinar)

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]
Multi-threaded replication with MySQL 5.6: Use GTIDs!

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 setting 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 SET 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]
What exactly does log_warnings=2 log?

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: (line 460) (lines 4873, 10020, 11209) (line 912) (lines 1538, 1596, 1735, 2066) (lines 3585, 4684, 5405, 5436) (lines 9591, 9613, 11351) (line 791)

Long story short, the main (most common) ones are when a filesort fails ( or a failed login occurs ( 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 Enhanced MTS: configuring slave for Intra-database parallelization

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 enhancement.
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 …

[Read more]
MySQL 5.7: Enhanced Multi-threaded slaves

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 …

[Read more]
Showing entries 1 to 8