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.