Replication from MySQL to Tarantool

Hi, Habr! Today I will share with you an article written based on my report on Tarantool Meetup. A little story, why in the company Mamba began to use Tarantool. Why did we start replicating from MySQL to Tarantool? The first reason is that at some point you had to start switching to MySQL 5.7, but it lacks a handler socket, which is actively used on our servers in MySQL 5.6. We even contacted the Percona team, and they confirmed that 5.6 is the latest version with the handler socket.

The second reason is that we started testing Tarantool, and we liked the speed: we just compared memcache and Tarantool as key / value-storage, getting performance gains from 0.6 to 0.3 ms on the same hardware. In relative terms, Tarantool is twice as fast, in absolute terms it is not that cool, but still. And the third reason is the desire to fully maintain the current structure: there is a MySQL Server Master and its Slaves, I didn’t want to rewrite anything, I wanted to leave it as close as possible to the architecture that I have now. How would we make it so that instead of Slave MySQL 5.6, which uses the handler socket, to apply something else and not completely rewrite the whole huge architecture?

We learned that the Mail.Ru Group team has a replicator that they wrote for their purposes. It was originally their idea - to replicate data from MySQL to Tarantool. We asked them for code that we had to redo because it worked with MySQL 5.1 and Tarantool 1.5, rather than 1.7. The replicator uses libslave, opensource development for reading events from the MySQL Master server. The replicator is fully assembled statically and does not use the system mysql lib. The replicator from MySQL to Tarantool is laid out in open source under the BSD license. You can use it absolutely free. Here are its sources: https://github.com/tarantool/mysql-tarantool-replication

Limitations of this replication

First, bin logs on the Master should only be Row-based. Neither Statement nor Mixed is suitable, only Row-based. Secondly, the replicator is not a module in Tarantool, but a separate Daemon. That is, this tool is in principle in no way connected with either Tarantool or MySQL. Thirdly, if you need, say, for one Master to have 10 Slaves, you will have to run 10 Daemon. One replicator can only replicate to one Tarantool. And fourth, the replicator will not work with MariaDB. We tried it on both 5.6 and 5.7, but it will be either an Oracle build or a Percona build, we historically use the Percona version. At MariaDB, the replication protocol has been changed.

How replication works

Neither MySQL knows about Tarantool, nor Tarantool knows about MySQL. The replicator reads the bin logs with MySQL Master, and all of this is recorded in Tarantool.

What can a replicator do?

When launching, the replicator completely retrieves data from the Master, based on the config, which indicates which bases / tables need to be replicated, i.e., to start, it is enough to simply take the tarantula with empty spaces, which is very convenient.

As a system administrator, you need to understand whether replication works at all, which bin log is currently being read by the replicator, what is its position, all this, of course, is. There is a separate Space, in which there are only three values: the name of the bin log and the position currently being read, that is, there is a small analogue to the usual Show Slave status.

10.5.2.17:5000> box.space.ReplicationLog:select() [0, 'db-bin.024218', 916925355] 

After implementing the replicator, we deployed seven instances of Tarantool, which run on only two servers, because one Tarantool instance cannot dispose of all the cores of the machine. Let me remind you a bit of the tarantula architecture, one instance can consume from three cores: one core and more - a network, strictly one core - a transactional part, strictly one core - work with wal-files.

Load

They started the replicator, and the load on the MySQL Slave, which the handler socket worked on, abruptly failed - almost to zero.

After that, I tried to leave only one, instead of the current eight MySQL slave servers, one server already had a full load. We did not completely abandon MySQL slave servers, we left those requests on them that work without a handler socket, which means we can completely switch to 5.7. As a result, we saved at least seven servers, Enterprise SSD drives that work in them, rack space, electricity, money.

What can I say interesting about the response time? The company Mamba has its opensource BTP product, here are its graphics.

The handler socket has a very unusual API. You must first call the Connect method, then the Open Index method, then the Execute method. The total time of all three methods is shown in the illustration: the duration of the request for a handler socket could reach 1 second.

And now everything is the same, but already with Tarantool servers, where the same base is replicated with Master:

The reason is very simple: Tarantool is an In-Memory database, and MySQL worked on an SSD, under the buffer pool size memory was allocated less than the size of the database. Here we have a full In-Memory, and even in all instances, wal-files are turned off, i.e. there is no work with the disk, only memory, there are separate instances that do not receive combat requests, there are wal-files included and they are snapshots.

Is it a bicycle?

If someone else was talking about all this, I would have asked the question myself: “Didn’t you invent the bicycle? You are replicating something from MySQL to Tarantool. Yes, you are thinking of abandoning the handler socket, because you want to switch to 5.7. But what is it for? If you do not pull without a handler socket, then just put a little more servers. If you definitely need an In-Memory database, you can either make Heap tables, or transfer database files to TMPFS, make symlinks, and all tables will again be in memory, everything will work fine ”

But it is not.

The main feature of the replicator is this. Suppose that in the database that we replicate and which lies on the Master, there are about a hundred tables, but on the Slave all the tables are useless, you need a limited number of tables, only seven. Accordingly, it would not be desirable to spend resources for replication of excess data. In MySQL, you can specify that we replicate these seven tables. But suppose that in seven tables there are 120 fields, and for queries that are used only on Slaves, only 21 of the 120 fields are needed. In MySQL, I will still replicate all seven tables, which in our case occupy about 80 GB in memory. And in the replicator, you can specify only a set of fields of these tables, that is, instead of 120 fields from seven tables, 21 fields are replicated, and in Tarantool they occupy 20 GB.

Another feature of the replicator: in fact, all the fields from these seven tables are merged into one Space, that is, they can be selected by one query, without join.

What is the output?

Tarantool is really fast. I showed the graphics where he demonstrated an advantage over MySQL - up to threefold.

Replication in Tarantool, which we got, is faster than in MySQL, and here's why. When we launched it, the first question that immediately arose was whether our replication is consistent. We wrote a very simple PHP script that takes an active audience for a month and makes a reconciliation on the MySQL master and Tarantool slave. Sometimes it turned out that this script unloading an active audience took a user who registered less than a second ago. It was not yet on MySQL Slave, but it was already in Tarantool, although the lag is always zero on MySQL Slave in Show slave status, there we never have Slave behind. But at the same time in Tarantool information gets much faster. Again, because Tarantool is a fully in-memory database.

Benefits of Tarantool

What happens if you pull out the power cable from the MySQL server and then start the server again? At the start, the InnoDB Recovery process will begin, and as a result, the base will be restored. But we happened a couple of times that at some point the controller stopped writing adequate information to the disk, the server crashed into the kernel panic due to a controller failure, and the innodb recovery process ended with a reboot Core Dump. In Tarantool, the write-ahead-log mechanism is so well thought out that even if for some reason the controller has written some nonsense into a wal-file, which is why Tarantool does not rise, then you simply demolish the wal-file. Or, open the file and kill the records from it line by line until Tarantool starts. And you can specify the desired number of transactions that will be recorded in the wal-file, at least for one transaction. I emphasize that the example with MySQL, which I cited above, is precisely the consequences of a server hardware failure, in normal conditions innodb recovery will work like a clock.

I also liked the fact that Tarantool is very simple. Everything is well understood, what and how to do. Suppose some new version of MySQL is coming out, we have updated, but it does not start. You climb into the Error Log and you understand: “Wow! Some of my.cnf settings are already deprecated. ” You open the documentation and see that a bunch of settings appeared instead, now you need to figure out how to write a new my.cnf to improve performance. In Tarantool this is nothing. Everything is simple and clear, a minimum of settings.

The next thing we all enjoyed working with Tarantool is a cool community. In Telegram there is a chat where you can get an invitation from Denis Anikin, where they answer your questions and quickly make a bug fix.

The snapshots in Tarantool are great. This mechanism works at an insane rate of 800 Mbit per second, maybe even 1 Gbit. Written sequentially in one file. It does not need any superdisks, everything works very quickly and competently, even on the cheapest SATA. Raising a snapshot of a 20-gigabyte database takes no more than five minutes. I checked, and in MySQL I did much slower.

disadvantages

The first, the biggest drawback - in Tarantool case sensitive indexes, this is very inconvenient, especially when you start using Tarantool after MySQL.

The second drawback is the console. How in MySQL, for example, understand that replication works? You scored twice the show slave status and saw that the digits were running. At the same time, in the console MySQL Show slave status will be filled not two times, but one. If you fill in the Show slave status even a hundred times in a row, all the same in the History MySQL console will be remembered as if you did it only once. But in Tarantool, you can try Enter a hundred times to enter, and you will have a hundred Enter in the console, you press up to scroll up to what you had to press repeatedly.

Conclusion

Tarantool is a really good product to use. Yes, it has its drawbacks, but the product is developing. We hope that after replication from MySQL appeared in it, it will develop even faster. The most important advantage of Tarantool, of course, is its speed. To be continued.