Thanks to the EFF for their E-Book Buyer’s Guide to Privacy.
Why I bought a Sony eBook reader
3
Monthly Archives: December 2009
Bullshit Tiger. bullshit.
It also took them over an hour to make the announcement that the flight had been delayed. The screens just flashed “Boarding” for an hour (and they still are).
Delays are fine. Complete and total lack of any communication whatsoever is not.
Simply jumping on a microphone and saying “we’re experiencing a delay, we’ll have more information for you in half an hour” would be better. We seriously heard nothing for a whole hour after “Boarding” had started.
Just a closed door.
Somehow I feel this will be the last time I fly this airline.
The Table protobuf message format
If you’ve ever opened up drizzled/message/table.proto in the Drizzle source tree you will have seen what’s in the table message: the structure that describes a database table in Drizzle. Previously I’ve talked about the Table message more generally, giving a fair bit of history of the FRM file and how we’ve replaced it with both the Table protobuf message and an infrastructure inside Drizzle so that Storage Engines own their own metadata.
Yesterday I talked about the Schema protobuf message format in more detail, and this time I’m talking about the Table protobuf message in a similar amount.
The first time we were loading (then only part of) the table definition out of a protobuf message was way back in January 2009 (I blogged about it too). It was an adventure untangling all sorts of things to get to a much nicer place (where we are now). The code in the server is not perfect… I’ll be the first to admit that some of it is rather strange, but that’s mostly all behind the scenes for people interested in the protobuf Table message!
The Table message has several embedded messages in it too. We need to have information on the Storage Engine, Fields and Indexes (and each of those can have other properties). It is much more complex than the simple Schema message.
Let’s have a look at the basic structure of the Table message:
message Table {
/* *SNIP* (Here goes the definitions for TableType, StorageEngine, Field, Index, ForeignKeyConstrain, TableOptions and TableStats) */
required string name = 1;
required TableType type = 5;
required StorageEngine engine = 2;
repeated Field field = 3;
repeated Index indexes = 4;
repeated ForeignKeyConstraint fk_constraint = 8;
optional TableOptions options = 9;
optional TableStats stats = 10;
}
(We’ve skipped the definitions for the embedded messages for now)
This seems all pretty logical; a table has a name, a type, is in a Storage Engine, has Fields, may have Indexes, may have foreign key constraints, it has some options and statistics (the statistics may go away at some point “soon”).
Let’s have a look at the TableType message definition:
enum TableType {
STANDARD = 0;
TEMPORARY = 1;
INTERNAL = 2;
}
It’s pretty simple, the table type is either a standard table (what you get from CREATE TABLE), a temporary table (what you get from CREATE TEMPORARY TABLE) or an INTERNAL table (what you get when Drizzle uses a temporary table during query execution).
Next, the StorageEngine message:
message StorageEngine {
message EngineOption {
enum EngineOptionType {
BOOL = 0;
INTEGER = 1;
STRING = 2;
}
required string option_name = 1;
required string option_value = 2;
required EngineOptionType option_type = 3;
}
required string name = 1;
repeated EngineOption option = 2;
}
The main part is the “name” member, which is just the name of the storage engine (e.g. “PBXT”, Â “INNODB”, “ARCHIVE”). We do however have support specified in the StorageEngine message for engine specific options (in key value form). Expect these to be used more in the near future.
Specifying Fields is probably the most complex part of the table message. The Field message looks like this (with many embedded messages):
message Field {
required string name = 1;
required FieldType type = 2;
optional FieldFormatType format = 3;
optional FieldOptions options = 4;
optional FieldConstraints constraints = 5;
optional NumericFieldOptions numeric_options = 6;
optional StringFieldOptions string_options = 7;
optional string comment = 16; /* Reserve 0-15 for frequently accessed attributes */
optional SetFieldOptions set_options = 17;
optional TimestampFieldOptions timestamp_options = 18;
}
So… what does this all mean? Well, Fields have a type, they’re stored in a format, there’s options attached to them, there may be constraints as well as field type specific options.
The different field types should be fairly familiar by now:
enum FieldType {
DOUBLE = 0;
VARCHAR = 1;
BLOB = 2;
ENUM = 3;
INTEGER = 4;
BIGINT = 5;
DECIMAL = 6;
DATE = 7;
TIME = 8;
TIMESTAMP = 9;
DATETIME = 10;
}
We also allow fields in different formats. Currently, these are default, fixed and dynamic. The idea is you can tell the engine (or the engine can tell you) how it’s storing the field. This is currently here as a nicety and the users for this are few and far between.
enum FieldFormatType {
DefaultFormat= 0;
FixedFormat= 1;
DynamicFormat= 2;
}
The FieldOptions get interesting though:
message FieldOptions {
optional string default_value = 1;
optional string update_value = 2;
optional bool default_null = 3 [default = false];
optional bytes default_bin_value = 4;
}
You’ll no doubt be intrigued by the existence of both “default_value” and “default_bin_value”. Ordinarily, using a string to contain a textual representation of the default value (e.g. “foo” or “42”) is fine. However, for BLOB columns, you can have defaults that aren’t representable in a text string, you need binary data (e.g. the default value contains ‘\0’).
For TIMESTAMP columns, we continue to support DEFAULT NOW() and the ability to update the timestamp column on UPDATE. How is this represented in the table message? Well… default_value will be “NOW()” and update_value will be “NOW()”. It is intended that in the future it will be possible to have arbitrary SQL expressions for these. This does, of course, require support in the Drizzle server.
The default_null bool should be rather obvious :)
Well… that’s enough for today. Next time: more of the Field message!
The Schema protobuf message: Drizzle’s metadata on a schema
I’ve previously talked about table metadata in Drizzle and how we use the table protobuf message to describe a table (see Drizzle FRM Replacement and others). The model in Drizzle is that the engine is responsible for its metadata. For schemas (you may be thinking ‘database’ but we’re moving to the Schema terminology in Drizzle) we also have a small amount of metadata.
The protobuf message is specified in drizzled/message/schema.proto and is incredibly short. In fact, here it is in its entirety:
| 1 |
package drizzled.message;
|
|
| 2 |
option optimize_for = SPEED;
|
|
| 3 | ||
| 4 |
message Schema {
|
|
| 5 |
required string name = 1;
|
|
| 6 |
optional string collation = 2;
|
|
| 7 |
}
|
We don’t keep an awful lot of metadata about schemas. A Schema has a name and it has a default collation.
You can also read the db.opt file directly using the provided (and very simple) schema_reader utility.
In the near future, we could have CREATE DATABASE and CREATE SCHEMA replicated via this protobuf message. This would make it extremely easy to parse for utilities parsing the replication stream.
We’ll also (rather shortly) have key,value pairs for options to CREATE SCHEMA/CREATE DATABASE. More on that later :)
Photos of Burning Man: Getting to Black Rock City
This year was my first burn. More amazing than I could have imagined. I think it was day two when Brian caught me saying “so, next year what we’re going to do…”
Due to the harsh environmental conditions, I wasn’t too keen on the idea of taking my digital gear (it ain’t cheap) and had the idea of handing cameras to people and having a kind of communal photo album (planning for a larger scale implementation of this next year). So… I went purely film. Several older and smaller compact 35mm cameras that I picked up either for nothing or next to (no loss if lost or dead!) plus a Ricoh SLR was my arsenal.
Now… that means I need film.
I mainly shot the new Kodak Ektar 100 and a Kodak Ektachrome E100VS. For the smaller, cheap 35mm ones, I just used some Fuji Superia.
Trees in Portland
Firstly though, there was a stop in Portland to a) recover from jetlag and b) hang out with Eric, Wendy and their dogs. I do like Portland, quite a lot actually. While there, managed to get some work done, fiddle with some SPARC hardware that Eric has, enjoy excellent vegan ice cream, enjoy awesome vegan food (both at home and out) and walk around both downtown and up in the hills. Portland (and Oregon) is certainly pretty.
Trees in Seattle
Before heading to Burning Man, I was in Seattle, where Leah joined me to prep for (and then go to) Burning man.
Then Leah and I were in San Francisco for a day. This is when I started shooting exclusively film for the first time in… well.. Since 2002 (I got my first digital camera for linux.conf.au in 2003)
Leah enjoying going around San Francisco
We actually did some touristy things… so I saw a bit more of San Francisco than I have before. One issue with mostly being around San Francisco just before/after the MySQL Users Conference is a severe lack of time/energy to go for much exploring. Preparing is exhausting, and afterwards I just want to really chill out – usually heading out to some forest or down to Santa Cruz or just hanging out with cool people.
Going over the Golden Gate Bridge
One of the most surprising things was running into David while just walking down the street. Although knew he was in town, and we’d planned to all go down to Burning Man together, actually running into somebody in the street always surprises me.
I’m pretty sure this was the first time that I went across the Golden Gate Bridge. Seen it, taken photos of it, used said photos as my desktop background, but this was the first time going across it and looking back on the city. Manual focus, moving bus: epic amounts of fun… I’d kinda forgotten how much fun this could be.
Sun going down in San Francisco
There are things I like about San Francisco, but if I had to call somewhere in the US home, Seattle and Portland are both much higher on the list. Maybe it’s because of the wonderful coffee of Seattle, or the laid backness and awesome vegan food of Portland or if I’m just delusional and think it’d be possible to catch a Nirvana gig in Seattle.
In the evening in San Francisco we met up with David again and went down to the beach. It was pretty. Somehow, I deluded myself into thinking “ISO 100 Film, no tripod, cold, sunset…. Photo time!” I did get one I quite like too:
The Sun setting over the water, San Francisco
After what can only be described as a “I love side impact airbags” car crash on the way back to the Hotel (everybody okay: shaken, not stirred. also not our fault), headed back for a stiff drink, some sleep and eagerly awaiting the drive to Reno and then Black Rock City.
Waiting in line to get into Black Rock City (for many, many hours)
It was not a short wait once we got to the gate. We did, however, not too long after sunrise, make it to camp. I have no idea where I shot this from… but it was before we got to camp (or at least the first photo of us helping to set up):
People arriving at Black Rock City: first thing in the morning
More to come… including setup of Pi Camp!
Drizzle FRM replacement: the table proto
Drizzle originally inherited the FRM file from MySQL (which inherited it from UNIREG). The FRM file stores metadata about a table; what columns it has, what type those columns are, what indexes, any default values, comments etc are all stored in the FRM. In the days of MyISAM, this worked relatively well. The row data was stored in table.MYD, indexes on top of it in table.MYI and information about the format of the row was
in table.FRM. Since MyISAM itself wasn’t crash safe, it didn’t really matter if creating/deleting the FRM file along with the table was either.
As more sophisticated engines were introduced (e.g. InnoDB) that had their own data dictionary, there started to be more of a problem. There were now two places storing information about a table: the FRM file and the data dictionary specific to the engine. Even if the data dictionary of the storage engine was crash safe, the FRM file was not plugged into that, so you could end up in a situation where the storage engine
recovered from a crash okay, but the FRM was incorrect for what the engine recovered to. This would always require manual intervention to find out what went wrong and then fix it (in some rather unusual ways).
When the MySQL Cluster (NDB) engine was introduced, a new set of problems arose. Now the MySQL server was connecting to an existing database, where tables could be created on other nodes connected to the cluster. You now not only had the problems of crash recovery, but the problems of keeping the FRM files in sync across many nodes, requiring
all sorts of interesting solutions that, for the most part, do work.
The “obvious” solution to some of these problems would be for an engine to write out an FRM file itself. This is much easier said than done. The file format was never created to be read and written by multiple pieces of software, the code that did the reading and writing inside the server was not reusable elsewhere and the only documentation (that
wasn’t a decent chunk of the MySQL source tree) is the rather incomplete definition in the MySQL Internals wiki (http://forge.mysql.com/wiki/MySQL_Internals_File_Formats) – not nearly enough to write a correct FRM file as the specifics are very, very odd.
Our goals for reworking the metadata system in Drizzle were: to allow engines to own their own metadata (removing any opportunity to have inconsistencies between the engine and the ‘FRM’) and for engines without their own data dictionary, to replace the FRM file format with something simple and well documented.
One option was to use SQL as the standard storage format, but it is rather non-trivial and expensive to parse – especially if we were to use it as the preferred way of talking table definitions with storage engines. We had been looking at the protobuf library
(http://code.google.com/p/protobuf/) ever since its first release and it has a number of very nice characteristics: a description language of a data structure that is then used to generate APIs for reading and writing it in a number of programming languages and a standard (documented) way to serialize the data structure.
After a bit of discussion, we arrived at a good outline for the table definition proto. The current one can always be found in the Drizzle source tree at drizzled/message/table.proto. The current format is very close to final (i.e. one that we’ll suppport upgrades from).
The process of modifying the Drizzle code base so that it would write (and read) a file format different to the FRM isn’t worth going too much into here although there were some interesting hurdles to overcome. An interesting one was the FRM file contains a binary image of the default row for the table (which is in the row format that the server uses); we now store the default value for each column in the proto and generate the default row when we read the proto. Another interesting one was removing and refactoring “pack_flag” – the details of which should only be extracted from Jay or Stewart with a liberal application of fine ale.
The end result is that we now have storage engines that are completely responsible for their own metadata. One example is the ARCHIVE engine. In the CREATE TABLE code path, the ARCHIVE storage engine gets the table definition in an object that represents the table proto. It can examine the parameters it needs to and then either store the proto directly, or convert it into its own format. Since ARCHIVE is simple, it just stores
the table proto in a serialised form (using a standard function provided by the protobuf library) and stores it in the .ARZ data file for the table. This instantly makes the ARCHIVE storage engine crash safe for CREATE and DROP table as there is only 1 file on disk, so no two files to get out of sync.
If an engine does not have its own data dictionary, it can still use the default implementation which just stores the serialised table proto in a file on disk.
We can also now use this interface to move INFORMATION_SCHEMA into its own storage engine. This means we can remove a lot of special case code throughout the server for INFORMATION_SCHEMA and instead just have a INFORMATION_SCHEMA storage engine that says it has the following tables in the INFORMATION_SCHEMA database. Because the table definition is now in a documented format with a standard API, this becomes a relatively trivial exercise.
What we’re all looking forward to is when the InnoDB data dictionary is linked into the new interface and we can have a truly crash safe database server.
Another wonderful side effect is since we now have a standard data structure for representing a table definition, we can integrate this with the replication system. In the “near” future, we can represent a CREATE TABLE in the replication stream as a table proto and not the raw SQL. If you were wanting to apply the replication stream to a different database server, you then only have to write a table proto to SQL
converter. If the target database system doesn’t do SQL at all, you could generate API calls to create the table.
So we now have a rather flexible system in place, with the code implementing it being increasingly simple and possible to be “obviously correct”.
Things that easily fall out of this work that people have written about:
– CREATE TABLE LIKE with ENGINE clause
http://krow.livejournal.com/671235.html
– table_raw_reader – looking at the raw representation of table metadata
http://www.flamingspork.com/blog/2009/10/01/table_raw_reader-reading-the-table-proto-from-disk-and-examining-everything/
– Table discovery
http://www.flamingspork.com/blog/2009/07/29/table-discovery-for-drizzle-take-2-now-merged/
Some more info:
http://krow.livejournal.com/642329.html