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]Oracle have just made availble the new MySQL Cluster 7.4.2 Development Milestone Release – it can be downloaded from the development release tab here. Note that this is not a GA release and so we wouldn’t recommend using it in production.
This is the second DMR for MySQL 7.4; the delta between this DMR and 7.4.1 can be viewed in the MySQL Cluster 7.4.2 Release Notes
There are three main focus areas for this DMR and the purpose of this post is to briefly introduce them:
- Active-Active (Multi-Master) Replication
- Performance
- Operational improvements (speeding up of restarts; enhanced …
I just publish a post on the Booking.com blog: http://blog.booking.com/better_crash_safe_replication_for_mysql.html Spoiler: it uses Binlog Servers.
This is also the opportunity to tell you that I will be at Percona Live London at the beginning of November, and that I will give a talk about Binlog Servers: High Availability, Disaster Recovery and Extreme Read Scaling using Binlog Servers. I
Multi-source replication for MySQL has been released as a part of
5.7.5-labs-preview
downloadable from labs.mysql.com. It is one among the several
features that are
cooking in the replication technologies at MySQL. (For a
birds eye view of all
replication features introduced in 5.7 and labs, look at
the blog posts here and here.
Previously, we have introduced a preliminary multi-source feature labs preview. Based on the feed back from that labs release, we …
[Read more]
Computer science is like an enormous tool box you can rummage
through whenever you have a problem to solve. Most of the tools
are sturdy and practical, like algorithms for B-trees. Some are
also elegant, like consistent hashing in Dynamo. Finally there
are some tools that you never quite figure out even after years
of reflection. That piece of steel you are looking at could be
Excalibur. Or it could be a rusty knife.
The CAP theorem falls into the last category, at least
for me. It was a major topic in the blogosphere a few years
ago and Google Trends shows steadily increasing interest in the term since
2010. It's not my goal to explain CAP fully--a good
informal description is …
Now it's time to release something useful! At least I hope so. I
have been going through how I came up with this idea and how I
came up with the implementation in a series of blog posts:
But now it's time for the real deal, the software itself. This is an Alpha 1.0 release but it should work OK in the more basic setups. It's available …
[Read more]
Now it's time to get serious about replicating to MariaDB from
Oracle, and we are real close now, right? What I needed was a
means of keeping track of what happens in a transaction, such as
a LOG table of some kind, and then an idea of applying this log
to MariaDB when there is a COMMIT in Oracle. And thing is, these
two don't have to be related. So I can have a table which I write
to and also have a Materialized View that is refreshed on COMMIT
on, and I need a log table or something. And when the
Materialized View is refreshed, as there is a COMMIT, then the
log can be applied. From a schematic point-of-view, it looks
something like this:
This looks more complex than it is, actually,
all that is needed is some smart PL/SQL and this will work. I
have not done much of any kind of testing, except checking that
the basics …
With the introduction of Global Transaction Identifiers(GTID) in MySQL from mysql-5.6 GA a whole lot of different functionality have been developed around it so that GTID could be used in a much simpler and efficient way.
One of the interesting functionality with GTID in use is the
function to sync a slave with its
master server using the WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS. This
is used to do a timed or an indefinite wait till the servers
in contention are in sync with respect to the GTID executed set.
This function is used to address specific use cases in which transactions are applied using SQL/worker thread. In order to give more flexibility to the user a new function of syncing the servers with GTID has been introduced in mysql-5.7.5 called WAIT_FOR_EXECUTED_GTID_SET.
Limitations of WAIT_UNTIL_SQL_THREAD_AFTER_GITDS
The already existing WAIT_UNTIL_SQL_THREAD_AFTER_GITDS functions …
[Read more]On MySQL-5.7.2, we released a new type of multi-threaded slave (MTS). It is called logical clock based MTS. Because it can parallel apply transactions in the same schema, it has the potential to improve slave throughput on almost any application, regardless of the schema layout. After it was released, we continued to work on the framework to improve it further. Therefore, 5.7.5 includes a few enhancements, in addition to those released as part of previous DMRs. This blog post introduces one of the new features in the latest DMR.
slave_preserve_commit_order
It is a system global variable and can be set dynamically.
- SET GLOBAL slave_perserve_commit_order = {ON|OFF};
Enabling this variable ensures that the order which transactions were committed on the master is preserved on the slave. The replication threads must be stopped before enabling this variable and this variable only affects logical clock based …
[Read more]I am happy to announce that slave_transaction_retries is available on multi-threaded slave now. You will no longer see the warning that slave_transaction_retries is not supported by multi-threaded slave since MySQL-5.7.5.
Multi-threaded slave and single-threaded slave share the system variable slave_transaction_retries and they have similar transaction retry logic. So this blog just brings you a little new knowledge and then refresh you with the transaction retry logic.
Transaction Retry In Worker Threads
For multi-threaded slave, Transaction retry is done in worker threads when they are applying their transactions. When a worker thread encounters a temporary error, it needs to read the transaction’s events from relay log by itself and then apply them again.
The only new thing is that worker threads need to read events from relay log by themselves. To support it, coordinator stores relay log position of …
[Read more]