How to get your proposal accepted to the MySQL Users Conference 2009

The call for papers for the MySQL Users Conference and Expo 2009 is open. Proposals are accepted until October 22, 2008. This post will tell you how to get your proposal accepted. First: READ the following posts. I mean it! Baron Schwartz's advice on how to write a great proposal. If you follow these guidelines, you can't be wrong. Colin's list of 10. If you still had doubts after reading Baron's post, this one will clear your mind.

Sorry is we sound harsh, but we have the responsibility for the quality of MySQL UC sessions, and the only way to ensure quality is to be picky in our choice. Here are a few more rules of the game for the next conference.

Make sure you know the subject you propose.

We may ask you to prove your claims. We have had a few surprises during the last conference, with a couple of sessions lasting 20 minutes because the speaker had exhausted the topics. We are going to be stricter in our acceptance. We will accept some proposals conditionally. If that happens, we will inform the authors that we need more material before the final acceptance. If the material is not provided, we replace the session with some other proposal.

Always assume that someone else has proposed the same topic.

The competition is fierce. We usually reject two proposals for each one we accept. If you want your proposal to be selected, don't be lazy. Write a comprehensible abstract and a good bio. Work hard on it. For each subject, there are probably two or three more people who have spent days polishing their proposal. We will be only too happy to choose the best one! If you put together a half baked proposal in 5 minutes, you won't get in. Period.

A good abstract is not too short.

If your abstract is just a few lines promising the wonders that everyone knows you can deliver, that won't do. You may be a well known big shot in the field, but we won't default to "accepted" if we recognize your name (see what Colin says about rock stars). If that's the case, prove your status by writing a superb proposal.

A good abstract is not too long.

If you have written an article on a given topic and you simply cut and paste 13,000 words in the abstract box, it will only show us that you can't summarize, and your session would be unbearably boring.

Don't even try to advertise your company in a proposal.

The review committee is full of open source enthusiasts and technology lovers. We smell a stealthy marketing message a mile away. If you want to boost your company business, don't propose a session, but look at the conference page for sponsorship opportunities.

Read the above posts by Baron and Colin again.

Seriously. If you want that pass for the conference and a podium to greatness, you need to work hard. How hard? well, consider that, according to Damian Conway, you need to prepare from 10 to 50 hours for each hour of presentation. Some of this work must show in your abstract. If you read the above posts and follow the advice you are given, some of the hard work will show for sure.

All done? Sure? Have you read all the above twice? Good. Then, SUBMIT A PROPOSAL!.

Thanks GNU!

I met GNU for the first time fifteen years ago. I was working as a consultant in support of a criminal investigation, and as part of my duties I had to analyze a database hosted on a SCO server.
As often happens, the database was proprietary, and it did not include any facility to analyze data. I needed to build an application to explore the data thoroughly.
Most of my working libraries were written in C, which I used in other operating systems. So I contacted SCO and asked to buy a C compiler. I was told that it would cost me quite a lot (I was prepared for that, although not for the price they told me, but since I was going to expense it, I would not care), and that it would take one month to get the software. I did not have one month at my disposal. I needed to nail down the evidence for the investigation immediately. So I started asking around. I was doing some side work for my town university, and I mentioned the difficulty of getting the right software.
Then, a friend said: "You may use Linux." And this was the first time I heard the name. And I also heard about the GNU C compiler that looked like the right tool for the job.
So, instead of buying a software package, I bought a tape streamer, and transferred all the SCO server contents into tapes. Then I had fun installing my first Linux server (version 0.99c) and loaded all the SCO material into a free disk.
In less than two days, using gcc, I had in place all the analysis tools that I needed, and soon I delivered all the goods that my employer wanted.
That was one of the most satisfactory jobs in my career, and it had the side effect of leaving me with a server full of free software that did exactly what I wanted.
Since then, my relationship with GNU and Linux has evolved continuously, and by now I can say that GNU software has contributed largely to my professional growth, accomplishment, and satisfaction, from my consulting days to my employment with MySQL and Sun.
GNU is older than fifteen. It is in fact celebrating today its 25th birthday. It has been a bumpy ride. GNU has got much support and much debate. It is almost ubiquitous, but it is not as respected and recognized as it should be.
For my side, I am happy with all the GNU project has contributed to the evolution of the IT industry. Thanks, GNU. My professional life would be much different without free software!

Introducing the MySQL community-driven Replication Monitoring Tools

If you are using MySQL replication, you know how hard is to monitor it properly. You have a wide choice of commercial and free tools, all of which check the health of your replication system from the outside. A few years ago, I wrote an article advocating a self-monitoring and self-healing replication system, using new features in MySQL 5.1. At the time, there were some missing technology pieces to make this project feasible. Now the pieces exist, and you can create your own self monitoring replication system.

Hartmut rules!

It started during FrOSCon, when I discussed with Hartmut Holzgraefe the practical application of a plugin that he wrote some time ago. One of the missing pieces for the internal replication monitoring is the ability of reading replication values into variables. Currently, you can see the replication status with SHOW MASTER STATUS and SHOW SLAVE STATUS. Unfortunately, you can't manipulate the values from these commands inside a stored routines. And thus there is no way of measuring the health of a replication system without the help of external programming languages.
This means that, even if you manage to detect a master failure, you can't create a CHANGE MASTER STATUS with the right parameters.
During our FrOSCon talks, Hartmut released a small Launchpad project, the MySQL replication status INFORMATION_SCHEMA plugin, which implements two INFORMATION_SCHEMA tables for MASTER and SLAVE STATUS. Using these extensions, you can get single values inside a stored routine.

The Replication Monitor is born

From this starting point, the Replication Monitoring Tools become possible.
The project is young, and it only contains a proof-of-concept monitoring tool (see below), but given enough time and help, it can be expanded into a full fledged system.
The roadmap has a long list of possible features, some of which are quite ambitious

The project is open. I need ideas, manpower, testers, to implement all the intended features. This is a project from the community to the community.
The principle that you have seen several times when downloading MySQL server fully applies. If you have time, you can save money and help yourself and the community at the same time.

Replication Monitor in action

The proof of concept implements a table on the master, where the slaves write their status, by means of federated tables.
Each slave uses a federated table to the master INFORMATION_SCHEMA.MASTER_STATUS to compare its status with the master,a nd a second federated table to write its status to the master table. The result is that you can read the global status of a replication system in the master, and in each slave (because the table is replicated).

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |      82321 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000001 |      82530 | IO+,SQL+,P+,E+ |
|       103 | mysql-bin.000001 |      82739 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000001 |      82948 | IO+,SQL+,P+,E+ |
+-----------+------------------+------------+----------------+

The status reads as follows:
IO+ means that the slave IO_thread is working (the opposite is io-.
SQL+ means that the SQL thread is working.
P+ means that the slave is reading from the latest binlog and that the position is equal or greater to the one shown by the master.
E+ means that the slave has executed all the statements received from the master.
Let's try a small experiment. We stop slave 2 and see what happens.

slave2> stop slave;

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |      91517 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000001 |      91517 | io-,sql-,p-    |
|       103 | mysql-bin.000001 |      91932 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000001 |      92141 | IO+,SQL+,P+,E+ |
+-----------+------------------+------------+----------------+

The monitor shows that slave 2 is not working. If we restart the slave, the situation is restored.

slave2> start slave;

select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |     114894 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000001 |     115104 | IO+,SQL+,P+,E+ |
|       103 | mysql-bin.000001 |     115314 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000001 |     115524 | IO+,SQL+,P+,E+ |
+-----------+------------------+------------+----------------+

We can do the same experiment while loading some heavy data, like the employee sample database.

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |     134158 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000001 |     134975 | IO+,SQL+,p-    |
|       103 | mysql-bin.000001 |     133777 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000001 |    1165155 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |    7343962 | IO+,SQL+,P+,e- |
|       102 | mysql-bin.000001 |    8374099 | IO+,SQL+,P+,e- |
|       103 | mysql-bin.000001 |    7343751 | IO+,SQL+,P+,e- |
|       104 | mysql-bin.000001 |    8374310 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

Very rapidly (the experiment happens in a MySQL Sandbox, so all servers use the same disk and CPU), the slaves are left behind in execution.
If we stop a slave while loading, the situation is even more explicit.

slave3> stop slave;

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |   45764491 | IO+,SQL+,P+,e- |
|       102 | mysql-bin.000001 |   45764703 | IO+,SQL+,P+,e- |
|       103 | mysql-bin.000001 |   42685103 | io-,sql-,p-    |
|       104 | mysql-bin.000001 |   42685103 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

Let's introduce a new element of difference, and flush logs while still loading data.

master> flush logs;

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000002 |    2044673 | IO+,SQL+,P+,e- |
|       102 | mysql-bin.000002 |    3066965 | IO+,SQL+,P+,e- |
|       103 | mysql-bin.000001 |   42685103 | io-,sql-,p-    |
|       104 | mysql-bin.000002 |    3067176 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

Now the stopped slave is really far behind. Let's see what happens when we put it back online.

slave3> start slave;

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000002 |   27604369 | IO+,SQL+,P+,e- |
|       102 | mysql-bin.000002 |   27603945 | IO+,SQL+,P+,e- |
|       103 | mysql-bin.000002 |   27604157 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000002 |   25558385 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

The restored slave was the first one to catch up in execution. This would not probably happen in a situation with separate hosts for each slave, but it's interesting to test our system.
And finally, after a few seconds more, all slaves have caught up with the master, with both data fetching and execution.

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000002 |  104641288 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000002 |  104641501 | IO+,SQL+,P+,E+ |
|       103 | mysql-bin.000002 |  104641714 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000002 |  104641927 | IO+,SQL+,P+,E+ |
+-----------+------------------+------------+----------------+

Getting started

There is a step-by-step set of instructions in MySQL Forge Wiki that explain how to build and install the necessary plugin.
After that, you should get the code from the bazaar repository and make the changes in the option files of master and slaves (check the sql/master_scripts and sql/slave_scripts directories). Then, you should load the initializing script in the master and in each slave, and you are in business.
Notice that this first version relies on a MySQL Sandbox running with MySQL 5.1.28, compiled from source. Further versions will lift this limitation, but for now, if you want to try it out, you need to follow my steps closely.

Next steps

What's left? Ah, yes. A totally internal monitoring system is not much helpful. If the DBA needs to run a query to know what's happening, then the system is almost worthless.
This is another challenge, because MySQL architecture forbids connection to operating system services such as mail applications. There is already a solution to this problem. We only need to integrate it with the rest of the replication monitoring system. More challenges are outlined in the roadmap.
If you think this is interesting, check the roadmap, and comment on it. Even better, if you want to implement parts of it, join the sandbox developers group and start coding!

Slides of my FrOSCon presentations

Thanks to all who attended my talks at FrOSCon 2008. It was a pleasurable experience, as it has always been in the past.
The slides of my two sessions are published in the MySQL Presentation Repository

• MySQL Community How To
• MySQL Sandbox

The ephemeral glory of books

A picture says it all. Special offers at a FrOSCon books booth.
For how long is an IT book in its prime?

MySQL at FrOSCon 2008

MySQL will be well represented at FrOSCon 2008. August 23-24 2008.
This will be my third presence there as a speaker. I fondly remember the pleasant experience of the 2006 and 2007 editions.

This time, MySQL is also a sponsor. In addition to having 5 speakers from the database group, we will also have a table and a BoF (MySQL 5.1 and beyond).
The list of speakers and talks makes you want to be there. (I hope)

Giuseppe Maxia	MySQL Community How To MySQL Sandbox
Lenz Grimmer	bzr - The Bazaar source revision control system OpenSolaris for Linux users (in German)
Hartmut Holzgraefe	A code generator for PHP extensions
Johannes Schlüter	PHP 5.3 - the state of the art
Susanne Ebrecht	PostgreSQL Workshop

If you are in the area, come and say hello!

Editing a dump file from the airport

Keith Murphy asks if you have ever edited your dump files before feeding them to the database. And he recommends using sed. This reminds me of a war story, when I was consulting in Milan. The customer needed, among other things, to import a huge text file (created by a third party application) into a MySQL database. I did everything, including a cron job to load the data every day. By the time I left for the airport, the third party application hadn't yet provided its first sample, but since we knew the fields list, it was a simple case of creating a LOAD DATA INFILE command.
I was boarding my plane, when the customer called, saying that the application was giving dates in the wrong format. Instead of 'YYYY-MM-DD', it was using the Italian human readable format 'DD/MM/YYYY'. No Internet was available, and with just a few minutes before being confined to the airplane, I told the customer "I will send you a text message. Just pipe this command before feeding the file to the database."
And I sent this message:

perl -pe 's{(\d\d)/{\d\d}/{\d\d\d\d}}{$3-$2-$1}g'

I took a picture of my phone before sending the message.

When I landed, I got a text message from the customer. Everything worked fine.
So, that was when I edited 2 GB data file from the airport, using a mobile phone.

The database hacker glossary and other (funny) stories

Every year, MySQL engineers gather together for the developers meeting. It's the time of the highest productivity and fun at the same time. It starts usually by performing an old play, Guess who I am, featuring 100 employees who know each other by name but not by face. At every new entrance, a flurry of introductions is acknowledged and promptly forgotten, leaving the unfamiliar faces deprived of the familiar names. So a few minutes later the same people run around each other again, staring nonchalantly at the name tags, trying to avoid a gaffe. Despite the fierce fight for a name, nobody gets hurt, and in the end the leading actors and support roles go back to the script of exchanging geeky thoughts in person, as confidently as if they were doing that through the IRC. During these meetings, the following glossary was developed. Its origins lost in the fumes of beer. This blog post is a cherry picking collection from the daily newsletter (Heidelberg NachtRichten) distributed during the latest developers meeting.

(Warning! Dangerous silly humor ahead!)

database hacker glossary

• Primary key. The key that you get first from your keyring when trying to open the door. Unfortunately, it's usually the wrong one.
• Table. A piece of furniture that contains records instead of cutlery.
• Cursor. Someone who curses, especially after noticing how slow cursors are.
• Buffer overflow. Drinking more beer than your stomach can hold.
• Morning. Start of the work day for managers.
• Afternoon. Time of day expressly invented to make conversation difficult.
• Evening. Time for honest developers to wake up.
• Night. Time to do serious work.
• Specifications. Things that developers will eventually write after seeing how their own application works.
• User requirements. Unnecessary burdens to developers creativity.
• Test suite. A useful invention created to give you time to drink coffee.
• Weekly report. The output of a clever Perl script, producing random text that managers pretend to understand.
• Developers meeting. The funniest time of the year, and getting paid for it!
• Line manager. A former developer suffering from ERS (Early Rising Syndrome)
• Vice President. A former manager too lazy to go back to developing duties.
• Keyboard. Natural extension of the hands for real people.

An ancient legend

When Sir Geoffrey Hawks went to fight the First Crusade, in A.D. 1096, he left a gift to his young wife, a chastity belt chiseled by the finest artisans in Spain, a jewel of efficiency, as beautiful as robust, able to protect her honor while he was abroad. The belt was kept closed by two engraved keys, also work of the same Spanish craftsmen. He made sure that she wore the belt, locked both locks, put the keys in his purse, and went to war. Three years passed, without any news. The lady was restless and bored, and she welcomed the arrival of a minstrel from the South as a much needed distraction. The minstrel was skilled in his arts, and soon he managed to be in close intimacy with the lady. He managed to overcome all her defenses, but he found the disappointing obstacle of the chastity belt. He then applied his skills to the locks, to no avail. He had a set of keys, built by a blacksmith in London, but every attempt at violating the locks fail. With a sad and defeated sigh, the lady acknowledged that she would never achieve her pleasure without the precious keys made by the Spanish craftsmen. She admitted defeat. "I will never be happy - she told her lover - without those foreign keys!"

Final item of the hacker glossary

• Foreign key. A key to open a foreign lock.

Developers bowing protocol

You may have seen this scene several times during any open source meeting. Two people approach each other cautiously, stop at a short distance, and bow curtly to each other for a few seconds. After what seems a very formal kind of greetings, they break in broad smiles and exclamations, and shake hands vigorously. That is not a new policy from the HR department. What looks like a bow is in fact the reading of the name tag, and the subsequent explosion of joy is just the recognition of a name that until now had been only a signature under an email message. Once the association between the known name and the unknown person has been established, they can start being friendly as if they had known each other faces for years.

Don't guess. Test! - A sample database with test suite

Some time ago, with the help of Patrick Crews, I built a sample database for testing.
Now this database is published as a stand-alone project on Launchpad.

What's special about it?
Unlike the previous databases used in MySQL documentation and tutorials, this database has some weight. The total data is over 160 MB, distributed across 6 table, for a total of about 4 million records. It is not huge, but it is large enough to be non-trivial.
The second important feature is that this database comes with a test suite. This will allow you to make sure that you have loaded the right data.

Getting started

Using the sample database is trivial.
Open the test db downloads page and get the current full archive (25 MB) containing the installation script, the dump files, and the test suite. Then, expand it.

$ tar -xjf $HOME/Downloads/employees_db-full-1.0.4.tar.bz2
$ cd employees_db/
$ vim employees.sql

Edit the employee.sql script, and uncomment the storage engine that you want to use for your tests. By default, it uses InnoDB.

   set storage_engine = InnoDB;
-- set storage_engine = MyISAM;
-- set storage_engine = Falcon;
-- set storage_engine = PBXT;
-- set storage_engine = Maria;

Then, load the files.

$ mysql -t < employees.sql
+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO                   |
+------------------------+
| storage engine: MyISAM |
+------------------------+
+---------------------+
| INFO                |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO              |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO             |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO                 |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO           |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO             |
+------------------+
| LOADING salaries |
+------------------+

Finally, test that your data is what it is supposed to be. The test suite include two methods, one with MD5 and one with SHA1.

$ time mysql -t < test_employees_sha.sql
+----------------------+
| INFO                 |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+------------------------------------------+
| table_name   | expected_records | expected_crc                             |
+--------------+------------------+------------------------------------------+
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp     |           331603 | f16f6ce609d032d6b1b34748421e9195c5083da8 |
| titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+
+--------------+------------------+------------------------------------------+
| table_name   | found_records    | found_crc                                |
+--------------+------------------+------------------------------------------+
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp     |           331603 | f16f6ce609d032d6b1b34748421e9195c5083da8 |
| titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+
+--------------+---------------+-----------+
| table_name   | records_match | crc_match |
+--------------+---------------+-----------+
| employees    | OK            | ok        |
| departments  | OK            | ok        |
| dept_manager | OK            | ok        |
| dept_emp     | OK            | ok        |
| titles       | OK            | ok        |
| salaries     | OK            | ok        |
+--------------+---------------+-----------+

real 0m37.067s
user 0m0.007s
sys 0m0.009s

$ time mysql -t < test_employees_md5.sql
+----------------------+
| INFO                 |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+----------------------------------+
| table_name   | expected_records | expected_crc                     |
+--------------+------------------+----------------------------------+
| employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
| dept_emp     |           331603 | c2c4fc7f0506e50959a6c67ad55cac31 |
| titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
| salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
+--------------+------------------+----------------------------------+
+--------------+------------------+----------------------------------+
| table_name   | found_records    | found_crc                        |
+--------------+------------------+----------------------------------+
| employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
| dept_emp     |           331603 | c2c4fc7f0506e50959a6c67ad55cac31 |
| titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
| salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
+--------------+------------------+----------------------------------+
+--------------+---------------+-----------+
| table_name   | records_match | crc_match |
+--------------+---------------+-----------+
| employees    | OK            | ok        |
| departments  | OK            | ok        |
| dept_manager | OK            | ok        |
| dept_emp     | OK            | ok        |
| titles       | OK            | ok        |
| salaries     | OK            | ok        |
+--------------+---------------+-----------+

real 0m33.453s
user 0m0.007s
sys 0m0.009s

Now we're ready to use it.
The test-db project is open. Participate!

OSCON in pictures

There was a huge Sun booth at OSCON 2008. During opening hours, there were always sessions at the booth, with well known open source presenters.


After hours, you had the choice between socializing on your own or go to the organized events or go to the BoFs, like this one, where Monty presented what he called "community services" in the shape of black vodka mixed to mint candies.

The event was, of course, the Sun Party, where many strange things happened.

Jay challenged Ronald to a sumo wrestling match, only to bite the dust repeatedly.

The match of the evening was Monty (MySQL) vs. Josh Berkus (PostgreSQL).

MySQL outperformed PostgreSQL in one of the most unusual comparison in the history of these projects!

More pictures from Terri and Barton.

Julian Cash at OSCON 2008

Julian Cash is well known, among other things, for being the photographer of the open source communities, as you can see from his photo stream, or this picture of his newest book.

For more books, you can look at The human creativity project.
At OSCON 2008, participants could attach one or more ribbons to their badge, to state their affiliation with a project or an idea. When the sought ribbon was not available, there were blank ones that users could fill with their favorite topics.
As you can see here, I made free use of the ribbons. Julian Cash, instead, stated his affiliation with Perl quite strongly.

Thanks for sharing your art, Julian!

OSCON 2008 - MySQL Proxy - from architecture to implementation

The presentation about MySQL Proxy at OSCON 2008 is over.

Here are the slides.

Thanks to my co-presenter Ronald Bradford and to all the participants. If you have more questions about the session, please use this blog's comments.

Some quirks of circular and row-based replication

One of the new features introduced by MySQL 5.1 is row-based replication.

Unlike the classic statement-based replication, used in MySQL up to version 5.0, row-based replication transfers the data instead of the statement used to create it.

If you want to have a taste of row-based replication, you can do some experiments with MySQL Sandbox.

First, we create a sandbox of circular replication with MySQL 5.0

./make_replication_sandbox --topology=circular --how_many_nodes=3 \
/path/to/mysql-5.0.51a-YOUR_OS.tar.gz

cd $HOME/sandboxes/rcsandbox_5.0.51
./n1 -e "create table test.t1(i int)"
./n3 -e "insert into test.t1 values (@@server_id)"
./use_all "select * from test.t1"
# server: 1:
i
101
# server: 2:
i
102
# server: 3:
i
103

This is statement-based replication at its best.

A look at the binary log confirms that this is the case.

 $ ./node3/my sqlbinlog node3/data/mysql-bin.000001 |tail
#080720  8:57:59 server id 103  end_log_pos 1958    Query   thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1216569479/*!*/;
SET @@session.auto_increment_increment=3, @@session.auto_increment_offset=3/*!*/;
insert into test.t1 values (@@server_id)/*!*/;

It may be desirable to preserve the different variables in every slave, and I know of a few schemes where it is useful, but in most cases a user wants the data on master and slaves to be always the same (think of backup slaves, for instances), and therefore the statement-based replication should be considered faulty in this respect.

Let's do the same experiment with the latest 5.1

./make_replication_sandbox --topology=circular --how_many_nodes=3 \
/path/to/mysql-5.1.26-rc-YOUR_OS.tar.gz

cd $HOME/sandboxes/rcsandbox_5.0.51
./n1 -e "create table test.t1(i int)"
./n3 -e "insert into test.t1 values (@@server_id)"
./use_all "select * from test.t1"
# server: 1:
i
103
# server: 2:
i
103
# server: 3:
i
103

Now, that's different. We can see row-based replication in action. And we can see that the binary log looks a lot different.

 $ ./node3/my sqlbinlog node3/data/mysql-bin.000001 |tail
#080720  9:05:00 server id 103  end_log_pos 3247        Query   thread_id=25    exec_time=0     error_code=0
SET TIMESTAMP=1216569900/*!*/;
SET @@session.auto_increment_increment=3, @@session.auto_increment_offset=3/*!*/;
BEGIN
/*!*/;
# at 3247
# at 3288
#080720  9:05:00 server id 103  end_log_pos 3288        Table_map: `test`.`t1` mapped to number 19
#080720  9:05:00 server id 103  end_log_pos 3322        Write_rows: table id 19 flags: STMT_END_F

BINLOG '
LGKDSBNnAAAAKQAAANgMAAAAABMAAAAAAAAABHRlc3QAAnQxAAEDAAE=
LGKDSBdnAAAAIgAAAPoMAAAQABMAAAAAAAEAAf/+ZwAAAA==
'/*!*/;
# at 3322
#080720  9:05:00 server id 103  end_log_pos 3392        Query   thread_id=25    exec_time=0     error_code=0
SET TIMESTAMP=1216569900/*!*/;
COMMIT

So far, all is as expected (if you have been keeping up with the documentation, of course). The default binlog_format in MySQL 5.1 is mixed, meaning that the server will use statement-based as much as possible, switching to row-based when there is the risk of incorrect replication, like in this case.

Users can change binlog_format at will, thus influencing how replication is performed. However, in the case of circular replication, you must be extra careful, because you don't have control on how the other nodes are behaving.

For example.

$ ./n1 -e "truncate test.t1"
$ ./n3 -e "set binlog_format='statement';insert into test.t1 values (@@server_id)"
$ ./use_all "select * from test.t1"
# server: 1:
i
101
# server: 2:
i
101
# server: 3:
i
103

WTF? Why server 1 and server 3 have inserted their corresponding server ids, and server 2 has not?

Let's do some debugging.

1. Server 3 inserts @@server_id with statement-based replication, overriding the defaults.
2. Server 3's slave, server 1, executes the statement. But its default binlog_format is still "row", not "statement".
3. Server 2, slave of server 1, receives a row-based chunk from the binary log, and therefore inserts the value of its master, because the statement was not passed along.

Using row-based format explicitly would not cause any side effect.

$ ./n1 -e "truncate test.t1"
$ ./n3 -e "set binlog_format='row';insert into test.t1 values (@@server_id)"
$ ./use_all "select * from test.t1"
# server: 1:
i
103
# server: 2:
i
103
# server: 3:
i
103

Looking for really useful uses of row-based replication, we can experience the greatest advantage (compared to statement-based) when inserting the result of an expensive calculation. Let's try. First of all, we force row-based replication on all nodes permanently.

$ ./use_all 'set global binlog_format="row"'

Then we execute an expensive query, and see the results.

$ ./n3 -e "insert into test.t1 select count(*) from information_schema.columns"
$ ./use_all "show global status like 'opened_tables' "
# server: 1:
Variable_name   Value
Opened_tables   17
# server: 2:
Variable_name   Value
Opened_tables   17
# server: 3:
Variable_name   Value
Opened_tables   41

If you know how information_schema works in MySQL, you know that if you want to get statistics on the number of columns, the server has to open all the tables and count them. Server 3 has done exactly that, and the number of opened tables is more than double than its fellows servers.

This is a useful operation to perform when you know that the master is inserting the result of an expensive calculation, and you don't want to delay the slaves.

Switching to row-based replication permanently would be a mistake, though. Consider a simple update like this one:

UPDATE huge_table set some_column = 'some value' where some_other_column = 'X';

This would be relatively inexpensive in statement-based replication. If the table has 200 columns, though, using row-based format your binary log will pass the whole record to the slaves.
Used with care, row-based replication can deliver good results.

Proxy webinar - slides, script, FAQ

We did it. Designing Scalable Architectures with MySQL Proxy was delivered successfully, with over 150 attendees.
There is a large number of questions that were asked during the session, and you can find them in MySQL Proxy FAQ.
The slides, with the highly entertaining images used by John Loehrer to illustrate his point are also online
Finally, John posted his Connection pooler Lua script in the Forge.
Thanks to John Loehrer for his lively presentation, to Jimmy Guerrero and Rich Taylor for organizing the event, to Jan Kneschke, for answering questions online while we were talking, and to all the attendees, for showing such degree of interest.
And don't forget that there is still a quick poll on the future of MySQL Proxy that you can still vote on.

Shaping the future of MySQL Proxy

Here is a chance for all users to influence the future development of MySQL Proxy. If you care about Proxy, you may want to check the current quickpoll in the Dev Zone, and vote for your favorite features. The developers have a truckload of ideas, of course, but only a finite amount of time. So they can't develop all the features at once. They must start somewhere, and your vote can help them decide which ones should get higher priority.

In the meantime, don't forget the incoming webinar on Designing Scalable Architectures with MySQL Proxy. It's TODAY (tomorrow for some, depending on your time zone), July 8, 2008 at 10am PDT, 1pm EDT, 7pm CEST.

Even faster online backup!

I discussed my findings with Guilhem Bichot, one of the online backup creators, and he remarks: You could also try export MYISAM_BACKUP_NO_INDEX=1 before starting mysqld. It should not backup index (and rebuild them at repair time). Should make a smaller backup and a longer restore. I am not really looking for a longer restore, but let's give it a try. I restarted the database with the suggested option, and here is what I got:

backup database employees to 'emp2.bkp';
+-----------+
| backup_id |
+-----------+
| 4         | 
+-----------+
1 row in set (3.18 sec)

drop schema employees;
Query OK, 10 rows affected (0.02 sec)

restore from 'emp2.bkp';
+-----------+
| backup_id |
+-----------+
| 5         | 
+-----------+
1 row in set (18.33 sec)

The backup is definitely faster, and the restore is slower than the 11 seconds I got with the previous test. However, it's much faster than the standard restore!
Moreover, the backup file is barely 70 MB. That's an excellent compromise. Perhaps it won't be that fast with a several GB of data, but for now, it looks very good.

Faster online backup with MyISAM driver

Remember the first test of online backup? I tested the new feature, which was performing quite well, compared to mysqldump. OK. Get ready for a surprise.
I tested the native MyISAM driver from the mysql-6.0-backup tree, and I compared the results with the normal backup.

version	backup time	restore time
standard	25.58	79.11
MyISAM driver	4.15	11.53

Please be amazed!
The difference is also visible from the metadata. The standard version says:

select * from mysql.online_backup\G
*************************** 1. row ***************************
      backup_id: 1
     process_id: 0
     binlog_pos: 0
    binlog_file: NULL
   backup_state: complete
      operation: backup
      error_num: 0
    num_objects: 8
    total_bytes: 69970045
validity_point_time: 2008-07-07 11:39:04
     start_time: 2008-07-07 11:38:39
      stop_time: 2008-07-07 11:39:04
host_or_server_name: localhost
       username: msandbox
    backup_file: emp.bkp
   user_comment:
        command: backup database employees to 'emp.bkp'
        engines: Default, Snapshot

And the native driver version says:

select * from mysql.online_backup\G
*************************** 1. row ***************************
      backup_id: 1
     process_id: 0
     binlog_pos: 0
    binlog_file: NULL
   backup_state: complete
      operation: backup
      error_num: 0
    num_objects: 8
    total_bytes: 335531054
validity_point_time: 2008-07-07 11:32:04
     start_time: 2008-07-07 11:32:00
      stop_time: 2008-07-07 11:32:04
host_or_server_name: localhost
       username: msandbox
    backup_file: emp.bkp
   user_comment:
        command: backup database employees to 'emp.bkp'
        engines: Snapshot, MyISAM

Doesn't it just make you want to rush to download the code at once?
The information about total_bytes is somehow puzzling. The size of the backup file is actually 89 MB for the standard version and 160 MB for the MyISAM driver. Thus, you pay a faster backup and restore with a bigger file. Personally, I can live with that.


As you may suspect, the picture does not have any relationship with backup, but it's cute, isn't it?

MySQL Sandbox 2.0 has been released

Taking advantage of Launchpad excellent development services, I completed the tasks necessary to release MySQL Sandbox 2.0. So, you may ask, what's new? Quite a lot, actually: more intuitive script names master sandbox directory faster supports circular replication more error checking includes a test suite uses a default options file

more intuitive script names

One of the problem in the old sandbox was that the names of the scripts suggested some installation of the sandbox itself. This has been addressed in version 2.0. You now have the very clear names:

 make_sandbox                  creates a single sandbox
make_replication_sandbox      creates a replication sandbox
make_multiple_sandbox         creates sandbox containing many servers
                                of the same version
make_multiple_custom_sandbox  creates a sandbox containing many servers
                                of different versions

master sandbox directory

By default, MySQL Sandbox will create a directory under your home, named "sandboxes", and all sandboxes will be created under that directory. In addition of making your sandboxes more organized, the master directory provides some handy scripts to deal with many sandboxes at once. You can start, stop, or send a SQL command to all the sandboxes with just one command.

faster

The Sandbox had a few "sleep" instructions, to wait for the server being installed to come online. This solution was slowing down fast machines, where the server can start much faster than the standard 3 seconds I added to the sandbox, but was sometimes not enough for slower machines, where the server may take more than 3 seconds to start up, especially with replication. The new sandbox uses a loop coupled with a timeout limit. Thus, quick machines are faster, and slow machines don't fail.

supports circular replication

Now you can create a replication ring.

./make_replication_sandbox --topology=circular --how_many_nodes=5 VERSION
# or
./make_replication_sandbox --master_master VERSION

includes a test suite

If you have resources and MySQL binary tarballs at your disposal, you can stress test MySQL Sandbox (and your box!) with this test suite. For example, assuming that your 5.0.51 and 5.1.25 binaries are in $HOME/opt/mysql

./test/test_sandbox.pl --versions=5.1.51,5.1.25

This will create 22 sandboxes

more error checking

This is a direct consequence of the above item. Having a test suite has made the sandbox much more robust.

uses a default options file

There is also a default option file, where you can keep the options that you need to be executed always.

Try it!

Go get the 2.0 tarball from launchpad. If you find any bugs, there is a very convenient bugs reporting system. Please use it!

OSCON 2008 - Getting in touch with the open source makers

For some reasons, I have never been able to attend OSCON so far. I have been going to the MySQL Users Conference almost every year in April, and that usually left me without a great desire of crossing the Atlantic again. This year is different. I have crossed the Atlantic already three times, and yet I am about to board a plane once more, heading for the Portland, Oregon.

And for some lucky circumstances, I am also a speaker.
I will join Ronald Bradford on the podium, to speak about (surprise surprise!) MySQL Proxy, from architecture to implementation.
I will have also my hands full in the evenings. There will be plenty of BoFs (including two that I will host) and parties, social gatherings, competitions, which I plan to enjoy for my personal enlightenment, not only to fulfill my duties.
I am looking forward to meet old friends and to make new ones.

Looking back at the first half year

Half a year has gone, most of it with my new company, Sun Microsystems.Kaj has made a detailed digest of his first half.
I took the DBA approach and queried the Planet MySQL database. I know that this qualifies as cheating, but I could not resist.
I published a total of 111 blog posts since January, with peaks in March and April (the users conference was coming).
I wrot about most everything, but some topics are prominent. Users Conference and MySQL proxy are the winners. All in all, I would say that I have fairly distributed my topics.