Replication from MySQL to Tarantool

    image


    Hello, Habr! Today I will share with you an article written based on my report on Tarantool Meetup. A little story about why Mamba started using Tarantool. Why are we doing replication from MySQL to Tarantool? The first reason is that at some point it was necessary to start switching to MySQL 5.7, but it did not have 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 of c handler socket.


    The second reason is that we started the trial use of Tarantool, and we liked the speed of work: we just compared memcache and Tarantool as a key / value storage, getting a performance gain from 0.6 to 0.3 ms on the same hardware. In relative terms, Tarantool is twice as fast, in absolute terms, it’s not so cool, but still. And the third reason is the desire to completely preserve the current structure: there are MySQL Server Master and its Slaves, I did not want to rewrite anything, I wanted to leave it as close as possible to the architecture that is now. How would we make sure that instead of the MySQL 5.6 Slaves that use the handler socket, 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 own purposes. It was originally their idea to replicate data from MySQL to Tarantool. We asked them for a code that we had to redo because it worked with MySQL 5.1 and Tarantool 1.5, not 1.7. The replicator uses libslave, opensource development to read events from the MySQL Master server. Replicator, fully compiled statically and does not use system mysql lib. The replicator from MySQL to Tarantool is available 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 Master should only be Row-based. Neither Statement nor Mixed will do, only Row-based. Secondly, the replicator is not a certain module in Tarantool, but a separate Daemon. That is, this tool, in principle, is in no way associated with either Tarantool or MySQL. Thirdly, if you need, let's say, that one Master has 10 Slaves, then 10 Daemons will have to be launched. One replicator can replicate in only one Tarantool. And fourthly, the replicator will not work with MariaDB. We tried it on both 5.6 and 5.7, but it will be either a build from Oracle or a build of Percona, we historically use the version of Percona. MariaDB has a replication protocol changed.


    How replication works


    image


    Neither MySQL knows about Tarantool, nor Tarantool knows about MySQL. The replicator reads the bin logs from the MySQL Master, and all this is written to Tarantool.


    What can a replicator do?


    When starting up, the replicator completely takes data from the Master, based on the config, which indicates which databases / tables should be replicated, i.e., to start it, just take a tarantula with empty spaces, which is very convenient.


    You, as a system administrator, must understand whether replication is working at all, which bin log the replicator is reading now, what its position is, all this, of course, is. There is a separate Space, in which there are only three values: the bin log name and the position currently being read, that is, there is a small analogue of the usual Show slave status.


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

    After introducing the replicator, we deployed seven Tarantool instances that work on only two servers, because a single Tarantool instance cannot utilize all the kernels of the machine. Let me remind you a bit of the architecture of the tarantula, one instance can consume from three cores: one core or more - the network, exactly one core - the transactional part, exactly one core - work with wal-files.


    Load


    The replicator was launched, and the load on the MySQL Slaves, on which the handler socket was running, fell sharply - almost to zero.


    image


    After that, I tried to leave only one instead of the current eight MySQL slave servers, already one server completely held the load. We did not completely abandon the MySQL slave servers, we left those queries on them that work without a handler socket, which means that you 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, and money.


    What can be said interesting about the response time? Mamba has its own BTP opensource product, here are its graphs.


    image


    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 to the handler socket could reach 1 second.


    And now everything is the same, but with Tarantool servers, where the same database is replicated from Master:


    image


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


    Is it a bike?


    If someone else was talking about all this, I would ask myself: “But have you invented a bicycle? You from MySQL are replicating something in Tarantool. Yes, you are thinking of dropping the handler socket because you want to upgrade to 5.7. But what is it for? If you can’t 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 the database files to TMPFS, make symlinks, and all the tables will be in memory again, everything will work fine. ”


    But this is not so.


    The main feature of the replicator is this. For example, in the database that we replicate and which lies on the Master, there are about a hundred tables, but on Slave all the tables are useless, you need a limited number of tables, only seven. Accordingly, I do not want to spend resources on 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 out of 120 fields are needed. In MySQL, I will still replicate all seven tables, which in our case occupy about 80 GB of 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 with one query, without join.


    What is the output?


    Tarantool is really fast. I showed graphs where he demonstrated an advantage over MySQL - up to three times.


    The replication in Tarantool, which we have, works faster than in MySQL, and that'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 in a month and reconciles with MySQL master and Tarantool slave. Sometimes it turned out that this script for unloading an active audience took a user who registered less than a second ago. On MySQL Slave it was not there yet, but on Tarantool it already was, although on MySQL's Slave in Show slave status the lag is always zero, we never have Slave lagging there. But at the same time, information gets to Tarantool much faster. Again, because Tarantool is a fully In-Memory base.


    Tarantool Benefits


    What happens if you pull out the power cable from the MySQL server and then start the server again? At start, the InnoDB Recovery process will begin, and as a result, the database will be restored. But it happened a couple of times that at some point the controller stopped writing adequate information to the disk, the server crashed into kernel panic due to a controller failure, the innodb recovery process ended with Core Dump after reboot. In Tarantool, the write-ahead-log mechanism is so well thought out that even if for some reason the controller wrote some nonsense to the wal-file, because of which Tarantool does not rise, then you simply demolish the wal-file. Or open the file and kill the recording data from it line by line until Tarantool starts up. Moreover, you can specify the desired number of transactions that will be recorded in the wal-file, at least one transaction. I emphasize


    I also liked that Tarantool is very simple. Everything is clear there, what and how to do. Let's say some new version of MySQL comes out, we updated, but it does not start. You climb into Error Log and understand: “Wow! Some settings for my.cnf are already deprecated. " You open the documentation and you see that instead there is still a bunch of settings, now you need to figure out how to write a new my.cnf to improve performance. Tarantool has none of this. Everything is simple and clear here, a minimum of settings.


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


    Tarantool snapshots are great. This mechanism works at an insane speed - 800 Mbit per second, maybe even 1 Gbit. It is written sequentially in one file. No super disks are needed here, everything works very quickly and competently even on the cheapest SATA. Raising a snapshot of a 20-gigabyte base takes no more than five minutes. I checked, and in MySQL I was much slower.


    disadvantages


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


    Второй недостаток — это консоль. Как в MySQL, например, понять, что репликация работает? Вы набрали два раза Show slave status и увидели, что циферки бегут. При этом в консоли MySQL Show slave status заполнится не два раза, а один. Если вы даже сто раз подряд заполните Show slave status, то всё равно в History MySQL консоли запомнится, будто вы сделали это только один раз. Но в Tarantool можно попробовать сто раз Enter ввести, и у вас в консоли будет сто Enter, вы замучаетесь вверх нажимать, чтобы прокрутить до того, что было до многократного нажатия.


    Вывод


    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 has appeared in it, it will develop even faster. The most important advantage of Tarantool, of course, is its speed. To be continued.


    Also popular now: