As an exercise I wrote a proof of concept of a proxy for MySQL. It isn't using any code from the MySQL Server, only some headers files. I only used the internals document and implemented the server-side protocol of it.

After having the basic stuff running:

• socket handling
• mysql wire-protocol
• len-encoding of some fields
• field-types
• the life-cycle of a connection

... I could finally use the mysql command line client to connect to my dummy-server. The mysql-clients sends a few statements at startup:

select @@version_comment LIMIT 1;
select USER();

Detecting slow queries

Proxying requests is pretty simple, just speak the server protocol one side and the client on the other just forward the packets as the come in. Pretty straight-forward.

As extension of the initial task we want to find out which queries are causing a …

A little less than a week ago, I opened a zoomerang survey about database stored procedures. In this post, I'm presenting the results.

In just a few days, the survey was visited 232 times, and no less than 155 people completed the survey! (There was a very small (2) number of people that answered some, but not all the items, and these are excluded from the results proper.)

I didn't really know what to expect, but I did not expect as much as this! So, I'm quite pleased, and I want to thank everybody that took the time to complete the survey. Thank You very, very much, I appreciate your efforts a lot!

Before we Begin
Before I present the results, I want to explain a few things about the survey.

First of all, I want to stress that the results I am about to present are not to be generalized. There is no way to discover to what …

Every database that houses user generated content is a master in at least 1 pair within my environment. To make sure that id's don't collide we engineered around the auto-increment functionality or lack there of in mySQL-4.1. For the most part the auto-increment is externally generated.



mySQL 5.0 introduces a cool feature that increments auto increments different per mysql instance.

In my my.cnf file I define this:

auto_increment_increment = 5
auto_increment_offset = 1


This means that increments will be produced by 5 so for example starting from 1
1,6,11,16, etc.

On another server I define

auto_increment_increment = 5
auto_increment_offset = 2

This functionality is cool, but there are limitations:

I can only have 5 masters to generate this sequence before I need to do some …

It is possible to use an active - active shared-disk cluster in MySQL in some cases. For doing this you have to fulfill the following requirements:

<ul> <li>Works with MyISAM tables only.</li> <li>POSIX-locking compliant cluster file system on the device (such as OCFS2 or GFS).</li> <li>External locking must be enabled.</li> <li>The MySQL query cache must be turned off.</li> <li>The MySQL delay key write must be turned off.</li> <li>OS where file locking is supported in MySQL.</li> </ul>

It is possible to use an active - active shared-disk cluster in MySQL in some cases. For doing this you have to fulfil the following requirements:

• Works with MyISAM tables only.
• POSIX-locking compliant cluster file system on the device (such as OCFS2 or GFS).
• External locking must be enabled.
• The MySQL query cache must be turned off.
• The MySQL delay key write must be turned off.
• OS where file locking is supported in MySQL.

Interested? To read more, follow the link: MySQL active - active Cluster (PDF 157 kbyte).

Related pages

• A very interesting comment from Peter Zaitsev you MUST read. He comments, why this set up you should NOT use: …

Jonas just switched me on to Gnome SSH Tunnel Manager - a simple GNOME app that stores a list of SSH tunnels you want and can automatically start and stop them.

Totally useful for those who travel (hrrm.. fair few MySQLers there) and/or always have SSH tunnels to places (hrrm… MySQLers there too).

There’s a debian package up there (and you can build one easily) but it’s not yet in the Ubuntu archive… maybe for the next release. But works fine on edgy for me!

Made it out to the Boston MySQL meetup tonight. I haven't been out for awhile, is nice to be back and see folks.

Tonight Brian DeLacey talks about Ruby on Rails and Reporting, looking at MySQL through the eyes of Ruby. Interesting that when Brian asks MySQL folks about the languages that they use there are folks from all walks; Perl, PHP, Ruby, Java, Python. And many hands that go up for two, three or four languages. I wonder how MySQL users compare with other DBMS system users as far as multi programming language experience and use.

Brian essentially is giving an introduction to Ruby, Rails, and then digs down into how it interacts with MySQL looking at ActiveRecord and concluding with how to get down to the direct SQL layer.

A side note: Brian lives right cross the street from me and also works at home. A post is brewing about that and some of the interesting intersections of our work.

This year many American states and Canadian provinces will switch to daylight saving time on March 11 (the second Sunday of March) instead of switching on the first Sunday of April as happened during previous years.

I live in absolute and abject poverty. No, I don't mean financially - I've been blessed beyond what I deserve and MySQL pays me very well. What I mean is that I am utterly time poor. Isn't it amazing how you can be so free in terms of lifestyle and yet have so little time to enjoy and benefit from it all? There are so many options and choices in the world that many times you have a hard time knowing just what to say "Yes" to and what to turn down.

Trust me - you'll face this dilemma at the upcoming MySQL User's Conference. Our chairman, Jay Pipes, has outdone himself and put together the most engaging and power-packed agenda that we've ever had. The presenter's list reads like a who's who in the open source and modern database world, with sessions that address everything from deep dives down into database internals to the business side of implementing online database applications. You will find yourself hard-pressed to put together a …

Want to know where the US Navy, Flickr, United Online, NASA, Facebook, Ticketmaster, Google, and others are going to be hanging out April 23-26 in Santa Clara, CA? The MySQL Conference & Expo. I'm not normally one for conferences (which sounds strange, given that I started one), but this is one of those foundational events that it's important not to miss. (I'll also be at the Red Hat Summit for the same reason.)

When Tim O'Reilly is on the board, MySQL must be doing something right. What it's doing is making lots of money, providing lots of free downloads, to lots of mission-critical applications. And if you want to grok how and why, it's good to be …