I recently spent a week in Nelson, New Zealand helping couchsurfing.com scale their database a little better. This was the first major LAMP project I've touched in a while, and it just happened to coincide with my one year anniversary working for MySQL.

When I arrived some things really scared me; They'd discovered that mysqldump would "lock" the entire database for the duration of it's execution, but if you dump the tables individually, they are only locked individually. Note: There is a reason mysql does this, it's called a consistent backup[1]!

I was in good company for the week though with Kasper & Joe. One of my best memories was the three of us pair programming through a shared screen (gnuscreen - we were multitasking). We had quite a …

I'm not sure if I mentioned, but I'm speaking at PHP Quebec 2007!

I had two talks accepted, one on Performance optimisations in MySQL and the other on migration

The one one migrating data should be a lot of fun - it will be based on a talk I did at the MySQL miniconf this year in Sydney, but with a few more examples added.

The performance optimizations will have the "war story" touch, and be based on the performance scaling work I've been doing for couchsurfing.com as a volunteer.

I've created a patch for MySQL 5.0.33 to provide a function SHA2().Download it here: http://www.karwin.com/sha2.patch.gzIt really just calls out to the OpenSSL library for the digest functions. So you have to build MySQL from source with OpenSSL support enabled.You can use the function in SQL syntax like:SELECT SHA2('message', 256);The second argument is 224, 256, 384, or 512, depending on what

Working on subquery optimizations, got an idea how to speed up join execution a little. Read on.

The idea

Consider a query:

select * from t1, t2, t3 where t3.key=t1.col1 and t2.key=t1.col2

Suppose the join order is t1, t2, t3, i.e. the EXPLAIN is like

+-------+------+---------------+------+---------+--------------+-..
| table | type | possible_keys | key  | key_len | ref          |
+-------+------+---------------+------+---------+--------------+-..
| t1    | ALL  | NULL          | NULL | NULL    | NULL         |
| t2    | ref  | key           | key  | 5       | test.t1.col1 |
| t3    | ref  | key           | key  | 5       | test.t1.col2 |
+-------+------+---------------+------+---------+--------------+-..

The important property is that access to t3 is independent of access to t2. MySQL's nested loops join algorithm will run this as in this swimlane diagram:

Here we assume that

…

Sweden to set up embassy in Second Life
I reckon that's very cool. I'd already heard about some people making their living "in there".

MySQL AB today reported another milestone in its 11-year history with record results for the year ended December 31, 2006. The privately-held company continued to deliver rapid revenue growth -- fuelled by the delivery of significant product, community and partner initiatives throughout the year. The recent launch of the new MySQL Enterprise subscription offering is especially drawing high praise among MySQL's customers for its innovative monitoring tools and low total cost of ownership (TCO).

MySQL’s FEDERATED storage engine is a fascinating example of the flexibility gained by abstracting the storage engine layer away from the rest of the server. FEDERATED tables allow you to access a table on another server as though it exists on the local server. However, the manual doesn’t say much about how it really works, MySQL’s only developer article about it is vague and unrealistic, and there are more questions than answers on the net about the engine’s features and behavior.

While speed was not the first goal of the new 5.1 Archive format (aka
version 3 of the engine), whenever space on disk is reduced the
overall performance goes up assuming that the new format doesn't
require chewing up the processor too much.

The schema was the following:
CREATE TABLE accesslog (
id int(10) unsigned NOT NULL ,
method varchar(255) default NULL,
bytes_sent varchar(255) default NULL,
protocol varchar(255) default NULL,
uri varchar(255) default NULL,
args varchar(255) default NULL,
hostname varchar(255) default NULL,
client varchar(255) default NULL,
Referer varchar(255) default NULL,
slash_user varchar(255) default NULL,
e2_node varchar(255) default NULL,
time datetime NOT NULL default '0000-00-00 00:00:00'
);

The schema type was selected because its the type of Schema in 5.0
that …

The OSCON talk & tutorial submission period ends in about a week on February 5. Please submit your proposals soon!

And woah - we've done a poor job at beating people over the head with the submission deadline! Usually we have many many times more submissions than slots, but as of right now it's quite the other way around (which probably is normal too, I don't remember the stats for when the submissions come in). In the categories I'm particularly interested in there are only a handful of submitted Perl talks and only one(!) MySQL talk submitted to the databases track. The Ruby submissions are pretty sparse too, as of now. We'd like some more. :-)

Many more talks are submitted to "Web applications, client- and server-side" already, but as far as I'm concerned it absolutely won't hurt to have more to pick from. ;-)

…

I find myself thinking about Federated and about how to improve it's connection pooling. Whatever is done should be made safe for transctions. Unfortunately, we cannot suspend and join XA transactions which is a pity as it would make it much easier.