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]
In this third installment in this series, I'll explain why the
smart solution I described in the previous post actually wasn't that good, and
then I go on to explain how to fix it, and why that fix wasn't
such a smart thing after all. So, this was the design we ended
with last time:
We have Oracle replicating to a Materialized
View, this to ensure that we can run triggers when the is a
commit, and then triggers on this Materialized View updates
MariaDB by sending a UDP message to a server that in turn is
connected to MariaDB.
The issue with the above thingy was that a Materialized View by
default is refreshed in it's entirety when there is a refresh, so
if the table has 10.000 rows and 1 …
The theme for this series of posts is, and indicated in the
previous post, "Try and try, again", and there
will be more of this now when I start to make this work by
playing with Oracle, with PL/SQL and with the restrictions of
Oracle Express (which is the version I have available).
So, what we have right now is a way of "sending" SQL statements
from Oracle to MariaDB, the question is when and how to send them
from Oracle. The idea for this was then to use triggers on the
Oracle tables to send the data to MariaDB, like this, assuming we
are trying to replicate the orders table from Oracle to
MariaDB:
In Oracle, and assuming that the
extproc I have that created to send UDP …
We are very happy to introduce a new MySQL utility named
'mysqlbinlogmove', which is used to relocate binary log files.
This utility is one of two new utilities included in MySQL
Utilities release-1.6.0 Alpha. The other utility is
'mysqlgrants', which is used to display the privileges (grants)
of database objects.
Note: I use "binary log" to refer to both "kinds" of binary log
files (binlog and relay log files) in general, and use "binlog"
to refer specifically to those that are not "relay log"
files.
The mysqlbinlogmove utility allows you to move binary log files
to a new location taking care of correctly updating the
respective index file for you. This utility can be very useful if
you want to change the location to store the binlog file and you
want to move all of the binary log files. It is also handy to
archive older binary log files to a new location thereby saving
disk space in the server's partition.
…
We are very happy to introduce a new MySQL utility named “mysqlbinlogmove“, which is used to relocate binary log files. This utility is one of two new utilities included in MySQL Utilities release-1.6.0 Alpha. The other utility is “mysqlgrants“, which is used to display the privileges (grants) of database objects.
Note: I use “binary log” to refer to both “kinds” of binary log files (binlog and relay log files) in general, and use “binlog” to refer specifically to those that are not “relay log” files.
The mysqlbinlogmove utility allows you to move binary log files to a new location taking care of correctly updating the respective index file for you. This utility can be very useful if you want to change the location to store the binlog file and you want to move all of the binary log files. It is also handy to archive older binary log files to a new location thereby saving disk …
[Read more]