Selena and I gave a talk on the various issues of running databases “in the cloud” at the recent linux.conf.au in Ballarat. Video is up, embedded below:
Some of you may be aware that MySQL is increasingly using an Oracle-internal bug tracker. You can see these large bug numbers mentioned alongside smaller public bug numbers in recent MySQL release notes. If you’re particularly unlucky, you  just get a big Oracle-internal bug number. For a recently fixed bug, I dug further, posted up on the Percona blog: http://www.mysqlperformanceblog.com/2011/11/20/bug12704861/
Possibly interesting reading for those of you who interested in InnoDB, MySQL, BLOBs and crash recovery.
At Percona, we’re now using sphinx for our documentation. We’re also using Jenkins for our  continuous integration. We have compiler warnings from GCC being parsed by Jenkins using the built in filters, but there isn’t one for the sphinx warnings.
Luckily, in the configuration page for Jenkins, the Warnings plugin allows you to specify your own filters. I’ve added the following filter to process warnings from sphinx:
For those who want to copy and paste:
Regex:Â ^(.*):(\d+): \((.*)\) (.*)
Mapping Script
import hudson.plugins.warnings.parser.Warning
String fileName = matcher.group(1) String lineNumber = matcher.group(2) String category = matcher.group(3) String message = matcher.group(4) return new Warning(fileName, Integer.parseInt(lineNumber), "sphinx", category, message);
Example log message:Â /home/stewart/percona-server/docs-5.1/doc/source/release-notes/Percona-Server-1.0.2-3.rst:67: (WARNING/2) Inline literal start-string without end-string.
Then I can select this filter from the job that builds (and publishes) our documentation and it shows up like any other compiler warnings. Neat!
TODO: get the intersphinx warnings also in there
TODO: fix the linkcheck target in Sphinx so that it’s easily parseable and can also be integrated.
Both Henrik and myself will be at Percona Live London 2011 in late October speaking on the wonderful Drizzle database server.
Other speakers at the conference will be talking about a wide range of topics surrounding the MySQL ecosystem including performance monitoring, backup, search, scaling and data recovery.
P.S. I do have a discount code – ask me in the comments for it!
Just in case anybody missed it:Â http://blogs.oracle.com/MySQL/entry/new_commercial_extensions_for_mysql
MySQL has long been an open source product, not an open source project…. and this really is the final nail in that.
To me, this was expected, but it’s still sad to see it.
I am very, very glad we have diverse copyright ownership in Drizzle so that this could not happen easily at all.
OSCON is coming up again: July 25th-29th in wonderful Portland, OR. If you come to OSCON, not only will you be at OSCON, but you’ll be in Portland in July – which is just lovely. We’ll also have other people from Percona there and it should be a great lot of fun.
So come along and talk interesting technology – I’ll be speaking on “Dropping ACID: Eating Data in a Web 2.0 Cloud World“. If you know my love of reliable software with known failure modes, you’ll probably enjoy this one – especially if you saw my “Eat My Data: How everybody gets POSIX file I/O Wrong” which when given at OSCON 2008 was given to a packed room with people in the aisles and out the door.
Speaking has its pluses and minuses of course. It takes a very, very long time to construct a good talk (I would guess 12hrs for each hour of speaking) and you do miss out on some other sessions (preparing, going over, final touches, rehearsals). It is rather rewarding though, and sparks very interesting conversations afterwards.
I should also mention, if you want 20% off OSCON rego: use os11fos as discount code.
I have just upgraded the xtrabackup bazaar code repositories to the 2a format. This means that bzr 1.16 is required to access the source code repositories now.
If you get an error like the one below when working with a local branch, you’ll need to run “bzr upgrade” in it (see below for example). For branches on launchpad, you can use the web UI and hit the “upgrade branch” button.
stewart@willster:~/src/percona-xtrabackup$ bzr pull
Using saved parent location: bzr+ssh://bazaar.launchpad.net/%2Bbranch/percona-xtrabackup/
Doing on-the-fly conversion from RemoteRepositoryFormat(_network_name='Bazaar repository format 2a (needs bzr 1.16 or later)\n') to RepositoryFormatKnitPack1().
This may take some time. Upgrade the repositories to the same format for better performance.
bzr: ERROR: KnitPackRepository('file:///home/stewart/src/percona-xtrabackup/.bzr/repository/')
is not compatible with
RemoteRepository(bzr+ssh://bazaar.launchpad.net/%2Bbranch/percona-xtrabackup/.bzr/)
different rich-root support
stewart@willster:~/src/percona-xtrabackup$ bzr upgrade
Upgrading branch file:///home/stewart/src/percona-xtrabackup/ ...
starting upgrade of file:///home/stewart/src/percona-xtrabackup/
making backup of file:///home/stewart/src/percona-xtrabackup/.bzr
to file:///home/stewart/src/percona-xtrabackup/backup.bzr.~1~
starting repository conversion
repository converted
finished
As you may have read on the MySQL Performance Blog post – I’ve recently joined Percona. This is a fairly exciting next step. I’ll be in New York for Percona Live next week, where I’ll be giving a session titled “Drizzle 7, GA and Supported: Current & Future Features”.
I’ll write more soon, there’s a lot keeping me busy already!
https://code.launchpad.net/~stewart/drizzle/json-interface/+merge/59859
Currently a very early version of course, but it’s there in trunk if you want to play with it. Just have libcurl and libevent installed and you can submit queries via HTTP and JSON. Of course, the next steps are getting a true non-sql interface going and seeing how people go with it.
So… I had another one of those “hrrm… this shouldn’t be hard to hack a proof-of-concept” moments. Web apps are increasingly speaking JSON all around the place. Why can’t we speak JSON to/from the database? Why? Seriously, why not?
One reason why MongoDB has found users is that JSON is very familiar to people. It has gained popularity in spite of having pure disregard for the integrity and safety of your data.
So I started with a really simple idea: http server in the database server. Thanks to the simple code to do that with libevent, I got that going fairly quickly. Finding a rather nice C++ library to create and parse JSON was the next challenge. I found JSONcpp, a public domain library with a nice API and proceeded to bring it into the tree (it’s not much code). I then created a simple way to find out the version of the Drizzle server you were speaking to:
$ curl http://localhost:8765/0.1/version
{
"version" : "2011.04.15.2285"
}
But that wasn’t nearly enough… I also wanted to be able to issue arbitrary queries. Thanks to the supporting code we have in the Drizzle server for EXECUTE() (also used by the replication slave), this was also pretty easy. I created a way to execute the content of a HTTP POST request as if you had done so with EXECUTE() – all nicely wrapped in a transaction.
I created a simple table using the drizzle client, connecting over a normal TCP socket speaking the MySQL protocol and inserted a row in it:
$ ../client/drizzle --port 9306 test
Welcome to the Drizzle client.. Commands end with ; or \g.
Your Drizzle connection id is 4
Connection protocol: mysql
Server version: 2011.04.15.2285 Source distribution (json-interface)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
drizzle> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` INT NOT NULL AUTO_INCREMENT,
`b` VARCHAR(100) COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB COLLATE = utf8_general_ci
1 row in set (0.001209 sec)
drizzle> insert into t1 (b) values ("from mysql protocol");
Query OK, 1 row affected (0.00207 sec)
Now to select rows from it via HTTP and get a JSON object back with the result set:
$ curl http://localhost:8765/0.1/sql --data 'select * from t1;'
{
"query" : "select * from t1;",
"result_set" : [
[ "1", "from mysql protocol" ],
[ "", "" ]
],
"sqlstate" : "00000"
}
I can also insert more rows using the HTTP interface and then select them from the MySQL protocol interface:
$ curl http://localhost:8765/0.1/sql --data 'insert into t1 values (NULL, \"from HTTP!\");'
{
"query" : "insert into t1 values (NULL, \\\"from HTTP!\\\");",
"sqlstate" : "00000"
}
drizzle> select * from t1;
+---+---------------------+
| a | b |
+---+---------------------+
| 1 | from mysql protocol |
| 2 | from HTTP! |
+---+---------------------+
2 rows in set (0.000907 sec)
So what does this get us? With the addition of proper authentication, you could start doing some really quite neat and nifty things. I imagine we could add interfaces to avoid SQL and directly do key lookups, table scans and index range scans, giving really quite sweet performance. We could start building web tools to manage and manipulate the database speaking the native language of the web.
But… there’s more!
Since we have a web server and a way to execute queries via HTTP along with getting the result set as JSON, why can’t we have a simple Web UI for monitoring the database server and running queries built into the database server?
Yes we can.
If you wanted a WHERE condition or anything else, easy. Change the query, hit execute:
No TCP connection or parsing the MySQL protocol or anything. Just HTTP requests straight to the database server from the browser with a bit of client side javascript producing the HTML for the table.
Proof of concept code is up on launchpad in lp:~stewart/drizzle/json-interface
I’m giving two talks tomorrow (Tuesday) at the MySQL Conference and Expo:
HailDB: A NoSQL API direct to InnoDB, 2:00pm, Ballroom D
Dropping ACID: Eating Data In A Web 2.0 Cloud World 3:05pm, Ballroom G
The HailDB talk is all about a C API to embed an InnoDB based relational database engine into your application. Awesome stuff (also nice and technical).
The second talk, “Dropping ACID: Eating Data in a Web 2.0 Cloud World” is not only a joke that only database people get, but a humorous and serious look at data integrity and reliability as promised by the current hype. This was quite well received at linux.conf.au in January. So, if you weren’t in Australia in January this year, then certainly come along and see how you go heckling an Australian.
I had a quick look at the source tree (I haven’t compiled it, just read the source – that’s what I do. I challenge any C/C++ compiler to keep up with my brain!) that’s got a tarball up on labs.mysql.com for the memcached interface to innodb. A few quick thoughts:
With this revision, My xtrabackup branch has been merged into trunk.
What does this mean? It means that we now get a drizzlebackup.innobase binary which is the xtrabackup port for Drizzle. Exciting times.
As Lee mentioned on the Drizzle blog, we have an upcoming Drizzle Developer Day just after the MySQL Conference and Expo. Sign up here to make sure we have enough space and can help with planning.
The last couple developer days have been great – helping people getting started with Drizzle, discussing improvements that could be made (both big and small), operations concerns and new to this one: a GA release.
Having a GA release out is really exciting, I’m hoping that on the developer day we get people coming who are looking at developing with Drizzle, not just hacking on internals.
Moar users nom nom nom!
Follow it over on launchpad.
After having fixed an incredibly odd compiler warning (and with -Werror that we build with, error) on OSX (die die die) – xtrabackup for Drizzle is ready to be merged. This will bring it into our next milestone: freemont. Over the next few weeks you should see some good tests merged in for backup and restore too.
While not final final, I’m thinking that the installed binary name will be drizzlebackup.innobase. A simple naming scheme for various backup tools that are Drizzle specific. This casually pre-empts a drizzlebackup tool that can co-ordinate all of these (like the innobackupex script).
For backups, historically in the MySQL world you’ve had mysqldump (a SQL dump, means on restore you have to rebuild indexes), InnoDB Hot Backup (proprietary, but takes a copy of the InnoDB data files, so restore is much quicker), LVM snapshots (various scripts exist, does have larger IO impact, requires LVM) and more recently xtrabackup. Xtrabackup essentially does the same thing as InnoDB hot backup except that it’s free and open source software.
Many people have been using xtrabackup successfully for quite a while now.
In Drizzle7, our default storage engine is InnoDB. There have been a few changes, but it is totally InnoDB. This leaves us with the question of backup solutions. We have drizzledump (the Drizzle equivalent to MySQL dump – although with fewer gotchas), you could always use LVM snapshots and the probability of Oracle releasing InnoDB Hot Backup for Drizzle is rather minimal.
So enter xtrabackup as a possible solution… I had though of porting xtrabackup across for a while. Last weekend, while waiting for one of my iterations of catalog support to compile, I decided to give it a go. I wanted to see how far I could get with it also in that weekend.
I was successful – there’s a tree up at lp:~stewart/drizzle/xtrabackup thatproduces an xtrabackup binary that’s built for Drizzle (it’s not quite ready for merging yet, there are some obivous bugs around command line option parsing… but a backup and restore did work).
I wanted the following:
I’ve already submitted merge requests to upstream xtrabackup containing the compiler fixes and added compiler warnings (they’ve also by now been merged into xtrabackup). Already my work has improved the quality of xtrabackup for everyone. Some of the warnings were fixed slightly differently in xtrabackup than in my Drizzle tree, but I plan to merge.
One issue was that the command line parsing library that xtrabackup uses – my_getopt which is part of mysys (the portability library inside MySQL) is long since gone from Drizzle. We currently use Boost::program_options. Thanks to the heroic efforts of Andrew Hutchings, xtrabackp in Drizzle is also using boost::program_options. This was a brilliant “hey, can you have a look at this conversion” followed by handing him a tree that did not even remotely compile, followed by a “I have to take the kids somewhere, here’s a tree – it may compile”. Amazingly enough, it pretty much did compile once I fixed the other issues.
An unresolved issue is how to deal with this going forward – my guess is that upstream xtrabackup doesn’t want to require Boost.
One solution could be just to factor out command line options into a sepfile that we can ignore for Drizzle and replace with our own. The other option could be to use a differnt command line option parsing library (perhaps from CCAN, as it’s then maintained by somebody else and doesn’t require heaps and heaps of other stuff).
Another issue I had to tackle is the patch to innobase that’s required to build xtrabackup.
I took a very minimal approach for the Drizzle patch. We are currently based on innobase 1.1.4 from MySQL 5.5 – so I mostly looked at the xtradb55 patch. I think it would be great if these were instead of one giant patch a series of patches to apply (a-la quilt) to a) make iteasier maintain and b) easier for myself to work out the exact reasoning of each bit (also, generating the patches with -p would help a fair bit too).
So how did I do it?
Step 0
was removing support for old innobase – we totally don’t need it for Drizzle.
Step 1
was creating a srv_read_only option for Drizzle’s innobase. This was fairly easy. The one thing I did have to change was adding a checkin os_file_lock() so that we don’t attempt to write lock the ibdatafiles when in read only (otherwise backups can’t be taken while drizzledis running). I’m a little surprised that this wasn’t hit in 5.5 at all.
Step 2
was implementing srv_fake_write. I’m pretty sure I’ve gotten this right in the Drizzle implementation, but the patch wasn’t as easy toread as I’d really like. I probably need to do a bit more of a code audit that this is actually correct (I may try and come up with anLD_PRELOAD library that will scream loudly if writes are made to files matching a pattern).
Step 3
was implemnting srv_apply_log_only. Pretty sure I have this right, again, more testing will be required. Why? Because I’m that paranoid about getting things very, very right.
Step 4
was to go through all the functions that xtrabackup needed to not be static. Instead of having prototypes for them inxtrabackup.cc, I instead added a xtrabackup_api.h header to Innobase and included it where needed (including in xtrabackup). I’d recommend this way going forward for xtrabackup too as it could be a lot less problematic to maintain (and makes xtrabackup source a bit easier to read)
Step 5
was fixing up a few skeleton functions that were needed to make our innobase happy. It may not be a bad idea to split out the skeleton functions into a sep source file so it’s a bit easier to track (and some #ifdefs around those not needed for certain releases).
I’m hoping to work with the upstream xtrabackup devs on the various points I’ve made above.
Another thought of mine is to port xtrabackup into HailDB where we can use much more neat API functions to create good tests for xtrabackup.
Thanks go out to all who’ve worked on xtrabackup. It honestly wasn’t too hardgetting it ported across to Drizzle – and with a bit of collaboration I think we can make it easy to keep up to date.
What’s the future for Xtrabackup in Drizzle? It’ll likely end up being a binary named drizzlebackup-innobase or similar (this means that there is a clear difference between xtrabackup for MySQL and what we have in Drizzle – which is more accurately defined as based on xtrabackup). We’ll also probably want a nice wrapper or integration with a backup tool to deal with everything Drizzle related. We shall also introduce a lot of testing; backups are important.
Xtrabackup is topical, check out the latest OurSQL podcast and the the Percona Xtrabackup website for more info!
My previous post focused on some of the problems of doing multi-tenant MySQL.
One of the reasons why I started hacking on Drizzle was that the multi-tenancy options for MySQL just weren’t very good (this is also the reason why I run my blog in a VM and not a shared hosting solution).
What you really want is to be able to give your users access to a virtual database server. What you don’t want is to be administering a separate database server for each of your users. What you want are CATALOGs.
A CATALOG is a collection of SCHEMAs (which have TABLEs in them). Each CATALOG is isolated from all the others. Once you connect to a catalog, that’s it. They are entirely separate units. There are no cross-catalog queries or CHANGE CATALOG commands. It is as if each catalog is its own database server.
You can easily imagine a world where there are per-catalog resource limits too (max connections, max temp tables etc).
For the Drizzle7 release, we got in some preliminary support to ensure that the upgrade path would be easy. You’ll notice that all the schemas you create are in the ‘local’ catalog (you can even spot this on the file system in the datadir).
For the next Drizzle milestone, the intent has been to flesh out this support to enable a very elegant multi-tenant solution for Drizzle.
One of the things we worked on a little while ago now is introducing TableIdentifier and SchemaIdentifier objects into Drizzle. Historically (and still in the MySQL codebase) tables would be referenced by a string in the format “database/table_name” (except sometimes when it could be “database\table_name”). There were also various times when this was the name as entered by the user and times when this was converted into a safe form for storing on disk (and comparing to one another).
Everywhere in Drizzle where we have to deal with the path to a table, we call a method on a TableIdentifier for it. There is also a method for getting a series of bytes that can be used as a key in a data structure (e.g. table definition cache). It’s even used to work out what path on the file system to store the data file in.
I would say that the use of TableIdentifier and SchemaIdentifier has prevented many bugs from creeping into the server during development. It took several aborted goes to get something like this into the codebase, just because how the database name, table name and table path strings were being used and abused around the server.
So, with our cleaned up codebase using TableIdentifier and SchemaIdentifier, how hard would it really be to introduce the new level of CATALOG?
So along comes an epic hacking session last weekend. Basically I wanted to see how close I could get in such a short time. I introduced a CatalogIdentifier and then forced the SchemaIdentifier constructor to require one as a parameter….
The great benefit? Every place in the code that I needed to change was a compile error. The overwhelming majority of places I had to fix were shown to me by GCC. I cannot express how wonderful this is.
Anyway, by going through all of these places and fixing up a few things that also needed fixing (including just throwing in a few temporary hacks), I got it working!
I have a tree on launchpad (lp:~stewart/drizzle/multitenant) where you can create multiple catalogs, connect to any of the catalogs you’ve created (or local), create schemas and tables in them. Each catalog is separate. So you can have 1 server with 2 schemas called ‘test’ and 2 tables called ‘t1’.
This is a pretty early development tree… and it comes with some limitations (that will need to be fixed):
But, as a result of only a weekend of hacking, pretty cool.
Paving the way for easy Database-As-Aa-Service.
Just about every web host in the world gives people “mysql databases”. Usually one. Why? because that’s how permissions work in MySQL and it’s a relatively simple way to set it up (each of your web host clients is a user with access to their one database).
This has a lot of limitations for the end user:
One solution is to give everybody their own MySQL instance. Ick. Why ick? Well… you now have a lot more MySQL servers to administer. That’s not the problem however: you’ve just screwed yourself on IOPs – each mysqld gets to compete to sync its data to disk.
The next step people go to is running MySQL inside a virtual machine. You are again screwing yourself on IOPs. Every VM on the box can now fight each other for a limited number of sync operations to make the data safe on disk. Forget if group_commit works for your MySQL version, having many VMs running MySQL on the same physical box will screw you much more than lack of group_commit ever will. You can probably kiss consistent performance and latency goodbye too (this will largely depend on how VMs are being run by your hosting provider).
The best way to get screwed is to get “free” extra CPU cycles and IOPs that are excess on the physical machine and then to suddenly switch to not getting any “free” ones and instead only the ones you pay for… wonder why your site is suddenly slow to respond where the number of visitors is the same and you’ve changed NOTHING?
Even running MySQL inside a VM that is the only VM on the box has a performance impact. You want to be using each physical machine to its fullest. If you’ve got a bunch of MySQL servers running inside VMs – you are not doing that.
(you can substitute just about any other database server for “MySQL” in all of the above… interestingly enough I have been told that a certain proprietary database server has a very low performance drop when run inside a VM)
When writing my Dropping ACID: Eating Data in a Web 2.0 Cloud World talk for LCA2011 I came to the realisation that I had forgotten a lot of the things I had worked on in MySQL and MySQL Cluster. So, as a bit of a retrospective as part of the Drizzle7 GA release, I thought I might try and write down a (incomplete) list of the various things I’ve worked on in Drizzle.
I noticed I did a lot of code removal, that’s all fine and dandy but maybe I won’t list all of that… except perhaps my first branch that was merged :)
2008
2009:
2010:
2011:
We’ve released Drizzle7! Not only that, we’re now calling it Generally Available – a GA release.
What does this mean? What does this GA label mean?
You could view as a GA label being “we’re pretty confident people aren’t going to on mass ask for our heads when they start using it”… which isn’t a too bad description. We also plan to maintain it, there could be future releases in this series that just include bug fixes – we won’t just immediately tell you to go and use the latest tarball or bzr tree. This release series is a good one to use.
Drizzle7 is something that can be packaged in Linux distros. It’s no longer something where the best bet is to add the PPA and upgrade every two weeks or build from source yourself. If you’re looking to deploy Drizzle (or develop against it) – you can rely on this release.
I’ll never use the words “production ready” to describe a release – it’s never up to me. It’s up to each person or organisation looking to deploy a piece of software to decide if that bit of software is production ready for them.
Personally, I’m looking forward to see how people can break it. While Drizzle is the best tested FOSS SQL RDBMS server, I’m sure there’s new an interesting ways it can be broken by saying we’re ready for a much larger crowd to hammer on it.
Overall, I think we’ve managed to take the now defunct MySQL 6.0 tree (way back in 2008) and release something that can truly live up to the line “database for cloud”. Drizzle is modern, modular, rather solid and understandable. The future is bright, there is so much more to do to make the ultimate database for cloud. Drizzle7 is a great platform to build on – both for us (developers) and us (people who use relational databases).