dbdeployer community - Part 2: Percona XtraDB Cluster

This was not on the radar. I have never been proficient in Galera clusters and related technologies, and thus I hadn’t given much thought to Percona Xtradb Cluster (PXC), until Alkin approached me at FOSDEM, and proposed to extend dbdeployer features to support PXC. He mentioned that many support engineers at Percona use dbdeployer) on a daily basis and that the addition of PXC would be welcome.

I could not follow up much during the conference, but we agreed on making a proof-of-concept in an indirect way: if several nodes of PXC can run in the same host using shell scripts, dbdeployer could reproduce that behavior.

A few weeks later, when dbdeployer had already been enhanced with flavors and capabilities, I got the script that can deploy several nodes in the same host. It’s a simplification of the ones used in Percona PXC tests, which got me started.

I followed a method similar to the one I used for MySQL Group replication. The technology is similar, although the MySQL Team used a different approach for the installation. The basic principle is that the cluster needs two ports per node: in addition to the regular MySQL port, there is a communication port (SST or Snapshot State Transfer port) that is needed to exchange cluster data. Using this information, and following the sample in the script, I could produce a prototype that surprisingly worked at the first try!

The cluster did deploy, and the replication test, which comes free of charge when you implement a replication-type sandbox using standard templates, worked flawlessly.

Then I hooked the deployment method into dbdeployer concurrency engine, which is able to deploy several nodes at once. Here I hit the first problem. In PXC, the nodes are not equal at startup. The first node needs to be initialised without other nodes addresses, and it becomes the reference for other nodes to join the cluster. If I provided complete references for all nodes (as I do for MySQL Group Replication,) it didn’t work.

After some talk with Percona engineers on Slack, I figured out that the nodes can be deployed together, and the second and third node will just wait for the first one to come online and then join. That worked in principle, or when I deployed sequentially, but not when they are deployed all at once. Fortunately, dbdeployer has several ways of enabling debugging output, and after a few unsuccessful attempts I got the reason: PXC initialisation happens using rsync on port 4444. When the nodes are started sequentially, the receiving node takes control of port 4444 without conflicts, gets the job done and releases the port. When we deploy all nodes at once, there is a race for the possession of the synchronisation port, and a random node will win it, leaving the others waiting forever.

Thus, I modified the installation to allocate a different rsync port for each node, and after that the concurrent installation worked as well.

The last obstacle was the discovery that there is yet another port (IST, or Incremental State Transfer port), which is always one number bigger than the SST port. Thus, if the SST port is, say, 5555, the IST port is set to 5556. This means that, unlike other dbdeployer clusters, I can’t set port numbers incrementally, but I need to set them with an interval. I did that, and the cluster came with a default allocation of four ports per node (MySQL, rsync, SST, IST). If we also enable MySQLX, which comes includes as PXC binaries are based on MySQL 5.7, we would set 5 ports per node, and a majestic 15 ports for a three-node cluster.

Anyway, the support for Percona XtraDB Cluster is available in dbdeployer 1.21.0. Let’s see a sample session to use the new functionality.

$ $ dbdeployer --version
dbdeployer version 1.21.0

$ dbdeployer unpack --prefix=pxc ~/downloads/Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl100.tar.gz  
[...]  
Renaming directory $HOME/opt/mysql/Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl100 to $HOME/opt/mysql/pxc5.7.25

Before trying the cluster, it would be a good practice to make sure that your system can deploy a single node.

$ dbdeployer deploy single pxc5.7.25  
Database installed in $HOME/sandboxes/msb_pxc5_7_25  
run 'dbdeployer usage single' for basic instructions'  
. sandbox server started

$ $HOME/sandboxes/msb_pxc5_7_25/test_sb  
[...]  
# Tests :    11  
# PASS  :    11  
# fail  :     0

$ dbdeployer delete msb_pxc5_7_25  
[...]

And now for the real test:

$ dbdeployer deploy replication --topology=pxc pxc5.7.25  
Installing and starting node 1  
. sandbox server started  
Installing and starting node 2  
...... sandbox server started  
Installing and starting node 3  
..... sandbox server started  
Replication directory installed in $HOME/sandboxes/pxc_msb_pxc5_7_25  
run 'dbdeployer usage multiple' for basic instructions'

We should now see all the allocated ports.

$ dbdeployer sandboxes --header  
            name                       type             version                                      ports  
---------------------------- ------------------------ ----------- ----------------------------------------------------------------------------  
 pxc_msb_pxc5_7_25        :   Percona-Xtradb-Cluster   pxc5.7.25   [26226 26352 26353 26364 26227 26354 26355 26365 26228 26356 26357 26366 ]

If we want more detail, we can look at the sandbox description file:

$ cat $HOME/sandboxes/pxc_msb_pxc5_7_25/sbdescription.json  
{
    "basedir": "$HOME/opt/mysql/pxc5.7.25",  
    "type": "Percona-Xtradb-Cluster",  
    "version": "pxc5.7.25",  
    "flavor": "pxc",  
    "port": [  
        26226,  
        26352,  
        26353,  
        26364,  
        26227,  
        26354,  
        26355,  
        26365,  
        26228,  
        26356,  
        26357,  
        26366  
    ],  
    "nodes": 3,  
    "node_num": 0,  
    "dbdeployer-version": "1.21.0",  
    "timestamp": "Thu Mar  7 17:20:03 CET 2019",  
    "command-line": "dbdeployer deploy replication --topology=pxc pxc5.7.25"  
 }

Now we can run the replication test. Given that we have a cluster where all nodes are masters, the test will create a table in each node, and read the result in each slave (again, each node):

$ $HOME/sandboxes/pxc_msb_pxc5_7_25/test_replication  
# master 1  
# master 2  
# master 3  
# slave 1  
ok - '3' == '3' - Slaves received tables from all masters  
# slave 2  
ok - '3' == '3' - Slaves received tables from all masters  
# slave 3  
ok - '3' == '3' - Slaves received tables from all masters  
# pass: 3  
# fail: 0

It’s a simple test, but it tells us that the cluster is fully functional.

Thanks to Alkin and other Percona engineers who have tested the prototype in real time.

dbdeployer community - Part 1: TiDB

After a conference, when I take stock of what I have learned, I usually realise that the best achievements are the result of interacting with other attendees during the breaks, rather than simply listening to the lectures. It might be because I follow closely the blogosphere and thus the lectures have few surprises in store for me, or perhaps because many geeks take the conference as an excuse to refresh dormant friendships, catch up with technical gossip, and ask their friends some questions that were too sensitive to be discussed over Twitter and have been waiting for a chance of an in-person meeting to see the light of the day.

I surely had some of such questions, and I took advantage of the conference to ask them. As it often happens, I got satisfactory responses, but the latest FOSDEM conference was different than usual, because I got the best experience from the questions that others did ask me.

As it turned out, others were waiting for a chance to discuss things over coffee or food, and I saw that my pet project (dbdeployer) is a lot more popular than I thought, and it is being used silently in several environments. It should not be surprising if you read several MySQL reports on bugs at bugs.mysql.com where it is common the usage of sandboxes to reproduce user issues. Anyway, I got some praise, some requests, a few ideas for improvements, advance notice of an incoming graphical interface, and a few concrete collaboration proposals.

One of such proposals came from Morgan Tocker, who suggested enhancing dbdeployer to support TiDB. At first, it seemed uninteresting, as TiDB is designed to be distributed, and installing just a component didn’t immediately look useful. However, Morgan pointed out that it could be used as a tool to test compatibility with existing applications, and as such it could gain much more value than I initially thought. We decided to try a quick hackathon to make a proof of concept.

It was a great pleasure to figure out, in just over one hour of close interaction, that dbdeployer design for flexibility was up to the task. We managed to make TiDB work with dbdeployer simply by exporting, editing, and re-loading a few templates.

The exercise showed strengths and limitations in both projects. We agreed that dbdeployer had to lose some assumptions (such as “I am working with a MySQL server”) and become able to recognise which flavor of MySQL-lookalike we are dealing with. At the same time, we noted that TiDB is not compatible when it comes to deployment and bootstrap: it is so simple and straightforward that its initialisation doesn’t fit in the complex operation that is a MySQL server warm-up.

Pleased with the initial success, we kept in touch and, after dbdeployer acquired the ability of telling one flavor from another, we put together the various pieces to make dbdeployer recognise and install TiDB. We found and fixed several bugs in both project, and finally released dbdeployer 1.19.0, which can use a TiDB server transparently.

What does transparently mean? It means that tests for TiDB deployment can run alongside tests for other MySQL servers, and the sandbox scripts (such as start, stop, use, status, and test_sb) work as expected and produce a compatible output. Thus, there is a TiDB test running together with another dozen MySQL versions.

Now, if you want, you can evaluate TiDB in your computer without installing the full stack. It won’t be as fast as the real thing: what is installed as a single node is a slower emulation of the real database, but it is enough to give you an idea of what queries you can and cannot run in TiDB, and perhaps try to see if your application could run on TiDB at all.

The collaboration with TiDB was especially useful because the changes needed to smooth the TiDB integration have made made dbdeployer better suited to add support for more not-quite-mysql servers, such as the one that we’ll see in the next post.

But before reaching that point, here’s an example of TiDB deployment on Linux:

$ wget https://download.pingcap.org/tidb-master-linux-amd64.tar.gz  
[...]  
2019-02-24 04:46:26 (2.26 MB/s) - 'tidb-master-linux-amd64.tar.gz' saved [16304317/16304317]

$ dbdeployer unpack tidb-master-linux-amd64.tar.gz --unpack-version=3.0.0 --prefix=tidb  
Unpacking tarball tidb-master-linux-amd64.tar.gz to $HOME/opt/mysql/tidb3.0.0  
1
Renaming directory /home/msandbox/opt/mysql/tidb-master-linux-amd64 to /home/msandbox/opt/mysql/tidb3.0.0

TiDB tarballs doesn't come with a client. We need to use one from MYSQL 5.7. Rather than downloading the huge tarball from MySQL site, we can get a smaller one from a GitHub repository, using dbdeployer itself (NB: this reduced tarball is only for Linux)

$ dbdeployer remote list  
Files available in https://raw.githubusercontent.com/datacharmer/mysql-docker-minimal/master/dbdata/available.json  
5.7 -> [mysql-5.7.24 mysql-5.7.25]  
8.0 -> [mysql-8.0.13 mysql-8.0.15]  
4.1 -> [mysql-4.1.22]  
5.0 -> [mysql-5.0.15 mysql-5.0.96]  
5.1 -> [mysql-5.1.72]  
5.5 -> [mysql-5.5.61 mysql-5.5.62]  
5.6 -> [mysql-5.6.41 mysql-5.6.43]

$ dbdeployer remote get mysql-5.7.25  
File /home/msandbox/mysql-5.7.25.tar.xz downloaded

$ dbdeployer unpack mysql-5.7.25.tar.xz  
[...]  
Renaming directory /home/msandbox/opt/mysql/mysql-5.7.25 to /home/msandbox/opt/mysql/5.7.25

Now we are ready to install TiDB:

$ dbdeployer deploy single tidb3.0.0 --client-from=5.7.25  
Creating directory /home/msandbox/sandboxes  
Database installed in $HOME/sandboxes/msb_tidb3_0_0  
run 'dbdeployer usage single' for basic instructions'  
. sandbox server started

Once installed, a TiDB sandbox behaves like a MySQL sandbox.

$ $HOME/sandboxes/msb_tidb3_0_0/use  
Welcome to the MySQL monitor.  Commands end with ; or \g.  
Your MySQL connection id is 2  
Server version: 5.7.10-TiDB-v3.0.0-beta-111-g266ff4b6f MySQL Community Server (Apache License 2.0)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost:3000] {msandbox} ((none)) >

MySQL and Friends schedule at FOSDEM 2012

The MySQL DevRoom at FOSDEM is ready. The schedule has been voted. Thanks to all who have participated. Now, let's make sure that the event is successful. The schedule is juicy, and not only because I have three talks in it!

Sunday 2012-02-05
Event	Speaker	Room	When
All you need to know about migrations and you never dared to ask	Ralf Gebhardt	H.1309	09:05-09:30
Sphinx User stories	Stéphane Varoqui	H.1309	09:35-10:00
MySQL HA reloaded - old tricks and cool new tools to guarantee high availability to your MySQL Servers	Ivan Zoratti	H.1309	10:00-10:25
MariaDB 5.3's query optimizer: taking the dolphin to where he's never been before	Sergey Petrunya	H.1309	10:30-10:55
How to offload MySQL server with Sphinx	Vladimir Fedorkov	H.1309	11:00-11:25
** Build simple and complex replication clusters with Tungsten Replicator	Giuseppe Maxia	H.1309	11:30-11:55
Cluster internals	Ralf Gebhardt	H.1309	12:00-12:25
Optimising SQL applications by using client side tools	Mark Riddoch	H.1309	12:30-12:55
** MySQL Replication 101	Giuseppe Maxia	H.1309	13:00-13:25
Choosing Hardware for MySQL	Kenny Gryp	H.1309	13:30-13:55
Replication features of 2011: what they were, how to get and how to use them	Sergey Petrunya	H.1309	14:00-14:25
** MySQL creatively in a sandbox	Giuseppe Maxia	H.1309	14:30-14:55
Case Study: La Poste - Real Time, High Volume Data Warehousing Using MySQL & InfiniDB	Stéphane Varoqui	H.1309	15:00-15:25
Sphinx performance top secret	Vladimir Fedorkov	H.1309	15:30-15:55
Managing MySQL with Percona Toolkit	Frédéric Descamps	H.1309	16:00-16:25
Data Warehousing with MySQL	Ivan Zoratti	H.1309	16:30-16:55

UPDATE The schedule has changed. Speakers with more than one talk have been asked to give up one. Now I have two talks instead of three.

Time to vote for MySQL sessions at FOSDEM

There is a room dedicated to MySQL at FOSDEM 2012. (Thanks to @lefred for organizing). The CfP has received 37 submissions, but there will be time slots only for 12 to 15 talks. So now it's up to the community. If you want to attend a particular talk, you should vote for it. Like in previous years, the selection of the talks is public. You can see the list of the proposals, with the instructions, which I repeat here. You can vote either publicly, using Twitter, or privately, by sending an email. Each talk proposal will be referred by the number immediately after the title in this page. This number indicates the order in which the proposals were received. In public, you should send a tweet to @opensqlcamp, indicating a maximum of 12 talks that you would like to see, in the order you like them. e.g. "@opensqlcamp #FOSDEM2012 1,2,3,4,5,6,7,8,9,10,11,12 http://bit.ly/mysql_fosdem_2012" (adding the link will help others to find the page. In private, by email at mysqlfriends AT gmail DOT com, using the same method used for Twitter. Maximum 12 talks, in the order of your preference. In both cases, votes for your preferences will result in 1 point for each talk. In case of equal voting, we will assign 12 points to the first in the list, 11 to the second, and so on. We'll do the tally, and choose the most popular ones. Anonymous votes either by Twitter or email won't be counted. If you want your vote to count, make sure your twitter account has a recognized name (or known nick) on it. If your email address doesn't spell your name, please sign the message with your real one. DEADLINE: Your votes must be entered by January 8th, 2011.

Call for participation for MySQL events - MySQL conference and FOSDEM

It's that time of the year where MySQL would be speakers are called to action. As usual, the main event is the MySQL spring conference, this year hosted by Percona. The call for participation to the MySQL Conference And Expo 2012 is still open until December 5th. To submit a proposal, you should register as a speaker and then fill in the form.
There is a conference committee which is already busy evaluating the proposals that have been submitted so far. The committee is demanding (I know for a fact, since I am in it!) and therefore, if you want to submit something, be very critical with yourself and polish your proposal as if your job depended on it!
Please read an update on Percona Live MySQL Conference & Expo 2012.

Speakers in Europe have some more duty, though. This is also the time to submit talks for FOSDEM MySQL DevRoom. Thanks to Frédéric Descamps and Sergey Petrunia, we have Room H.1309 with 150 seats on Sunday 5th February 2012, all day. The deadline to submit a talk proposal is December 26th. There is no review committee. Like we did on past editions, as soon as the talks are submitted, we will ask everyone to vote on the talks via Twitter or email. More updates will come soon. Of course, participation to FOSDEM DevRoom is not limited to European speakers. There have been several brave speakers who have willingly crossed the pond to offer their services at European conferences before, and they are welcome to repeat the experience. Submit your talk proposals now.

MySQL Developers Room at FOSDEM 2010

I am in Brussels, waiting to attend FOSDEM 2010, one of the biggest open source gatherings in Europe, taking place this weekend in Brussels. On Sunday, there is a Developers Room for MySQL and Friends, with 14 talks from open source professionals coming from Europe and North America.

The novelty of this round of talks is that thy will be 20 minutes long, rather than 1 hour. This will force all presenters to be more cautious about their timing, and to concentrate their talks on the essential. Even the experienced ones, who have given the same talk several times, will have to make an effort to come to the point in less time. The idea cam from reading Scott Berkun's book, Confessions of a public speaker, where he argues successfully on the usefulness of short lectures. It's going to be interesting!
The hosts of the Developers Room are Ronald Bradford and myself, but nothing of this could have happened without the excellent preparatory work done by Lenz Grimmer, who can't be here to enjoy the results of his organization, because he must stay home, waiting for his second child to come any moment. Thanks, Lenz, and good luck!
In addition to the DevRoom, I will have a lightning talk on an unusual (for my public speaking record) topic: Blaming the unknown: a positive approach to technology. If you happen to be around, come see it. It's fun, I promise you, and also informative, or so I hope.