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.

Scalable architectures with MySQL Proxy

MySQL community, mark your calendars!. On July 8th, 2008, there is a Webinar on Designing scalable architectures with MySQL Proxy.
This is not the usual marketing sponsored webinar. Although we love to show off, this is not a "look-how-good-we-are" presentation. This is a community driven event, where a community member, using only MySQL Proxy and some creativity, solved his production problems.

This is a real story of a community member who used open source software to build a customized scalable architecture to suit his purposes. Isn't it a good story?
I won't steal his thunder and tell you in advance what was the problem about. I will introduce the general concepts about Proxy, and then our guest John Loehrer will tell hist story in full colors.
Don't miss this one!

MySQL Sandbox - planning for 2.0

Taking advantage of Launchpad environment, I started paving the way for version 2.0 of MySQL Sandbox.
I opened a new branch for version 2.0, which currently holds 1.99.1, and I listed the necessary tasks using the built-in blueprints feature.

So now I have a list of tasks to keep track of the development.

• implement test suite (have a good prototype - I am getting to it)
• replace sleep with check loop (thinking - some analysis required)
• create function library (good progress)
• script to kill unresponsive servers (good progress)
• remote sandbox installer (thinking - hard to implement without being intrusive)
• allow url for tarball (thinking - needs external components)
• define circular replication (thinking - easily doable)
• refactoring for internationalization (thinking - easily doable - long task)

The above features should all go to version 2, although the list is still open. No feature freeze has been called. It's still alpha.
I am unsure about the remote installer. I have implemented such a feature a few years ago, and it turned out to be quite intrusive (the choice was between being intrusive and fragile). I may consider making it a plugin or moving it to a later version. Still thinking.

The development model is open. If you use the Sandbox and want to submit a patch or have comments on its development plans, please contribute!

From Bazaar to Sandbox in 5 moves

Now that MySQL has switched its development to Bazaar, we can play a bit with the new tools. One thing that I have tried immediately upon adoption of the new RCS is to create a test bench for multiple MySQL versions by combining Bazaar and MySQL Sandbox.

If you are into bleeding edge testing, you may like to know how to get a new MySQL server up and running in a sandbox from a source tree.

First move - make the repository

This is a zero time consuming move, and you will have to do this only once. But this move will save you heaps of time in future. By creating a shared repository, you are telling Bazaar to avoid redundant copies of files (actually Bazaar is much smarter than this, but as a simple explanation it will do).

$ bzr init-repo shared
$ cd shared/

Second move - get the source

Now we're ready to get the first copy of our source. The first branch we get is the current tree, 5.1. Depending on the speed of your network connection, it will take between 20 and 40 minutes.

$ time bzr branch lp:mysql-server/5.1  51

lp stands for launchpad. mysql-server is the project name, and 5.1 is the version. 51 is the directory we are creating on the local disk. You can call it whatever you want. I prefer short names.

This operation may be tricky. The current Bazaar client (1.5) is not verbose. It may actually appear to be doing nothing for very long time. It is actually importing code. Don't be discouraged and bear with me on this. It will pay off in the end. After a while you will see a progress bar and eventually it will display the result.

Branched 2655 revision(s).                                                                                                   

real 19m14.110s
user 7m22.883s
sys 1m21.372s

In my laptop, it took less than 20 minutes, but it could be much slower, depending on connection type and intensity of net traffic.

Let's have a look at the size of what we have downloaded. The hidden directory .bzr contains the biggest chunk.

$ du -sh .bzr/ */
498M .bzr/
129M 51/

Now for the juicy part. We want to branch also MySQL 5.0. You may be ready for yet another 20 minutes and 600 more MB of data to be added, but you are in for a good surprise:

$ time bzr branch lp:mysql-server/5.0 50
Branched 2634 revision(s).                                                                                                   

real 0m42.205s
user 0m28.100s
sys 0m4.757s

Less than one minute! And what about storage?

$ du -sh .bzr/ */
498M .bzr/
100M 50/
129M 51/

As you see, the new code added just the difference between 5.1 and 5.0, so it did not even had to get it from the net because it mostly got the previous version from the internal history (we'll come back to this point in a later post).
A similar experience awaits you when branching the newest version, 6.0. A bit slower than 5.0 (because it has to get something more from the net), but still quite fast compared to the full tree we got in the first run.

$ time bzr branch lp:mysql-server/6.0 60
Branched 2664 revision(s).                                                                                                   

real 4m45.383s
user 1m39.727s
sys 0m20.776s

$ du -sh .bzr/ */
566M .bzr/
100M 50/
129M 51/
146M 60/

Third move - export the code

We have now on disk the code for three versions. We could just go inside each directory and build it, but that would not be clean. So in this move we just export the code to a build directory that we create for this task.

$ mkdir ~/install/build

$ ls
50    51    60

Using a shell loop, we export each tree to a build directory. It takes about 30 seconds for each tree.

$ for R in 50 51 60 ; do echo $R ; cd $R ; bzr export ~/install/build/$R ; cd .. ; done
50
51
60

Fourth move - build the code

We are finally ready to build. Each build can take quite long, depending on your box. In my laptop, it takes about 40 minutes per build.

In the ./BUILD directory in each source tree there are several building scripts. Choose the one that suits your purposes, eventually disabling the parts that you don't need (e.g. cluster).

$ cd ~/install/build

$ for R in 50 51 60 ; do \
  cd $R ; \
  ./BUILD/your_favorite_script && ./scripts/make_binary_distribution ; \
  cd .. ; \
  done

After each build, this loop runs make_binary_distribution, which creates a binary tarball, right what you need for MySQL Sandbox.

Fifth move - sandbox it!

Now, let's use our favorite installation tool to use these new binaries.

Download the sandbox. Make sure that you got at least version 1.21.

$ export SANDBOX_HOME=$HOME/sandboxes
$ cd ~/install/mysql_sandbox-1.21

For each version, you invoke the sandbox installer as

./express_install full/path/to/tarball_name.tar.gz

Here we go

$ ./express_install.pl ~/install/build/50/mysql-5.0.66-darwin9.3.0-i386.tar.gz
unpacking /Users/gmax/install/build/50/mysql-5.0.66-darwin9.3.0-i386.tar.gz
Executing ./install.pl --basedir=/Users/gmax/install/build/50/5.0.66 \
 --sandbox_directory=msb_5_0_66 \
 --install_version=5.0 \
 --sandbox_port=5066 \
 --no_ver_after_name
  The MySQL Sandbox,  version 1.21 09-Jun-2008
  (C) 2006,2007,2008 Giuseppe Maxia, MySQL AB
installing with the following parameters:
home_directory                 = /Users/gmax/sandboxes
sandbox_directory              = msb_5_0_66
sandbox_port                   = 5066
datadir_from                   = script
install_version                = 5.0
basedir                        = /Users/gmax/install/build/50/5.0.66
[...]
loading grants
sandbox server started
installation options saved to current_options.conf.
To repeat this installation with the same options,
use ./install.pl --conf_file=current_options.conf
----------------------------------------
Your sandbox server was installed in /Users/gmax/sandboxes/msb_5_0_66

$ ./express_install.pl ~/install/build/51/mysql-5.1.26-rc-darwin9.3.0-i386.tar.gz
unpacking /Users/gmax/install/build/51/mysql-5.1.26-rc-darwin9.3.0-i386.tar.gz
Executing ./install.pl --basedir=/Users/gmax/install/build/51/5.1.26 \
 --sandbox_directory=msb_5_1_26 \
 --install_version=5.1 \
 --sandbox_port=5126 \
 --no_ver_after_name
  The MySQL Sandbox,  version 1.21 09-Jun-2008
  (C) 2006,2007,2008 Giuseppe Maxia, MySQL AB
installing with the following parameters:
home_directory                 = /Users/gmax/sandboxes
sandbox_directory              = msb_5_1_26
sandbox_port                   = 5126
datadir_from                   = script
install_version                = 5.1
basedir                        = /Users/gmax/install/build/51/5.1.26
my_file                        =
[ ... ]
sandbox server started
installation options saved to current_options.conf.
To repeat this installation with the same options,
use ./install.pl --conf_file=current_options.conf
----------------------------------------
Your sandbox server was installed in /Users/gmax/sandboxes/msb_5_1_26

$ ./express_install.pl ~/install/build/60/mysql-6.0.6-alpha-darwin9.3.0-i386.tar.gz
unpacking /Users/gmax/install/build/60/mysql-6.0.6-alpha-darwin9.3.0-i386.tar.gz
Executing ./install.pl --basedir=/Users/gmax/install/build/60/6.0.6 \
 --sandbox_directory=msb_6_0_6 \
 --install_version=6.0 \
 --sandbox_port=6060 \
 --no_ver_after_name
  The MySQL Sandbox,  version 1.21 09-Jun-2008
  (C) 2006,2007,2008 Giuseppe Maxia, MySQL AB
installing with the following parameters:
home_directory                 = /Users/gmax/sandboxes
sandbox_directory              = msb_6_0_6
sandbox_port                   = 6060
datadir_from                   = script
install_version                = 6.0
basedir                        = /Users/gmax/install/build/60/6.0.6
[ ... ]
sandbox server started
installation options saved to current_options.conf.
To repeat this installation with the same options,
use ./install.pl --conf_file=current_options.conf
----------------------------------------
Your sandbox server was installed in /Users/gmax/sandboxes/msb_6_0_6

At the end of the exercise, you have three sandboxes with the three major MySQL versions, ready to use. Enjoy!