So Jonas pointed out that the NDB kernel hasn’t changed too much in size over releases. Let’s have a look:
In fact, the size went down slightly from 4.1 to 5.0. In this, 6.4 and 7.0 are the same thing but appear twice for completeness.
You can see the raw results in the spreadsheet here.
Way back on Monday (at the MySQL Conference and Expo), I gave a full day tutorial on MySQL Cluster. I awoke early in the morning to a “oh ha ha” URL in an IM; but no, it wasn’t jetlag playing tricks with me. Luckily, this didn’t take much (if anything) away from the purpose of the day: teaching people about NDB.
Distracting-and-this-time-really-annoying-thing-of-the-day-2: It seems that O’Reilly had cut back on power this year, and there were no power boards in the room. A full day interactive tutorial, and nowhere to plug in laptops. Hrrm.. Luckily, having over the many years I’ve been speaking at this event, I’ve gotten to know the AV guys okay, and asked them. They totally deserve a medal. Tutorial started at 8:30, I noticed at 7:30, and it was all fixed by 7:45. The front half of the room (enough for everyone coming) had enough power for everyone. It was quite okay to bunch everybody up – means I have to run around less.
This years tutorial was modified from last year (and that does take time, even though I’ve given it many times before). I wanted to remove out of date things, trim bits down (to better fit into the time we have, based on more experience on how long it takes to get interactive parts done) and add a bit.
When we got to the end of the day (yes, I ran over… and everybody stayed, so either I’m really scary or the material is really interesting) I pleaded for feedback. It’s amazingly scary doing an interactive tutorial. You’re placing the success of the session not so much on you, but on everyone who’s come to it.
Sometimes I’ve gotten not much feedback at all; this time was different. I spoke to a number of people afterwards (and some via email) and got some really good suggestions for small changes that would have greatly enhanced the day for them. I was pleased that they also really enjoyed the tutorial and liked the interactivity. I (and it seems a great many others) do not much like tutorials that are just long talks.
People walked out of my tutorial with a good overview of what MySQL Cluster was, how to set one up, use one, do a bit of admin and some of how it works.
I even dragged Jonas up to explain in great detail the 2 phase commit protocol for transactions. Of course, this is detail you don’t ever need to know to deploy – but people are intersted in internals.
So far the session has received an average of 4 stars in evaluations (four five star, two four star and one two star). I’d be really interested in feedback from the person who gave two stars, as this may mean I missed getting something done for them (e.g. providing information, help etc). Even though it is hard to spread yourself around a room of 60-ish-plus people, I do like to do it well. There is the other possibility of people not coming prepared, which will mean they may be bored for a lot of the day if they don’t jump in with another group and help learn that way.
So, I’m rather happy with how my first session went.
Well… Santa Clara – not as cool as California leads you to believe…. but it’ll be an awesome week. See you all there soon.
This year I am again giving a MySQL Cluster Tutorial at the MySQL Conference and Expo. As those who have attended before can tell you, this is a hands on tutorial. I don’t just stand up the front and talk at you for a day, that would be very boring (for all of us). While there is a good amount of presented material (there is a decent amount of theory to get through), there is a large component that involves setting up a cluster, putting data in, getting data out, backup, restore.
So if you’re wanting to learn about MySQL Cluster in a nice and friendly hands-on environment, I can recommend coming to my tutorial.
The tutorial isn’t the be-all and end-all tutorial. It does not teach you everything. It does give you a decent introduction though.
Just noticed that the Drizzle list has had more messages since June last year (2008) than the mysql internals@ list has had since March 2006.
Well… kinda… (and nobody make fun of me for using my MythTV box as a testing ground for SQL against MySQL).
myth@orpheus:~$ mysql -u root test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2496 Server version: 5.0.51a-3ubuntu5.4-log (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table t1 (a tinytext default 'fail'); ERROR 1101 (42000): BLOB/TEXT column 'a' can't have a default value mysql> create table t1 (a tinytext default ''); Query OK, 0 rows affected, 1 warning (0.07 sec) mysql> show warnings; +---------+------+-------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------+ | Warning | 1101 | BLOB/TEXT column 'a' can't have a default value | +---------+------+-------------------------------------------------+ 1 row in set (0.00 sec)
I increasingly enclose the API part of “Storage Engine API” in quotes as it does score a rather large number on the API Design Rusty levels (Coined by Rusty Russell). I give it a 15 (out of 18. lower is better) in this case “The obvious use is wrong”.
The ideas is that your handler gets called to write a row (the amazingly named handler::write_row()). It’s passed a buffer which is the row to be stored. An engine that uses the MySQL row format (lets say, ARCHIVE) will simply pack the row and write it out.
Unless there is a TIMESTAMP field with auto set on insert. Up until now (and still now in MySQL) the engine had to check if this was the case and make sure the timestamp field was updated.
To remove this particular bonghit is actually a really small patch, which Jay recently got merged:
~drizzle-developers/drizzle/development : revision 873.1.16
Hopefully somebody does this soon for MySQL as well.
It’s no secret that I love linux.conf.au. My first was linux.conf.au 2003, in Perth and I’ve been to every one since (there are at least two people who’ve been to every single one, including CALU as it was called in 1999).
I’ve been on the board of Linux Australia for some insane proportion of the years since then (joining in 2003). Linux Australia is the not-for-profit community organisation that puts on linux.conf.au. It’s all volunteers and amazingly enough we have more than one group of people wanting to put on linux.conf.au each year!
This year, we Marched South to Hobart.
Here I detail what I saw, what I wish I saw and whatever else comes to mind.
Ran into Bdale while checking in. Short flight down. A million and one people on the plane and on the ground that I knew. It must be linux.conf.au.
Seeing way too many awesome people I know, checking into accommodation (oh my, what a hill), registering for conf, beer and then off to a “ghosts of conferences past” dinner – where a few people who had organised previous linux.conf.au’s were hastily gathered together to chat to part of the 2010 team.
Oh, that’s right – I’m running the OSDB Miniconf :)
First up, Monty Taylor spoke on “NDB/Bindings – Use the MySQL Cluster Direct API from languages you actually like for fun and profit”. Possibly taking the prize for the longest talk title of the conference. The NDB API is not SQL, it’s what the MySQL server (and one day, when Monty and I get around to it, Drizzle) translates SQL into for NDB. That being said, you can (pretty much always) write NDB API code that dramatically outperforms equivilent SQL (for a variety of reasons). Monty maintains the NDB/Bindings project that lets you use languages other than C++ for the NDB API.
At the same time as Monty was speaking, I wish I’d been able to fork() and go and see “Is Parallel Programming Hard, And, If So, Why?” by Paul McKenney and Michael Still talking about MythNetTV (pull RSS feeds of video in as MythTV programs).
After morning tea, we were meant to have “InnoDB scaling up and performance” by Bruce Huang, but he was a no-show. Hint: if you don’t want bad things to be said about you by conference organisers, either show up or let them know you’re not able to make it.
Instead, we led a crazy Q&A type session around the room which was a whole lot of fun. Really a “ask the experts” meets running up-and-down stairs with a microphone.
Next up, Arjen Lentz who runs Open Query spoke on “OurDelta: Builds for MySQL”. The best way to describe OurDelta is a “distribution of MySQL”. It’s the MySQL server plus a bunch of patches provided by various people that haven’t yet made it into the main source tree (for any number of reasons).
At the same time (if you’ve never been to linux.conf.au, you’ll find that you often want to be in at least 3 places at once) I would have really liked to see “MythTV Internals by Nigel Pearson” (I co-wrote Practical MythTV with Michael Still, which is having a “second edition” in wiki form over at http://www.mythtvbook.com/) as well as the panel on geek parenting as this may be something I’m one day faced with.
Up next: Russell Coker filled in for Kaigai (same talk, different speaker) to talk on The Security-Enhanced PostgreSQL – “System-wide consistency” in access controls. I found this quite interesting and different approaches to database security are worth looking at. Modern applications (read: web applications) don’t map their uses to database users at all. There are usually two users on the database server: the super user and the user that the app uses. It would be nice to have a good solution for those who want it.
Again, If I had the ability to be in two places at once, I would have also seen “How I Learned To Stop Worrying And Love ACPI” by the extremely handsome Matthew Garrett.
Monty Widenius (blog here – and yes, we have two Monty’s now… which does cause confusion) talking about the Maria storage engine. Maria is based on MyISAM, but adding crash safety and transactions (among other things).
Again, if I was able to be in several places at once I would have also seen Rusty‘s “Large CPUmasks”, Nathan Scott talking about “System level performance management with PCP” and Bdale’s “Collaborating Successfully with large corporations”.
An awesome start to the conference.
It’s fortunate that I’m watching Veronica Mars again with a mate; a more-than-you-think amount of detective work is required to understand the relationship (and format) of the TABLE_SHARE, the FRM file and HA_CREATE_INFO. Oh, also you’ll need drizzled/base.h and drizzled/structs.h and drizzled/table_share.h is also a good one to have open.
The FRM file is really a FoRM file from UNIREG (see copies of really old mysql docs around the place or even better, the links off Sheeri‘s blog post). Also, Jan has some thoughts on FRM too and Thava has a scary frmdump php script.
I have to agree completely with Jan:
It contains interesting nuggets like unireg_check (or unireg_type, depending on where you read) that does:
enum utype { NONE,DATE,SHIELD,NOEMPTY,CASEUP,PNR,BGNR,PGNR,YES,NO,REL,
CHECK,EMPTY,UNKNOWN_FIELD,CASEDN,NEXT_NUMBER,INTERVAL_FIELD,
BIT_FIELD, TIMESTAMP_OLD_FIELD, CAPITALIZE, BLOB_FIELD,
TIMESTAMP_DN_FIELD, TIMESTAMP_UN_FIELD, TIMESTAMP_DNUN_FIELD};
But really only the timestamp things… which should be default magic, but it’s somewhere tied in (Jay had thoughts last time I spoke to him… hopefully going away soon). A bunch of these aren’t ever used and are just relics from UNIREG. In fact… I went and removed what wasn’t needed and just ended up with:
 enum utype { NONE,
               NEXT_NUMBER,
               TIMESTAMP_OLD_FIELD,
               TIMESTAMP_DN_FIELD, TIMESTAMP_UN_FIELD, TIMESTAMP_DNUN_FIELD};
Which does seem a bit nicer. The fact that TIMESTAMP_OLD_FIELD is used as in interim value is, wel, scary. At least with a smaller set of possiblities it will be easier to convert into the proto format.
A hint of a brighter future is in the comment there:
/*
We use three additional unireg types for TIMESTAMP to overcome limitation
of current binary format of .frm file. We'd like to be able to support
NOW() as default and on update value for such fields but unable to hold
this info anywhere except unireg_check field. This issue will be resolved
in more clean way with transition to new text based .frm format.
See also comment for Field_timestamp::Field_timestamp().
*/
Hrrm… a text based FRM? That would be much nicer to read the code for. Unfortunately, it doesn’t really exist. Some FRMs are text in MySQL, but not ones to do with tables. You can look at the FRM for a VIEW in a text editor and see the SQL quite easily (the file format is text).
So I can’t go look at any nice text based format code – it’s all uint2korr() and friends. Yes folks, this is about the only place left in the code with function names in Swedish. What does korr mean? “accurate, correct, correctly”. If you look at korr.h, you’ll see that it’s just for storing in machine independent format: low byte first.
My favourite korr functions:
It also (as Jan showed) does the whole layout on a 80 column terminal for you! This functionality is going, going gone in Drizzle and won’t be coming back.
There’s also an “empty record on start of formfile” (see make_empty_rec in unireg.cc). This bit is going to cause me some pain relatively soon. Not so much for writing something like it out (default values can be easily put in the proto) but by then constructing it on open (with some careful footing around the issue of the egg coming before the chicken).
Incidently, when discussing with Daniel Stone about this (and explaining all the weirdness) it did cause him to exclaim “omg, it’s XKB!” – so that probably helps the X hackers in the room to relate.
The biggest test in moving from FRM to proto is to only rewrite this part of the code – the TABLE_SHARE, field, Create_foo etc have sooo many bits I want to change/fix. Going down the rat-hole into an endless cycle of fixing is always a possibility. Sometimes (like with unireg_type) the cleanup lets me really discover what the code is doing, so that’s being done (but will go away “soon”).
For such a long worked on feature, with such potential – I find the resistence to publishing a source tree curious (my comments on the topic have been moderated away but others have asked too). I could go and grep through the commits list searching for things (hint: look for mysql-6.0-perf), and then start to re-construct a tree; but I have more important things to do (yes, Brian, like FRM patches :)
Instead of re-inventing the wheel in Drizzle for a performance schema like interface, it’d be great to go with existing work. Evaluating the code as it’s coming along is important.
I also have concerns about the code itself:
So stuck waiting for some code to look at to answer the questions (random commits on the commits list doesn’t really do it like the finished product – i really hate commit lists).
So I can’t currently comment on the performance schema work much at all, nor if it’s useful to Drizzle. Hopefully will soon.
Some database engines have a fundamental concept of a row id. The row id is everything you need to know to locate a row. Common uses include secondary indexes (key is what’s indexed, value is rowid which you then use to lookup the row).
One design is the InnoDB method of having secondary indexes have the value in the index be the primary key of the row. Another is to store the rowid instead. Usually (or often… or sometimes…) rowid is much smaller than the pkey of the row. This is how innodb can answer some queries just out of the index. If it used rowid, it may involve more IO to answer the query. All this is irrelevant if you never want just the primary key from a secondary index.
Some engines are designed from the start to have rowid, others it’s added later (e.g. NDB).
Anyway… all beside the point. Did you know you can do this in mysql or drizzle:
drizzle> create table t1 (a int primary key); Query OK, 0 rows affected (0.02 sec) drizzle> insert into t1 (a) values (1); Query OK, 1 row affected (0.01 sec) drizzle> select _rowid from t1; +--------+ | _rowid | +--------+ | 1 | +--------+ 1 row in set (0.00 sec)
Is that the rowid from the engine? No (although at least NDB will let you select the real ROWID through a pseudo column through NDBAPI). Quoting from the MySQL manual:
If a
PRIMARY KEYorUNIQUEindex consists of only one column that has an integer type, you can also refer to the column as_rowidinSELECTstatements.
Unfortunately, this isn’t correct… as this lovely bit of “oh my, what an excellent way to obfuscate my database app!” shows:
drizzle> create table t1 (a int primary key, b varchar(100));
Query OK, 0 rows affected (0.02 sec)
drizzle> insert into t1 values (1,”foo”);
Query OK, 1 row affected (0.00 sec)
drizzle> update t1 set b=”foobar!” where _rowid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
drizzle> select * from t1;
+—+———+
| a | b |
+—+———+
| 1 | foobar! |
+—+———+
1 row in set (0.00 sec)
So how is this implemented? In two places: in sql_base.cc find_field_in_table() and in table.cc during FRM parsing (this is how I found it). We can even do things Oracle can’t (insert, update and delete):
drizzle> update t1 set a=2 where _rowid=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 drizzle> select * from t1; +---+---------+ | a | b | +---+---------+ | 2 | foobar! | +---+---------+ 1 row in set (0.00 sec) drizzle> update t1 set _rowid=3 where _rowid=2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 drizzle> select * from t1; +---+---------+ | a | b | +---+---------+ | 3 | foobar! | +---+---------+ 1 row in set (0.00 sec)
SQLite also has something similar (see the autoinc docs).
I do wonder if anybody uses this functionality. It’s even tested (I was quite shocked at this) in the auto_increment and heap_auto_increment tests.
“Ban FRM Now!†branch in Launchpad
Now we’re reading part of the table information out of the proto file on disk instead of the frm.
Not everything (yet) but a bit. Good first steps. Had to fix bugs along the way as well (and find weirdness in FRM file format…).
Progress is being made.
#mysql isn’t too fair to include, as it’s really about users, not dev. #mysql-ndb is there because i heart ndb.
Oh, and linux.conf.au is there because it’s *awesome* and you should go.
Totally unscientific due to i’m only taking a sample once and whatever… but it kinda interests me…
We’ve been working on fixing up the remaining test cases so that they run with Drizzle. We’ve found: bugs in Drizzle, bugs in MySQL (one that seems to have been there for at least 10 years), bugs in the tests, tests that no longer apply and occationally, something like this:
/* Please god, will someone rewrite this to be readable :( */
if (to->pack_length() == from->pack_length() &&
!(to->flags & UNSIGNED_FLAG && !(from->flags & UNSIGNED_FLAG)) &&
to->real_type() != DRIZZLE_TYPE_ENUM &&
(to->real_type() != DRIZZLE_TYPE_NEWDECIMAL || (to->field_length == from->field_length && (((Field_num*)to)->dec == ((Field_num*)from)->dec))) &&
from->charset() == to->charset() &&
to->table->s->db_low_byte_first == from->table->s->db_low_byte_first &&
(!(to->table->in_use->variables.sql_mode & (MODE_NO_ZERO_DATE | MODE_INVALID_DATES)) || (to->type() != DRIZZLE_TYPE_DATE && to->type() != DRIZZLE_TYPE_DATETIME)) &&
(from->real_type() != DRIZZLE_TYPE_VARCHAR || ((Field_varstring*)from)->length_bytes == ((Field_varstring*)to)->length_bytes))
{ // Identical fields
/* This may happen if one does 'UPDATE ... SET x=x' */
if (to->ptr != from->ptr)
memcpy(to->ptr,from->ptr,to->pack_length());
return 0;
}
and no, I haven’t really changed the formatting.
Yes, I’m speaking at the upcoming MySQL Conference & Expo 2009 – on April 20 – 23 (and yes, it’s in Santa Clara again).
I have three sessions:
MySQL Cluster Tutorial: this time with 6.4 feature goodness. Very hands-on, very interactive.
MySQL Cluster on Windows:Â (insert witty text about hating operating system freedom here)
Memory Management in MySQL and Drizzle: not magic setting of buffer variables, but memory allocation and management inside the server, a bunch of malloc() discussion and hopefully some interesting numbers.
Just saw a brief “this is how it’s currently working” on Martin’s implementation of the SQL frontend to the NDB$INFO work.
It’s awesome.
What would you expect the following to fail with?
CREATE TABLE t1 (a int, b int);
insert into t1 values (100,100);
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
BEGIN;
INSERT INTO t2 values(100,100);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
If you answered ER_DUP_ENTRY then you are correct.
If you use IF NOT EXISTS in a CREATE TABLE ... SELECT statement, any rows selected by the SELECT part are inserted regardless of whether the table already exists.
Does anybody else find this behaviour “interesting”?
It’s the FRM file format version number.
It’s not the version of the table as one might expect (i.e. after CREATE it’s 1. Then, if you ALTER, it’s 2. Alter again 3 etc).
In Drizzle, we now return 0.
In future, I plan that Drizzle will allow the engine to say what version it is (where 0 is “dunno”).
This’ll be a good step towards being able to cope with multiple versions of a table in use at once (and making sense of this to the user).
Scaling MySQL on a 256-way T5440 server using Solaris ZFS and Java 1.7
*cough*
(and then wipe coffee off the computer)
of course the real aim should be to scale with one instance on the machine as scaling with multiple instances on the one machine isn’t scaling at all – it’s scale out, but with more problems (now when one machine goes down, so do 1110202434 database instances).
The past 3 years, 11 months I have worked full time on NDB (MySQL Cluster). It’s been awesome. Love the product and people. In the time I’ve been on the Cluster team, we’ve gone from a small group that would easily fit in the (old old) Stockholm office to one that requires large rooms to house us all in. It’s also been all about smart people (you have to be to work on a distributed database).
With MySQL Cluster 6.4 we’re getting in a bunch of features that have been on the “wide adoption” wishlist. With each release of NDB we’ve gained a wedge of applications that can be used with it – and 6.4 is no exception.
One of the biggest things that’s been worked on is multithreaded data nodes. If you check out Jonas‘ recent posts on 500,000 reads/sec and then a massive 700,000 reads/sec.
We’ve also got a Microsoft Windows port coming up, which a number of people have asked for over the years. Mostly I think this is a “I want to try it out” thing and not a deployment thing. (can any sane person deploy a HA app on Win32?)
I’ve used “NDB$INFO” as the ultimate answer to any problem for a while now. It’s been the much-wanted monitoring interface. We have a lot of info inside NDB that currently isn’t easily user accessible (or only accessible through the magic DUMP interface or by gathering up many events in the cluster log). We have the start of NDB$INFO in 6.4 now and Martin will be continuing my work in making it truly awesome.
So go and grab the 6.4 tree and have a look – things are looking sweet.
What next for me?
Well… a while ago I started hacking on Drizzle. Why? Well… I thought we could move the database server in a new direction and make it more modular, leaner, meaner query machine.
And now, I’m starting to work on it full time.
It’s exciting, and I’ll be blogging on the first TODO which is remove the FRM file and switch to a full discovery method shortly.
UPDATE: Yes, I’m working full time on Drizzle for Sun Microsystems (in the CTO group). While not spending work time on NDB anymore, no doubt you’ll still see fun-time patches.