Hartmut was asking me some time ago how table discovery in the storage engine interface works. After reading through the code from ndb, archive and memcache I was a bit disappointed: all of them are just copying the definition in binary form around.

For the wormhole SE the lua-file has to create table structure on the fly. You only drop in the .lua into the db-folder and a SELECT will pick it up automaticly.

We use a small function which returns the table definition:

function discover()
        return {
                { name = "fld1", type = 1 },
                { name = "fld2", type = 2 },
                { name = "fld3", type = 15, length = 64 }
        }
end

A SHOW CREATE TABLE against the table shows us:

root@127.0.0.1:test> show create table foobar\G
*************************** 1. row ***************************
       Table: foobar
Create Table: CREATE TABLE `foobar` (
  `fld1` tinyint(4) …

I got a few comments about my last example not describing a wormhole, but a whitehole. Time to improve the picture a bit and getting data from another dimension on a shorter route than the long standard way.

To use the picture let's take a look at what has been done:

root@127.0.0.1:test> select * from finance where symbol = "MSFT";
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
| symbol | last_trade | trade_time          | trade_change | open_trade | max_trade | min_trade | volume   |
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
| MSFT   |      29.84 | 2007-10-05 00:00:00 |         0.13 |      29.84 |     29.99 |     29.73 | 45016520 | 
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+

root@127.0.0.1:test> select * from finance; …

Brewer's Conjecture: "Consistency, Availability, and Partition Tolerance. Pick two."

Database ACID is something nice to wish for, but for real loads in real environments, it's not there to be had. Suck it up, move on, and do something else.

Reference: Brewer's Conjecture and the Feasibility of Consistent Available Partition-Tolerant Web Services.

Almost two weeks have passed since HighLoad 2007 conference in Moscow, Russia so I'm pretty late with feedback and my only excuse I was pretty busy to spare some time for it.

Lets start with bright side.
The conference had a lot of great sessions by experts in their field. A lot of top Russian companies such as Yandex Rambler, Mail.Ru had sessions covering their internal architecture. Speakers were very open in many cases sharing data about their internal implementation, for example Yandex would tell you a lot more details about their search process than Google would be willing to share. I specially mention Yandex here as they had very well presented team on the conference and had largest number of sessions presented …

If you're using MySQL with the MyISAM engine for a high-writes application, delay_key_write is usually very good.

Let me explain further: delay_key_write is a table option which causes the database NOT to flush the MyISAM key file after every write. This is a really good thing, as if you're doing another write very soon anyway, this is likely to just waste I/O time.

This doesn't sound like a good idea right, because it means that if the power fails (or mysql crashes, or something), then you'll be left with a broken index file? No, it's still a very good idea:

• delay_key_write does NOT appear to affect the MyISAM data file - that will still be flushed according to the normal policy.
• If you had delay_key_write off, then a power failure or crash during the index write would cause the same level of corruption.
• Broken MyISAM index files need to be rebuilt, regardless of how little …

Recently I had one customer for consulting and aside from mysql optimization, etc they asked me for cacti installation/setup to monitor their pretty generic LAMP application. I’ve started setting up all this stuff and I’ve never thought it could be so painful… lots of different templates for the same tasks, all of them are incompatible with recent cacti releases, etc, etc… So, this post is generally a list of used templates with a fixes I’ve made to make them work on recent cacti release.

(more…)

So Im now back from our developer conference in Heidelberg via a short stop in Munich for some 1L beers at Oktoberfest.

We had representatives from cross departments and overall it was a great success. We got a ton of good work done in short time. Theres something unique when you get 100+ people in one physical location working day and sometime night over a few days. Its a burst of very effective energy though not sustainable over a longer period and with local beer being way to good to avoid, sometimes in excess...It can't be done over a longer period of time though, regardless of cost, it's to intense and one need to work independently on the identified action items before regrouping again. A week or just short of that is optimal.

Having a department cross section attend is also great. Getting input from other services groups than support also widens the decision making process and becomes more in tune with the overall …

I was reading, this article (subscription required) by Ben Worthen on WSJ about a study on how well companies are prepared for a disaster. I was not at all surprised by (one of) the findings (paraphrased):

…While 70% of 189 tech leaders surveyed by Forrester Research Inc. said that their companies are prepared for a disaster, the reality was quite different…

We run into this all the time, in talking to (prospective) customers. People “think” they are prepared for a disaster. The truth hits them when you start peeling the layers of the supposed plan. Some things to keep an eye on are

• Is it robust and reliable?
• Do you have the right resources engaged in the plan - both design and implementation?
• How often are the plans tested?
• How well is the plan documented?
…

(Also see the follow-up post about some progress)

Today I was (again) facing a log file from a machine that had for some reason not been able to start a temporary MySQL daemon during the night to prepare for a streaming MySQL slave installation. The necessary 2nd daemon had created its new ibdata files, however just after that aborted the startup process with the following message:

Can't start server: Bind on TCP/IP port: No such file or directory
071001 23:09:55 [ERROR] Do you already have another mysqld server running on port: 3310 ?
071001 23:09:55 [ERROR] Aborting
071001 23:09:55 [Note] mysql\bin\mysqld.exe: Shutdown complete

As you can see, the port is a different one from the default MySQL port, so I can be …

Our friend Dathan recently suggested that The Quality of mySQL lately sucks.

I’ve just ran into yet another obvious bug that has made it’s way into production.

While I’m not going to argue that more unit tests would be a good thing, I’d like to point out that what he’s referring to is not so much a bug as it is a change that happened (and was documented) in 5.0.12:

Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The changes include elimination of redundant output columns for NATURAL joins and joins specified with a USING clause and proper ordering of output columns. The precedence of the comma operator also …