Planet MySQL

Finding the needle in your MySQL haystack

Tue, 19 Aug 2008 19:03:09 -0700

(first in what will probably be a series of blogs as we move all our projects to a replicated, MySQL 5.0 environment, and I finally get to start playing with all the useful features that come with it)Say you've got a client, Mystery Client A. Mystery Client A has hired a marketing consultant. As a part of their rebranding efforts, they have decided to refactor their company spelling convention to MysteryClientA!, for whatever reason. That's fine for replacing a few logos, but MCA has a database-driven content management system, and their name is riddled throughout the database in page content, event description, news headlines and so forth. Your job is now to sift through the entire system and apply the newly crafted spelling to the entire database. So what do you do? ...

How to unit-test code that interacts with a database

Tue, 19 Aug 2008 19:03:09 -0700

I got some interesting comments on my previous article about unit testing Maatkit, including echoes of my own conversion to the unit-testing religion. One of the objections I’ve heard a lot about unit-testing is how it’s impossible to test code that talks to a database. “It’s too hard,” they say. “Oh, it’s easy to test a module that calculates a square root, but a database? Way too much work!”

Is it really impossible or even hard?

I disagree. In one of my previous articles I said The Rimm-Kaufman Group, my previous employer, has a comprehensive unit-test suite. When I say comprehensive I mean it: database interaction is fully tested, too. I know because I was heavily involved in building it. Even extremely complex things like big reports that are generated from lots of data are tested. And believe me, sharding the databases would have been much harder without complete code coverage. It’s really not that complicated to unit-test against a database, and it’s so worth it. Here are some hints about how you can do this.

There are many ways to do it, but I’ll just describe the basics of the system I helped build. There are several moving parts to the test suite (”smoke“), but one of them sets a magical environment variable. And then, all code that connects to a database server magically gets back a different database connection from the create_me_a_connection() function. This is because there is a database connection abstraction library that respects the environment variable. It’s really pretty simple for the most part; instead of doing DBI->connect(…) you just call this function, which is a thin wrapper that hands back a connection object.

This wrapper is itself unit-tested thoroughly, too. This ensures that when some code is being run from a test, it cannot (I mean cannot!) connect to a production database, and vice versa. There are some conventions about production and test servers that make sure the abstraction library can tell for sure. If there’s any confusion, of course, it will die in a non-recoverable way. Safety first.

Building a good development environment

Just as each developer has their own copy of the code from version control, each developer has their own private database server running on the dev machine. There are some simple conventions that make this possible: Unix user ID plus a constant for the port number, etc. It’s really quite easy. The private database server is a slightly modified version of Giuseppe Maxia’s MySQL Sandbox tool. It can be torn down and set up afresh as desired. It is wiped clean and re-filled at the start of every test, with a small, tightly focused dataset carefully chosen to represent the conditions the code is supposed to work with. (Each test has its own dataset).

If this sounds like a system that can’t work on a large scale, well, it does. That’s the secret sauce that I won’t reveal in this post. (It’s my past employer after all, and I can’t go revealing everything about them can I?) You just have to be smart about it. When a database is central to your business, you either figure out how to get this right, or you pay the consequences in lost time and poor code quality.

I and the other developers there (another secret: it’s a small team; small teams build great things) built several quick utilities to help develop unit tests against a database. There are utilities to get a minimal necessary dataset for testing and dump it into a file that can be loaded by the test. There are utilities that can migrate schemas and update the tests to match the schema changes. And so on, and so on. This is possible because of careful planning for testability, and really smart things like super-consistent and sensible naming conventions for database objects. (Ruby On Rails owes a lot of its success to simple things like this, too. Conventions are really powerful.) Maybe I’ll write about the database naming conventions some other time — I have to credit Alan Rimm-Kaufman a lot for designing those conventions. It was a stroke of genius.

Things to avoid

There are several things I do not recommend doing when you unit-test code that talks to a database. I’ll just mention a couple:

mock

test an implementation instead of a behavior

Never let a test connect to a production database. Never, ever. Worlds of hurt will follow. Not only are you risking your production data, but what about the risk to your code? You’re testing against things that will almost certainly change and break your tests; and you’re possibly polluting your live data with testing data and/or changing live data from the tests.
I also recommend developing unit tests for your current database functionality if you’re thinking about changing it much. Don’t like MySQL’s lax error handling? Plan to set the SQL_MODE to something stricter? Dive into that database abstraction library and make your tests run in strict mode first by setting SQL_MODE on every new connection that’s created when running inside a test; fix all the breakage in the test suite; feel sure that your code isn’t going to break in production. That was easy!

Summary

Once your creative juices get flowing, you’ll see tons of places your unit test suite can help you out.

If you’re in the Oracle or SQL Server world, or any other world where you can’t just set up and discard database instances at will due to licensing problems, you’re going to have to be a little more inventive. But you can still do it. (Don’t you wish you’d chosen Freedom?) And unit tests are just as beneficial for apps based on Oracle as they are for MySQL.

Have fun! Go forth and test some more!

mysql, Test Driven Development, testing a database, The Rimm Kaufman Group, unit testing

Curl Functions for MySQL, v 0.1 released!

Tue, 19 Aug 2008 14:49:11 -0700

I was looking for an example of how to write a MySQL UDF for my upcoming book "Writing Web Applications in Perl, Memcached, MySQL and Apache", and decided using Curl would be fun. Maybe there are some philosophical issues of why not to do it. But I can do it, and I did do it. So be it ;) Once I started the idea, I had to at least finish it. I hate not finishing something.

The single function is http_get(). It's very simple -- it just fetches a url (doesn't yet take the "http" part, it's _that_ simple). This severely needs to be fleshed out.

So, the proof in the pudding is at: http://patg.net/http_get.txt

A Launchpad project: https://launchpad.net/curludfs

Code: http://patg.net/downloads/curl_functions_mysql-0.1.tar.gz

I'd entertain anyone wanting to contribute!

Announcement: The Pythian Group and Open Query: Partners

Tue, 19 Aug 2008 14:49:10 -0700

I’d like to share some great news — The Pythian Group and Open Query have become partners!

Open Query is a leading provider of high-quality MySQL, PostgreSQL and related training in Australia and New Zealand. They offer consulting services too, and are also known for their MySQL Graph Storage Engine. Feel free to browse through Open Query web-site for more info.

Open Query was founded by Arjen Lentz, who was employee number 25 at MySQL AB. If you follow the MySQL community then I’m sure you already read Arjen’s blog.

Since you’re reading this blog, I guess you probably already know what Pythian does, but if you want to learn more, please click through to our home page.

Together with Open Query, we are going to extend our service offerings and strengthen our positions in outsourced database management services, consulting, and training.

Getting Rich Fast ?

Tue, 19 Aug 2008 13:12:09 -0700

Lukas Kahwe Smith has an awesome post titled "Open Source is not making enough rich people richer" .
Indeed, there is much talk that the VC's , the Investors and different others aren't seeing the big money fast enough, according to them that is.
Does that mean that the open source industry is going bad ? Does that mean you can't make a living when working in the Open Source industry ?

Absolutely not, as he points out there are uncountable people gaining a good living with Open Source, developers working on the different projects as their day job, system administrators managing open source platforms. We are helping out customers to implement Open Source and Free Software. And there are numerous other Drupal, Mysql, Xen, shops out there. Some have their own open source products and create a business on top of that , others are supporting popular platforms locally.

Lots of other companies are innovating on top of Open Source, they are using the LAMP stack or just Linux as an OS to build their products on they need the stability and the features an Open Source stack provides them with and only in this way they can deliver their customers with a stable and cost effective platform. Lots of these companies contribute back to the open source community (sadly some don't ..yet) .

But at the end of the day there is much more money involved in the Opensource world than the money the Open Source product vendors like RedHat, Alfresco, Zimbra, Zenoss and are getting from selling their products.

And that's what counts !!

A few seats still left for DRBD Performance Tuning webinar

Tue, 19 Aug 2008 13:12:08 -0700

For those of you interested in getting maximum performance out of your DRBD-based HA clusters, I am hosting a webinar on the subject on Wednesday, 8/27 at 1800 UTC (2pm Eastern, 11am Pacific).

The webinar is offered free of charge and it’s already pretty tight in terms of attendance, the event being limited to a maximum of 60 attendees. But if you’re quick, you might still stand a chance of grabbing a front-row seat. Check out this page on our website, it has all the relevant information.

Two for the price of one

Tue, 19 Aug 2008 11:35:00 -0700

Haven’t done any sneaky puzzles in a while. How would you accomplish this?

mysql> CREATE TABLE t1 (id int);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

Last Week For Database Survey

Tue, 19 Aug 2008 09:41:29 -0700

This is the last week to participate in a database usage survey. If you haven't already done so, please take a few minutes to answer 25 questions.

LewisC

Technorati : database, mysql, survey

The key to accessing your data: MySQL Connectors and bindings for various languages

Tue, 19 Aug 2008 09:41:29 -0700

Being able to use an Open Source DBMS to manage your data is nice, but what good would it be if you can't easily access it from your applications? One key factor to the popularity of MySQL is probably its wide range of available language bindings, which started with support for C, PHP and Perl from early on.

I've tried to gather a list of languages and their respective MySQL drivers/modules below. It's by no means complete or exhaustive, but I think I covered quite a lot of popular as well as exotic programming languages.

There is a number of connectors which are actually developed by the Sun Database Group (aka MySQL) itself and that are ready to use:

Connector/ODBC - Standardized database driver Windows, Linux, Mac OS X, and Unix platforms.
Connector/J - Standardized database driver for Java platforms and development.
Connector/Net - Standardized database driver for .NET platforms and development.
Connector/MXJ - MBean for embedding the MySQL server in Java applications.
MySQL native driver for PHP - mysqlnd - The MySQL native driver for PHP is an additional, alternative way to connect from PHP 6 to the MySQL Server 4.1 or newer.
libmysql - The original implementation of the MySQL Client/Server protocol (in C). This library is the basis for a large number of client libraries for other languages.

In addition to the above, there are several other connectors developed by Sun/MySQL, which are still under development:

MySQL Connector/OpenOffice.org - a driver that allows you to connect to MySQL with OpenOffice.org natively, without having to use JDBC/ODBC.
PHP PDO mysqlnd - a PHP/PDO driver based on the mysqlnd driver
Connector C++: a driver for C++ applications that uses an API similar to the JDBC 3.0 API.

But it's not only us who develop language bindings for the MySQL server. There is an abundance of drivers that are developed and maintained by the Community, independently from Sun/MySQL (but sometimes with support or guidance from MySQL engineers). The list below is not sorted in any particular order other than the sequence in how I found them over time:

DBD::mysql - a MySQL driver for the DBI database abstraction layer of Perl scripting language. There used to be a a native driver named "Msql-Mysql-modules", but it does not seem to be maintained anymore.
Net::MySQL - an implementation of the MySQL Client/Server Protocol in Perl (does not require the MySQL client library). There also is MySQL::Packet, a Perl module that can encode and decode the binary protocol.
MySQLdb - a MySQL driver for the Python scripting language.
mysqli, ext/mysqli - MySQL Improved Extension for PHP
mysql, ext/mysql - MySQL Extension for PHP
PDO_MYSQL - MySQL PDO driver for PHP
MySQL/Ruby - the MySQL API module for Ruby, based on the libmysql C library.
Ruby/MySQL - the MySQL Ruby API, implemented in Ruby.
MySQL++ - a C++ wrapper for MySQL's C API. It is built around the same principles as the Standard C++ Library, to make dealing with the database as easy as dealing with STL containers.
MySQL wrapped - another C++ wrapper for the MySQL C API.
MySQLtcl - a simple API for accessing a MySQL database server from the Tcl programming language.
MySQL-Cocoa - An Objective-C Cocoa API for MySQL (based on libmysql).
Eiffel MySQL - an interface to the MySQL database server using the Eiffel programming language.
Bryan O'Sullivan's pure Haskell MySQL bindings
hsql-mysql - A MySQL driver for Haskell
MySQL Driver for the programming language D
MyDB module for the Kaya programming language (included in the distribution)
MySQL bindings for the Objective Caml language
MySQL bindings for GNU Ada
The SPL programming language ships with a MySQL module included in the distribution
Database bindings (incl. MySQL) for GNU Octave
LuaSQL is a simple database interface for the Lua programming language
erlang-mysql-driver - a revamped MySQL driver for Erlang
Myscsh - a Scheme implementation of the MySQL protocol

I probably forgot some other drivers/bindings - if you have any more to add, please let me know!

And if you'd like to create your own implementation for your favourite language: the protocol is documented here and here. Jan's additional notes may also be helpful to get you started.

Using protobuf for designing and implementing replication in Drizzle

Tue, 19 Aug 2008 07:11:03 -0700

So, following the lead of Brian, I spent a few hours of the weekend to create a very simple replication scheme for Drizzle using protobuf for specifying the binary log events.

Since we are developing a replication for a cloud, there are a few things we have to consider:

Servers are unreliable. We shall not trust server, but we shall expect them to crash at the worst possible time (Murphy is a very good friend of mine, you know. He must be, since he visits me very often.) This means that we need to have support to allow statements to be sent to the slaves before the transaction is complete, which means that we need to support interleaving and hence both commit and rollback events.
In order to handle interleaving, we need to have a transaction id, but in order to handle session specific data in the event of a crash (for example, temporary tables), we need to have a session id as well. However, the session id is only needed for statements, not for other events, so we add it there. This will allow the slave to expire any session objects when necessary.
Since we cannot always know if the transaction is complete when a statement has been executed, we need to have the commit and rollback events as separate events, instead of using the alternative approach of adding flags to each query event.
Reconnections are frequent. Since masters go up and down all the time, we have to do what we can to make reconnections to another master easy. Among other things, it means that we cannot interrogate the master of a slave after it has crashed to figure out where we should start replication, so we need some form of Global Transaction ID to be able to decide where to start replication when connecting to another master.
In our case, we want the transaction id to be transferable to other servers as well, so we combine the server id and the transaction id to form the Global Transaction ID for this replication.
Since reconnections are frequent, we also need to have techniques for resolving conflicts between events, and using a timestamp is such a one. To handle that, we add a timestamp to each event, and we make room for a nano-precision timestamp immediately, meaning that we need at least 64 bits for that.
Network is not reliable. We expect the cloud to be spread all over the planet, so we cannot really trust the network to provide a reliable transport. This means that we need some form of checksum on each event to ensure that it was transferred correctly.

Now, for the first simple implementation, we're aiming at a statement-based replication, which means that there has to be a way to transfer session context information. Remember that statement based replication just sends statements that change the existing state of the database, but which is also dependent on the context for the session. For example, the INSERT statement below cannot be reliably replicated without the value of the user variable @foo.

SET @foo = 'Hello world!';
INSERT INTO whatever VALUES (@foo);

This is already handled in MySQL Replication by preceeding each query log events with a sequence of context events, but for this solution there is another approach that is more appropriate: adding a set of name-value pairs to the query event. Another problem is that there are functions that are non-deterministic, or context dependent in other ways, but these can be handled by rewriting the queries as follows:

Instead of...use this

INSERT INTO info VALUES (UUID(), 47);

SET @tmp = UUID();
INSERT INTO info VALUES (@tmp, 47);

Now, the protobuf specification for all the above items, including some events used to control the storage of the binary log, is:

package BinaryLog;

message Header {
  required fixed64 timestamp = 1;
  required uint32 server_id = 2;
  required uint32 trans_id = 3;
}

message Start {
  required Header header = 1;
  required uint32 server_version = 2;
  required string server_signature = 3;
}

message Chain {
  required Header header = 1;
  required uint32 next = 2;            // Sequence number of next file
}

message Query {
  message Variable {
    required string name = 1;
    required string value = 2;
  }

  required Header header = 1;
  repeated Variable variable = 2;
  required uint32 session_id = 3;
  required string query = 4;
}

message Commit {
  required Header header = 1;
}

message Rollback {
  required Header header = 1;
}

After tossing together a reader and a writer for the format, the result is:

$ ./binlog_writer --trans 1 \
> --set nick=mkindahl --set name='Mats Kindahl' \
> 'INSERT INTO whatever VALUES (@nick,@name)'
$ ./binlog_writer --trans 1 \
> --set nick=krow --set name='Brian Aker' \
> 'INSERT INTO whatever VALUES (@nick,@name)'
$ ./binlog_writer --trans 1 \
> --set nick=mtaylor --set name='Monty Taylor' \
> 'INSERT INTO whatever VALUES (@nick,@name)'
$ ./binlog_reader
# Global Id: (1,0)
# Timestamp: 484270929829911
set @name = 'Mats Kindahl'
set @nick = 'mkindahl'
INSERT INTO whatever VALUES (@nick,@name)
# Global Id: (1,0)
# Timestamp: 484330886264299
set @name = 'Brian Aker'
set @nick = 'krow'
INSERT INTO whatever VALUES (@nick,@name)
# Global Id: (1,0)
# Timestamp: 484391458447787
set @name = 'Monty Taylor'
set @nick = 'mtaylor'
INSERT INTO whatever VALUES (@nick,@name)
$ ls -l log.bin
-rw-r--r-- 1 mats bazaar 311 2008-08-19 14:22 log.bin

Protobuf Rocks! You find the branch containing the ongoing development of this at Launchpad. Right now, there is no changes to the server, we want the format to be stable first, so the branch is merged on a regular basis to the main tree as well.

Branch: lp:~mkindahl/drizzle/replication-simple
Blueprint: drizzle/replication-simple

Sybase sues database start-up in east Texas court favored by 'patent pirates'

Tue, 19 Aug 2008 05:50:42 -0700

In a lawsuit filed quietly in a federal court in Texas that is seen as a favorable venue for patent suits, Sybase is charging start-up Vertica Systems with infringement.

Worse than DDOS

Tue, 19 Aug 2008 03:21:50 -0700

Today I worked on rather interesting customer problem. Site was subject what was considered DDOS and solution was implemented to protect from it. However in addition to banning the intruders IPs it banned IPs of web services which were very actively used by the application which caused even worse problems by consuming all apache slots which were allocated to the problem. Here are couple of interesting lessons one can learn from it.

Implement proper error control In reality it took some time to find what was the issue because there was no error reporting for situation of unavailable web services. If log would be flooded with messages about web services being unavailable it would be much easier to find.

User Curl PHP Has a lot of functions which can accept URL as parameter and just fetch the data transparently for you. They however do not give you good error control and timeout management compared to curl module. Use that when possible it is easy. You can implement your own class to fetch required URL with single call while having all needed timeout handling and reporting to match your application needs. If you’re using PHP functions make sure default_socket_timeout has proper value or set it per session.

Set Curl Timeouts Set both TIMEOUT and CONNECT_TIMEOUT as these apply to different connection stages and just setting timeout is not enough.

Beware of PHP sessions “files” handler I already wrote about this topic, but when troubleshooting this all takes another angle. Default file handler means file gets locked while PHP request is being served. In this case because of network stall request could be taking 100+ seconds. Users are inpatient and do not wait so long pressing reload multiple times… which just adds to the list of users waiting on session file lock. This not only makes apache slots consumed at much higher pace but makes it harder to find what exactly is causing the lock because most of offending processes you can find from apache “server-status” will be just waiting on file to be unlocked. I used “gdb” to connect to the process showing high number of seconds since start finding where it is stuck. If it is somewhere in curl module (or mysql - waiting on long query to come back) - this is our query if it is waiting on the session file lock we can get that file and use fuser to see what other processes are using that files - these would be either waiting on locks or owning the lock and so one of them is the process we’re looking for. Things are much easier with say memcached session storage - this does not cause any locks for parallel session use so only the process which actually stalls waiting on external resource will show high number of seconds since request start.

Entry posted by peter | 3 comments

Add to: | | | |

PHP to Javascript | Hyperlink Navigation

Tue, 19 Aug 2008 03:21:43 -0700

first pass at a domain driven architecture

Mon, 18 Aug 2008 18:56:33 -0700

We've completed the first pass of the new domain driven architecture, so I wanted to give you an update about how it works and tell you about some of the interesting things we found out along the way.

How It Works

The basic idea of the domain driven architecture is to separate your application's business logic from the underlying nuts and bolts, so that you can use several different applications interchangeably or migrate from one platform to another while preserving the essential core of your application. Another related goal is for you to be able to write business logic code in business terms, so instead of working with tables in a database or their equivalents, you can work with business concepts such as customers, orders, and invoices. Later, when someone else comes in to read your code, it will be easier for them to understand what you've done and help maintain it going forward.

To do this, we have implemented a set of entity class objects representing the data model of opentaps. Most of these classes are automatically generated from the original entitymodel XML's, and you can extend or reuse them to implement more complex business logic. The actual interactions with external resources such as databases or legacy services are defined as Java interfaces, and a central domains directory defines where they are implemented. So, you can define an OrderRepositoryInterface of the methods for obtaining order related information, and your Order domain can interact with this interface. The actual implementation of it is in the OrderRepository, and at run time opentaps will use Spring to look in the domains-directory.xml file to find the order domain that you are actually using and get the order repository from it. If later you decide to switch from the opentaps order management system to another order management system, you can just re-implement all the Order domain interfaces, and the core order processing logic will continue to function.

What We Learned

A couple of the things that we realized as we work on defining the domain driven architecture:

We decided it was necessary to implement a true object layer for the opentaps data model. I originally thought that this was unnecessary, and we could just extend the old ofbiz GenericValue into our domain objects as needed. This turned out not to work, though, because we'd be stuck with chunky GenericValue objects instead of lightweight Java objects. More importantly, it would have introduced logical inconsistencies. For example, imagine if you implemented a method called getTotal(), and later somebody introduced a field called "total" to an entity. If you were extending GenericValues to Java objects, get("total") and getTotal() would give you different results.

My original plan also called for an Infrastructure class which provided the "plumbing" for each platform. Thus, for the ofbiz-based applications there would be the delegator and dispatcher, and for a hibernate-based application there will be a session factory, etc. etc. This actually made the code very messy, because we could not define a common Infrastructure interface which could be used across domains. Each domain with have to use an Infrastructure which is specific to its framework. After further thought, though, I realized that what we really needed was a common Infrastructure that was global to all the frameworks used in opentaps. This did not compromise the reusability of any particular domain implementation, and has a nice benefit of being able to transfer resources from one platform to another.

The Next Step

Now that we have the structure in place, we will be reimplementing some of the existing features with the new domain driven architecture and in the process refine it further. The initial emphasis will be on parts of the application that could really use an object-oriented redesign, either to improve code manageability, like the Party and contact information features, which could benefit from inheritance, or performance, by rewriting some old minilang XML code in Java.

GSoC Weekly Report - Week 10-12

Mon, 18 Aug 2008 15:15:58 -0700

Project: MySQL Forge RSS/Atom feeds

Note: I was on vacation for the last 2 weeks (10+11) and didn't have internet access, so this report is a little later than it should be.

KEY ACCOMPLISHMENTS LAST WEEKS

* Installing SeleniumRC.
* Writing basic SeleniumRC test script for the Forge.
* Writing PHPUnit tests for all HiPPo classes.

KEY TASKS THAT STALLED LAST WEEK

* None

KEY CONCERNS
* None

TASKS IN THE UPCOMING WEEK

* Prepare for extending the SeleniumRC test script to cover a larger part of the Forge.
* Submitting the Final Student Survey.

I would like to thank Google, MySQL and specially my mentor Jay for the opportunity to collaborate in the open source community and let me contribute something which will be useful in the future to many other open source hackers I hope.

Running with Alexander Stubb

Mon, 18 Aug 2008 14:15:07 -0700

On Saturday, I spent a couple of hours running with Alexander Stubb. No, Alex is not our newest recruit to the MySQL Support Team, he’s the Foreign Minister of Finland (the guy in the yellow T-shirt below).

But let me rewind to the beginning. I have been increasing my running to over 1200 km a year, and when I heard that the Münchner Stadtlauf half-marathon doesn’t crash with Finnish midsummer (something never to be missed), I registered for it and finished it in 1:45:58. And I calculated I would have a chance at going below four hours at a full marathon, so I registered for the Helsinki City Marathon, which took place last Saturday on 16.8.2008 in my native Finland.

At a restaurant close to the Olympic Stadium just before start, I met with my long-time MySQL colleagues Patrik Backman and Giuseppe Maxia, as well as our fresh Sun colleague Peter Eisentraut, of PostgreSQL fame. When the time for the start (fairly late, three o’clock in the afternoon) approached, I went for the start area, and was pleasantly surprised to hear the race moderator interview our Foreign Minister.

To continue my pleasant surprise, Alex said he was not only going to be the starter of the marathon, but he was going to run himself. The interview was started in Finnish, and then went on to Swedish. “What’s your target time?” the interviewer asked. “Oh, I have one, but I haven’t published it” — he evidently had the same marathon goal communication policy as I. The interviewer swapped away from our two domestic languages to foreign ones, going over English and German to French. And Alex continued smoothly and fluently in his colloquial, youthful jargon in all of the five languages. He had a targeted and clearly unscripted message in all languages. In German, for instance, Alex shared how Frank-Walter Steinmeier (Germany’s Foreign Minister) the day before had found him to be slighly mad for going for a marathon, right after a week full of intense Georgia related negotiations.

The interview formed a very good pep talk for us runners, and an opportunity for me to no longer have to be ashamed of the linguistic disabilities of our Foreign Minister (as used to be the case when I grew up, leading to a whole genre of jokes). I share Alex’s view that one can show respect by speaking the language of the audience (and Helsinki City Marathon has a very international audience).

So off we went, and the start went well. The weather conditions were perfect: Drizzle. And 16-18 degrees. Not too cold, not too hot. A few km after the start, I saw Patrik, Giuseppe and Peter amongst the spectators, and they took some pictures of me.

The race passed by the Parliament Building, went out through familiar parts of Helsinki (Mejlans, Tölö, Munksnäs) to Esbo close to where MySQL’s first Finnish offices were, and continued via the Nokia House to the absolute centre of Helsinki. That was the most enjoyable part of the running experience. I kept to my pace, and I felt like being on a train. I just went with the flow and didn’t feel any effort. But after a while, my left knee started to remind me of its existence. Past the 21,1 km half way mark, it evened out as my right knee also begged for some attention. But basically, everything went fine until a while beyond 30 km, when the energy reserves of my body were depleted.

“So why didn’t I drink more of the Gatorade offered?”, the running reader will ask. Well, I had prepared a perfect excuse for the scenario in which I would have to stop: When in India for the MySQL Camp in Bangalore, I had caught salmonellosis. Salmonella bacteria are not a nice companion if you run, but also not total inhibitors for running. That said, I chose not to upset my stomach any further by consuming potent, unfamiliar drinks. And the effect was that I dropped from 1606th position at 30 km to 1970th position in the goal (out of 5436 participants).

So this meant that the last few km weren’t all that enjoyable. But I still didn’t get cramps like the 55 year old male athlete who was screaming “aijaijai” less than a km from the goal, at as many decibels as his lung capacity allowed. Humbling.

I finished at 3:55:22. That’s my best marathon ever, about 45 min faster than my previous personal record. I didn’t feel well in the goal, rolling back a few of my latest drinking transactions just after crossing the finish line (honest, it was just water).

After the run, I went for something very typically Finnish: A sauna. And there can hardly be a better timing for a nice, warm bath than right after getting into the goal of a cool (at least temperature-wise) marathon run. My body is often out of balance in many ways after a marathon, including but not limited to shivering out of cold. Ah, was it nice to relax and do some joint bragging together with fellow runners in the Olympic Swimming Stadium. And after the sauna, there were still plenty of runners coming to the goal area. I felt zero superiority over them, as I still vividly remember my own first marathon with a time of 5:41. A marathon for an amateur must strictly be about competing against yourself. And if you (like me) start lousy enough, you’ve got an easy target to beat.

And, what happened with Alex Stubb?

Well, he had shared with us that he had finished a previous Helsinki marathon in 3:59 and a Brussels marathon a bit below 3:40. I suspect he might have targeted 3:30, and like me, he lost positions towards the end of the race. At any rate, he finished 688th with a fabulous time of 3:31:25. Extremely impressive for anyone, especially for a Foreign Minister!

(Yes, Alex did wear something slightly more appropriate than a suit for the marathon).

mysqlbinlog Tips and Tricks

Mon, 18 Aug 2008 14:15:06 -0700

So, you have a binlog. You want to find out something specific that happened inside of it. What to do? mysqlbinlog has some neat features, which I thought we would look at here.

I should first explain what mysqlbinlog really is. It is a tool that lets you analyze and view the binlogs/relaylogs from mysql, which are stored in binary format. This tool converts them to plaintext, so that they’re human-readable.

For the first tip, let’s start with the --read-from-remote-server option, which allows you to examine a binlog on a master server in order, perhaps, to dump it onto your slave and compare master/slave logs for potential problems*.

$ mysqlbinlog --read-from-remote-server -uwesterlund -p mysql-bin.000001 -h 127.0.0.1 -P 3306 | head -5
Enter password:
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#080815 19:25:23 server id 101  end_log_pos 107 	Start: binlog v 4, server v 6.0.5-alpha-log created 080815 19:25:23 at startup

Pretty useful!

Now, let’s assume we have a binlog that is 94 lines long*:

(more…)

Open Source is not making enough rich people richer

Mon, 18 Aug 2008 12:14:55 -0700

I keep seeing this posts by some of the manager types on planet MySQL about how they or some other guy is worrying about open source vendors not raking in billions or are not stealing billions of money out of peoples pockets that should not be playing on the stock market and things along those lines. While I do agree that its great to see open source software flourish .. actually let me clear that up, why do I even care if open source software flourishes? I care because I think open source software enables a different kind of growth for society, one that is shared, one that lowers barriers, one that I feel is more in tune with a world at peace.

Of course I want people that take part in this to be able to provide themselves and their families a decent life. But the fact of the matter is, these people do not need millions, the people that use this open source as an enabler do not need millions in marketing budgets either to realize the usefulness. Of course market capital can help in funding boring tasks like QA and documentation or full time developers etc. But the show will go on even without that.

And guess what? Those small companies that make a buck with open source, they foster a culture where people go home happy at the end of the day instead of being bitter like most others. This is of course not something easily valued in monetary terms, but its nice that the pool of would be homocidal maniacs is reduced by these companies. At the same time even big guys are making plenty of money and giving back a bit here and there. New companies are popping up and slowly making it to big bucks too. So whats the point of this blog post? Lets get over this obsession with these companies that are supposed to make a few people insanly rich by selling their life (aka modern slaves) to VCs that are owned by people that haven't figured out yet to do better things in their lifes but to stack their millions higher and higher.

Reminds me about the irritation I see these aquisition rumors: Of course it matters to people who their are getting gobbled up by and so its not trivial that a Microsoft goes knocking at the doors of Yahoo (not that Yahoo is a small shop or entirely dependent on open source, but from the outside it seems none the less sufficiently open sourcy for a fair share of the tech staff). Do they really believe that key people will stick around after they are required to use their new "@microsoft.com" email address? Especially since these key people have plenty of options. Then again I guess these guys know their risk-analysis 101 and they are mostly after buying users.

While I am trying to save the world, I might also want to mention that its time we kick the system a serious jolt. I like standards, but the out dated processes by ISO and the likes are making a mockery if the idea. Seems like a little less corruption in such public services is too much to ask. Anyways, lets end this post not well formed </rant>

Must we always escape values?

Mon, 18 Aug 2008 12:14:55 -0700

One of the cardinal rules of writing web applications is to escape user-generated input with functions like PHP’s real_escape_string. This is a great rule, but one that can have a negative impact on your application’s performance if used unnecessarily. For instance, when querying data with an integer parameter that is passed internally (not [...]

BusinessWeek on Open Source at Red Hat

Mon, 18 Aug 2008 10:16:51 -0700

Whitehurst is focused on growing Red Hat's top line; will open source purists cheer him on? READ MORE

The ultimate tool for generating optimal my.cnf files for MySQL

Mon, 18 Aug 2008 09:16:03 -0700

There are quite a few “tuning primers” and “my.cnf generators” and “sample my.cnf files” online. The ultimate tool for generating an optimal my.cnf is not a tool. It’s a human with many years of experience, deep knowledge of MySQL and the full application stack, and familiarity with your application and your data.

I don’t know exactly the percentage, but quite a few of the servers I take a look at have been “optimized” with some tuning primer or question-and-answer script that spits out “optimal” parameters for my.cnf.

Most of the time these servers are far from optimal. Sometimes the my.cnf parameters are extremely wrong, to the point of causing a severe performance penalty.

If it were as easy as writing a tool to do this, don’t you think Maatkit would have mk-optimal-mycnf already? In my opinion — as someone who knows very well the complexity of creating a good my.cnf — it’s practically impossible. Much harder than syncing data, or manipulating a replication hierarchy, or any of the other things Maatkit can do already. And I doubt I’ll ever even feel motivated to try creating such a tool.

Don’t bother with scripts. Don’t waste your time with most of the advice you see on the web in forums — much of it is fundamentally wrong, even when it seems to come from an informed source. Don’t put too much faith in the my.cnf samples that come with your operating system; many of them have very bad advice in the comments, such as instructing you on how to set up replication in ways that guarantee breakage.

If you want solid advice, ask someone who knows what they’re doing (and can prove it). Or buy our book.

But even more fundamentally, you should not focus so much on my.cnf. It is not the be-all and end-all of performance. Tuning your server settings has far less impact on performance than tuning your schema, indexing, queries and — you guessed it — thinking deeply about your application architecture. Server settings are a distraction and a waste of time for most people.

Most my.cnf files I see only need minor tweaks, which give only so-so performance improvements. Tuning my.cnf only helps a lot when my.cnf has extremely bad parameters. The kind you’ll get from tuning primers and automated my.cnf optimization scripts.

Entry posted by Baron Schwartz | 13 comments

Add to: | | | |