Concurrent sandbox deployment

Version 0.3.0 of dbdeployer has gained the ability of deploying multiple sandboxes concurrently. Whenever we deploy a group of sandboxes (replication, multiple) we can use the --concurrent flag, telling dbdeployer that it should run operations concurrently.

What happens when a single sandbox gets deployed? There are six sets of operations:

1. Create the sandbox directory and write down its scripts;
2. Run the initialisation script;
3. Start the database server;
4. Run the pre-grants SQL commands (if any;)
5. Load the grants;
6. Run the post-grants SQL commands (if any;)

When several sandboxes are deployed concurrently, dbdeployer runs only the first step, and then creates a list of commands with an associated priority index. These commands are assembled for every sandbox, and then executed concurrently for every step.
The sequence of events for a deployment of three sandboxes in replication would be like this:

1. Create the sandbox skeleton for every sandbox;
2. Initialise all database servers;
3. start all the servers;
4. run the pre-grants, grants, post-grants scripts.
5. Runs the group initialisation script (start master and slaves, or setup group replication).

Depending on the computer architecture, the server version, and the number of nodes, the speed of deployment can increase from 2 to 5 times.

Let's see an example:

$ time dbdeployer deploy replication 5.7.21
[...]
real    0m13.789s
user    0m1.143s
sys 0m1.873s

$ time dbdeployer deploy replication 5.7.21 --concurrent
[...]
real    0m7.780s
user    0m1.329s
sys 0m1.811s

There is a significant speed increase. The gain rises sharply if we use an higher number of nodes.

$ time dbdeployer deploy replication 5.7.21 --nodes=5
[...]
real    0m23.425s
user    0m1.923s
sys 0m3.106s

$ time dbdeployer deploy replication 5.7.21 \
    --nodes=5 --concurrent
[...]
real    0m7.686s
user    0m2.248s
sys 0m2.777s

As we can see, the time for deploying 5 nodes is roughly the same used for 3 nodes. While the sequential operations take time proportionally with the number of nodes, the concurrent task stays almost constant.

Things a re a bit different for group replication, as the group initialisation (which happens after all the servers are up and running) takes more time than the simple master/slave deployment, and can't be easily reduced using the current code.

A similar optimisation happens when we delete multiple sandboxes. Here the operation is at sandbox level (1 replication cluster = 1 sandbox) not at server level, and for that reason the gain is less sharp. Still, operations are noticeably faster.

There is room for improvement, but I have seen that the total testing time for dbdeployer test suite has dropped from 26 to 15 minutes. I think it was a week end well spent.

Linux MySQL distros meeting in Brussels

When I saw Shlomi's post on why not to use apt-get or yum for MySQL, I thought immediately that his conclusions are quite reasonable. What you get from the Linux distributions is not the same thing that you find in the official MySQL downloads page. Now, whether you value more the completeness of the server or the ease of administration through the distribution installation tools, it's up to you and your business goals. We at the MySQL team have organized a meeting with the Linux distributions with the intent of finding out which differences and problems we may have with each other, and to solve them by improving communication. What follows is a summary of what happened in Brussels during the meeting.

Summary

Linux distributions ship MySQL products (Server, GUI Tools, connectors, Cluster) with different criteria and different grade of maturity, according to their own goals.
Due to lack of communication and policy conflicts, the distros almost always ship outdated versions of MySQL server and MySQL Cluster. The lag between the shipped version and the latest product shipped by MySQL ranges from a few months to several years.
By mutual understanding, the distros will now try to ship recent versions of Cluster (7.x) in a separate package.

Participants

(see some more pictures from the meeting).

Giuseppe Maxia, MySQL Community Team Lead, Italy
Tomas Ulin, MySQL VP Engineering, Sweden
Harmut Holzgraefe, MySQL Support, Germany
Lars Heill, MySQL Build, Norway (Trondheim)
Joro Kodinov, MySQL Engineering / 5.1, Bulgaria
Oden Eriksson, Mandriva, Sweden
Mathias Gug, Canonical/Ubuntu, Canada
Robin H. Johnson, Gentoo Linux, Canada
Michal Hrušecký, Novell/openSUSE, Czech Republic
Geir Høydalsvik, MySQL QA, Norway (Trondheim)
Norbert Tretkowski, Debian Linux, Germany
Kaj Arnö, MySQL VP Community Relations, Germany

Main issues from the distributions:

d1. Security bugs are invisible until MySQL releases a fix. They would like to get visibility of the bug report, to become aware of the problem and eventually help fixing it. We are looking into this matter.

d2. Due to lack of communication, the distros were running the test suite with different parameters. Gentoo packages the server with UTF-8 as default character set, and this causes several tests to fail. Our QA team is looking into it.

d3. Bug databases are different for each distros. They usually solve problems on their own, or send the issue upstream (to the MySQL team at Sun, now Oracle) when it is a legitimate bug.

d4. Debian and Ubuntu don't apply all our patches to the server that they ship. They only apply security bugs and fix for bugs that don't introduce new or changed functionality. This is, IMO, mostly a matter of terminology, since the new functionality is only added as a side effect of fixing a bug. For example, when we fixed Bug#49222: Mark RAND() as unsafe, there is a change in functionality. Now RAND() is logged in ROW format, as it should have been in the first place. It is indeed a new functionality, but as a user I would rather have this bug fix in my server, than adhering to the strict rules of no changes.

d5. GUI tools are still shipped as current although they aren't actively supported, with patches provided by OpenSuse.

d6. While we provide specifications for .rpm packages, we don't do that for .deb ones. Debian/Ubuntu ask if we can include them in our code.

Main issues from the MySQL team:

m1. Cluster packages are outdated. Mainly for miscommunication, some distros are building the cluster binaries with the server package, thus shipping quite old and non-functional cluster binaries. After an explanation on the Cluster roadmap, the distros agreed to ship 7.x binaries from now on. We agreed that we will modify the build scripts in the server to avoid compiling the cluster binaries unintentionally.

m2. MySQL Workbench is not included in the stable releases. There are two reasons: it is not GA yet, and its source includes non-GPL code (for Windows and Mac) that needs to be removed before being used by Debian and derivatives. Moreover, Debian communicates that some DBAs don't like the idea of deploying a design tool for daily database administration.

Conclusions

All in all, I feel that this meeting was a success. We achieved a lot during the proceedings, solving problems ranging from simple communication mismatches to neglected bugs. And meeting in person with the ones who deal actively with MySQL in the Linux distros is quite a rewarding experience.
From a technical standpoint, I hadn't realized that every distribution is shipping a different server. That is quite a challenge for the common users who may need to choose between versions in several sites. However, this meeting has also shown me that all the participants have very high quality standards, and the difference in shipped versions is mostly due to the peculiarity of many shipping calendars.
Thanks to all the participants. We fixed many issues, and we had lots of fun at the same time. We should do that more often!

MySQL virtual columns

Wouldn't it be cool to enhance MySQL creation table syntax to do this?

CREATE TABLE t1 (
  my_date DATE,
  my_month VIRTUAL INT AS (DATE_FORMAT(d,'%Y%m'))
);

And later, when you insert '2008-08-23' in my_date, then automatically my_month will be set to 200808.
Yes, it would be cool, and it has been done. Andrey Zhakov, a so far unknown to me community contributor, has extended the server code to implement virtual columns (WL#411) and functional indexes (WL#1075)
Andrey has published the code on Launchpad and has published detailed documentation in MySQL Forge. (A Virtual Column Preview and the refernce manual).
I still know very little of Andrey, but I am going to find out more soon. For now, I know that his code works as advertised. You can create virtual columns that are calculated at run time, and optionally stored in the table. Compared to views, this approach is much faster, because it calculates only during insert and updates, not during selects. Compared to triggers, virtual columns do the same work of INSERT and UPDATE triggers combined. And not only that.
As you know, I am an inquisitive person, and I don't accept a technical claim blindly. I need to test it with my own data before believing it. And so I did.
I built the code from the bazaar repository, and I modified a table from my employees test database.
Take for example the salaries table, and suppose you want to enhance it to include the difference between from_date and to_date. We're going to do it in three different ways. In the simple way, we add an INTEGER column, and we run an UPDATE statement after inserting 2.4 million records.

mysql> CREATE TABLE salaries (
    emp_no      INT             NOT NULL,
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    ddiff       int,
    PRIMARY KEY (emp_no, from_date)
); 

$ time ../use simple < load_salaries.dump

real 0m30.884s
user 0m3.129s
sys 0m0.367s

$ time ../use simple -e "update salaries set ddiff=datediff(to_date,from_date)"

real 0m17.774s
user 0m0.005s
sys 0m0.008s

Cost of the whole operation (insert + update) was about 48 seconds.
The second way is using a trigger. The same table structure, with an insert trigger.

mysql> CREATE TRIGGER bi_salaries
before insert on salaries
for each row
    set new.ddiff=datediff(new.to_date,new.from_date);

$ time ../use with_trigger < load_salaries.dump

real 1m18.370s
user 0m3.134s
sys 0m0.372s

Uh oh! Quite a overhead, I would say. The trigger takes 30 seconds more than combining INSERT and UPDATE.
The third way is with the virtual column.

mysql> CREATE TABLE salaries (
    emp_no      INT             NOT NULL,
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    ddiff       virtual int as (datediff(to_date, from_date)) stored,
    PRIMARY KEY (emp_no, from_date)
); 

$ time ../use with_virtual < load_salaries.dump

real 0m31.807s
user 0m3.137s
sys 0m0.369s

Wow! I was expecting some improvement in performance, but this is really cool!
Almost three times faster than triggers!
We need to prove that the data is correct before rejoicing. So I ran this query for each table

mysql> select count(*), sum(ddiff), avg(ddiff) from salaries;
+----------+--------------+-------------+
| count(*) | sum(ddiff)   | avg(ddiff)  |
+----------+--------------+-------------+
|  2844047 | 702296791062 | 246935.7191 | 
+----------+--------------+-------------+

And the result was the same for all of them. Virtual columns rock!