I am currently working on a script to auto-enable parallel replication / multi-threaded replication (MTR) when there is replication lag. For testing this script, I need to trigger replication lag that would disappear after enabling MTR. I came-up with a simple solution for that, and I thought it could be useful to more people, so I am writing this blog post about it. Read-on for the details.
In today’s blog, I will show an issue
seconds_behind_master that one of our
clients faced when running
0. We found out that the reported
STATUS was lying. To be more specific, I’m talking
about bugs #84415 and #1654091.
MySQL will not report the correct slave lag if you have
slave_parallel_workers> 0. Let’s show it in
I’ll use MySQL Sandbox to speed up one master and two slaves on MySQL version …[Read more]
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 …
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]
Multi-threaded replication is a new feature introduced in MySQL 5.6 and MariaDB 10.0. In traditional single-threaded replication, the slaves have a disadvantage as they have to process in sequence what a master executed in parallel. This, plus the fact that slaves usually have a lot of read-only connections to deal with as well, can easily create performance problems. That is, a single-threaded slave needs to be set to allow fewer connections, otherwise there’s a higher risk of it not being able to keep up with the replication stream. There is no exact rule for this, as it relates to general I/O capacity and fsync latency, as well as general CPU and RAM considerations and 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]
On Wednesday I’ll be leading a webinar exploring MySQL 5.6’s new replication features. And yes, as usual I’ll deliver news on the good, the bad and the ugly (that is to say the benefits, limitations and challenges).
The webinar, appropriately titled, “New Replication Features in MySQL 5.6: Benefits, Limitations, and Challenges“, is scheduled for Oct. 23 at 10 a.m. Pacific Daylight Time. You can register now to reserve your spot (this webinar will also be available for playback afterward).
This session aims at exploring some of these …[Read more]