#define READ_ALL 1 /* openfrm: Read all parameters */ #define EXTRA_RECORD 8 /* Reservera plats f|r extra record */
and later on….
  if (prgflag & (READ_ALL+EXTRA_RECORD))    records++;
Feel free to think about that for a second.
(I have an urge to add this to questions asked in a job interview…)
Releasing and announcing software is win! I’ve had this bumming around for a bit, and for me (and I think others hacking on MySQL) it’s been rather useful. Simple plugin that takes each email in an mbox, applies the patch and commits it with the correct author to a bzr repo. Very useful if you use quilt and bzr together (“quilt mail –mbox” and then “bzr importmbox”).
I finally published it up at:
http://launchpad.net/bzr-importmbox
enjoy.
If you have needed a good reason to drink heavily and forget, may I suggest taking a look at create_tmp_table() and those who call it. It’s probably one of the best illustrations of rot and awful, incomprehensible APIs in the server (Drizzle inherited it from MySQL).
In the normal paths for CREATE TABLE, you construct a data structure describing the table you want, create it and then open it. Opening a table gives you objects you can use to access it.
create_tmp_table() instead constructs these objects directly and then does some direct calls into specific storage engines (ever wondered why you can’t use your own storage engine for temporary tables created during query execution? this is why). So instead of having one place to construct TableShare and Table, we have create_tmp_table() doing its own thing.
I struck a blow against it today. Using the standard interfaces to create and open a table, I got the temporary tables created for INFORMATION_SCHEMA tables to use them and not create_tmp_table().
When I finish converting the other 3 or 4 places that use create_tmp_table, I’ll be able to cut out at least 700 odd lines of code, cut out the horrible special case in the HEAP engine for “create table on open() even if create() was never called” and have an easy time making the transition to fully using the table proto as the specification for table structure.
It’s coming up on a year since I started working full time on Drizzle. So, I got a bit reflective…
Have we done things that I (and others) really wanted done? Back in 2007, I wrote my top 5 wishlist for the MySQL Server.
I am not going to pretend I speak for the MySQL development team; I’m just trying to evaluate how Drizzle is doing against some wishlists that (to me) embodied some of the reasons we started Drizzle.
Please think of this as “database server wishlists” and comparing them against Drizzle….
My wishlist was:
5. Six-monthly release cycles
Done. Not only does Drizzle have milestone releases, but we’re also dropping tarballs every two weeks (currently for the bell milestone). We’re also doing a decent job of keeping trunk free of massive breakage.
4. Much more in depth automated testing
Done (and in progress). We have drizzle-automation running things all the time. Hudson (and buildbot) test across many platforms (what pushbuild did and more when inside MySQL) before code hits trunk. We also have regular performance benchmarks that we compare across versions, crash-me, the random query generator as well as checking that we don’t regress in code size (via sloccount).
3. Sane build system
(slightly distorting my original words). Well, we’re not quite at the ready for packaging in distributions stage, but a debug or non debug build is just -g and optimization level for the compiler, plugins are using autofoo to work otu if they can be built… so yeah, this is pretty sane.
We also are building with -Werror (and more!) which increases code quality no end.
So, mostly done.
3.5. (yes, i have a 3.5): Kill HPUX
Done.
2. Increased liberal use of asserts
An in-progress thing, but the better compiler warnings have won us a lot.
1. Pluggable data dictionary
Not only that, but done away with FRM totally. Really happy with this.
What about other peoples wishlist?
Kostya had one:
1. Remove excessive fuss.
i.e. “just do it”.
I think we’re doing really well with this for Drizzle. Plugins are pretty easy to get merged, and if your patches to the kernel are good, they’re also easy. Big changes can be harder, but in the end it has turned out well.
2. Open the development process.
Done. There is no internal wiki, there are no “committers” versus “non-committers”.. everything is judged on merit of the idea/code. Sometimes the most valuable contribution is somebody telling you their real world experience.
3. Get to a normal release schedule.
Done.
4. Establish productive relationship with the majority of users.
I think drizzle-discuss mailing list is doing quite well in this regard. Is quite active with discussion.
5. Find a way to do incompatible changes with minimal pain for users.
We’ll see how we go :)
1. Real time Query Monitoring
With gearman logging and my recent experimentation with using CPU performance counters I think we’ll end up somewhere rather awesome.
If you’re looking for MySQL monitoring though, the MySQl Enterprise query monitoring stuff looks pretty good to me.
2. Consistent Release Cycles
We’re doing pretty well so far!
3. INFORMATION_SCHEMA Extensions
We’ve inherited the architecture from MySQL 5.1 (and 6.0) of being able to pretty easily add INFORMATION_SCHEMA tables and improved it. It’s currently pretty easy to add them. We also have ongoing work having an INFORMATION_SCHEMA storage engine which means that you won’t have to have the I_S tables be materialized every time you query them.
4. Online table maintenance
All progress has been due to Storage Engine authors. With the data dictionary work though, this gets easier and saner to do.
5. Published benchmarks
We’re encouraging others who will be more objective :) Although we also do regular performance regression tests as part of our standard development process.
Dormando also had a list (complete with “there is no five”):
1) Logical separation of connections from threads
We have this in Drizzle through plugins. Interesting ones are pool_of_threads (fixed number of threads), multithread (thread per connection) and single_thread (one thread).
2) A more modular core
We’re very much doing well here. It’s a long process, but I’m quite impressed by our progress.
3) Better replication (better replication management/protocol?)
The work being done on Drizzle replication is really exciting. I love the fact that modularity is encouraged and the ability to replace any bit you want easily, as well as read the replication stream in about any language you want.
4) Better test suite
I may never be 100% happy with a test suite, but we’re doing good…
PeterZ‘s view is always interesting, and he had one too:
1. Be Pluggable
Check. (and also, of course, in progress)
2. Be Scalable
We’ve done a lot of work scaling on many CPUs and many connections. Really, 8 concurrent database connections just isn’t interesting. We ever run as part of our regression suite up to 2048 concurrent connections.
3. Be Distributed
With the new protocol work to have built in sharding, plugins for logging and replication via Gearman, we’re getting better.
4. Be Solid
This will be a test for us. I think we should end up pretty good because of a number of reasons:
So I hope we’re going to be okay here.
5. Don’t forget about the roots
The group of us working at Sun on Drizzle have said we want to focus on being awesome for large scale Web apps while enabling others to make Drizzle good for other things. I think this is the right approach to not forget our roots (and target users) while allowing it to be awesome for any use somebody wants to have of it.
From a Storage Engine author PoV, Paul had some insights while thinking about PBXT:
1. A generic engine test suite
We’re doing pretty well… the whole Drizzle test suite runs with InnoDB, and doesn’t require much change to get going with another transactional engine. The proof is in the Drizzle PBXT branch! But I also think we could do better and have a test suite more directed at each part of an engine (including error cases!).
2. Internal APIs
Paul mentions FRMs, which are gone :) In their place is a simple interface that engines can implement for ever increased functionality (i.e. they own their own metadata). We’re getting better in other places too.
3. Customizable table and column attributes
MariaDB has this now, and we have space in our table definition proto, but not at the parser level (yet).
4. Push-down restrict and join conditions.
Not yet, and not for a little while.
5. Custom data types
It’ll be great when we rework the type system even more so that this really is as easy as it should be – not only from a SQL level but also for adding new types as server modules.
Finally, Antony’s wish list:
1. Modular Architecture
Covered.
2. libmysys as a separate project
We’ve removed it where we can, and are using gnulib where we can. It very much improves the situation when you ditch weird-ass platforms and assume some level of POSIX.
3. New/modular parser
We’re getting close to a stage where you could load a different parser… not there, but relatively close. It would still be messy, but a lot better than even 6 months ago.
4. Unit tests for server components
With our move towards modularity, this is actually getting possible!
5. Aggregate Stored functions and External Stored Procedures
We don’t currently have either, we have decent thoughts though.
Antony also cheated and added a few more:
A new Recursive Descent parser
The required work to be able to replace the parser is in progress.
Abstract Syntax Trees
See above.. getting the pre-work done.
SCTP and/or link aggregation
We’ll see improvements around this with the new drizzle protocol.
Parsing within the client
We’ve had some very good discussion on the drizzle-discuss list. We’ll no doubt have something to help remove more of the cycles used in executing a query.
Integrated Federation
That’s the game plan :)
Elimination of FRM Files
I never get tired of saying this is done :)
Elimination of errmsg.sys
We’re now just using gettext, like every other free software project on the planet. Although I think we could take a few steps in making errors more easily parsable by code.
So how do we stack up?
I think we’re doing pretty well. There’s still a lot of work to get where we want to be, but it’s amazing how much progress we’ve made in the short time we’ve been around.
I also just realized I missed Jay’s list… but we’re doing pretty well there too.
As Lee announced, we have the Random Query Generator added to Drizzle Automation. It always amazed me that we were lacking such a fundamental testing tool for MySQL for all that time. I always found the similar (NDB API) tools for MySQL Cluster (NDB) to be really, really useful when wanting to make sure your code changes, well, worked.
I’m really looking forward into this being developed further as a cross-database testing tool and framework.
Also, upstream maintainers++ Good example of how even small FOSS projects should work.
Users of the ARCHIVE or NDB storage engines in MySQL may be aware of a MySQL feature known as “table discovery”. For ARCHIVE, you can copy the archive data file around between servers and it magically works (you don’t need to copy the FRM). For MySQL Cluster (NDB) it works so that when you CREATE TABLE on another MySQL server, other MySQL servers can get the FRM for these tables from the cluster.
With my work to replace the FRM with a protobuf structure in Drizzle and clean up parts of the API around it, this feature didn’t really survive in any working state.
Instead, I’m now doing things closer to the right way: pluggable metadata stores. The idea being that the whole “table proto on disk” (in MySQL it’s the FRM, but in Drizzle we’re now using a protobuf structure) code is pluggable and could be replaced by an implementation specific to an engine (e.g. the innodb or ndb data dictionaries) or a different gerenic one.
Currently, the default plugin is the same way we’ve been doing it forever: file-per-table on disk in a directory that’s the database. The API has a nasty bit now (mmmm… table name encoding), but that’ll be fixed in the future.
The rest of this week will be dedicated to plugging this into all the bits in the server that manipulate the files manually.
With luck, I’ll have modified the ARCHIVE engine by then too so that there’ll just be the archive data file on disk with the table metadata stored in it.
mysql [localhost] {msandbox} ((none)) > select length(crc32(3)) * 8, bit_length(crc32(3));
+----------------------+----------------------+
| length(crc32(3)) * 8 | bit_length(crc32(3)) |
+----------------------+----------------------+
| 80 | 80 |
+----------------------+----------------------+
1 row in set (0.00 sec)
For a bit more info, what about various storage engines over MySQL releases. Have they changed much? Here we’re looking at the storage/X/ directory for code, so for some engines this excludes the handler that interfaces with the MySQL Server.
You can view the data on the spreadsheet.
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.
For whatever reason, let’s look at “Total Physical Source Lines of Code” from a recent mysql-6.0 tree (and PBXT from PBXT source repo):
See the spreadsheet here.
Raw data:
Blackhole       336
CSVÂ Â Â Â Â Â Â Â Â Â Â Â 1143
Archive        2960
MyISAMÂ Â Â Â Â Â Â Â 34019
PBXTÂ Â Â Â Â Â Â Â Â Â 41732
Maria         69019
InnoDBÂ Â Â Â Â Â Â Â 82557
Falcon        91158
NDBÂ Â Â Â Â Â Â Â Â Â 365272
NDB has a 100,000 line test suite.
PBXT supports MySQL and Drizzle.
Conclusions to draw? Err… none really.
The MySQL Administrator’s Bible is out. Writing a book is not something you can just squeeze into a Sunday afternoon; it takes real dedication and more effort than you could possibly imagine.
So congrats on having the book for MySQL DBAs (and I’d venture to say application devs should also be reading it) out and on Amazon so people can buy it now.
I’m surprised I haven’t used it before. I do have to say that MySQL Sandbox is incredibly awesome. In this case, it saved me waiting for a compile of MySQL 6.0, which is always a good thing.
In an email disscussion for pre-allocating binlogs for MySQL (something we’ll likely have to do for Drizzle and replication), Yoshinori brought up the excellent point of that in some situations you don’t want to be doing zero-fill as getting up and running quickly is the most important thing.
So what does Linux do? Does it zero-fill, or behave sensibly and pre-allocate quickly?
Let’s look at hte kernel:
Inside the fallocate implementation (fs/open.c):
if (inode->i_op->fallocate)
ret = inode->i_op->fallocate(inode, mode, offset, len);
else
ret = -EOPNOTSUPP;
and for ext4:
/*
* currently supporting (pre)allocate mode for extent-based
* files _only_
*/
if (!(EXT4_I(inode)->i_flags & EXT4_EXTENTS_FL))
return -EOPNOTSUPP;
XFS has always done fast pre-allocate, so it’s not a problem there and the only other filesystems to currently support fallocate are btrfs and ocfs2 – which we don’t even have to start worrying too much about yet :)
But this is just kernel behaviour – i *think* libc ends up wrapping it
with a ENOTSUPP from kernel being “let me zero-fill” (which might be
useful to check). Anybody want to check libc for me?
This was all on slightly post 2.6.30-rc3 (from git: 8c9ed899b44c19e81859fbb0e9d659fe2f8630fc)
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.
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.
Filed GCC bug 39228:
#include <stdio.h>
#include <math.h>
int main()
{
       double a= 10.0;
       double b= 1e+308;
       printf("%d %d %dn", isinf(a*b), __builtin_isinf(a*b), __isinf(a*b));
       return 0;
}
mtaylor@drizzle-dev:~$ gcc -o test test.c
mtaylor@drizzle-dev:~$ ./test
0 0 1
mtaylor@drizzle-dev:~$ gcc -o test test.c -std=c99
mtaylor@drizzle-dev:~$ ./test
1 0 1
mtaylor@drizzle-dev:~$ gcc -o test test.c  -mfpmath=sse -march=pentium4
mtaylor@drizzle-dev:~$ ./test
1 1 1
mtaylor@drizzle-dev:~$ g++ -o test test.c
mtaylor@drizzle-dev:~$ ./test
1 0 1
Originally I found the simple isinf() case to be different on x86 than x86-64, ppc32 and sparc (32 and 64).
After more research, I found that x86-64 uses the sse instructions to do it (and using sse is the only way for __builtin_isinf() to produce correct results). For the g++ built version, it calls __isinf() instead of inlining (and as can be seen, the __isinf() version is always correct).
Specifically, it’s because the optimised 387 code is doing the math in double extended precision inside the FPU. 10.0*1e308 fits in 80bits but not in 64bit. Any code that forces it to be stored and loaded gets the correct result too. e.g.
mtaylor@drizzle-dev:~$ cat test-simple.c
#include <stdio.h>
#include <math.h>
int main()
{
       double a= 10.0;
       double b= 1e+308;
   volatile   double c= a*b;
       printf("%dn", isinf(c));
       return 0;
}
mtaylor@drizzle-dev:~$ gcc -o test-simple test-simple.c
mtaylor@drizzle-dev:~$ ./test-simple
1
With this code you can easily see the load and store:
 8048407:      dc 0d 18 85 04 08      fmull 0x8048518 804840d:      dd 5d f0               fstpl -0x10(%ebp)  8048410:      dd 45 f0               fldl  -0x10(%ebp)  8048413:      d9 e5                  fxam
While if you remove volatile, the load and store doesn’t happen (at least on -O3, on -O0 it hasn’t been optimised away):
 8048407:      dc 0d 18 85 04 08      fmull 0x8048518  804840d:      c7 44 24 04 10 85 04   movl  $0x8048510,0x4(%esp)  8048414:      08  8048415:      c7 04 24 01 00 00 00   movl  $0x1,(%esp)  804841c:      d9 e5                  fxam
This is also a regression from 4.2.4 as it just calls isinf() and doesn’t expand the 387 code inline. My guess is the 387 optimisation was added in 4.3.
Recommended fix: store and load in the 387 version so to operate on same precision as elsewhere.
Now I just have to make a patch I like that makes Drizzle behave because of this (showed up as a failure in the SQL func_math test) and then submit to MySQL as well… as this may happen there if “correctly” built.