Overall, I enjoyed the conference–not just meeting up folks I don’t see often enough, but also getting a renewed sense of how active the MySQL ecosystem really is.
As I recently noted, I’m giving a short talk at Percona Live about our experience with Fusion-io for MySQL at Craigslist. As is often the case, I agreed to give the talk before giving too much thought about exactly what I’d say. But recently I’ve started to sweat a little at the prospect of having to think up a compelling and understandable presentation.
Thankfully, due to a cache misconfiguration this week, we ended up taking a number of steps that not only will help us to deal with future growth, but as a side-effect we got to directly quantify some of the benefits of Fusion-io in our big MySQL boxes. For whatever reason, the bulk of the presentation basically fell into my lap today.
Now I just have to put it all together.
I won’t go so far as to claim that this is an argument for procrastination,but it sure is nice when …[Read more]
On Wednesday, February 16th, I’ll be attending Percona Live in San Francisco to hear about what’s new in the MySQL ecosystem and talk about our adoption of Fusion-io storage for some of our systems at Craigslist. Not only do we have a busy web site, the data itself has posed some unique challenges over the last few years.
Part of getting a handle on that was upgrading to faster storage and moving from years-old MySQL 5.0.xx to more modern releases. I’ll also provide a bit of background on our plans to continue scaling and growing in the coming years.
If you’re in the area and interested in some of the cutting edge work that’s been going into production as part of major MySQL/XtraDB deployments, check out the conference. It’s …[Read more]
When I work on database designs, either on my own projects or as advisor to others, I often find people very reluctant to use an enum type for any columns. Now, I'm not about to advocate the gratuitous use of an enum column, they definitely have some pitfalls, but I think it is important to understand these rather than just shouting "enum evil!" if anyone should mention them.
There are cases where an enum is the correct choice for a particular type of data, so let's look at what an enum type actually is and does.
Enum Type Intentions
An enum column type is a column which allows only the specified data values. This means that if a particular column can only take certain values, for example 'art', 'music' or 'sport' then you can specify this when you create the table:
CREATE TABLE `teachers` ( `id` INT(11) PRIMARY KEY …[Read more]
As I previously noted, I’m in the midst of converting some data (roughly 2 billion records) into documents that will live in a MongoDB cluster. And any time you move data into a new data store, you have to be mindful of any limitations or bottlenecks you might encounter (since all systems have had to make compromises of some sort or another).
In MySQL one of the biggest compromises we make is deciding what indexes really need to be created. It’s great to have data all indexed when you’re searching it, but not so great when you’re adding and deleting many rows.
In MongoDB, the thing that gets me is the document size limit. Currently an object stored in MongoDB cannot be larger than 4MB (though that’s likely to be raised soon). Now, you can build your own MongoDB binaries and tweak that parameter, but I’ve been …[Read more]
Back in April I wrote that MySQL 5.5.4 is Very Exciting and couldn’t wait to start running it in production. Now here we are several months later and are using 5.5.4-m3 on all the slaves in what is arguably our most visible (and one of the busiest) user-facing cluster. Along the way we deployed some new hardware (Fusion-IO) but not a complete replacement. Some boxes are Fusion-io, some local RAID, and some SAN. We have too many eggs for any one basket.
We also converted table to the Barracuda format in InnoDB, dropped an index or two, converted some important columns to BIGINT UNSIGNED and enabled 2:1 compression for the table that has big chunks of text in it. Aside from a few false starts with the Barracuda conversion and compression, things went pretty well. Coming from 5.0 (skipping 5.1 entirely) we had some my.cnf work to do to …[Read more]
There’s been a surprising amount of drama (in some circles, at least) about database technology recently. I shouldn’t be surprised, given the volume of reactions to the I Want a New Datastore post that I wrote. (Hint: I still hear from folks pitching the newest data storage systems.)
The two things that caught my eye recently involve Cassandra and MongoDB (and, indirectly, MySQL). First was what I read as a poorly thought out and whiny critique of MongoDB’s durability model: MongoDB Performance & Durability. Just because something is the default doesn’t mean you have to use it that way. Thankfully there was reasoned discussion and reaction elsewhere, including the …[Read more]
I’ve been doing some prototyping work to see how suitable MongoDB is for replacing a small (in number, not size) cluster of MySQL servers. The motivation for looking at MongoDB in this role is that we need a flexible and reliable document store that can handle sharding, a small but predictable write volume (1.5 – 2.0 million new documents daily), light indexing, and map/reduce operations for heavier batch queries. Queries to fetch individual documents aren’t that common–let’s say 100/sec in aggregate at peak times.
What I’ve done so far is to create a set of Perl libraries that abstract away the data I need to store and provide a “backend” interface to which I can plug in a number of modules for talking to different data stores (including some “dummy” ones for testing and debugging). This has helped to clarify some …[Read more]
I wrote about my MySQL wishlist on November 14th
2007 and now it's time for an update. I will copy-paste the
old entry. The original text will be in italics.
1. Per user and/or per database quota
Would very useful in setups for shared hosting. This would also prevent one database from bringing down the whole server. Separate tablespaces on different mountpoint can ease the pain, but I consider that a nasty hack.
No update. Still problematic
2. External authentication
I've seen numerous scripts which fetch the authentication info from ldap, a file, another database or some other authentication store. This should be integrated into mysql. The mysql grant tables should be pluggable so it is possible to write a custom authentication plugin. We already have plugable engines and function (UDF) so this shouldn't be that hard …