Speaking at FrOSCon 2009 and getting ready to OpenSQLCamp-Europe

For the fourth time in a row, I will be speaking at FrOSCon, one of the most charming open source events in Europe.
Hosted in the bright environment of the Department of Computer Science of the University of Applied Sciences Bonn-Rhein-Sieg, this event will get you hooked from the beginning. The organization is done by volunteers, who have always done an amazing job, with even better results than more expensive and famous conferences.
This year, there will be some more action than ever before. In addition to the main event, the organizers have given away a few developers rooms, to let some projects build their own event within the main one. There will be a Java subconference, and, closer to my interests, the European edition of the OpenSQLCamp 2009, which applies to all open database related projects, such as MySQL, PostgreSQL, JavaDB, and many others that are listed in the announcements page. The principal organizer is Lenz Grimmer, who is looking for volunteers to share the burden.
The organizers have also launched a creative contest, which most geeks will find irresistible. Check it out!
FrOSCon started as one of the many LAMP events, and in its fourth year it has emerged as an example for others. I warmly recommend it to all people interested in open source.

Wordcamp in Milan - slides on MySQL 5.x performance

I am attending WordCamp 2009 in Milan. I presented MySQL 5.1 and 5.4, with stress on performance. People are interested. And many questions are flying around, some of which are answerable and some aren't. The questions about Oracle were swiftly avoided, and the ones about forks comparisons were answered with live examples. The attendees have appreciated it.

MySQL 5.4 performance with logging

About a month ago, I published the results of MySQL 5.x performance with logging. The results covered several versions, from 5.0.45 to 5.1.33. Among the conclusions of the post was the consideration that MySQL 5.0.x is faster than MySQL 5.1 in read only operations. I hinted that better results may come for MySQL 5.1. When I wrote that post I had, in fact, an ace up my sleeve, because I had already benchmarked the performance of MySQL 5.4, using the same criteria shown in my previous post. The results, as you can see from the charts below, tell that you don't need to downgrade to 5.0 to achieve the performance you want, but MySQL 5.4 may be the right choice.

MySQL 5.1 + 5.4 read-only logging performance

MySQL 5.1 + 5.4 read-only logging performance

MySQL 5.0 read-only Logging performance

MySQL 5.0 read-only Logging performance

MySQL 5.1 + 5.4 binlog performance

MySQL 5.1 + 5.4 binlog performance

MySQL 5.0 binlog performance

MySQL 5.0 binlog performance

A piece of trivia. On April 9, users of MySQL Sandbox may have noticed that I left a clue of what was about to happen. Among the accepted versions, you could see "5.3, 5.4. 5.5" :-)

MySQL Campus tour at USC - Slow start, brilliant ending

The Southern California branch of the MySQL Campus Tour is almost over. At the USC, attendance was very good, and even better was the enthusiasm and the participation we met. The meeting didn't start well. Our hotel is 11 miles away from the USC campus, and we figured out that heading to the campus 1 hour before the planned time ought to be enough. After 65 minutes, we had done exactly 7 miles, in one of the worst traffic jams that I have ever seen, but the locals tell me that it's pretty standard stuff down here. Anyway, we started 20 minutes later than expected, and we were pleasantly surprised that nobody had left, and a faithful audience had been waiting for us patiently.

The event went very well after that, and the participation was intense and passionate. Four students asked me how to become MySQL developers, and many had questions on Sun business model. After a while, the delay and the traffic jam were forgotten, and the event turned out to be very pleasant. There were 52 attendees. Like in a deck of cards. So Sheeri noted that probably we were the jokers!
I would like to thank the campus ambassadors Lynn Janh and Raed Shomali for the excellent organization and their patience with our forced delay.
Tomorrow we will talk at the L.A. MySQL meetup and then we will move North, to the MySQL Conference.

Quiet excitement at UCLA

The second leg of the Southern California MySQL Campus Tour was at UCLA. There was less attendance than Cal Poly. Only 22 brave souls who endured a lengthy session with a long tail of Q&A.; The excitement came on my way back to my hotel, 25 miles from the campus. Distances have a different meaning here. A few dozen miles is just a tiny portion of the town, and so I found myself once more driving the endless highways of Los Angeles.

When I was almost home, I saw all the cars in front of me stopping, for what I believed was a traffic jam. Instead, it was a truck that was burning fiercely. When I realized that almost everyone was escaping to a side road, I took a rapid picture and then I followed suit in a prudent retreat.

Cal Poly - A success

The MySQL Campus Tour 2009 started very well at Cal Poly, San Luis Obispo, CA. Despite the warm day, which would have tempted many attendees to desert the conference and go to the nearby beaches, there was a full room, with 82 people, many of them standing. The presentation was appreciated (and so was the pizza that Sun had delivered to the classroom). I did the main part, while Sheeri was actively contributing with witty and informational remarks.

Most notable, after the session, we continued the cultural exchange to a local restaurant that bears my name (correctly spelled!), where this heap of bread caught my eye. Thanks to Stephanie and Wendy for organizing the event, and to all the students for their active participation!

MySQL Sandbox 3 - Now feature complete

MySQL Sandbox 2.0.98i is now feature complete. The most notable additions are a robust test suite, with over 120 tests, and some features that have been in the wish list for long time. The first one that makes a lot of difference is the ability of installing a sandbox from a build directory.

This feature has requested many times, and I have been reluctant to implement it, because it involved fiddling with the internals of low_level_make_sandbox, which should behave differently depending on the base directory. An installed BASEDIR has a different structure than a source BASEDIR. In the end, I solved the problem by creating a script that builds a tarball and calls the appropriate script to make a sandbox.
The second most important addition is the sbtool, which includes tasks to be performed on existing sandboxes, such as moving, copying, deleting, and preserving a sandbox.
The one feature that I like the most is the ability of checking for an used port, and instead of requesting a manual intervention, it can detect the first available port and use it. No more conflicts!
The documentation has been enhanced and included into MySQL::Sandbox.
$ perldoc MySQL::Sandbox provides the full manual.
Now I await bug reports, and I plan to release the final 3.0 version within one month.

Another command line tip

Encouraged by Baron Schwartz tip on result set comparison, here are a few more, on the same vein.
First, you can send a result set to a file. Probably you will say "yeah, I know, using SELECT INTO OUTFILE". Correct. Except that you can't rewrite to an existing file, if you want to, and you will get a raw output, not the well formatted one that you usually see on the command line. For example:

mysql > select 1 into outfile '/tmp/f1.txt';
mysql > \! cat /tmp/f1.txt
1

mysql > select 1 into outfile '/tmp/f1.txt';
ERROR 1086 (HY000): File '/tmp/f1.txt' already exists

BTW, \! command is a handy shortcut for executing a shell command.
Let's see what happens with the alternative method:

mysql > pager cat > /tmp/f1.txt
mysql > select 1;
\! cat /tmp/f1.txt
+---+
| 1 |
+---+
| 1 | 
+---+

Now, Using the above trick, you can check the differences between two result sets visually:

mysql > pager cat > /tmp/f1.txt 
mysql > select "one" union select "two" union select "three";
3 rows in set (0.00 sec)

mysql > pager cat > /tmp/f2.txt 

mysql > select "one" union select "two" union select "Three";
3 rows in set (0.00 sec)

mysql > nopager

mysql > \! vimdiff -o  /tmp/f[12].txt

And here is what you get:

Not only you'll know that there is something different, but you will also know exactly what.
This trick is part of a collection of command line advice that I have meant to write for long time. I will publish it all before the Users Conference. I just couldn't resist with this one!

MySQL 5.x performance with logging

There has been much talking about MySQL performance related to logging. Since MySQL 5.1.21, when Bug #30414 was reported (Slowdown (related to logging) in 5.1.21 vs. 5.1.20) I have been monitoring the performance of the server, both on 5.0 and 5.1.
Recently, I got a very powerful server, which makes these measurements meaningful.
Thus, I measured the performance of the server, using all publicly available sources, because I want this benchmark to be repeatable by everyone.
I will first describe the method used for the benchmarks, and then I report the results.

The server

The server is a Linux Red Hat Enterprise 5.2, running on a 8core processor, with 32 GB RAM and 1.5 TB storage.

$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.2 (Tikanga)

$ cat /proc/cpuinfo |grep "processor\|model name" | sort |uniq
model name : Intel(R) Xeon(R) CPU           E5450  @ 3.00GHz
processor : 0
processor : 1
processor : 2
processor : 3
processor : 4
processor : 5
processor : 6
processor : 7

cat /proc/meminfo
MemTotal:     32967056 kB
MemFree:      22790272 kB

Method

I downloaded the source code tarball, as released for every version of MySQL, from 5.0.45 to 5.0.77 and from 5.1.20 to 5.1.33. I also took the code for 5.0.79 from the Bazaar tree on Launchpad.
For each version, I did the following:
* expanded the tarball to a directory;
* compiled the code using ./BUILD/compile-pentium64-max;
* built a binary using ./source/make_binary_distribution;
* installed the binary using MySQL Sandbox, assigning 5GB of RAM to innodb_buffer_pool_size;
* Tun the sysbench OLTP transactional test, 8 threads, 1,000,000 records, 1 minute run;

After installing, all database instances where shut down, and only one database server was active at any given time during the tests, to have clean results.
The servers were cleaned up, with all databases and log files removed between tests.
Additionally, all operating system memory cache was erased between tests.

The sysbench commands used for the tests were the following:

sysbench --test=oltp --oltp-table-size=1000000 \
  --mysql-db=test --mysql-user=msandbox \
  --mysql-password=msandbox --mysql-host=127.0.0.1 \
  --mysql-port=$PORT --num-threads=8 prepare

sysbench --test=oltp --oltp-table-size=1000000 \
  --mysql-db=test --mysql-user=msandbox \
  --mysql-password=msandbox --mysql-host=127.0.0.1 \
  --mysql-port=$PORT --max-time=60 --oltp-read-only=on \
  --max-requests=0 --num-threads=8 run

sysbench --test=oltp --oltp-table-size=1000000 \
  --mysql-db=test --mysql-user=msandbox  \
  --mysql-password=msandbox --mysql-host=127.0.0.1  \
  --mysql-port=$PORT --max-time=60 --oltp-read-only=off \
  --max-requests=0 --num-threads=8 run

The not so exciting results first

Using Sysbench with --oltp-read-only=on, MySQL 5.0 outperforms MySQL 5.1 constantly. In a read-only situation, it looks like 5.0 is much better than 5.1.




Additional bad news is that Bug #30414 is still unfixed. Table logging takes away 50% of performance and thus is not recommended. General logs on file, instead, with the ability of logging on demand, is an affordable diagnostic tool.
Partial good news can be deducted by the ability of MySQL 5.1 to perform better with file logging, compared to MySQL 5.0.

The good news

Normal operations in a MySQL server are not read only, and include a binary log. For my second batch of testing, then, I compared performance with --oltp-read-only=off with and without binary logs. In this situation, MySQL 5.1 outperforms MySQL 5.0 in all cases. It's also nice to observe that the performance is improving gradually from earlier versions to recent ones. Kudos to MySQL engineers for their constant dedication to the server improvement.




The even better news is that, in MySQL 5.1, performance gain on 5.0.x has been increasing steadily.

What does this tell you? That MySQL 5.1 is much, much better than 5.0 for online transaction processing, while MySQL 5.0 seems to be better at concurrent read-only operations. My immediate reaction is that I should use MySQL 5.1 for a master and 5.0 for slaves. However, there are two considerations that stop me. First, using a master of a higher version than the slaves is not recommended, although some tests that I made after this discovery show that you can actually do it, provided that you don't use any specific MySQL 5.1 features. And second, the improvement path is such, that I believe MySQL 5.1 is going to catch up on 5.0 for read-only operations. As soon as this happens, I will let you know immediately! Probably, we will know more at the Users Conference 2009.

Summing up

I think that MySQL 5.1 has much to offer in comparison to 5.0.
The tests that I have performed are not definite. Sysbench is just one of the possible tests, and it doesn't mean that your production server will follow the same pattern or have the same performance. It's just an independent way of measuring the server performance, and especially is a repeatable way, which everyone can reproduce.
I warmly invite you to repeat these tests on different machines and let me know your results.

Another usability bug bites the dust

In MySQL 5.1.33 there is a fix for an apparently innocuous bug.
Bug #36540 CREATE EVENT and ALTER EVENT statements fail with large server_id.
This is a usability bug, that makes the DBA life unnecessarily hard. The reason for having a large server_id is because a DBA might want to use the IP address as server ID, to make sure that there are unique IDs, and to have an easy way of identifying the server through the IP.
All is well until you mix the server_id assignment with event creation:

 select version();
+-----------+
| version() |
+-----------+
| 5.1.32    | 
+-----------+
1 row in set (0.00 sec)

 set global server_id =inet_aton('192.168.2.55');
Query OK, 0 rows affected (0.00 sec)

 select @@server_id;
+-------------+
| @@server_id |
+-------------+
|  3232236087 | 
+-------------+
1 row in set (0.00 sec)

 create event e1 on schedule at now() + interval 60 second do set @a = 1;
ERROR 1538 (HY000): Failed to store event body. Error code 1 from storage engine.

Oops! the event table has a problem. The "originator" column is designed as a signed INT (2^31 -1), while the server_ID can take values up to 2^32 - 1.
In the latest version, soon to hit the market, the problem is solved.

select version();
+-----------+
| version() |
+-----------+
| 5.1.33    | 
+-----------+
1 row in set (0.01 sec)

set global server_id =inet_aton('192.168.2.55');
Query OK, 0 rows affected (0.00 sec)

select @@server_id;
+-------------+
| @@server_id |
+-------------+
|  3232236087 | 
+-------------+
1 row in set (0.00 sec)

create event e1 on schedule at now() + interval 60 second do set @a = 1;
Query OK, 0 rows affected (0.00 sec)

Easy server testing with MySQL Sandbox

MySQL Sandbox 2.0.18 introduces a new feature, changing port. You can now change the listening port for a sandboxed server, either as a standalone operation, or while moving it, using the sbtool. There is a feature in the Sandbox, introduced in 2.0.13, that makes really easy to test servers in special conditions. If you need to start or restart a server using an option that you know you will need only for the next test, you can add the option to the command line invocation of the start or restart scripts.

$ ./start --key-buffer=2G
$ ./use -e "show variables like 'key_buffer_size'"
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| key_buffer_size | 2147483648 | 
+-----------------+------------+
./use -e "show variables like 'innodb_buffer_pool_size'"
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 | 
+-------------------------+---------+

$ ./restart --innodb-buffer-pool_size=2G
./use -e "show variables like 'innodb_buffer_pool_size'"
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 2147483648 | 
+-------------------------+------------+

Some more news. There is a mailing list to discuss the Sandbox development, and new members are welcome. The Sandbox is also awaiting volunteer students for Google Summer of Code.

sbtool - a new advanced entry in the MySQL Sandbox toolkit

MySQL Sandbox 2.0.17 introduces a new item in the toolkit. sbtool is a multi-purpose tool for advanced tasks with MySQL sandboxes. Most notables: assigned and used port detection, port range detection, replication tree, copying and moving sandboxes.

Overview

./sbtool --help
usage: ./sbtool [options]
 -o     --operation       (s) <> - what task to perform
   'info'     returns configuration options from a Sandbox
   'copy'     copies data from one Sandbox to another
   'ports'    lists ports used by the Sandbox
   'tree'     creates a replication tree
   'move'     moves a Sandbox to a different location
   'range'    finds N consecutive ports not yet used by the Sandbox
 -s     --source          (s) <> - source directory for move,copy
 -d     --dest            (s) <> - destination directory for move,copy
 -u     --only_used       (-) <> - for "ports" operation, shows only the used ones
 -i     --min_range       (i) <5000> - minimum port when searching for available ranges
 -x     --max_range       (i) <32000> - maximum port when searching for available ranges
 -z     --range_size      (i) <10> - size of range when searching for available port range
 -f     --format          (s)  - format for "ports" and "info"
   'perl'     fully structured information in Perl code
   'text'     plain text dump of requested information
 -p     --search_path     (s)  - search path for ports and info
 -a     --all_info        (-) <> - print more info for "ports" operation
        --tree_nodes      (s) <> - description of the tree (x-x x x-x x|x x x|x x)
        --mid_nodes       (s) <> - description of the middle nodes (x x x)
        --leaf_nodes      (s) <> - description of the leaf nodes (x x|x x x|x x)
        --tree_dir        (s) <> - which directory contains the tree nodes
 -v     --verbose         (-) <> - prints more info on some operations
 -h     --help            (-) <> - this screen

The list of features may look intimidating. The important ones are the values that you can give to the --operation modifier. The rest is a list of ancillary options to be used with the possible operations.

Port detection

The most straightforward operation is 'ports', which returns the list of ports that have been assigned to sandboxes installed under $SANDBOX_HOME (default $HOME/sandboxes).

$ ./sbtool -o ports
5077  0
5080  0
5130  0
5131  1
5133  1
5333  0
6080  0

The ports followed by a "0" have been assigned but are not currently in use. The ones followed by a "1" are in use. If you need only the latter, it's easy.

$ ./sbtool -o ports --only_used
5131  1
5133  1

If you want to use this information from a Perl script, there is an appropriate modifier.

$ ./sbtool -o ports --only_used --format=perl
$ports = {
          '5133' => 1,
          '5131' => 1
        };

Speaking about ports, if you want to make a custom installation of one or more sandboxes for which you need a range of ports, sbtool can help. Let's assume that you need at least 20 ports, not lower than 5130.

$ ./sbtool -o range --min_range=5130 --range_size=20
5134 -  5154

Moving a sandbox

Moving a sandbox has been in the wish list for long time. Finally, there is a clean way of moving a sandbox to an arbitrary location, keeping its functionality intact.
For example, let's move msb_5_1_32 to /tmp/xyz, and see if it works.

$ ./sbtool -o move  --source_dir=$HOME/sandboxes/msb_5_1_31 --dest_dir=/tmp/xyz
the old scripts have been saved as filename.bak

$ /tmp/xyz/start
. sandbox server started

$ /tmp/xyz/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.31 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql [localhost] {msandbox} ((none)) > exit
Bye

# Now we move the sandbox back to its previous location

$ ./sbtool -o move  --dest_dir=$HOME/sandboxes/msb_5_1_31 --source_dir=/tmp/xyz
the old scripts have been saved as filename.bak

The sbtool script knows when a sandbox is in use, and stops it before starting the moving process. After the move, all original scripts are saved as ".bak", and the current scripts are updated with the new location.

Copying a sandbox

Copying a sandbox meets a different need. You want mostly to copy the data from one server to another. You don't want to use replication, especially if it is a lot of data. sbtool can copy the data from one sandbox to another, stopping the source and destination when necessary. It will only copy across compatible versions (5.1 to 5.1 or newer, but not 5.0 to 5.1 or vice versa).
When copying a data directory, sbtool skips relay-log.info, the binary logs, and the relay logs.
Both the source and destination directories must be sandboxes, or the operation is refused.

$ ./sbtool -o copy -s $HOME/sandboxes/msb_5_1_31/ -d $HOME/sandboxes/msb_5_1_33/

Replication trees

This option is very much required for testing.
You want to have one master, two or more relay slaves, and several final slaves.
For example. let's assume that you need this schema:

             1  
            |  
     +------+-----+-----------+
     2            3           4                   
     |            |           |  
 +---+---+    +---+---+   +---+---+
 |   |   |    |       |   |   |   |  
 5   6   7    8       9   10  11  12

The way to go is to create a multi_sandbox with 12 nodes, and then using sbtool to make the tree.

$ make_multiple_sandbox --how_many_nodes=12 5.0.77
...
group directory installed on $HOME/sandboxes/multi_msb_5_0_77

$ ./sbtool -o tree --mid_nodes="2 3 4" --leaf_nodes="5 6 7|8 9|10 11 12" --tree_dir=$HOME/sandboxes/multi_msb_5_0_77
node 1 is master
. sandbox server started
. sandbox server started
enabling node 2 to relay updates
. sandbox server started
   node 2 is slave of node 1
. sandbox server started
. sandbox server started
       node 5 is slave of node 2
. sandbox server started
. sandbox server started
       node 6 is slave of node 2
. sandbox server started
. sandbox server started
       node 7 is slave of node 2
. sandbox server started
enabling node 3 to relay updates
. sandbox server started
   node 3 is slave of node 1
. sandbox server started
. sandbox server started
       node 8 is slave of node 3
. sandbox server started
. sandbox server started
       node 9 is slave of node 3
. sandbox server started
enabling node 4 to relay updates
. sandbox server started
   node 4 is slave of node 1
. sandbox server started
. sandbox server started
       node 10 is slave of node 4
. sandbox server started
. sandbox server started
       node 11 is slave of node 4
. sandbox server started
. sandbox server started
       node 12 is slave of node 4

Let's test it:

gmax@gmac3[sandboxes]$ multi_msb_5_0_77/n1 -e "create table test.t1(i int); insert into test.t1 values (@@server_id)"
gmax@gmac3[sandboxes]$ multi_msb_5_0_77/use_all "select * from test.t1"
# server: 1:
i
101
# server: 2:
i
102
# server: 3:
i
103
# server: 4:
i
104
# server: 5:
i
105
# server: 6:
i
106
# server: 7:
i
107
# server: 8:
i
108
# server: 9:
i
109
# server: 10:
i
110
# server: 11:
i
111
# server: 12:
i
112

$ multi_msb_5_0_77/n1 -e "show slave hosts"
+-----------+-------+-------+-------------------+-----------+
| Server_id | Host  | Port  | Rpl_recovery_rank | Master_id |
+-----------+-------+-------+-------------------+-----------+
|       102 | node2 | 19778 |                 0 |       101 |
|       104 | node4 | 19778 |                 0 |       101 |
|       103 | node3 | 19778 |                 0 |       101 |
+-----------+-------+-------+-------------------+-----------+

$ multi_msb_5_0_77/n2 -e "show slave hosts"
+-----------+-------+-------+-------------------+-----------+
| Server_id | Host  | Port  | Rpl_recovery_rank | Master_id |
+-----------+-------+-------+-------------------+-----------+
|       105 | node5 | 19779 |                 0 |       102 |
|       106 | node6 | 19779 |                 0 |       102 |
|       107 | node7 | 19779 |                 0 |       102 |
+-----------+-------+-------+-------------------+-----------+

$ multi_msb_5_0_77/n3 -e "show slave hosts"
+-----------+-------+-------+-------------------+-----------+
| Server_id | Host  | Port  | Rpl_recovery_rank | Master_id |
+-----------+-------+-------+-------------------+-----------+
|       109 | node9 | 19780 |                 0 |       103 |
|       108 | node8 | 19780 |                 0 |       103 |
+-----------+-------+-------+-------------------+-----------+

So, this is it. Now the code needs some testing. Please comment on this blog, and report bugs at the MySQL Sandbox home.

Taxi rides and packed rooms at Athens Open Source Conference

I arrived in Athens shortly before midnight. An unusual sight. Most of the taxi drivers in the queue are female. My driver is a middle aged woman wearing a torn leather jacket and with no understanding of English beyond numbers. She said "yes. Liberian hotel", and she adds the name of a mysterious location that I don't understand. I show her the hotel name and address, written in both Latin and Greek alphabet. "Yes. Iberian hotel. I know where." "Not Iberian - I say patiently, pointing at the hotel name on the paper - Imperial.
She says yes, and off we go.
After a minute she fishes a cell phone from her jacket, and asks something. It's a flurry of incomprehensible Greek, but the hotel name I hear very clearly "yadda yadda yadda Liberia hotel yadda yadda". I lean forward and I repeat "Not Liberia. Imperial". "yes, she says. Iberia".
I tell her to stop the taxi. I show her again the name of the hotel, "Imperial," and the place where it is located, "Karaiskaki". She nods vigorously and we go again. After a while, she's back on the phone, asking about "karaiskaki". According to my Athenian colleague who booked the hotel, everybody knows about this place. After yadda-yaddaying more on the phone, she gives the phone to me. A young female voice in tentative English asks for my destination. I repeat. She starts giving me directions. I remind her that I am in the back seat and she should better give these directions to the driver. I Give back the phone to the driver, and she talks for a while more. We keep going on the main highway. No mistakes so far. It's a straight drive.
After a few minutes, in the same highway, she calls again, "Karaiskaki" is mentioned several times. She gives me the phone again. The distant woman asks where I am coming from. I snap that where I come from is not of big significance, and the matter of where I am going to end up has more practical and immediate value to me.
The driver seems more confident now. We take an exit from the highway and she calls again. "Liberia hotel" and "karaiskaki" are definitely the topic of the conversation. I sigh and give up. If we arrive to Karaiskaki, the Imperial hotel should be there.
At last, we stop in a large square. The drive says "Iberia hotel". I get out, look up at the large building in front of me. It says "Imperial hotel", and I ask for a receipt. The taxi meter reads 31 EUR, but she asks for 50, saying something that sounds like "extra charge". I refuse, and offer 30. She says 50 again, and we start to bargain. After a few minutes, we settle for 40. Welcome to Athens.
The next morning, we start late. My session is delayed, but I managed to tell the MySQL story in front of an audience of about 150 people. They are a bit shy about asking questions, so I show them my MySQL 5.1. GA T-shirt, noting that the names on the back are the distribution list. If they see someone with that T-shirt, they can ask the hard questions. Both I and Salle are mentioned there, and we will welcome questions, both hard and soft, during the afternoon workshop.
In the afternoon, the workshop room was prepared with about 30 seats. It is a parallel workshop, so the intention is that the few people interested in MySQL should attend the workshop, while the majority will stay in the main event. We start the workshop with about 20 people in the room, but after about five minutes, the room is invaded by the more attendees who realized late that the workshop was not in the large room. We get more than half the audience, about 70 people, packed in the workshop room. And so we start the show. We ask them what they want to hear, and this time, in the cozy illusion of comfort and camaraderie created by the small room, now crowded to capacity, the questions flow easily and friendly.
Between me and Salle, we get quickly rid of the hard questions "is MySQL Cluster the right choice for my business?" (no, unless you have a Telco-like database). "Does MySQL support load balancing?" (which leads to an explanation of what replication works, with demos. Then we dispatch the myth of Circular replication as a silver bullet scalability option, and explain how DRBD works. The scalability options are taken all into account, including the manual ones and the mythical transparent ones. We explain how MySQL Proxy works, its strength and weaknesses, and how they can play with it. A mandatory demo of how Proxy makes coffee is well received. The last topic is partitioning, and it is demonstrated with a connection to one of the community team test servers in a Norway server farm. After I show that a query that takes 30 seconds in a 741 million records non-partitioned table is reduced to 5 seconds in a table with the same records but partitioned by range, they are believers. The final applause is very much genuine and full of gratitude.

When I and Salle finally go out for a walk to the Acropolis, we feel like we have definitely been helpful.
The Acropolis was closed (business hours from 8:30 to 15 only), but, hey! You can't have it all! So we ended the day in a friendly restaurant near the archaeological sites.
All in all, it was a good experience. Athens deserves another visit.

A deceiving error message while setting a replication slave

I was setting up a slave manually for a quick-and-dirty experiment, when I found this one:

mysql  %gt; slave start;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Hmm... I did the CHANGE MASTER TO already, but just for the sake of it, let's do it again.

mysql %gt; change master to master_host='127.0.0.1', master_port=22346, 
master_user='msandbox', master_password='msandbox', 
master_log_file='mysql-bin.000002', master_log_pos=106;
Query OK, 0 rows affected (0.00 sec)

mysql  %gt; slave start;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Again! Why is it asking to execute CHANGE MASTER TO, which has been accepted right now?

Then it came to me. Because I did a manual configuration, and I didn't set the server_id.

mysql [localhost] {msandbox} ((none)) %gt; set global server_id=4000;
Query OK, 0 rows affected (0.00 sec)
mysql  %gt; slave start;
Query OK, 0 rows affected (0.00 sec)

Yep. That was it. The error message could have been more helpful, though.

Guest Post: Philip Stoev. If you love it break it. Getting started with the RQG

I am glad to host in these pages a post by Philip Stoev, a remarkable QA engineer, creative, resourceful, and a notorious troublemaker. I met Philip by email in 2007, when I was exploring his Perl modules, which I used for one of my most rewarding articles. A few months later, when we met in person during the MySQL Developers Meeting, Philip was hired as a QA engineers, with my warmest recommendations. This post is about the Random Query Generator, which is also the topic of a MySQL University session. Thanks, Philip!

Want to test and break MySQL without having to cook up your own test framework?

Sun Microsystems have open-sourced the Random Query Generator, a new tool for generating tables with random data and then executing a random query workload against them. Both the data and the queries are fully configurable.

For example, assume that you want to evaluate how well partitioning will work for your workload:

Step1. Describe your tables.

Create a configuration file named partitioning.zz, instructing the tool to create a partitioned table containing a few varchar() fields. The format of the file is described in the Forge wiki. The configuration file will contain the following.

$tables = {
     rows => [65535],
  pk => [ 'integer not null auto_increment' ],
     partitions => [ 'PARTITION BY KEY (pk) PARTITIONS 2' ],
};

$fields = {
     types => [ 'varchar', 'char' ],
     indexes => [undef ]
};

$data = {
  strings => [ 'english' ]
}

This basically says "create a table with 64K rows, two char fields, 2 partitions, auto_increment primary key and fill it with English words."

Step2. Describe your workload.

The configuration file describing the workload takes the form of an SQL grammar. Its format is described in the Forge wiki. For example, let's check how updates on a primary key are doing. Let's name the configuration partitioning.yy and put the following in:

query:
  UPDATE _table SET _field_no_pk = _english WHERE `pk` = _smallint_unsigned;

This says "UPDATE a random non-PK field from the table to a random english word for a random value of the PK".

This is just the very tip of the iceberg. Arbitrarily complex SQL queries can be expressed with the appropriate SQL grammar.

Step3. Run the test!

Fetch the Random Query Generator from Launchpad:

bzr branch lp:~randgen/randgen/main

and then run the test:

perl runall.pl \
--basedir=/path/to/mysql-distribution \
--gendata=partitioning.zz \
--grammar=partitioning.yy \
--queries=100000 \
--threads=10 \
--duraton=360

This will start a server by using the specified MySQL location and will then run random queries in 10 threads, executing up to 100000 queries per thread for no more than 360 seconds.

While the test is running, monitor the performance of your server (which will run on port 19306). Check the CPU usage and use SHOW STATUS and mysqladmin status to see how things are going. To see performance without partitioning, edit the partitioning.zz file and remove the "partitions" line and run the test again.

You are also most welcome to join the MySQL University session on the RQG, to be held tomorrow, December 11 at 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 BST / 15:00 CET / 17:00 MDT (Moscow).

More information is available here:

• http://forge.mysql.com/wiki/Random_Query_Generator
• http://forge.mysql.com/wiki/MySQL_University

Speaking about the Event Scheduler at the Users Conference 2009

The approvals are coming. I will be speaking at the MySQL Users Conference 2009. Using the event scheduler. The friendly behind-the-scenes helper. My co-speaker will be none other than Andrey Hristov, the designer and implementer of the Event scheduler, one of the main features of MySQL 5.1.

Boston MySQL meetup - November 10

The Boston MySQL meetup is held on November 10th at 7pm. The place is MIT Building E51 4 Amherst St Room 372 Cambridge, MA 02142 857 205 9786

We'll talk about MySQL Sandbox, and about any relevant MySQL topic.

On the road with Dups

Now that Dups is the Community Relations Manager for North America, we'll start working together in the most active way. Next week, we'll be busy at meetups in New York and Baltimore, and then it will be full immersion in Charlottesville for the Open SQL Camp.

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.

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.