MySQL as a Key Value Store (KVS) - Why, some background and some more tests

You might have seen my attempts at testing MySQL as a Key Value Store (KVS) (here and here), i.e. used like MongoDB, and I have had some comments to those. Above all, many wanted to know the details of my test environment. To be honest, this started off as a very simple thing, but it turned out a bigger than I thought, so I guess its reasonable to give some background. I apologize for not giving you this background before, but as I said, my initial intention was to just run bog standard MongoDB versus MySQL and see what happened.

Background

Here at Recorded Future, when I joined about 2 years ago, we used a lot of MySQL, and it was causing us serious problems. We had a pretty standard school-book style relational schema, and performance was really hurting us bad. Because of the amount and type of data we manage, because we run on Amazon and because we are still developing and changing, this schema didn't fit our needs at all.

Read - scalability was not a good option either, as we are massaging so much data that we had big issues with just having the Slaves keep up with the master. Schema changes, as we developed our application, was also difficult.

What we ended up doing was a redesign of a lot of things, ending up with something that could use a KVS, as these are easier to scale, using sharding. This had some negative implication too, like it was no longer possible to go into MySQL and issue a query for data just like that. And no, using the mongo shell for this, as not only were we now using MongoDB, we had data spread over several MongoDB stores. On the good side though, things was not much more performant and easier to scale, write scalability with MongoDB is good and easy to use and configure. Which is not to say that MongoDB solves all problems in the world.

So, having implemented MongoDB and having gotten used to it and even starting to like it a fair bit, I also started seeing some other things. Like, Why can't MySQL be used for this? I mean MySQL is a bit more mature than MongoDB and should be better at handling, say disk-IO. On the other hand, MongoDB is newer. One thing I already knew was that MongoDB really needs to be in-memory to a large extent, when it hits the disk, performance goes downhill fast, so here MySQL is better. But when we DO have enough RAM (and as we use several MongoDB datastores for different purposes, and we are on Amazon, where you can get respectable amounts of RAM, this is usually the case) what happens? If I let MySQL use RAM it can get so I don't get any disk-IO for reads at all, will it be as fast as MongoDB? Or maybe even faster.

And among the MySQL Storage Engines, the one mostly targeted as a Key Value Store is NDB? How's that going to work.

 

Test environment

So, to test all this, but to make sure I wasn't affected by Amazons ups-and-downs, I pulled one of our MongoDB datastores, some 105.000.000 rows of real world data. Initially, my plan was to use the full MongoDB datastore, but I had to exclude some fields as these were VARCHAR and putting them on Disk with NDB was causing just too much disk-I/O, as NDB stores disk data as fixed length (so a UTF-8 VARCHAR(256) field occupies some 768 bytes). I ended up with a table schema like this:

CREATE TABLE `keyvalue` (

  `id` bigint(20) NOT NULL,

  `value1` int(11) DEFAULT NULL,

  `value2` double DEFAULT NULL,

  PRIMARY KEY (`id`)

)

In the case of MongoDB, the id column was used as _id, which is MongoDBs way of saying "PRIMARY KEY".

 Having done all that, I need some tools for my test bench:

• Homebrew server with an AMD FX-8120 8 core CPU and 16 Gb RAM on an ASUS M5A88-V Mobo. This is a neat server MoBo really, supporting SATA 6, USB 3.0 and other niceties but I use a Lite-On LNE100TX network card instead of the built-in Realtek crap that many MoBos, inparticular AMD ones, seems to be equipped with.
• A bunch of disks, the disks where database resides is a LVM Stripe of two Seagate Barracuda 7200.11 500 Gb disks using xfs. But this is pretty insignificant as there should be no disk-I/O at all, or at least extremely limited, when testing.
• Ubuntu 10.10 (I have some reasons for this a bit ancient version. And yes, I have patched that 16 Gb memory issue in Linux with some AMD MoBos).
• MySQL 5.6.5 64-bit (for all MySQL tests except NDB)
• MySQL Cluster 7.2.6 64-bit (for MySQL NDB tests) 
• MongoDB 2.0.5 64-bit

Having all that ready I now had a slightly smaller table taking up some 4.2 Gb when exported as JSON. So this data has been imported into MongoDB and MySQL with a number of different storage engines, and I was ready to do some testing. I use a test program that kicks of SQL queries with over a specific T of threads, each SQL (with Mongo this is not SQL of course, but the test program is the same) containing a variable part, which is the ID for the row to look up, The program starts by reading a file with a number of random IDs, the IDs are spread over the threads and then each thread looks up the rows N times.

In this case I picked some numbers for my test, so the number of threads I use is 100, not an unreasonable number, but big enough to make any concurrency issues visible and the number of rounds is 10. So to be clear, the 1.000.000 IDs are distributed over the threads, so each thread processes 10.000 IDs each, running then 10 times, which means I do some 10.000.000 random reads in parallel. Not the most exciting benchmark on the planet, I know, but it was all the same interesting and also to some extent a real-world test.

Before each test run, I did a few initial runs to ensure that the cached were warmed up. Also, I did look at vmstat and stuff to ensure no disk-I/O was taking place.

 

Current state of tests

I have so far tested MongoDB, MySQL Cluster and MySQL InnoDB and since last I have also tested the MySQL MEMORY engine and MySQL with MyISAM. So far the results are like this:

• MongoDB - 110.000 rows read per second
• MySQL Cluster - 32.000 rows read per second
• MySQL with InnoDB - 39.000 rows read per second
• MySQL with MEMORY / HEAP - 43.000 rows read per second
• MySQL with MyISAM - 28.000 rows read per second

The last two ones were very disappointing to me, and trust me, I want them to be good. I really wanted MySQL to beat MongoDB, and I really wanted to see how the much lower overhead of MyISAM would make it run circles around InnoDB, but no.

As MyISAM only caches the keys itself, not the actual data, this was an issue, but still, the MyISAM data file should be well cached by Linux for me, and rightly so, there was no disk-I/O. Could context switching when going to Linux for data be an issue? I decided to try something fun. I created a new unique index on the table, covering all columns, which meant that if I used this covering index, all data would be fulfilled by the index (but the index in itself would be bigger of course). This didn't help at all, quite the opposite. Playing with named keycaches and stuff didn't help at all, nor did using mmap. Which is not to say that these guys do not help to improve performance, all this means is that when all data is already im memory, they don't seem to help. I did figure out one thing that did work though, which was using tcmalloc. Enabling tcmalloc when starting the mysql server, which is easy these days as MySQL comes with the tcmalloc library included and mysqld_safe has an command-line option for it. Real easy, this really should be the default.

With this in place, I managed to get:

• MySQL with MyISAM - 37.000 rows read per second

I'll try using tcmalloc with the other engines also, and see what happens.

 

In conclusion so far

Don't take me wrong here,  I'm not out to prove that MySQL or MongoDB sucks. Nor am I out to do some exact benchmarking. What I am out to do is to see if the claim that an RDBMS can perform as well as a standard Key Value Store in an environment optimized for a Key Value Store (i.e. all data in memory). And such an environment isn't that specialized actually. databases around a few 10's of gigabytes are really common and can easily fit on memory. And although I do prefer the more strict datamodel of an RDBMS to the pretty schema-free design of a NoSQL alternative such as MongoDB (MongoDB really has some reliance on schema, but in essence, it's schema-free), I have to admit that sometimes you need more performance, and distributing the load over multiple servers is a lot easier when you don't have to worry about global transactions or global consistency, and frankly, in many cases you can live without that, to an extent,

What remains to be tried is MySQL with NDB using multiple mysqld servers, InnoDB using tcmalloc and tarantool and the MySQL HANDLER interface (actually, I have already tested the latter, but I am not sure I got the tests right).

/Karlsson
Who apologizes for the omission of the test setup before publishing the first results

More on database consistency

I've written a few times about database consistency before, mainly in conjunction with NoSQL and the concept of Eventual consistency. Now, I'm about to do an update on the subject, as I have come to realize a few things.

From an oldtimer like myself, having been an SQL guy for 25 years, I remember Punk-rock and even The Beatles and I having hair growing out of my ears, what can be contributed? Well, let me beging with stating what I mean when I say Database consistency. What I mean is Consistency as the C in ACID (no, we aren't talking drugs here, we are talking databases). Let's see what the online authorative reference work on just about anything on this planet, from the size of J-Lo's feet to the number of Atoms in the universe (those two numbers are quite far apart by the way), Wikipedia: "The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including but not limited to constraints, cascades, triggers, and any combination thereof." In other words, consistency means that the databas is always in a consistent state, the different data items in it (rows, if you wish) are "in sync" with eachother. I think most of you agree with this notion.

Now, when it comes to NoSQL databases, like MongoDB, this terminology is different. These guys introduced Eventual consistency, which means that the database will eventually reach a consistent state with regards to a specific transaction that changes that "state" of the database. But there are multiple transactions at the same time, and they aren't necessarily, in an Eventual consistentcy model, consistent with eachother as they aren't on the same node. But the theory goes that some time, eventually, they will. If the system never stops, and transactions keep coming, then eventual consistens is determined to happen within 100 ms or less from the point in time when pigs fly. But if you stop all state changing transactions, then the state of the database will reach consistency. Eventually.

Now in NoSQL circles there is a thing called a Consistent read. If my database was consistent, then any read is consistent, right? And in the case of use SQL RDBMS folks, consistency is about the state of the database when I write to it? Well, if you have an eventual consistency model, where you have data distributed all over the place, things are different. To begin with, the basic thing that you have to make sure, and the NoSQL databases do this, is to ensure that the writes to the databases are all in order (we know this from MySQL also, and it is part of the issue with the MySQL slaves, and the NoSQL guys aren't fixing this particular bottleneck). And here we mean they are in order in each and every node. Across nodes, we don't care, which is where I get my abilility to scale out writes from!

A consistent read is a read where the data I am reading is in a consistent state, or sometime that my data is the most recent data. These two aren't always the same, but the second (reading most recent data) typically implies the former, although I assume this is not always the case. This is VERY different from the meaning of Database Consistency as we RDBMS folks look at it. All the same, the concept sure is useful, and as the NoSQL distributed systems doesn't need to keep the data consistent on a global level, a lot of shortcuts can be taken. But having Read Consistency has litte to do with Database Consistency. Your NoSQL fans will complain here and try to tell you that these achieve the same thing, but they don't. Achieving global Database Consistency costs an arm and a leg or two in performance, but the database is ALWAYS consistent.

So two different things, both with advantages and disadvantages, but they are STILL different! And the NoSQL folks will confuse things by allowing you not to have even Read Consistency, somewhat implying that turning it on means you get Database Consistency and that the Read Consistency model (which is very very simple by the way) means you get the effect of Database Consistency using Eventual Consistency. Nope. You don't. Which doesn't make it bad, but IT IS NOT THE SAME THING!

/Karlsson

My take on the "warning" against using MongoDB...

We have seen the "warning" against using MongoDB a few times now, and I have to say that this reminds me of other such warnings:

• 640 K is enough
  
• Relational databases are too slow (John Cullinane) (see for example: http://www.cbronline.com/news/ellison_makes_pre_emptive_strike_to_save_oracle_from_fate_of_cullinet)
• Linux is for nerds only.
• Virtual computing is waaay too slow.
• There is no reason anyone would want a computer in their home. (Ken Olsen, DEC)
• Who the h*ll wants to hear actors talk?. Warner

In a sense, most of them were right. If you had, in the 1920's, asked the movie going public if they wanted "talkies", chances are most of them would have said no. If you had told my mom and dad in the late 1970's that within 20 - 30 years, everyone would have a computer at home, with some resemblence to what their nerdy near-20 year old boy was tinkering with in the basement of their house, they probably would have laughed, at best.

But that's not the thing here. True innovation moves things forward. It introduces new things and new ways of doing things in a way that we have not heard of before, and the rest of the world has not a got a good view on it. Look at Virtual computing. This was considered so slow that it was close to useless some 10 years ago or so, but today it just cannot be ignored and is put to good use all over the place (I am now disregarding the fact that this technology is way older than this, I am talking Virtual computing in the field where I spend most of my time).

If something that provides new and unique features, and new ways of doing things, are still slow, when compred to traditional means of acheving similar results, isn't strange:

• New means not fully developed. What you want to demonstrate with something completely new isn't that it performs as well as existing technologies, then why would anyone change? No, you want to show the new features and demonstrate hwo unique this new thing is.
• The way we measure performance or whatever we use to measure existing technologies, is usually tied to measuring just that: the performance or whatever of existing technologies, not that of a new technology and a new way of doing things.

In the early 20th centrury, steam cars were mach faster and more reliable than internal combustion powered cars, the Stanley Steamer was more performant and reliable than most competitors.

Getting back to MongoDB then: My main gripe with it is not that's it's not in all aspect mature (it's not. face it, if you use MongoDB you used leading egde stuff. It will break, live with it!). Neither do I have any issues with many of the other attributes of MongoDB and neither that it really isn't even innovative (it's not, live with it). No, my main gripe is this: MongoDB and NoSQL isn't really that new, and this means it is probably a stop-gap solution. In the 1980's running a SQL database on a PC was possible, but slow (I was working for Oracle at the time, so I know), DBase was in the case of a single PC easier to use, faster and more developer friendly. And the way you used Ashton-Tate DBase, by the way, wasn't that much different from how MongoDB is used today.

But the SQL Based relational databases, like Oracle, Informix etc. had more features and was more flexible and standardized, and once the PCs got more powerful, DBase was history.

What really wins then, in my mind, is features, flexibility, scalability and broad spectrum of usacases. SQL Based relational databases, has this, to an extent, but what most of them lacks is scalability across servers in a cloud. In this aspect, they have some of this scalability, but they don't scale nearly as much or as easily as, say, MongoDB or the other NoSQL databases (yes, I hate that term. Find a better one fpor me that is broadly accepted and I start using it).

So what am I saying here? Let me summarize it:

• No. MongoDB doesn't suck, no way, but in terms of maturity it has some way to go.
• Yes, a relational RDBMS usualy has more flexibility and broader set of usecases than a NoSQL solution.
• And Yes: There are places where the RDBMS software industry got caught with their pants down: Cloud environment scalability for example. Also, licensing, if Oracle or MySQL or whoever could figure out a proper means of pricing cloud services, I'd be happy (insteaad of using the pricing models for software that was introduced with Auto-Flow in the early 1960. This is insane. The world has changed since then, guy!)
  
• Would I rather use MySQL than MongoDB here at Recorded Future? Yes, probably, but I don't insist, and it just wouldn't work, as MySQL will not scale in the way you can scale a MongoDB solution, far from it.
  
• Will MongoDB or the other NoSQL solutions mean that the era of SQL based databases is reaching an end? Nope, no way, José. Eventually some RDBMS vendor will get it and understand the issues and build a viable solution (like ScaleDB or NuoDB or something like that).
• What is my main issue with MongoDB? That it sacrifices features and functionality for performance. And it cannot add the features and flexibility of an RDBMS without sacrificing performance (look at this: access method: B+-Tree. Come on, how innovative is THAT?) . To be honest, running MongoDB without sharding seems like a useless excercise to me. If you don't need the scalability that this setup can provide you with, you have better options. (but this is just me talking here.)
• With MongoDB hang around for long? Yes, probably, but it will not be that hot for as long at SQL based databases. The reason is that compared to a traditional RDBMS it provides just one big advantage (a big advantage, yes, but just one): Performance. Which is why we use it. Remember the Stanley Steamer? Old hat today, but it was the hottest thing you could drive some 100 years ago or so. And this is what cloud computing is all about, a constant change of technology to get the best value for money right now, and to be on constant lookout for new technologies that drives features and performance.
  

/Karlsson

NoSQL for us RDBMS folks - MongoDB 101

As you probably know, I have been doing RDBMS work for many years, some 25+ years by now. At Recorded Future I am the database architect, and although an RDBMS is used extensively, MySQL in this case, we are looking at options, and are currently doing more and more work using a NoSQL Solution, probably te most popular one by now, namely MongoDB.

And before you complan: NoSQL is not a good term, but someone with a longer NoSQL background should then find something better, not yours truly. And for all intents and purposes, you know what I am talking about, right?

I plan to post a few MongoDB posts here, how it looks like from an RDBMS dudes (like myself) POV. This is the first installment, but there will be more. I should also point out that I am no expert in NoSQL technologies in general, nor specifically in MongoDB, but I am trying, and our MongoDB setup is large and complex enough to serve as a decent example: We have a couple of Tb of data in Mongo, we use Replication and we use Sharding / Clustering whatever you want to call it. In addition, we run this puppy on Amazon EC2.

So, why did we move to MongoDB, and what are the results? I think the reason is two-fold:

1. Better performance. Not better performance per se, but by using sharding and large in-memory stores, we can achive this.
2. Better and easier to set up scalability. Again, this doesn't come by itself, nor is it without problems. But the built-in sharding in MongoDB actually does work reasonably well, and is largely transparent to the application.

This sounds great, doesn't it? Everyone wants better perforamnce and better scalability, right? And why can't we have MySQL to scale in the same way as MongoDB? Well, there are drawbacks to all this, but in our case, I think we can live with it.

An important thing to note here, I think, is this: Scalability is transparent and easy to use and implement because the basic idea of a NoSQL system (a key-value store in this case) is so simple: One unique key points to one value. That's it. This is easy to shard. Dead easy. A multi-table relational schema is much less so. But you could simplify the schema in MySQL to make it just as easy to shrd, but then what is the point of an RDBMS at all? And MongoDB Scalability is an incredibly important part of MongoDB performance (in addition to it's in-memory nature).

In our case, using Amazon EC2, scaleability is really important. A single Amazon instance is limited in terms of memory and CPU power. The limits are high (some 70G RAM for example), but there is still a limit. If you have, say, 500 Gb or a Tb or so of data, 70Gb might not be that much, so you have to shard or cluster or something. And this is where MongoDB rocks, it is easy to shard, the simple data structures in a NoSQL database mena this this can be done largely transparent, and all means that MongoDB Rocks for us.

Do we have other options? You bet! MySQL Cluster, NimbusDB (aka NuoDB), Oracle, Cassandra, etc etc, but we ended up with MongoDB for now.

So from a DBA POV, what is mongo then? To begin with, there is no SQL interface and no "query language" per se, rather to the user, MongoDB looks a lot like a Java Script environment with a lot of space for variables, sort of. MongoDB has databases, and in databases there are collections, which are sort of like tables in an RDBMS. A collection is a key-value store, you can define a key for the collection, or MongoDB will generate one for you. MongoDB is schemaless, which means that a collection doesn't not have any predefined columns or anything. Instead, if an object that you store has an attribute foo then you assign a value to it in your object and insert the object. That's it. And you can have an index on foo if you want to. And index in this case is a traditional B-Tree index, nothing more exciting than that.

An Object as above in Mongo is a JSON structure, which internally in Mongos is represented as BSON. To access the collections you use Java Script as I said before, like this to insert an object into the collection bar with the attribute foo:
db.bar.insert({foo: "foobar"});
In SQL this means: INSERT INTO bar(foo) VALUES('foobar')
Used this way, Mongo will assign the object a unique id, called _id, which is similar to the PRIMARY KEY in an SQL RDBMS. To retrieve the row we just inserted, use the command:
db.bar.find()
Which in SQL means: SELECT * FROM bar
And you get something like this back:
{ "_id" : ObjectId("4e8c9b2e2fde67676c2381ae"), "foo" : "anka" }

MongoDB has a big bunch of built-in Java Script methods, what I showed above was just a very basic sample. To use Mongo, knowing Java Script and JSON in general is more or less a requirement, and if you don't know these, and start using MongoDB, you will know then soon.

I will write some more blog-posts on Mongo eventually, but before I close this post, let me tell you one more thing. I never created the collections (bar) I used above. The reason is that yiu do not have to, it is created for you as you access it. The same goes for databases. This is the reason that you often end up with a bunch of misspelled databases and collections in mongo, which you can of cource drop after you have made a mistake, but as we are all lazy, you mostly have a bunch of non-used databases and collections.

/Karlsson