I just wanted to point everybody at a recent blog post by Konstantin. In the post he discusses a solution for dealing with cache invalidation issues of very large caches under heavy load. He points out that cache invalidation can severely bog down the system. The general solution he proposes is to simply deactivate the query cache entirely during invalidation. I think this is an important caveat to be aware of and actually he is asking for feedback if this "solution" is acceptable. I think its awesome that MySQL engineers are giving us the opportunity to provide feedback on such changes. Maybe there should be a dedicated "pipeline" where such requests could be found?

So the other day I stumbled over the slides of the InnoDB talk at this years MySQL users conf and I noticed "gap locking" somewhere in the middle. I have never heard of "gap locking" so I was quite intrigued by what that might be. From what I understand its InnoDB's solution to implementing REPEATABLE READ (though until MySQL 5.1 it seems this feature is also enabled for READ COMMITTED). I guess its a fairly unique approach and from my current understanding to be feasible it expects short running transactions, which luckily are quite common in web applications that most of us care about.

The problem InnoDB is trying to solve here are situations where overlapping transactions cause phantoms. Now the first surprise was when I read that gap locking is implemented by locking the relevant ranges on the index or more particular …

I’ve finally gotten around to adding the last little things that I wanted to do to version 2 of Statpack - my python script for aggregating SHOW [GLOBAL] STATUS output.

Now it connects to a running instance, and allows you to gather statistics from there to generate reports.

Here are the options it supports:

[markleith@medusa:~/Development/statpack/statpack-v2] $ ./statpack.py

Error: No arguments supplied
usage: ./statpack.py [list of arguments]

Non-interactive mode (aggregate txt files containing SHOW STATUS snapshots):

-f –files List of statistics files to aggregate (–files=stat1.txt[, stat2.txt..])
Argument must be first within list of arguments, on it’s own

Interactive mode (connect to running MySQL server for SHOW STATUS snapshots):

-h –host Host for MySQL server to connect to in interactive mode
…

.. and more annoyingly, why I spend the night in Amsterdam, even though the Dutch PHP conference is not until next weekend. So there were apparently some pretty bad thunderstorms over France, Belgium and the Netherlands. As a result the plane that should take me to Amsterdam on the way to Sandjeford arrived about an hour late. Back in the day, when airlines still afforded themselves some ground time, this would not have been that big a deal, as I had no checked luggage and so ideally I could have switched planes in time as I had 1 hour stopover time in Amsterdam. Well these days it means that if the incoming plane comes one hour late, you will likely also be one hour late. Usually they can try to make up a few minutes by burning some more fuel. In this case the thunderstorms actually forced us to fly a non standard route, which took even longer.

Now when they broke the news in Zurich …

I have some warm up questions on how MySQL handles LOBs internally and then some questions on high availability. Especially with HA I feel like I have heard all/most the solutions, but they all seem to lack in one area that really makes you hurt, but I keep stumbling over questions from people asking me which way to go. Finally I have a little backup question for extra credit at the end.

So for the LOB questions, I am wondering if MySQL (or any of the storage engines) store the actual LOB data on separate data pages or not. I am also wondering if the MySQL query cache has any special handling for LOBs, like ignoring all queries that fetch LOB data. I guess there is a setting to set the maximum size for a result set to go into the query cache that could be employed to prevent a few large LOBs to fill up the query cache, but then again you …

Hej hej from Stockholm where I arrived about two weeks ago for a fun-filled two four six weeks of meetings and face-to-face contact with the MySQL Cluster developers. And the chance to check things out when it’s not 2°C and raining, all the time, as it was when I was here in November and December of 2005. (This week, it’s 12°C and kind of misty.)

The last five or six weeks have been pretty hectic, and this evening (it’s just after 10 PM local time as I write this) represents the first time in that span that I’ve actually had time that’s not been planned out for me in one way or another. At the end of April, I moved out of my place in Brisbane and stuffed all my gear into storage in Coffs Harbour, New South Wales, where I rented a post office box that now constitutes my sole legal address for the duration.

That last sentence is actually …

I realized when I released my very crappy version of My ?hourly? MySQL monitor script I really should have included my standard logging.

So I did that the night I wrote my original blog, but never published it. I’ve had need to use it again today, so a few more usability tweaks for parameterization and we are good to go.

Now Version 0.03 includes three files:

• hourly.sh
• common.sh
• mysql.conf

Simple use is:

$ cd /directory
$ vi mysql.conf
# correctly specify MYSQL_AUTHENTICATION
$ chmod +x ./hourly.sh
$ nohup hourly.sh &

This gives you the following files

-rw-r--r-- 1 rbradford rbradford  2643 2007-05-29 15:47 mysql.innodbstatus.070529.154757.log
-rw-r--r-- 1 rbradford rbradford   414 2007-05-29 15:47 …

.. is not so easy. I have been traveling for 4 weeks in USA, then this weekend I was at a wedding back in Germany. This weekend I will be trying out for the German national mixed frisbee team at the BUM (I rate my chances of making it pretty low - injured finger, never found a top level team I wanted to play in, which means that all the top level players do not know me that well .. and unfortunately I am also simply not that uber-good .. but hey I can try).

The following weekend I was also planning on going to play frisbee at the HUT in Halle, but since I will be in Norway the following weekend and the Netherlands the weekend after that, I guess I should really skip Halle. So I will try to go out and buy a bed, mattress, table and a cupboard or …

It seems the slides that generated the most interested in my recent "Database meets OOP" talk were the ones on how to model inheritance inside your RDBMS. I wanted to mention two (and a half) additional approaches.

The first one I have no real experience with and is native inheritance support that can be found in Object-Relational DBMS (not to be confused with Object-Oriented DBMS, which are not relational). PostrgreSQL is a shining example of this, so they have native support for inheritance. Actually the PostgreSQL docs mention that since SQL99 inheritance is part of the spec, not sure if anyone has implemented it though. From my reading it looks like its just a convenience feature to spare you from having to writing joins (when fetching data from a child …

Well this is going to be a short blog post that will not really manage to summarize all the experience on my San Francisco, Santa Clara, San Francisco, Austin, Tampa, Boston, Chicago, Zürich, Berlin, Güstrow, Berlin, Zürich round trip I was on for the last 4 weeks. But anyways, I wanted to mention that the slides from php|tek are now linked in the slides section.

I did a talk on various strategies of how to bridge relational databases and object-oriented programming. In retrospect I should have submitted this as a "double feature" talk, so that I could cover more ground on the actual using of the existing packages in the PHP space.

The other talk was on database locking. I think the biggest eye opener for people in that talk was about how MVCC, found …