When we tried to clean up a rather large (4.500.000 rows, 20GB) InnoDB table some days ago, we were astonished by the time MySQL took to complete the task. We had already LIMITed the transaction size, but every single chunk still took minutes to execute. The table itself contains some number columns, including a numeric primary key, and a blob. The delete condition was mainly based on the primary key (being smaller than a predefined value) and status field. After some mails between the support crew and us an optimizer bug was identified: MySQL Bug #28554.

The problem is that in some cases the optimizer makes a bad choice concerning which index to use. It will pick a secondary index that can be used to cover a WHERE indexed_column=<constant> condition, even though it will cause way more data to be scanned than necessary. The primary key for …

Maybe this is obvious, but I post it anyway, just to remind myself should I need it again.

Recently I had to change a table that I had not completely thought through when I first created it. The structure was so simple, I did not think I could do anything wrong with it:

CREATE TABLE `parent` (
  `par_id` bigint(20) NOT NULL,
  `somevalue` varchar(20) default NULL,
  PRIMARY KEY  (`par_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `child` (
  `x_parid` bigint(20) default NULL,
  `value` bigint(10) default NULL,
  KEY `fk_parid` (`x_parid`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`x_parid`) REFERENCES `parent` (`par_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

There is a 1:0..* relationship between parent and child. Some sample data:

mysql> select * from parent;
+--------+--------------+
| par_id | somevalue    |
+--------+--------------+
|      1 | Parent No. 1 | 
|      2 | Parent No. 2 | 
|      3 | Parent …

Maybe this is obvious, but I post it anyway, just to remind myself should I need it again.

Recently I had to change a table that I had not completely thought through when I first created it. The structure was so simple, I did not think I could do anything wrong with it:

CREATE TABLE `parent` (
  `par_id` bigint(20) NOT NULL,
  `somevalue` varchar(20) default NULL,
  PRIMARY KEY  (`par_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `child` (
  `x_parid` bigint(20) default NULL,
  `value` bigint(10) default NULL,
  KEY `fk_parid` (`x_parid`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`x_parid`) REFERENCES `parent` (`par_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

There is a 1:0..* relationship between parent and child. Some sample data:

mysql> select * from parent;
+--------+--------------+
| par_id | somevalue    |
+--------+--------------+
|      1 | Parent No. 1 | 
|      2 | Parent No. 2 | 
|      3 | Parent …

Closing keynote of the 2007 MySQL Conference is Pasha Sadri from Yahoo! talking about pipes.

Pasha starts with a story about looking for an apartment near a park (this happened to him a few years ago). It was very tedious to go looking on Craigslist every hour clicking on every apartment and looking to see if the apartment was near a park.

Made a 50-line Perl script to merge database between Craigslist and Yahoo! local to get data sets. This turned into Yahoo! pipes which provides a visual editor to take data sources and create a set of controls over that data source and output the data in a certain way.

If you haven't seen pipes it is hard to describe, but very cool.

For design, pipes relied heavily on the idea of Unix pipes. The goal to …

Listening to Dathan Pattishall talk about flickr at the 2007 MySQL User Conference. Dathan worked at AuctionWatch in 1999, then in 2003 worked at Friendster, now at Flickr.

Flickr was unable to keep up with demand. Replication was not working, too much slave lag. They came up with some requirements. Needed to support a write intensive site with multiple masters. There should be no single point of failures. Need to have real-time maintenance and be able to serve pages extremely fast.

At AuctionWatch they put folks on separate boxes. At Friendster they had an algorithm that spread folks across many machines. At Flickr they use federation, which is made up of shards, a global ring, and logic to connect shards.

Shards are a slice of a main database. Flickr uses active master-master replication but externalizing the auto increment process.

The global ring is a …

This morning as I was driving over to the MySQL conference I thought about something Guy Kawasaki said yesterday. He said that one indicator of a sketchy VC-seeking group is if their proposal includes a large chunk of money for database licenses ("a million dollars"). The idea was that if you're building a new product there's a better chance of funding if you use something like MySQL and don't have to spend a lot of the investment on your database.

Flash back to mid to late 90s. From my limited experience this was opposite. The company I worked for was using Oracle for one reason, because it made them look serious when talking to funders. It meant that we were serious about scalability and poised to handle the heavy click loads. (The ironic thing is that we weren't. The application fell flat on it's face during even minimal …

With palm's recent announcement that it will have a Linux-based Treo by year end and all the hoopla around the upcoming apple OS X palmtop, this seems like a really good time to be in the FOSS world.

I haven't seen the specs of either OS yet, of course. But I can guess at the hardware that they will both debut on, and I'm fairly confident that both devices will be more powerful than the first Linux box I ever worked on. I'm hoping that the end result will be a full enough set of posix libraries to move the LAMP stack with little trouble. "Little trouble" being a relative term, of course.

read more

I've heard a lot about Ruby on Rails, and finally had occasion to try to use it. This tutorial was very well laid out, and entirely true - maybe a half dozen commands and a couple dozen lines of code later, there was a nice set of "scaffolds" for browsing & editing my data model.

It didn't "guess" my data model, but I prefer to tell a framework how to handle relationships. Telling rails was as simple as adding a handfull of commands like "has_one :parent", "belongs_to :category". And then you get drop-downs for foreign key values and column sorting virtually free.

read more

In August 2006 I made a post about the MySQL blackhole engine and its possible use for replication scenarios.

Back then the primary focus was to reduce the amount of data being transferred between master and possible many slaves during normal business. This speeds up processing on the slaves, because they have considerably less data to first store to their relay logs and then immediately discard again.

What still takes a lot of time is the initial setup of a slave system. This is because there is a potentially large SQL based dump file to be executed on maybe relatively low-end systems (e. g. Celeron CPUs and single IDE hard drives). While this can be made to work reliably with a set of scripts to automate the process of downloading a zipped SQL file from a local server and running it against the clients' databases, it …

In August 2006 I made a post about the MySQL blackhole engine and its possible use for replication scenarios.

Back then the primary focus was to reduce the amount of data being transferred between master and possible many slaves during normal business. This speeds up processing on the slaves, because they have considerably less data to first store to their relay logs and then immediately discard again.

What still takes a lot of time is the initial setup of a slave system. This is because there is a potentially large SQL based dump file to be executed on maybe relatively low-end systems (e. g. Celeron CPUs and single IDE hard drives). While this can be made to work reliably with a set of scripts to automate the process of downloading a zipped SQL file from a local server and running it against the clients' databases, it …