Karlsson on databases and stuff

Database Innovation, pleeease!

2012-01-18T07:25:00.003+01:00

I think you have heard me say it before, but in this case I think repetion is needed: We should be much more innovative in the database world. And no, I am not talking NoSQL here, not at all. For all the good things with the NoSQL technologies and the movement itself, it's not really innovative. Rather, in my mind, NoSQL sacrifices functionality for performance, largely. The schema-less design of most of these technologies is probably the one aspect I would consider innovative, the rest is just RAM based storage, sharding, key-based lookups and good, old B-Trees.

Talking about B-Trees, isn't it time we retired them soon? There should be better ways if indexing data. Look at something like Mongo. With MongoDB, you really want to have your indexes in-memory, all of them, without that, performance will be awful (there are exceptions though, but in general this is true). Now, a B-Tree is an index mechanism that has worked well, as the structure of it lends itself to good performance be it on disk or memory, although in general, a B-Tree is built for disk-based storage with caching; for in-memory use, there are better, more efficient, indexing (or access) methods. So if an index in Mongo is supposed to be in memory, why choose a disk-oriented indexing mechanism? T-Trees are there, they are optimized for in-memory use and has been around for ages? I guess the answer is tradition.

Tell you what, tradition is a BAD BAD argument for anything in an industry that changes as fast as the IT-industry. Would anyone suggest that Facebook base their hardware platform in Motorola 6800 CPUs? I think not. But the B-Tree predates the 6800 by far.

Which is not to say that the B-Tree is so bad (or that the Motorola 6800 is either), it's not, but we have much more diverse needs these days, so there should be more diverse access methods in use, but the B-Tree persists, despite that.

And look at SSD-disks. Yeah, the future, right? A largely random access style memory hooked onto an interface designed for electro-mechanical harddisks in the 1970's. Innovative? I think not. Apple got it right in attaching Flash on the Mobo and PCI-based Flash is growing and coming down in rice, so it seems things are moving there at least.

But in any case, Flash / SSD isn't an electromechanical disk with cylinders and sectors, despite what the SSD interface tells us. And if the B-Tree works well on disk, we talk electro-mechanical disks. Where is the access methods designed specifically to reap the benefits of direct attached Flash?

And to be honest, the SQL-Based RDBMS, something which I have spent my career with, in one shape or te other, for 25'is years, is hopelessly outdated, but that is not why I'm no big fan of the NoSQL movement. Rather, my problem is just that the NoSQL movement really doesn't represent something new or is a disruptive technology in any way. Where, my friends, is the disruptive database technology? A Technology built for (you are sitting down now, I hope) the 21st century, If you missed it, we are there now, since 11 years back actually, so start inventing.

And yes, I know about the different MySQL variations with sharding, storage engines etc. etc. But that is not terribly innovative or new. The closest we get to a disruptive technology in the database world recently, is the column based storage databases. But thses are not gereric enough in my mind, and also, most of them have a SQL based interface tucked onto them. And I understand why they want SQL, they need this to be able to sell it, as all consumers of database products (most at least) wants SQL to integrate it with some tool or infrastructure. And I understand this too, but it brings up a question. Where is the customer or end-users who is willing to sacrifice using a query language as old as Led Zepplin to instead get the benefits from some new disruotive database technology?

But this is, I'm afraid, a bit of and chicken-and-egg-situation. The customer isn't requesting innovative products as that technology doesn't exist much, and the products aren't developed and research isn't much done as the customers aren't there. This really has to change soon, and I am sure it will. If for no other reason so that I can retire in peace, knowing that my SQL skills are truly outdated and I will not have to work, because noone wants my skills!

/Karlsson
Lookiing forward to retirement

MongoDB for MySQL Folks part 4 - Sharding

2012-01-17T16:38:00.003+01:00

Welcome part four of this series of blog-posts on MongoDB, where we previously looked at:

These were introducing some basic concept when it comes to querying MongoDB and to show some simple use cases. By now you realize that MongoDB is different from MySQL, but you probably knew that already, but why would you move from MySQL to MongoDB? Well, you know the answer to that: Performance and Scalability. If MongoDB didn't provide pretty seamless sharding, scaling out over a large number of nodes, then MongoDB wouldn't be that interesting.

With MongoDB, it's performance depends on having large portions of data in RAM, and this is no different from MySQL, but it's even more true with MongoDB. But if you were running on a single machine, the amount of RAM you can use is limited, there is a limit to how much RAM you can (c)RAM into a single box. This is will limit performance of course, and is again not much different from MySQL. What is different is that MongoDB has a solution: transparent sharding (yes, I am aware of the different transparent sharding implementation of MySQL, like Scalebase, but that is a different story, that I will get into at a later stage).

Without Sharding, we would not have gone into using MongoDB here at Recorded Future. Why anyone would use MongoDB in the versions before when sharding was introduced, is beyond me.

MongoDB sharding from above

From a high-level view, this is how sharding works in MongoDB:

Data is distributed across one or more mongo servers automatically. A server may be either a single MongoDB server or a replica set (more on this in a later post).
Data is distributed in ranges in a user-defined shard-key. The shard key is, as is obvioious, a unique key. It may well be the unique _id identifier that MongoDB assigns to each document, but that is not necessary. Each such range is called a chunk and is some 64 Mb is size by default.
Each MongoDB server holds a number of these chunks.
Balancing a sharded setup involves moving chunks between the servers and this is automatic. In a perfectly balanced MongoDB shard setup, each involved server has the same number of chunks.

So far so good. Nothing incredibly complex, right, and also useful, right? Yes,, useful and workable, but you have to know what you are doing here.

There are actually three kinds of servers involved here:

Mongo shard servers. These are the MongoDB servers or replicasets that holds the actual sharded data. These are the same servers as for a non-sharded MongoDB setup, and there is no stopping you connecting to it just like that, accessing the sharded data in a non sharded way. Also, there is no stopping you adding databases, collections and documents to this server. The server may well hold both sharded and non-sharded data. The latter seems like an advantage, but actually is not and should probably be avoided. The number of mongo shard servers or shard replica sets determines how much data is distributed obviously, with 2 shard servers, each server will hold about 50% of the data etc.
Mongo "router" mongos. The mongos process is the process that the application connects to, and it is responsible for distributing a query between to the appropriate shard servers for a particular query. This is a different program than the other mongo servers, and it also has the role of performing the automatic balancing. The interface to it, from the point-of-view of the poor old application, is the same though, so an application that works with a non-sharded MongoDB should also work with a sharded one, but it connects to mongos instead of mongod. You may have as many mongos servers as you please.
The Mongo "config server". This is process that runs the usual mongod server, but it has a special role. Instead of storing the actual data, the mongoc manages the metadata, in other words, the config server data tells mongos in which mongo shard server an actual document is located, based on some query. It might seem like this server is a single point of failure, but it is not, you may have 3 of them, in which case they are replicated using a special mechanism.

To summarize this: The application talks to mongos. Mongos need to know where that data that the application is located, so it asks the mongoc. Once it has this data, it goes ahead and asks the mongo shard server for the actual data. And you might think that the extra roundtrip that the mongos has to do do the config server will slow things down and that the config server(s) is a potential bottleneck, but this is not so as the mongos process caches the config data. If the mongos data gets outdated (stale in MongoDB terms), the cache is refreshed.

Setting up MongoDB sharding

There are a couple of weird issues when you configure MongoDB for sharding, and some which are not so obvious. What I have described above is how the MongoDB server processes interact, and setting that up is difficult enough, although far from as difficult as it used to be.

You might be tempted to think that once the above setup is running, all data will be sharded just like that. And that would be nice and that would be the natural way for things to work, but hey, this is MongoDB, so it was close, but no cigar! No, you still have to tell the system that a particular database is sharded, and then enable sharding and determine the key to use for each and every individual collection in that database.

And again, as often is the case with mongo, some things are controlled from the commandline, some things are in configuration files and some things are stored in MongoDB itself. Also, in many cases, even though you have to set certain things in a configuration file, they end up in the database itself anyway. Why this is so you have to ask someone else than yours truly.

So, to get thing straight, this is how you set up sharding with Mongo in not so few and not so easy steps:

Start your mongod daemons that stores the actual data. Start them with the --shardsvr option enabled. Apparently this is no longer needed, but I think it's a good thing to put it there anyway.
Start your mongoc servers, you have 1 or 3 of these. These are mongod servers running with the --configsvr option.
Wait until the config servers are up and running. All of them! This is not well documented, but mongos will not start unless at least 1 config server is running, and the first time around, ALL config servers must be running!
Start your mongos servers (routers). Make sure that they actually start, if the config servers cannot be contacted, then mongos will just fail with a message in the logfile.
Now, we must tell the config servers where our shards are, lets say we have two of them, "datasvr" and "datasvr2", both running on port 33010. Then enter the mongo shell, connecting to the mongos host and port (in this case mongos_host and 33011 respectively). First you must be in the admin database, and then you can add the shards:
$ mongo --host mongos_host:33011
mongo> use admin
mongo> db.runCommand({addShard: "datasvr1:33010"});
mongo> db.runCommand({addShard: "datasvr2:33010"});
Now we can make sure that we shard the data we have, so enable sharding for the database in question, lets call the database "mydb". Enter the mongo shell and connect with one of the mongos servers, again in the admin database:
mongo> use admin
mongo> db.runCommand({enablesharding: "mydb"});
And then we have one step left: enable sharding on the collections we want to shard, again from the mongo commandline connected to mongos. Here we specify the name of the collection and the key used for sharding. In this case, we use the always present unique _id column as the shard key and the collection is called mycoll:
mongo> use admin
mongo> db.runCommand({shardcollection: "mydb.mycoll", key: {_id: 1}});

Does the commands above that you run from mongos look weird to you? Like the key specification key: {_id: 1}? Yes, at first it looks awkward, even to me. But after a while you get used to it and can start to appreciate the strict Java Script / JSON syntax used all over the place in MongoDB, it is actually quite powerful and easy to use, once you get used to it.

Now, we have enabled sharding for one colletion here. If you have been through all this fuzz to create a sharded setup, I guess you would want all tables in that setup to use the powerful sharding mechanism? Like, automatically? Nope, can't do. You have to enable sharding manually for each MongoDB database and collection you create.

I plan to dig deeper into mongo in a later post, to show some monitoring features, how to use Java Script, how to set up replication and some other things. But for now, this is it!

Cheers
/Karlsson

Former Swedish Pirate Party leader and MySQL US Speaker on the list of the worlds top 100 thinkers

2011-11-28T14:28:00.006+01:00

For those of you who was at the MySQL User Conference in 2008, you might remember that the then leader of the swedish Pirate Party, Rick Falkvinge, was one of the keynote speakers, giving a talk on Copyright Regime vs. Civil Liberties. The MySQL User Conference has a history of inviting really interesting speakers, often slightly off what is expected at such a tech-focus conference. Falkvinge did a great talk there, and at least impressed me and got me thinking even more on the issues pf free and open speach, copyright, software licensing etc.

Now Falkvinge has stepped down as leader of the Pirate Party, but he is still very much out there and promoting openness. He has managed to achive such a reputation that he is now number 98 of the list of Top Global Thinkers published by the Foreign Policy magazine. I'm not sure how much influence the MySQL UC keynote meant in terms of getting on that list, but it is impressive and the MySQL UC organizers deserve a pat on the back!

/Karlsson

Cloud Tech Day in Stockholm Tue Nov 29

2011-11-25T15:52:00.003+01:00

I'll be doing the keynote at Cloud Tech Day here in Stockholm on tuesday. I'll be speaking a bit about what Recorded Future is up to, about Clouds at Amazon and what it is like, about databases, like MySQL and MongoDB, in the Clouds and about Big Data in the Cloud! Really big data in Mongo, in MySQL and the lot.

As usual, I will express my opinions in no uncertain terms. What works? What doesn't work? What really should work, but which doesn't! What is considered new and waay cool but what is really some old technology that didn't use to work and has little chance of working now. And stuff like that, you know what it's like and maybe you even know what I am like :-)

Hope to see you on tuesday
/Karlsson

Nov 23: At Cloud Camp Stockholm

2011-11-23T15:48:00.003+01:00

I am Cloud Camp in Stockholm today. Some interesting ideas are bounced around, pretty cool stuff.

One thing hit me today though: the lack in innovation, in IT as a whole and in databases in particular is stunning. I have thus decided to write a few blogpost on this I think should, and probably eventually has to change, but noone wants to change it, and few even see it as a problem.

That said, I still got a few interesting ideas today, and I will test some products I saw here, and I will write a few blogpost on some of them.

I think the good usecases for clouds is also getting clearer, and that is a good thing. In difference to the current IT trends, IT press and many high-profile bloggers as well as IT influencers, I do not think that cloud computing will help resolve the conflict in the middle east. Also, I do not believe that the introduction of cloud computing, in difference to what many IT security folks seem to think, will cause all the credit card info, all the personal data and everything else suddenly to be available to everyone on the net. Taking my own stand as usual, and in this case this is a real different view,I beleive that Cloud computing is great for some, but not for all. And I also do not think (you are sitting down now, right? This is revolutionary, ground-breaking thinking) there is no such thing as a silver bullet. Tough!

/Karlsson

MyQuery 3.4.3 GA Released

2011-11-15T11:17:00.003+01:00

I have had MyQueru 3.4.2 as beta for quite a while now. During this time, a few minor bugs has appeared, and they have now been fixed. This means I can proudly announce MyQuery 3.4.3 as GA! Download it from Sourceforge!

Except a few bugfixes, the only major change is that the NDB Monitor plugin is no longer part of the prebuilt binaries. There are three reasons for this:

The plugin relies on NDB API, which still is still not available from Oracle in binary form, and if they insist on not shipping these binaries, although the source exists, I have to assume they do not want me to use them.
I just can't be bothered to build MySQL luster form source, just to get these binaries.
The way Windows binaries are built with NDB is not properly documented, so I had to guess and use some trial-and-errors. This was not something I wanted to put in a GA release.

If you don't know what MyQuery is, this is a Windows-only MySQL Query tools, that concentrates on flexibility, extensibility and SQL-scripting. It has a colour coded text editor, using Scintilla and includes user-defined, if you want to, keyword lists. There are several means of extending the tool, from just running simple SQL statements to using a C API. The tool comes with complete docutemtation and API samples.

/Karlsson

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

2011-11-14T13:55:00.003+01:00

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

Clouds in Stockholm

2011-11-02T11:50:00.002+01:00

I'll be at Cloud Camp here in Stockholm on November 23. Some familiar faces will be there, beyond yours truly then. I will discuss and present some real-live Database Cloud experiences, but as this is an unconference, don't expect slides, rather I will talk from my heart and give you some annoying and upsetting views on how things really are. Really!

I hope to see you there, pop by and say hello!
/Karlsson

MongoDB for MySQL folks part 3 - More on queries and indexes

2011-11-02T09:44:00.005+01:00

Last time I wrote about MongoDB for MySQL DBAs I described some of the basics of MongoDB querying, and this time I'll follow that up with some more on querying.

As we saw last time, the basic format of a MongoDB query is:
db.find(<query>,<attributes>)
Note that you do NOT replace db with the name of the database you want to query here, you just make the database you want to use the current one and issue the query, such as:
> use test
> db.mycoll.find()
The example above will find all objects in the mycoll collection, and will include all the object attributes and also the key (_id), like this:
{ "_id" : ObjectId("4eb0634807b16556bf46b214"), "c1" : 1 }
{ "_id" : ObjectId("4eb0634a07b16556bf46b215"), "c2" : 1 }
{ "_id" : ObjectId("4eb0635607b16556bf46b216"), "c1" : 2, "c2" : 2 }
{ "_id" : ObjectId("4eb0635e07b16556bf46b217"), "c3" : 3 }
The Object id is generated by MongoDB itself here, although you can set it yorself if you want to, as long as it's unique. The insert method is used to insert data:
> db.mycoll.insert({c3: 4, c4: 'some string'})
> db.mycoll.find()
results in;
{ "_id" : ObjectId("4eb0634807b16556bf46b214"), "c1" : 1 }
{ "_id" : ObjectId("4eb0634a07b16556bf46b215"), "c2" : 1 }
{ "_id" : ObjectId("4eb0635607b16556bf46b216"), "c1" : 2, "c2" : 2 }
{ "_id" : ObjectId("4eb0635e07b16556bf46b217"), "c3" : 3 }
{ "_id" : ObjectId("4eb063d307b16556bf46b218"), "c3" : 4, "c4" : "some string" }
And as you can see, typing is automatic, or you can look at it as being type agnostic. Now, this wasn't much more than we saw last time, what we want is to select some specific objects and possibly get some specific columns from it, this is done by specifying one or two arguments to the find() method. For example, if I only want to get the object back that I inserted last above, I'd do this:
> db.mycoll.find({c3: 4})
{ "_id" : ObjectId("4eb063d307b16556bf46b218"), "c3" : 4, "c4" : "some string" }
And this wasn't really complicated, right? The condition is passed as a Java Script object notation, and that is fairly uncomplicated. But what happens for something slightly more than this really simple example, like a rangesearch? To get all objects where the c3 member is 4 or higher (which results in the same object as above by the way), you would write something like this, and :
> db.mycoll.find({c3: {$gt: 3}})
{ "_id" : ObjectId("4eb063d307b16556bf46b218"), "c3" : 4, "c4" : "some string" }
I will show some more $-operations beyond $gt in a later post, for now just accept that they exist and are documented here: Advanced Queries

The _id column is just annoying here, right now, but it is always shown by default, as are all the other object. To get rid of it for now, this will do the trick:
> db.mycoll.find({c3: {$gt: 3}}, {_id: 0})
{ "c3" : 4, "c4" : "some string" }
Not too bad, right, and kinda easy to understand. The falgs you pass for each field in the second argument may have one of three values:

1 - Include this field. This is the default.
0 - Do not include this field.
-1 - Include no fields except this one and the ObjectId. You may have more of these, in which case all the -1 flagged fields will be included.

Let's try a more advanced version. I want to the the c1 and c2 attributes, and nothing else, then I do this:
> db.mycoll.find({},{c1: -1, c2: -1, _id: 0})
{ "c1" : 1 }
{ "c2" : 1 }
{ "c1" : 2, "c2" : 2 }
{ }
{ }
As you can see, I have to explicitly exclude the _id field.

Online help
The mongo commandline tool for once has decent online help. Typing just help will show the options. For help on database specific operations, type db.help() and for collection specific operations, type db..help(), such as db.mycoll.help(). In JavaScript, a function is just another script, and adding arguments to the function will execute the function, but maybe you want to see how the function is implemented? The just type the name of the function, like this:
> db.mycoll.find
function (query, fields, limit, skip) {
return new DBQuery(this._mongo, this._db, this, this._fullName, this._massageObject(query), fields, limit, skip);
}

DBA Work - Indexing data and explain
What would a mongo DBA want to do? Let's try creating an index. Let's say we want an index on the c1 attribute in the mycoll collection as above, then we must use the ensureIndex() method on the collection in question, telling what columns I want to index, like this:
> db.mycoll.ensureIndex({c1: 1})
And that's it. Let's try to query that collection again, this time using the c1 column as an argument, and hopefully the index will be used:
{ "_id" : ObjectId("4eb0634807b16556bf46b214"), "c1" : 1 }
Right. But is the index used? I want to know that it is for a fact, or if it isn't, so I have something to complain to my developers about. In MySQL, you want use the EXPLAIN command and figure out what index are being used, but with mongo? Easy. Use the explain method, like this:
> db.mycoll.find({c1: 1}).explain()
{
"cursor" : "BtreeCursor c1_1",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"c1" : [
[
1,
1
]
]
}
}
Hey, that's prett cool, right! The index is a standard B-tree index (the only index type available in MongoDB). An index can also be unique, like this:
> db.mycoll.ensureIndex({c2: 1}, {unique: true})
Which will create a unique index on the c2 attribute, but in our case it will not work:
E11000 duplicate key error index: test.mycoll.$c2_1 dup key: { : null }
What's going on here? Well, the c2 attribute isn't included in all objects, and but the index will include all objects, and MongoDB considers NULL a duplicate here (unlike an SQL NULL in which case this is not the case). So the real question here is, what do you want? As MongoDB is schema-free, and you can have any kind of attributes, and also looking at the data above, what I would probably want is an index on the c2 attrbute that makes sure that c2 is unique WHEN INCLUDED, if the c2 attribute isn't part of the object, then please mr. Indexer, ignore it. This is called a sparse index in MongoDB, and what it means is an index that just indexes the objects where the attribute is included.

Note that this may not always be what you want with non-unique indexes, but it often it is, and it makes seaching and inserting faster (as the index is smaller). In the case you have an attribute that is only rarely part of the object, and you want to find the objects where it IS included, this is just what you want.

In our case, the index is created like this:
> db.mycoll.ensureIndex({c2: 1}, {unique: true, sparse: true})
And this time we had no errors. Let's see how it works, first get some data:
> db.mycoll.find({}, {c2: 1, _id:0})
{ }
{ "c2" : 1 }
{ "c2" : 2 }
{ }
{ }
Now, let's see if the unique index on c2 will guarantee uniqueness by inserting a new row with an existing value for c2:
> db.mycoll.insert({c2: 1})
E11000 duplicate key error index: test.mycoll.$c2_1 dup key: { : 1.0 }
Yo! That worked as expected! As does this (which gives no errors):
> db.mycoll.insert({c2: 3})

That's it for now, I'll be back soon with some more MongoDB DBA stuff: Sharding!
/Karlsson

Databases in the Clouds - My Slides from Percona Live!

2011-10-26T12:39:00.007+02:00

Due to some bad planning from my side, I failed to realize that I only had 30 minutes for my presentation at Percona Live in London on October 25, instead of the orginally planned 45 minutes. My bad! So I had to rush through the presentation, and I still had a bunch of slides I still wanted to show. To compensate for this, and for your reference, here are all my slides on Cloud Computing from the conference:

/Karlsson

Percona Live is now over!

2011-10-25T21:44:00.003+02:00

And this was a great event! I'm at Heathrow catching a severely delayed flight back home to Stockholm now. Before I board the flight, I just letting you know that my slides will be on-line in a very short time.

It was great to meet you all there, and Percona does a great job of organizing these conferences and keeping the eco-system alive and kicking!

Thanx Peter Z and the whole Percona crew!
/Karlsson

At Percona Live in London now!

2011-10-24T11:39:00.005+02:00

I'm now in London for the Percona Live conference! Come see me at 1:30 tomorrow tuesday October 24 and hear about running databases, BIG databases, in a cloud environment. MongoDB, Sphinx, MySQL what have you, all running in a Amazon EC2 environment. Lots of data,, lots of resources, lots of disks! And lots of fun!

/Karlsson

MongoDB for MySQL folks - Part 2

2011-10-24T10:18:00.002+02:00

In this second part of a series of blogs on using a NoSQL database, in this case MongoDB, aimed at MySQL users, I will describe querying a bit. And it's probably not what you expected if you haven't tried a NoSQL database or MongoDB more specifically, before. The first blogpost on this subject is here.

At the heart of accessing MongoDB is JavaScript, more specifically it uses the Mozilla SpiderMonkey JavaScript engine. As I wrote in the previous blog on this subject, JavScript is all over the place in MongoB, largely you can look at MonngoDB as JavaScript with HUGE space for variables, and although many would look at this as gross oversimplification, it works as simple descripton of what we have here. JavaScript for querying, inserting, administration, scripting and "stored procedures" (they aren''t called this in MongoDB, but you know what I mean). And JSON (JavaScript Object Notation) for data, when inserting an object, you construct a JSON object, when you query, what you get back is a JSON object. etc etc. Internally, Mongo uses a binary representation of JSON called BSON.

So we have A LOT of JavaScript and JSON, and if you are no fan of JavaScript, then probably MongoDB isn't for you! In the MongoDB database there are collections then, which are similar to a table, but in a collection, there isn't, like in an RDBMS, a bunch of rows all with the same columns (attributes), instead there are multiple object, all using the same "primary key" (called _id) and the object itself is a JSON Object with any attributes. This is why MongoDB is "schemaless", there is no set schema for the objects (i.e. "rows") except the _id then.

The schemaless design sometimes gives the expressionh that beyond the unique access key, there is no other way to access the data and that you cannot have secondary indexes, but you sure can! But now I am getting ahead of myself.

Let's give MongoDB a shot. After installing MongoDB (which is dead simple) and starting the server, access the command-line using the "mongo" command line program. In the examples I show here, I use a sharded setup, so the prompt is "mongos", and it might be different in your case, depending on your configuration and version of MongoDB. First, lets create a database to play with;
mongos> use mytest
Hey, you ask, what is going on here? You never created a database, you just accessed it? Yes, that is the deal with MongoDB, you just access a database or a collection, and if it doesn't already exist, it is created for you. Which is the reason many MongoDB databases, even in production, has a bunch of wrongly spelled database and collection names.
Now, let's see of the database we access really exists.
mongos> show dbs
admin (empty)
config 0.1875GB
test (empty)
and as you can see, I lied to you, the mytest database isn't there at all! Well, the reason is that there is no collection there yet, so let's create one:
mongos> db.foo.insert({attr1: 1})
Again I am accessing something that doesn't exist to create it, in this case a collection called foo, where the first object has one attribute, attr1, with the value 1. Now, the mytest database should exist, as there is data in it, let's have a look:
mongos> show dbs
admin (empty)
config 0.1875GB
mytest 0.203125GB
And there you go, the mystest database is created and so is the collection foo:
mongos> show collections
foo
system.indexes
OK, there you go, a database and a collection for you. This is getting hotter! Let's insert some more data:
mongos> db.foo.insert({attr1: 2})
mongos> db.foo.insert({attr2: 3})
mongos> db.foo.insert({attr2: 4, attr3: 5})
All this is valid data, every object may have different and different number of attributes, fact is, every object can have any attribute you would want. Which doesn't necessarily make it a good idea, but it's good to have that feeling of complete freedom, right?
Some more inserts:
mongos> db.foo.insert({attr2: 4, attr4: {attr5: 6, attr6: 7}})
mongos> db.foo.insert({attr2: 4, attr7: [10, 11, 12]})
Here I first insert an object where one attribute in itself is an object, and then an object where one attribute is an array. Hey, cool!
Now, let's do some youerying. First just get att the data:
mongos> db.foo.find()
{ "_id" : ObjectId("4ea528fb35a81331e048baa9"), "attr1" : 1 }
{ "_id" : ObjectId("4ea52a4835a81331e048baaa"), "attr1" : 2 }
{ "_id" : ObjectId("4ea52a5035a81331e048baab"), "attr2" : 3 }
{ "_id" : ObjectId("4ea52a5f35a81331e048baac"), "attr2" : 4, "attr3" : 5 }
{ "_id" : ObjectId("4ea52ae635a81331e048baad"), "attr2" : 4, "attr4" : { "attr5" : 6, "attr6" : 7 } }
{ "_id" : ObjectId("4ea52afc35a81331e048baae"), "attr2" : 4, "attr7" : [ 10, 11, 12 ] }
The find() function is a built-in MongoDB function that retries data for you, and it can take 2 optional arguments, both of which is an object in itself (surprise!):

A query-specification.
The columes to return.

For example:
mongos> db.foo.find({ attr2: 3})
{ "_id" : ObjectId("4ea52a5035a81331e048baab"), "attr2" : 3 }
Here I am querying for all objects where c1 is 3. The attr2 attribute doesn't have an index on it, but I can query on it. To the use enforced _id key, just do this (in your case, you will se a different object id of course):
mongos> db.foo.find({ _id: ObjectId("4ea52a5035a81331e048baab")})
{ "_id" : ObjectId("4ea52a5035a81331e048baab"), "attr2" : 3 }
Before I close for now, let me show you how you can tell that the latter of the two last queries above was more effective by using explain() (you didn't expect that, did you):
mongos> db.foo.find({ attr2: 3}).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 6,
"nscannedObjects" : 6,
"n" : 1,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {

}
}
The result from an explain is, as you would expect by now, a JavaScript object. Look at the nscanned attribute here. There is no index on the attr2 attribute, so all objects are scanned, compared to this:
mongos> db.foo.find({ _id: ObjectId("4ea52a5035a81331e048baab")}).explain()
{
"cursor" : "BtreeCursor _id_",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 22,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"_id" : [
[
ObjectId("4ea52a5035a81331e048baab"),
ObjectId("4ea52a5035a81331e048baab")
]
]
}
}
Here, just one object is looked at, to get the data, and the id column is used to find it (look in the indexBounds attribute). So far, querying doesn't all that difficult to what you see in an RDBMS, except all those objects and JavaScript.

That's it for now, I'll be back with some more MongoDB lessons real soon! The next post will be on more advanced querying!

/Karlsson

NoSQL for us RDBMS folks - MongoDB 101

2011-10-04T08:23:00.004+02:00

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:

Better performance. Not better performance per se, but by using sharding and large in-memory stores, we can achive this.
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

MySQL Installer, once again

2011-09-22T11:51:00.006+02:00

First an apology. I have not been blogging much lately, but I do have a reason, and I think a valid one for once (i.e. not along the lines of "I was out drinking last night" or "I was busy boiling potatoes" or something), I have been moving house. And my new office is now as tidy as ever (and if you believe that, let me tell you about a bridge in New York that I have for sale at a really good price). No, the place is a mess, but my beautiful wife keeps up with me, and I am slowly getting things together.

That said, let's get to the point, MySQL Installer! If you read this blog, you know I have copmplained about this puppy several times before (First attempts with MySQL Installer and MySQL Installer part 2), and I have been in touch with the good developers at MySQL / Sun / Oracle / Google (no wait, that last one wasn't right. At least not yet) and for all intents and purposes, they are doing their best (I already knew that) and are listening to us.

So now MySQL Installer is GA. Is it any better than when I complained last time? It sure is!

Now it does what it was set out to do, it does install most of what you need as a MySQL Developer on Windows, it installs the MySQL Server, the Workbench and the drivers. This time around, I removed all Windows products first and then did a clean install, but some old settings still seemed to be around. This could be annoying potentiually, but the machine I was testing really has had a lot of testing on it, so it might be that this is in a weird state. Also, this might be what you want to do. Before doing a clean install, I tried an Upgrade. I had MySQL 5.5.14 on my box, and the installer came with MySQL 5.5.15, so I figured it would offer mne to upgrade if I choose Add/Modify Products and Features. On the other hand, after a clean install, if I choose Add/Modify Products and Features again, the installer insist that there are "changes pending" to the MySQL .NET Connector. If I execute that it executes OK, but the Installer still insist that there are "changes pending" to Connector/.NET.

This may sound like a lot of complaints, but it really is not that bad. If I was a Windows developer coming new to MySQL, this is not a bad starting point at all. But if I was Oracle (which I am not), I'd add a few things:

Links - I'd offer in the installer too add a few links to useful places on the net for MySQL stuff. This includes Planet MySQL, MySQL Forge, dev.mysql.com, www.mysql.com, the Bug database (how active that is these days I don't know either).
Third party software - There is some third party software that could be interesting to include, such as drivers (PHP etc), tools, plugins etc. There is a whole bunch of these available, and if I was new to MySQL, I would have a hard time selecting which to include and what to do with them. I understand the licensing issues here, but as this is the Community edition of MySQL, that shouldn't be too difficult.
Tips and tricks - How do you do this or that? What questions might a Windows developer have when approaching MySQL for the first time.
Integration - I would really like to see more integration here, with Visual Studio and with many other Windows developer tools. I admit that this might be there, but that the integration is hidden from me as I don't have those tools.

These are just some ideas moving forward with MySQL Installer. The current product is a bit limited I think, but still useful and is now pretty stable. Why the MySQL Installer is not available on a swedish mirrow is beyond me, but that is a minor issue I think, unless you live in Sweden (like I do).

For reference, I used 1.0.17 of MySQL Installer, something which is not that easy to find out, as the download page states that this is version 5.5.15 (which is the MySQL Server version). The installer itself doesn't say what version it is, at least not as far as I can find out. It does say in the VERSION resource of the MySQLInstaller.exe file though, so looking at properties with Windows Explorer reveals the version.

/Karlsson

Percona Live getting closer and some odd notes

2011-08-27T08:17:00.003+02:00

Percona Live in London is getting closer, and Oct 24 will soon be with us. Before that I hope to finish my delayed honeymoon in S.t Petersburg, move house and Travel to New York (I am glad I am NOT doing that today, Hurricane Irene doesn't seem like a nice lady).

I have done some more work on my presentation, and I hope this will end up being pretty interesting. At the same time, Recorded Future is in the middle of some major rewamping on the backend, so things are a bit hectic, to say the least.

The other week we had an issue with one of our slaves falling behind and having problems to catch up. I did not have my accelerator working on that machine, so I started it, and immediately the slave was catching up way faster than before, so again I have proven to myself that this puppy really helps. You can read more on the accelerator here: Slavereadahead 1.3 released.

This fall I am testing a bunch of alternative database technologies, some based on MySQL, some complementary to MySQL and some not MySQL-related at all, and I'll be talking a bit on some of them in London.

Special offer: Use the discount code RecFPLUK when registering to get a special discount.

See you in London
/Karlsson

Speaking at Percona Live in London October 24-25

2011-08-17T10:06:00.003+02:00

I'll be speaking about MySQL in the Cloud at Percona Live in London October 24-25 and this will be talk centered on our experiences of running a database setup, based on MySQL but with serveral complementary technologies, in Mamzon EC2.

I will talk about our experiences so far, we have a lot of data at Recorded Future and we are also write intensive, which is a slight difference from what you may be used to from other Web-based systems. The amount of data we have right now is in the multi Tb range, and we are growing fast. Is a could environment really useful for something like this? What does work and what doesn't?

Also, I will talk a bit on what I think the technological issues are here when using traditional servers such as MySQL and MongoDB, and how I would like it to work instead. Finally, I'll take a shot at looking at some alternatives that I have been testing recently? I like MySQL, and I have used it for many years and I support it now, but I am also a pragmatist+ Are there options and why are they interesting?

See you in London!
/Karlsson

MySQL Global status difference using MySQL procedures / functions

2011-08-12T13:56:00.003+02:00

As a MySQL DBA, I guess you use the SHOW GLOBAL STATUS command or the corresponding INFORMATION_SCHEMA.GLOBAL STATUS table to show current mySQL status quite often. And many of us use different tools to collect that data and view it.

But sometimes we use same command from the SQL prompt, because we have to, because it's the only option or just because that is the most convenient way. And often you kick of two such command in a row, a few seconds apart, just to see how things are moving:
SHOW GLOBAL STATUS LIKE 'innodb%rows%';
... DBA picks his or her nose for a few seconds ...
SHOW GLOBAL STATUS LIKE 'innodb%rows%';

And then you can see how things are advancing. But how much? To figure of how much, you have to calculate the differnce between the values returned by those two statements. And then there is another issue. How much is the difference per second? To know what the difference is per second, we have to includ the UPTIME_SINCE_FLUSH_STATUS status value in those statements. So we do this then:

SHOW GLOBAL STATUS WHERE variable_name like '%rows%' OR variable_name = 'Uptime_since_flush_status';
... Yet another chance for the DBA to pick the nose ...
SHOW GLOBAL STATUS WHERE variable_name like '%rows%' OR variable_name = 'Uptime_since_flush_status';

Now we have the data we need to work with, but we still need to do some math. And math is fine of course, in and of itself, but wouldn't it be nice to have some kind of contraption to do that for us? Like a machine? Let's call it a computer! Whao!

No, joking aside here, this is perfect job for a stored procedure or something. What we need is a stored procedure to to the jobs for us, and somewhere to store the previous value inbetween status runs, and here I will use a MySQL variable for that. Before I show you the code, let me show you what running it looks like:


MySQL> call getstat('%rows%', FALSE);

+--------------------------+-------------+

| variable_name            | current     |

+--------------------------+-------------+

| INNODB_ROWS_DELETED      | 171085420   |

| INNODB_ROWS_INSERTED     | 4940247881  |

| INNODB_ROWS_READ         | 16041373517 |

| INNODB_ROWS_UPDATED      | 90534033    |

| NOT_FLUSHED_DELAYED_ROWS | 0           |

| SORT_ROWS                | 266732753   |

+--------------------------+-------------+

6 rows in set (0.00 sec)



Query OK, 0 rows affected (0.00 sec)



MySQL> call getstat('%rows%', FALSE);

+--------------------------+-------------+-------------+------------+--------------------+

| variable_name            | previous    | current     | difference | difference_per_sec |

+--------------------------+-------------+-------------+------------+--------------------+

| INNODB_ROWS_DELETED      | 171085420   | 171085420   | 0          | 0.00 (2 s)         |

| INNODB_ROWS_INSERTED     | 4940247881  | 4940249044  | 1163       | 581.50 (2 s)       |

| INNODB_ROWS_READ         | 16041373517 | 16041373597 | 80         | 40.00 (2 s)        |

| INNODB_ROWS_UPDATED      | 90534033    | 90534044    | 11         | 5.50 (2 s)         |

| NOT_FLUSHED_DELAYED_ROWS | 0           | 0           | 0          | 0.00 (2 s)         |

| SORT_ROWS                | 266732753   | 266732753   | 0          | 0.00 (2 s)         |

+--------------------------+-------------+-------------+------------+--------------------+

6 rows in set (0.00 sec)



Query OK, 0 rows affected (0.00 sec)

The first parameter to the getstat procedure is the parameter LIKE-string you want to use, the second is a boolean if you want to rerun the base for the saved parameters. In other words, the first time the procedure is run, it collects relevant data, but it only shows current values, as that is what it has. The second and subsequent runs, it shows the difference from the first run, unless you set the other parameter to TRUE, which causes a new set of values to compare with to be set. If you subsequently again run it with the second parameter set to TRUE, the procedure will only show the difference between the two runs.

The nice thing with this procedure is that is uses no temp tables, which means no disk IO or anything, just access to the INFORMATION_SCHEMA GLOBAL_STATUS table, that's it.

So, now you can wait to get the procedure and use it yourself? OK, here we go, enjoy! (And there is actually the procedure and a simple supporting function).

/Karlsson


DROP PROCEDURE IF EXISTS getstat;

delimiter //

CREATE PROCEDURE getstat(p_like VARCHAR(64), p_gennew BOOL)

BEGIN

   DECLARE v_stat TEXT;

   DECLARE v_name VARCHAR(64);

   DECLARE v_value VARCHAR(1024);

   DECLARE v_nodata INTEGER;

   DECLARE v_uptime_current INTEGER;

   DECLARE v_uptime_last INTEGER;

   DECLARE c1 CURSOR FOR SELECT variable_name, variable_value

     FROM information_schema.global_status;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_nodata = 1;



   SET v_nodata = 0;

-- Make sure we always generate new stats the first time we run.

   IF @savedstat IS NULL THEN

      SET p_gennew = TRUE;

   END IF;



   IF p_gennew THEN

      SET v_stat = '';

-- Get the current status now, and loop through it.

      OPEN c1;

      l1: LOOP

         FETCH c1 INTO v_name, v_value;

         IF v_nodata THEN

            LEAVE l1;

         END IF;



-- Add name and value to saved statuses.

         SET v_stat = CONCAT(IF(v_stat = '', '', CONCAT(v_stat, ',')), v_name, ':', v_value);



-- Get current uptime.

         IF v_name = 'UPTIME_SINCE_FLUSH_STATUS' THEN

            SET v_uptime_current = v_value;

         END IF;

      END LOOP;

      CLOSE c1;

   ELSE

      SELECT variable_value INTO v_uptime_current

        FROM information_schema.global_status

        WHERE variable_name = 'UPTIME_SINCE_FLUSH_STATUS';

   END IF;



   SET v_uptime_last = getstatvalue('UPTIME_SINCE_FLUSH_STATUS');

-- If there was a previous value to show, then show difference.

   IF  v_uptime_last != '' THEN

      SELECT variable_name, getstatvalue(variable_name) AS previous,

        variable_value AS current,

        IF(getstatvalue(variable_name) = '', '',

          variable_value - getstatvalue(variable_name)) AS difference,

        IF(getstatvalue(variable_name) = '', '',

          CONCAT(TRUNCATE((variable_value - getstatvalue(variable_name))

          / (v_uptime_current - v_uptime_last), 2), ' (',

          v_uptime_current - v_uptime_last, ' s)')) AS difference_per_sec

      FROM information_schema.global_status

      WHERE variable_name LIKE p_like;

   ELSE

      SELECT variable_name, variable_value AS current

      FROM information_schema.global_status

      WHERE variable_name LIKE p_like;

   END IF;



   IF p_gennew THEN

      SET @savedstat = v_stat;

   END IF;

END//

delimiter ;



DROP FUNCTION IF EXISTS getstatvalue;

delimiter //

CREATE FUNCTION getstatvalue(p_name VARCHAR(64))

RETURNS VARCHAR(1025)

NO SQL

BEGIN

   DECLARE v_pos INTEGER;

   DECLARE v_end INTEGER;



-- If no saved status exists, then return nothing.

   IF @savedstat = '' THEN

      RETURN '';

   END IF;



-- Find the value.

   SET v_pos = INSTR(@savedstat, CONCAT(',', p_name, ':'));



-- Try to find the value name at the beginning of the string.

   IF v_pos = 0 THEN

      IF LEFT(@savedstat, LENGTH(p_name) + 1) = CONCAT(p_name, ':') THEN

         SET v_pos = 0;

      ELSE

         RETURN '';

      END IF;

   END IF;



   SET v_pos = v_pos + LENGTH(p_name) + 2;

-- Now find the value in the string, following the name and a colon.

   SET v_end = LOCATE(',', @savedstat, v_pos);

   IF v_end = 0 THEN

      SET v_end = LENGTH(@savedstat) + 1;

   END IF;



-- Extract the value and return it.

   RETURN SUBSTR(@savedstat, v_pos, v_end - v_pos);

END//

delimiter ;

Elastic cloud computing? Sure, but don't forget the database.

2011-07-31T11:07:00.003+02:00

I was interviewed at the Xeround booth at the MySQL Users Conference and Expo in April, talking about databases in the cloud, and although this was a vendor interview, the issues were pretty much cloud generic. I think you know my views on clouds and elasticity and stuff like that by now, but if not, the you might want to read my blogpost The E is for Elastic. And if you don't don't want to read my rant, then Xeround has published the interview on YouTube, so you can see it there:
Challenges for databases in the cloud

On Elasticity and Cloud Databases

/Karlsson

Yikes.. Backing up a sharded MongoDB is no fun!

2011-07-29T13:21:00.002+02:00

Backing up databases has never been fun, not as fun as having a cool English Ale on the balcony on a hot summar day anyway, but MongoDB takes this one step further when it comes to annoyances.

In general, I often feel that many Open Source projects start with good intentions for what the project should do and how, but then more stuff is added as you require it, and suddenly what started out as a simple and fast application for a narrow usecase, suddenly turns into a bit of a mess. And the issue might well be that building fast, compact software for a specialized usecase, as they start out, is not the same as writing generic software, with a wide range of use cases, code that can easily be maintained and enhanced as we go along. And why should it not be like that? In many cases, this is just fine and the limited usecase is just what the project sets out to do, and it does it well. But sometimes this turns into something really annoying, and at the same time useful.

I think MySQL is partly like this. There are many things in MySQL that work really well, so many things that in a small set of code achives so much useful stuff. And then there are things with MySQL that are just outright wrong and yet another bunch of things that are just plainly annoying. Largely, MySQL is very much developer focused more than DBA focused, although this is improving (and this is also a personal opinion of mine).

And then we have MongoDB, one of the big contenders on the NoSQL side in the NoSQL vs. SQL battle (which is a silly battle, but lets ignore that for now). Now, MongoDB is supposed to be a database. One that is faster, more compact and more targeted towards general database needs than MySQL. A database that can scale and replicate and shards automatically! Brilliant. And then this boring old DBA comes around with his bitterness and boredom and ignorance of the "new" database system. And he says things like: "Can you do a backup"... Yikes! Never thought of that. And what boooring guy that DBA is!

Yes, with MongoDB, backups is clearly an afterthought. And although this is again my personal opinion, I base it on something, namely this: Backing up a sharded Cluster. This is just plain silly. The way these steps are taken is in no way consistent, some of the operations are asynchronous, which means you have to wait for them (write code. To do a friggin' backup? Who came up with THAT daft idea?). You have to backup config servers cold, i.e. shut down. Dead. Who came up with that? And in the end, you don't even get a consistent backup. And yes, if you use replicas, you have to physically back up the replicas also! What?

Whoever figured out how to shard and replicate with MongoDB did a reasonable job, it actually works OK. But the person in question apparently forgot that databases are to be backed up. And before you ask: No, I am NOT going to take a mongodump of 1.5 Tb data!

This said, most aspects of MongoDB are OK, but backups are a mess. Read the page I linked to above, and you also realize that it is not well documented, to say the least, how to backup and what happens if the steps aren't followed? What happens if I do not backup the stopped config server? Can I do a mongodump of the config server instead? Why in heavens name can't I:

Flush and lock the config servers?
Flush all the dataservers in one go? No, you have to do it in one dataserver at the time.
Flush and lock the config servers?
And yes, why can't you flush and lock the config servers.

Also, locking is weird here. You have can lock serveral times, and then you have to unlock as many times. I really do not know why. Also, I have yet to figure out how to know if it is locked, without at the same time unlocking, but it might be that I missed something in the docs (which aren't very good I'm afraid, but this is not the first Open Source project with lacking documentation).

And having written all this, I have now created a script that will do all this for us, in unattended fashion. Yes, backups are supposed to be able to run unattended! No, I do not want any manual checking in the midst of a backup process! I do not want to be up at 3AM!

And Yes! I want a way to verify my backups, with some ease! No, I'm not going to set up a cluster of a 8 nodes with 4 Tb disk, just to verify a backup! And this is usually much less of a problem with MySQL, as it is not sharded / distributed. But for anything that IS sharded / distributed, for heavens sake, make sure there are tools to support this. In particular Backup tools!

/Karlsson

MySQL Installer part 2

2011-07-20T15:50:00.004+02:00

OK, I was having too much fun in my last post on this subject, so I try again. Just to be safe i Uninstall MySQL Installer, reboot my machine, and download the Installer again. Installing it this time didn't make anything happen it seemed? No dialogs, no nothing? But it is back there in the Start menu at least... OK, Let's try that one...
Think think think.. Whammo:
Yes, I am aware this is running on Swedish Windows, but let's try it on a US Windows 7 installation....

Downloaded and tried it on this machine, and now the installer runs at least. The "configuration" step doesn't actually do much. The Connector/.NET installation still insists on not finding a download location at first, but suddenly it works. The Installer also finds old versions of MySQL, which is nice, but it doesn't seem to know how to handle that. I still get into a state which the Installer doesn't know how to get out of, and just hangs (on "Validating installation").

As there is no nice cnfiguration screen, and the VS integration plugin is not included, I don't really find this installer such an "easy to use" thingy. To being with, it will not let me choose WHERE to install things (no, I do not want MySQL on C:, even thogh may other programs are there. In particular, I do not want C: to be my drive where the database is kept! As C: is an SSD which is there to speed up booting, not to have MySQL fuzz around with it).

No, this wasn't really easy to use at all. No, it didn't help much. Yes, the interface is very nice. No, I do not think this is RC quality software.

/Karlsson

More on OR-conditions considered bad... And an apology..

2011-07-20T13:26:00.003+02:00

In my recent post on OR-conditions I made a mistake, and I appologize for that. I made the statement that MySQL will only use 1 index per statement, whatever you do.

This is no longer true, as a matter of fact, and that has been the case since MySQL 5.0 and I should have checked. MySQL is actually able to use index_merge. An explanation why I didn't look for thi more carefully, yes an explanation, not an excuse, is that the optimizer doesn't seem to want to use this very often. Which is too bad.

So, with this in mind, and using the same table as in the previous post, let's look at index_merge in action. Or possibly, not so much in action. Let's recap what the table looks like:
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`brand_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
`weight` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `ix_brand` (`brand_id`),
KEY `ix_weight_brand` (`weight`,`brand_id`),
KEY `ix_quantity_brand` (`quantity`,`brand_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2321268 DEFAULT CHARSET=utf8
OK, fair enough, one single table with a bunch of indexes. Looking at the index_merge documentation, we can see that if we have an OR condition with both sides appropriately indexed, then this algoritm would execute each path and then do a sort-merge of the result. Let's try with a simple example, using a similar query to the one used last time, except that we are to ignore the brand_id column this time:
EXPLAIN SELECT id FROM product WHERE weight = 41 OR quantity = 78;
and we get this:

+----+-------------+---------+-------------+-----------------------------------+-----------------------------------+---------+------+-------+------------------------------------------------------------------+
| id | select_type | table   | type        | possible_keys                     | key                               | key_len | ref  | rows  | Extra                                                            |
+----+-------------+---------+-------------+-----------------------------------+-----------------------------------+---------+------+-------+------------------------------------------------------------------+
|  1 | SIMPLE      | product | index_merge | ix_weight_brand,ix_quantity_brand | ix_weight_brand,ix_quantity_brand | 4,4     | NULL | 25729 | Using sort_union(ix_weight_brand,ix_quantity_brand); Using where |
+----+-------------+---------+-------------+-----------------------------------+-----------------------------------+---------+------+-------+------------------------------------------------------------------+

That is cool! We are seeing index_merge in action here! Coolness! So, knowing that, let's see if we can get index_merge to work for us in the case which we looked at last time, where we also had a brand_id column in the query. There are indexes on brand_id combined with both quantity and weight, the exact same two indexes used above actually, so adding brand_id should produce the same nice execution plan, but of course reduce the number of rows returned. Lets try it
EXPLAIN SELECT sql_no_cache id FROM product WHERE (brand_id = 6 AND weight = 41) OR (brand_id = 6 AND quantity = 78)
And we get this:

+----+-------------+---------+------+--------------------------------------------+----------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys                              | key      | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+--------------------------------------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | product | ref  | ix_brand,ix_weight_brand,ix_quantity_brand | ix_brand | 4       | const | 4291 | Using where |
+----+-------------+---------+------+--------------------------------------------+----------+---------+-------+------+-------------+

No luck there. For some reason, the optimizer seems to dislike the index_merge access method, except in the most obvious of cases. But hey, we don't give up that easily, do we, we can use a force index, right? Like this:
EXPLAIN SELECT sql_no_cache id FROM product FORCE INDEX (ix_weight_brand,ix_quantity_brand) WHERE (brand_id = 6 AND weight = 41) OR (brand_id = 6 AND quantity = 78);
And the result is this:

+----+-------------+---------+-------------+-----------------------------------+-----------------------------------+---------+------+------+-------------------------------------------------------------+
| id | select_type | table   | type        | possible_keys                     | key                               | key_len | ref  | rows | Extra                                                       |
+----+-------------+---------+-------------+-----------------------------------+-----------------------------------+---------+------+------+-------------------------------------------------------------+
|  1 | SIMPLE      | product | index_merge | ix_weight_brand,ix_quantity_brand | ix_weight_brand,ix_quantity_brand | 8,8     | NULL |   31 | Using union(ix_weight_brand,ix_quantity_brand); Using where |
+----+-------------+---------+-------------+-----------------------------------+-----------------------------------+---------+------+------+-------------------------------------------------------------+

What is annoying here is that this query, using FORCE INDEX actually hits only 31 rows according to the statistics, whereas the one not using FORCE INDEX potentially hits 4291. Why the optimizer determines the latter to be faster I do not know, but it doesn't seem right to me.

In the example given here, I was using a brand_id of 6. That particular brand_id has less entries than the other ones, so lets giva a shot using brand_id 4, which takes a bit longer. The SELECT using a UNION then looks like this:
SELECT sql_no_cache id FROM product WHERE brand_id = 4 AND weight = 41 UNION SELECT id FROM product WHERE brand_id = 4 AND quantity = 78;
and the one using FORCE INDEX looks like this:
SELECT sql_no_cache id FROM product FORCE INDEX (ix_weight_brand,ix_quantity_brand) WHERE (brand_id = 4 AND weight = 41) OR (brand_id = 4 AND quantity = 78);
Both of these use the same access path: A merge sort using the indexes ix_weight_brand and ix_quantity_brand. Which one do I prefer then? My personal opinion (but it is just that: An opinion that is personal) is to use the UNION, based on four facts:

When running these two statements, side by side on the same data and using SQL_NO_CACHE (i.e. not using the query cache), the UNION is consistently faster. Not that the index_merge is much slower or anything, in particular not compared to when using the ix_brand index that is preferred by the optimizer, unless I tell it not to, but the UNION is still faster.
Sometimes I could see the optimizer still not doing it's job correctly, even with the FORCE INDEX in place. In some cases only one of the indexes I forced would be used. Don't ask me why, and I cannot reproduce it now, so maybe it was my eyeglassed having fun with me.
The UNION construct means that I can use ANSI SQL, the FORCE INDEX not so. This is important to me, as I want to keep my options open when it comes to databases. Which doesn't mean I always use ANSI SQL, but if I have the choice between ANSI and non-ANSI SQL for two statements that are otherwise similar, I choose ANSI SQL.
I have a feeling that in my case, the UNION will be more flexible. If more indexes and conditions are added, the FORCE INDEX part will be difficult to maintain, whereas in the UNION this will be easier. Which doesn't mean that I particularily enjoy using a specific SQL declarative construct to optimze performance, I would much rather want the optimizer to deal with this for me. But it doesn't.

In conclusion, yes, I was wrong, I admit it, MySQL sure can use two indexes and do an index merge. But I was right in the sense that this seems to happen rarely, and that the optimizer isn't really doing it's job properly here anyway. But I am glad there is some openings for fixing this, as an access methods exists and the optimizer knows about it.

/Karlsson
Who was wrong! I admit it!

First attempts with MySQL Installer

2011-07-19T21:36:00.003+02:00

This was a sad day for me. I once, when I was at MySQL, was a big fan of a better installer for MySQL on Windows. Something that would install all you wanted on just Windows, in a way applicable for Windows and integrating with the appropriate Windows products. So installing MySQL would not just install MySQL, it would also install the Visual Studio plugin for MySQL, for example, if you so requested (and the installer might even be so smart so it could check if VS was installed, and then ask you politely if you wanted the plugin). The same goes for ODBC drivers, .NET drivers and what have you not.

Fact is, I had this idea a long time ago and was promoting it inside MySQL with my usual frenzy when I think I have a good idea, although I wasn't the first to have this idea.

So, now, after all these years, and I have even left MySQL since, this Installer is soon ready for prime time, MySQL Installer is at it's last RC before GA! So I decided to download it and see what they have done with this smart idea. And boy was I disappointed.

To begin with, it is announced as "RC" but the download page calls it "Beta" still. Hmmm whatever. Also, it is 32-bit only, there is no 64-bit download. But thinking about it, I realize this might be for the installer itself (32-bit that is), it might well install 64-bit software. And it does, thank you, but this should be made more clear, and why we even bother with 32-bit builds anymore i beyond me? Why not go for 16-bit while we are at it?

And without further ado, here is my verdict: It would be quite OK, assuming it was a Pre-alpha release! No, this is very far from RC quality! I am sorry MySQL and all developers who have put in lots of effort into this thingy, but is not good enough. far from it actually! Maybe it wasn't tested enough, maybe it wasn't tested at all? But frankly, from an RC product I don't expect "Unhandled exception" after just a few minutes or perfectly normal use.

One thing it does is that it downloads the suff on an as needed basis. In my case, it failed to find a download location for Connector/.NET (I am in the middle of nowhere, I know that, I mean Sweden, what kind of weird place is THAT? and Stockholm?). After that failure, nothing worked. retrying it to no avail. Not finding a download location for Connector/.NET (I have heard that .NET is pretty popular on Windows, but what do I know) and this stopping the whole installation process with an Unhandled exception. No, that is not RC, that is what we in the rest of world call Pre-Alpha.

For your own good, and for the sake of MySQL Credibility on Windows (which I am a fan of, and I am sure we can get this installer going): Do not release MySQL INstaller in this shape!

Calling it quits for the day, thinking Good idea, bad execution and feeling a bit sad
/Karlsson
And if you ask: Yes, I did report a few bugs on MySQL Installer today

OR conditions considered bad... Or? And a workaround.

2011-07-19T17:01:00.007+02:00

Some things are known to be just bad. GOTOs used to be one such thing (something I still use them, but only where appropriate, which isn't that many places). Maybe it is just so, that some things are useful, but not for everything, so maybe the issue is that they are used inappropriately. Or?

The OR condition is one such things in MySQL circles! Oh, you have an OR condition! That is going to be so slow! sort of. And the reason an OR is "slow" is that as MySQL will use only one index for each statement, only one "side" or the or condition can use an index. Or sometimes even worse, MySQL will consider using an index that is common to the two "sides" or is outside the OR conditition, despite that fact that there are perfectly fine, highly selective indexes on both sides of the OR condition.

If you ask me, this is not a fault with the OR condition but rather a problem with the MySQL optimizer. Why in heavens name can't a single statement use two indexes, if that is what it takes? And let me let you in on a little secret: MySQL can use multiple indexes for one statement! But that depends on what you mean with a statement. And MySQL means something slightly different than many of us do!

Without further ado, lets have a look at an example. We work at a retail store, and a package from us has been stuck at the post office. We want to check what product this is, but we don't know the product id. What the guy who called us from the post-office said was something that looked like a brand name, that I can map to a brand ID, the number of units in the package and the weight. But to be honest, the last two weren't terribly reliable. OK, lets find the product in the product table, which looks like this:
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`brand_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
`weight` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `ix_brand` (`brand_id`),
KEY `ix_weight_brand` (`weight`,`brand_id`),
KEY `ix_quantity_brand` (`quantity`,`brand_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2321295 DEFAULT CHARSET=utf8

I know for certain that brand_id is 6, I already looked that up. But there are millions of products in the product table! Luckily, looking for approriate products using brand_id and either quantity or weight should be easy, right? We know now that the weight is 41 and quantity is 78. And we have approriate indexes, this should not be a big deal, right:
SELECT id FROM product WHERE brand_id = 6 AND (weight = 41 OR quantity = 78)

Well, although this works, it is a big sluggish, real slow actually. Lets look at what mySQL does with this statement:
EXPLAIN SELECT id FROM product WHERE brand_id = 6 AND (weight = 41 OR quantity = 78)
And what we get is this:

+----+-------------+---------+------+--------------------------------------------+----------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys                              | key      | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+--------------------------------------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | product | ref  | ix_brand,ix_weight_brand,ix_quantity_brand | ix_brand | 4       | const | 4291 | Using where |
+----+-------------+---------+------+--------------------------------------------+----------+---------+-------+------+-------------+

That wasn't so good. Let's try a different way:
EXPLAIN SELECT id FROM product WHERE (brand_id = 6 AND weight = 41) or (brand_id = 6 AND quantity = 78);

And that will result in the same query path. Only one index can be used, and there is one index that fits with both paths, that on brand_id, so MySQL picks that. Using FORCE_INDEX will work, but still only 1 index will be used, and the result may well be even worse, as a FORCE_INDEX on, say the ix_weight_brand index, will make the other path, on quantity, dead slow! What you would like MySQL to do, which doesn't seem so complicated, is to realize that there are two distinct paths here which can be looked up using an index real easy, execute them both and merge the results. But no, MySQL will not DO that! Only 1 index per statement, that's it. Or?

Well, when you understand that MySQL will only use one index per statement, consider what MySQL means with statement here. For a SELECT it is the individual SELECT statement that is the statement, which sounds reasonable until you consider a UNION! Each and every statement in a UNION is considered a separate statement (in this particular case that is, but it is a but messy, UNIONs in MySQL are a bit of a kludge, really)! So if we rewrite the statement above as a UNION, which is easily done for many queries involving OR-conditions, you get something like this:
SELECT id FROM product
WHERE brand_id = 6 AND weight = 41
UNION
SELECT id FROM product
WHERE brand_id = 6 AND quantity = 78;

What are we saying here? We are telling MySQL that these are actually two separate paths, which is what we did with the OR condition, but in this case, MySQL can use two indexes, and will nicely merge the results, so an explain looks like this:

+------+--------------+------------+------+----------------------------+-------------------+---------+-------------+------+-------------+
| id   | select_type  | table      | type | possible_keys              | key               | key_len | ref         | rows | Extra       |
+------+--------------+------------+------+----------------------------+-------------------+---------+-------------+------+-------------+
|  1   | PRIMARY      | product    | ref  | ix_brand,ix_weight_brand   | ix_weight_brand   | 8       | const,const |   31 | Using index |
|  2   | UNION        | product    | ref  | ix_brand,ix_quantity_brand | ix_quantity_brand | 8       | const,const |    1 | Using index |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL                       | NULL              | NULL    | NULL        | NULL |             |
+------+--------------+------------+------+----------------------------+-------------------+---------+-------------+------+-------------+

This latter query is often so much faster than the alternatives, and we have tricked MySQL into using two indexes and merge the result. But for some reason, MySQL is unable to figure this one out for itself. Is this an important tip I am giving you here? Is this a neat optimization trick that I am handing out? Short-term, the answer is yes.

But am I with this saying that you should stay clear of OR-conditions? Absolutely not, no way. What I am presenting here is an awkward way of circumventing some obvious flaws with the MySQL optimizer, and this should be fixed! But what I AM saying is this: If you currently have big performance problems with MySQL SELECTs involving OR conditions, you might consider rewriting those statements to UNIONs, sometimes that hels. But do not do this will ALL your OR-conditions, only where you have to and it makes sense. Let's meanwhile wait for the MySQL developers to fix this. (No, I'm not good enough at the optimizer code or most other parts of the MySQL kernel to fix this myself. I'm happy to build things around MySQL, but I do not have the time to get more involved with the kernel).

And before I keave you for now: This was tested with MySQL 5.5.7 on Linux. I have NOT checked for fixes, updates to this, but I do hope it has NOT been fixed? Why? Why do I now want it fixed?? Have I gone bonkers? Yes, I am bonkers, but that's not the issue here, the issue is that such rather involved fixes to the optimizer is NOT something I want introduced in the middle of a GA release! But I'd be really glad to have it fixed in 5.6 or whatever that release is to be called! And yes, I am ware this is not exactly with the optimizer itself, but more so with the query execution, but for now, I have decided to call it the optimimizer anyway, as the sun is shining and the weather is nice and all that, sometime around christmas I might consider changing my mind.

Cheers for now
/Karlsson

MyCleaner 1.3 released

2011-07-18T12:45:00.002+02:00

Do you need to clean up your MySQL data? Maybe run regular DELETE statements that deleted rows that are no longer used or referenced? Or update rows with valid data? And possibly you want to do this is batches, so as not to interupt day-to-day operatioons? Then maybe MyCleaner is for you! This is a very configurable tool that runs a main thread that gets identifiers of data to clean, and then runs several cleaner threads to clean this up.
What statements to run, and how many, if you need to run some statement before the other statements, all is configurable. I have now released version 1.3 which is available for download on Sourceforge. As usual, full documentation is also available in PDF format.
And before you ask, no, this is not a windows tool. This is a straigtformward *x tool, licnced under the GPL and is built using the GNU autobuild tools.

Happy cleanup
/Karlssono

New hardware blog in Swedish!

2011-06-21T16:43:00.003+02:00

I realized that this blog sometimes got too focused on my hardware experiemnets, and with less MySQL content. This will not stop completely, but it will be complemented with a new blog, in Swedish I'm afraid, focused on hardware and on my computer setup at home. I looking forward to writing about these things, and if you read swedish, then you might want to pop by and have a look, the blog is called Kaos hos Karlsson which mean "Chaos at Karlssons house" or something like that, and it's online at http://www.kaoshoskarlsson.se.

/Karlsson
And don't worry, I will not stop blogging here, far from it!

This stuff rocks, It really does! And Open Source is a big part of why it does so!

2011-06-19T18:37:00.002+02:00

Karlsson goes to Chaos Manor

Sometimes you find a product that just amazes you. If you follow me on facebook you may already know that I am talking about the Synology DJ211j NAS unit. This is a feature-packed 2-disk NAS unit that really rocks, but let me tell you how I got started on this.

I have a bunch of machines here at the Karlsson mansion, and there are three main boxes:

A laptop workhorse
A Linux desktop workhorse
A Windows desktop workhorse

By far, the highest spec of all these is the Windows desktop, as this is used for image processing and managing my papablues website, among other similar things. It is a Windows box as the tools I use for Image procesing happens to be Windows based (I am a long-time user of Paint Shop Pro, complemented these days by Adobe Lightroom). This Windows box has been gradually updates over the years, but the last update I did was no good: I put in an Asus 1156 Mobo (P7P55D), an Intel i7 CPU and 8Gb of RAM, which turned this box real powerful for desktop use, but also real unstable. I did test memory, and found no problems. In this box, I retain an old RAID setup, using an Adaptec 1420SA Raid controller running on 2 sets of RAID1 (mirror) setup, one using 500 Gb disks and one using Tb disks, making up a total of 1.5 Tb. I really suspected this RAID controller as being the cause of my stability issues, but to be honest, it hasn't been that bad, I have never ever lost any data sonce I started using it. But the box was really packed with disks, I had an SSD to boot from in addition to these 4 disks. The stability issues were always when starting the box though, once I had it running stable it could run for weeks and weeks. Strange. But every reboot caused a nightmare!

So an upgrade was necessary to fix the problems. The MoBo was on the suspect list, as well as athe Dsik RAID setup then. Fixing the MoBo was reasonably inexpensive, as I decided to postpone the RAID issue with doing this in another way: Replace the RAID setup with a single 2TB disk, and as I had decided on a new MoBo with two SATA III channels (the ASUS p7P55D-E Pro), I went with a WD SATA III 2 Tb disk in addition to the MoBo. Now, with this hooked up, I copied the data from the RAID disks to the new 2Tb internal disk, the system was still unstable, so maybe it really was the RAID controller that was the issue then. With all the data sucessfully on the 2Tb internal disk, I removed all the old RAID disks and the Adaptec controller, and the reasult (this is really scary, I know): The system was still unstable like hell! Yikes!

So, one more thing to try. I removed to of the memory sticks, to run with just 4Gb, to try it out. And rightly so, the system was now stable. Why I didn't try this before, is beyond me, and I should have done that, I know.

With all my important data on just one disk though, I had another issue to fix, but I had already decided how to do that: Get a NAS and RAID it. I already have a NAS actually, an old clunky D-Link 323, which works and seems to have good quality, but is lacking features and is a bit sluggish, and I also have some other data on that. So I wanted a new NAS. In addition to that, the Windows box was now running with 4Gb only, and it still had one original problem, the Antec 180 based box was way too noisy! I had installed a reasonable quiet Noctua CPU cooler, but that was not by far quiet enough. The noise problem was fixed by installing 2 new Noctus NF-S12B OLF fans, where one is used for the CPU cooler. The two remaing fans out of 4 in this box was attached through a front-panel fan controller. This is really enough cooling as long as you do not overclock (something I do not do), and it turned this machine from an In-House jet engine into something that is just barely noticeable: Lesson: Get some good fans, they are worth it, and not even the expensive ones are THAT expensive!

I also had some more memory in the box, and memory is cheap these days, so with two new 4Gb each memory sticks, the box is now running stable.

One more thing to solve then: the new SAN. Reviews has been good for the Synology DJ211j box, so I was prett set on that. The alternative was the smaller DS111, which has just 1 internal disk, but compensates for this with an E-SATA port and slightly better performance than the 211.But as I was after RAIDing the setup in the box, using the E-SATA for the mirror disk seemed like a bad idea, so I went with the 211.

And what a marvelous piece of kit that was! The hardware build quality was good, although the disk mounting and the process for that was not as smooth as with the D-Link 323. But when you know what you are doing, this was easy enough. Getting started with the DS211j was again a bit more difficult than the 323, as there was some software installation and stuff needed, whereas the 32 was just a matter of installing the disks and plugging in the net and pwoer cables. But there the lus points for the 323 ends, and there was really nothing difficult with the DS211j either, asumiing you know a bit of what you are doing (and if you don't, maybe a NAS is not for you anyway). With the software installed in the DS211, I started the process of formatting the RAID set, and already at this stage I saw something interesting, the DSM (Synologys Disk Stoarge manager software) was way cool, it looks much like a clssic windowed GUI, like Mac, Windows or a desktop Linux distro, but running in a web-broswer window! Real neat stuff!

This "desktop in a browser" thing is WAY powerful. All the usual windowing function, including multitaking, resizeable windows, icons and stuff like that is there. Managing the DS211J is hence a breeze, despite this box having a truckload of features compared to my old 323. Among the features are the ability to use the 211 with network based survivalence cameras, use it to stream music, using Squeezebox, iTunes and DLNA or a load of other protocols.

Start the Audio-station software in the "Browser GUI", and you find that this puppy also supports internet radio, that then gets streamed to the PC that you are browsing from. I was real amazed at this: The browser based GUI knows that the PC that I run the Browser on can play sound is streams audio to it, just like that. There nothing to setup of configure to manage this, nothing to download, no drivers to install in either the PC or the NAS, it just was there.

Copying data from the front mounted USB port to the NAS disk is possible by just plgging in a USB disk device on the front panel and then just clicking a button. It also has two USB ports on the back. I have tried using the #"# and other network based things for desktop printer sharing, with little sucess. This time, as things looked so good, I decided to try this on the 211j. I plugged in the printer, enabled printer sharing, and that was it, it worked out of the box.

Now, assuming you do not use the sharing facility in the box, can you upload files to it? Sure you can, ftp and many other protocols are available. But the Browser based GUI comes with a neat trick up it's sleeve, which I didn't expect. When I open the file browser, I get a classic explorer style window with the file tree on the NAS, but not only that, I also get the file tree on the machine that I am running the GUI on, and I can copy files between the NAS and my PC by simple drag-and-drop. Real neat!

These are just some of the great features of this NAS. And is there a MySQL connection here? Sure there is, of course! The Open Source based toolkit that makes up the Synology DSM also includes a web-server, PHP and MySQL, out of the box. Setting up a LAMP stack web-server has neever been easier.

To wrap this up, in conclusion: If you want a real pwerful NAS, which isn't that expensive either, and that you can have some fun with, and not just use as a NAS, but also do loads of other useful stuff with, then the Synology DS211j is the machine you want. And it also provide realy good performance. And no, I do NOT work for Synology and I am in no way affiliated with them.

/Karlsson

Database cleaning made easy: MyCleaner 1.2 released

2011-06-13T18:54:00.004+02:00

I have released MyCleaner 1.2 today and it is avaiable for download from sourceforge, as is the documentation pdf as usual. This version includes a whole bunch of new features, to allow different kinds of database cleaning operations than before. In this case, using this program is not terribly unique, it just a multi-threaded SQL runner. The noce thing is to allow you to use the same program for all your database cleaning needs.

In addition to this, this version has better statistics printout, better control og logging and a whole bunch of minor features.

Cleaning has never been easier or more fun than now! Also, when not used, MyCleaner will conveniently fold and fit under your bed :-)

/Karlsson

Cleaning up a MySQL database: mycleaner 1.1 released

2011-06-08T13:18:00.002+02:00

The MyCleaner program, that is used to clean up data in the MySQL database that is no longer used, no longer referenced or is just unnecessary, is now available for download from SourceForge.net in version 1.1. This new version adds a few things, like the ability to sleep between rounds, more extensive log print control, and above all, the ability to run a non-SELECT to get IDs.
The latter feature may seem weird, but in some cases it is useful, as it will allow you to select IDs into a temporary table, for example, and then use these for deleting the real data, something which can be achieved in many other ways of course, but which is still useful in some cases.

In addition, the ability to run SQL after the program having finished all other processing, has been added, to allow cleanup of temporary objects.

/Karlsson

Automatically restarting MySQL slaves the easy way.

2011-06-02T20:01:00.004+02:00

MySQL Replication is something that is used by many, many MySQL users, and here at Recorded Future we are no exception. In our case, the slaves are used for different purposes, and as we develop our system so much and so fast, so sometimes things happen which could have been avoided. Like something getting executed in the master that really should not end up in a slave at all, and which would cause all sorts of problems on the slave.

Also, some things that work fine on the master can sometimes cause things to break on the slave, a typical such issue is a big operation on the master that when executed on the slave would cause a lock timeout. And you could argue that the lock timeout should be increased, but the question is how much, and frankly, do you really want those locks hanging around? And in some cases, it's the replication thread that has a lock timeout.

I can easily identify three different actions to take based on the errors that the MySQL slave had, and which cased the slave to stop processing, and which I have so far processed manually:

Errors which really is no issue at all to restart the slave on. One such issue is lock timeouts, but there are more.
Then there are errors which you just know you can skip a record in the binlog on. In our case, error number 1317 is one such error. This is an error when a large operation has been interupted on the master, for example a large INSERT INTO .. SELECT statement. In our case, this only happens rarely when we use large temp tables, and more or less always we really should not have the temp table on the slave at all. What this means is that, in our case, these errors can be disregarded, we can stop this binlog record and go on to restart the slave. I.e. we can do a SET SQL_SLAVE_SKIP_COUNTER=1; and then a SLAVE START; just like that.
And then there are errors which needs manual intervention always, we just should not attempt a restart.

This seems simple enough, right? So it should be possible to have a program that runs in the background, checks the status of the slave a regular intervals, and if it's an error we can restart, or possibly skip 1 and restart, on, do just that. Look no further then, I created a small shellscript for just that, and I have tried it a bit with our systems and it seems to work OK.

To be honest, this is as far from rocket science as you can get I think, but the script works well so here it is.

It's a bash shell-script and I run it on Ubuntu, but I don't use many fancy bash features, so it should be easy enough to understand. There is a --help option, and you can configure which errors not to attempt a restart on, which errors to skip one binlog record and restart on (any errors beside these, a restart is attempted on) and a sleep-time to set the time between checking the slave status (default is 10 minutes).

As i said, nuthin' fancy, but simple and workable. And before I finish this up, no i didn't check if someone has already published a script for this, I assume someone has, but here is my attempt at the same thing then.

Cheers
/Karlsson

And on a completely different note.. Potatoes..

2011-05-27T10:56:00.003+02:00

At MySQL I think I once instituted something which became sort of a tradition. If you are not a current or former MySQLer, the rest of this post may not be too interesting, and possibly it's not that interestying in the general sense either. But as I no longer work for MySQL, and many other MySQLers who remember this are also former MySQLers today, I use this medium, my blog, for this years review of the #1 swedish delicatessen, Swedish New Potatoes. And yes, I am aware that this has little to do with the MySQL product, although it does have something to do with the culture of the old MySQL (i.e. you could email company-wide about potatoes and still keep your job).

So, what about this years new potatoes? They are kinda early this year, it's not even June yet, and I had my first last night. Newly, and lightly, boiled, eaten with some butter and a pinch of salt. The verdict? In potato-technical terms, the term is Yummy! This exquisite little vegetable really made my day yeasterday!

So, yes, Potatotes are still good, even at Recorded Future. And by the way, I hope to keep my job here, despite the potatoes...

/Karlsson

Annoyances, annoyances. Or Yet another HBF (Half Baked Feature)

2011-05-26T11:43:00.003+02:00

About any product, be it computer hardware, software or any other product, has features that are annoying to some of us. But few products has so many features that are annoying to just about everyone as computer software. And among computer software, database software in particular seems to to have these features, which some people seems to like, and some just find annoying. And then there are features, or lack of them or implementation specific details that seems to annoy just about everyone. Things that work in a partuicular way because someone, somewhere, in some distant universe, had the notion that this was a good thing. Often features relating back to ancient times. And sometimes features that you just know work in a weirdo way because the person, if it was a person, figuring out the feature of the implementation of it, really must have been smoking something that is illegal in many parts of the world. Which is not to say that these features sometimes have a use or that the lack of a feature doesn't have a reason (like "It seemed like a good idea at the time").

And for some reason, MySQL seems to be hit with these things more than most software and database software products. And although I like working with MySQL, having done so for many years by now, there are certain aspects of it that just annoys the h*ll out of me. Things such as:

We have feature A, yes. And we we also support feature B. Great. But you cannot use feature A and B at the same time.
Oh, yeah, we support feature C, but really it is so slow that we recommend against using it.
Sure we support feature D, but the implementation really sucks.
Oh, storage engine E is sure supported, but the quality is so bad that we have disabled it completely.
On platform F, we don't really support feature G, as there is a limitation H on that platform. A limitation that was removed from that platform sometime during the Nixon administration, but all the same.
Feature I has been replaced by feature J, so using I will not be supported in the near future, but J still lacks the full functionality of I.
Oh, yes, you can sure do that! You can use either feature K or feature L, which have overlapping functionality and none is really feature complete in and of itself.
Our development team is hard at work at A) NOT getting rid of these annoyances and B) adding more of them.

So now I have another annoyance (I guess you could see that coming). Again, a new cool useful feature bogged down by someone, somewhere, in some universe wasn't thinking. InnoDB (a great Storage Engine BTW) has from version 5,5 of MySQL (and for longer than that in the plugin) been able to drop an index without rebuilding the whole table. Or rather, this is not a cool new feature unless you look at nothing else than MySQL, this feature was way WAAAY overdue. InnoDB also supports another neat feature, which is FOREIGN KEYS. You may say that FKs are no good for performance, and maybe that is so, but they are useful for, at a declarative level, ensure data consistency and they also add a level of "documentation". The implementation of FKs assumes that you have an index on the referencing column (why I do not know. There are cases when you don't want this.), but the rest is just in the declaration of the table and in the checks for FK consistency when issuing DML on the FK referencing and referenced tables or cource. So, if we now assume that in terms of data base data related to the table in question, the only thing that is added when we add an FK is an index, a fully normal index, then you should be able to drop the FK without rebuilding the table, as we can do that with the index? Right?

Nope. No cigar. Dropping an index is nearly instantaneous and doesn't require rebuilding the table, dropping the FK does. (I just tried this in 5.6, just to make a point. Same thing there). But have I not forgotten something, should I not be able to issue a:
SET FOREIGN_KEY_CHECKS=0;
And then drop the FK and then the index? Nope, that actually seems to make things worse and seems to confuse MySQL a lot. If you do this, you may drop the index, without the FK, and in a sense things now works, as although the FK is there, it isn't checked! Even if you set FOREIGN_KEY_CHECKS back to 1! This really makes no sense at all to me!

Playing with FOREIGN_KEY_CHECKS and droping FKs and indexes in MySQL 5.6 seems to confuse the h*ll out of MySQL also. I might one day get down to create a reproducible case here.

Anyway, why can't you drop an FK without rewriting the table? When the index can be dropped without rewriting the table? And don't tell me not to use FKs, if a feature is implemented it really should work, else remove it. And if I am not to use FKs with MySQL (in almost any other RDBMS they work just fine), then why were they implemented? No, FKs are useful, not always and sometimes they are too limiting for performance, but in many cases, they are all OK and really should work.

As Thomas Watson of IBM fame put it: THINK!

/Karlsson

MySQL Database cleaner 1.0 released

2011-05-25T13:48:00.003+02:00

If you ever, and I think many of use DBAs have, been in the situation where you are stuck with data in the database that isn't used and isn't accessed, data which may consist of rows that are no longer used, data rows that aren't references, because you don't use FOREIGN KEYs or they weren't applicable in this case. Or data that was once used, but no longer is.

And in many cases, this data is tucked in among your other good rows of data :-( One way of cleaning up the database in a case like this is to run standard DELETE statements, but there are a few issues with this:

You may be accessing a lot of data, so this may take a while.
You will be locking large amount of data for this.
The join statement to get the data that is no longer used and / or no longer referenced is complex.
There is no really good way to split this DELETE in smaller chunks, except using LIMIT, but if what takes a long time to execute if the complex join to find offending rows, this is not always useful.

What is needed is a cleaner. A program that can run a simple or complex SELECT statement to find identifiers of the data you want to delete, and as this is a SELECT, we will have less issues with long running transactions and database locking. And the the IDs gotten from this SELECT is used to DELETE the rows we want to delete, based on the PRIMARY KEY to make it fast and to minimize the time we lock things.
In addition, we would prefer the DELETE to run in multiple threads and for the program to be very configurable. Well, here is the fix for you: mycleaner. MyCleaner is a program that does just this. It is not very complex, but useful.

Before I point you to the download location, I have one thing that is on the TODO list but which is not yet fixed: MyCleaner only works with integer numeric keys (INT, SMALLINT, BIGINT etc). If you are find with this and have a need to clean your data, give MyCleaner a try, it 's GPL Open Source and is ready for download from sourceforge here.

Ideas for improvement, code contributes etc are welcome. And yes, there is also documentation, not the most comprehensive of documentation, but it is there.

/Karlsson

Slavereadahead 1.3 available

2011-05-09T18:06:00.003+02:00

Version 1.3 of Slave read-Ahead is available for download. If you don't know what this tool is about, it is a tool that runs in the background, reads the incoming replication log on a slave and converts INSERT, UPDATE and INSERT ... SELECT statements into SELECT statements and executes these before the statement in question is executed on the server, the idea being that this will "prewarm" the MySQL caches for this date, for example the rows that an UPDATE is affecting will already be in the cache when the UPDATE arrives on the slave. Because of the way replication data is read, this tool only works with MySQL5.5 and up.

Version 1.3 introduces the auto-reconnect feature. This will reconnect to the MySQL server if the connection fails. To be sure that we restart, all existing connectings are released before a reconnect attempt is made. This version also has some more log printouts, mainly operations per second.

In addition, Version 1.3 fixes a few minor bugs, and one semi-major one, which caued not all INSERT ... SELECT statements to be handled properly.

/Karlsson

EVENTs - The hidden MySQL 5.1+ feature

2011-04-29T11:42:00.003+02:00

MySQL 5.1 and up has EVENTs, but this features seems to be little used. I started using them some time ago, and once I was using them, I found them more and more useful. There are a few bugs with them, I reported this one for example, and this alone tells me that probably not too many use this feature.

I will post a few example soon of things I use EVENTs for, but right now, let me tell you a few basics:

One IMPORTANT reason I like them: In comparison to, say, crontab jobs, I run them inside the database itself, which I do not have to put MySQL passwords in files or scripts on the server. BIG advantage.
For anything but the most simple of actions, write a procedure and then call the procedure from the EVENT, instead of having everything in the event itself, this just makes things difficult to manage.
Error handling: the MySQL server error-log is your friend!
Using the INFORMATION_SCHEMA tables instead of SHOW commands, where possible, making writing events easier and in many cases, this is the only way to do things. Simple cursors on INFORMATION_SCHEMA tables is the way to go. And have a second look at the INFORMATION_SCHEMA tables, there has been a whole bunch added over time since 5.1.
The syntax is sometimes weird, but once you get used to it, it works.
Have a look at the bug mentioned above. It may well bite you during development (which is one reason I recommened using a procedure at first, and then one this works as expcted, create the EVENT that calls this).
For a DBA, events are cool and useful, and easier to use an crontab and also cross platform. As long as what you are doing can be done in a procedure, chances are that EVENTs are better than crotab jobs.
The event scheduler must be running for EVENTS to work, and this is done by setting the variable event_scheduler to 1. Note that this is a dynamic variable, so this is valid syntax:
SET GLOBAL event_scheduler=1;

/Karlsson
See you with some more event information soon

Oh no, what have I done! Or: My cloud evangelism got cloudy. Or: The dog ate my network..

2011-04-22T18:25:00.004+02:00

At the recent MySQL User Conference, I had a talk on how we at Recorded Future use Amazon EC2 to keep our servers humming (the slides for the talk are available here). And of cource, Amazon EC2 turned back on me (and us all at RF) about a week later. I will not go into details, but somehow, we still don't know exactly why ("The cleaning lady unplugged THE SERVER to plug in the vacuum-cleaner", "The dog ate my network"?).

The thing has been down for 24+ hours now, and there is no end in sight, as far as I can tell. As I said in my talk, we are considering a move to Amazon RDS instead of running our MySQL servers ourselves, and one of my first reactions to this trouble was that we really should have done that already. That was until I realized that the Amazon RDS service was affected as well. Which all goes to show: The more things you ut in one SPOF, then more things will fail when that SPOF fails. And we are not alone, Reddit, Quora and many more in the Amazon us-east1 Availability Zone are in a similar situation. I wonder how the other database HA Solutions for Amazon survived (xeround at all)? Did they do OK or not, if they did, then this would be a selling argument for them. And if I was Rackspace (which I am not), I would launch a compaign right now...

Today we have been trying to set up our services in another Availability Zone. Our EBS disks are no good, but the snapshots are, so we should have something up an running real soon.

Cheers
/Karlsson
Who will stay with EC2, but will look at managing more things myself and to prepare for a solid backup plan, without Amazon intervention (Amazons statement that the Availability Zones are isolated wasn't really true, it seems). And who will not translate HA into "I let Amazon handle that"...

Large Datasets on Amazon EC2 talk slides are available here...

2011-04-14T02:10:00.003+02:00

The slides from my talk on tuesday at the MySQL Users Conference 2011 on "Large Datasets on Amazon EC2" are available for download here.

I also want to thank everyone coming to my talk, as well as to the people arranging the conference and make it happen, and to all the sponsors. This conference is a blast, as usual!

/Karlsson

Getting the best of Clouds, Internet and BI

2011-04-11T03:27:00.006+02:00

The internet really is full of stuff, and we are starting to fill the Clouds with all sorts of software that moves from the serverrooms and in to the clouds. One aspect here that is often missing is what a Cloud environment and the Internet in itself can add to what we already know and have. Or in other words, running in the cloud is not only about lowering costs, and if you look at it by lowering costs by running things in the cloud the same way you used to run it on your private server, then chances are you might not gain anything, running in a cloud is also about having distinct advantages not easily available somewhere else.

Christopher Ahlberg here at Recorded Future just blogged about another way of looking at it: What do we have access to on the internet that is not readily available in the Server room, and the answer is: data. A lot of data. For someone in the field of BI, not using Internet data, and sticking to traditional querterly results as the base for analysis, is really missing the point.

Read Christophers blog here for the full Monty (!). This is an interesting perspective, and way you look at it, and as an amateur computer historial, this is particularily interesting as one of the few commercial uses for the worlds probably only useful hydralic computer was just this: Using analysis to project sales. (This is actually largely true. Feel freee to ask me more on this last subject at the upcoming MySQL UC. This is truly weird geeky history!).

/Karlsson

Hey, it's time for the MySQL User Conference again! Come see me!

2011-04-05T09:57:00.002+02:00

And I mean that, come see me, say hello, buy me a beer (extra points!). Or even more so, come see my session at 10:50 on tuesday morning April 12. I'll be speaking on how to manage large datasets in an Amazon EC2 environment, and this is largely based on my experiences at doing just that at my new job (or new, I've been doing it for more that 6 months now) as Database Architect at Recorded Future.

This will not be an incredibly technical presentation, in terms of showing actual code and things. Rather, I will look at some of the issues when running in an EC2 environment, and how we manage it here at Recorded Future. Also, I will present a bit of how our architecture works, which is more relevant that one may thinks, as we have Cloud based architectures on mind all the time, all our development, testing and productions servers run in the cloud.

Anyway, this is going to be a lot of fun, and if you have specific requests or questions before my talk, then drop me an email at anders@recordedfuture.com.

/Karlsson

Slave Readahead 1.2 available

2011-04-05T09:42:00.002+02:00

Version 1.2 of Slave Readahead is now available for download here. If you don't know what this little project is about, it is used to pre-warm the MySQL Cache for the Replication thread on MySQL slaves.

It is built to support MySQL 5.5 and up only, as it uses some new commands in MySQL (like the SHOW RELAYLOG EVENTS admin command). For more information regarding this little project, either read this blogpost or download the documentation for the project.

/Karlsson

MyQuery 3.4.2 available

2011-04-04T14:06:00.003+02:00

MyQuery, the ad-hoc query and scripting tool for MySQL, as always running on Windows, supporting servers on any platform, is now available in version 3.4.2. This version has a few minor bug-fixes in the "Break long lines" plugin that cleans up SQL text, and also a few additions to this plugin.
I have not heard of any issues with MyQuery itself since I released 3.4.1, but I will not take the 3.4 version into GA until I have fixed a few more things in the "Break long lines" plugin to turn it into a more full-features SQL beautifier with a lot more features than now.
Until then though, 3.4.2 is what you want, and the Compact Spaces option in the Break Long Lines plugin that is added with this release, should provide useful.

In addition to all this, if you want to meet up and discuss this tool, then I am at your service at the MySQL Usre Conference coming up next week.

Best regards
Anders Karlsson

SQLStats 1.3 released

2011-03-16T17:17:00.002+01:00

I have released SQLStats 1.3 now, and it is available for download from sourceforge. The changes are small:

Cleaned up the build script a bit.
Fixed a bug that caused statements to be counted twice.
Made statement comparison non case sensitive.
A small fix to the normalizer, that would miss a few statements in some cases.

SQLStats is a plugin for MySQL 5.5 and up that allows live monitoring of SQL statements, without using the general query log and with the added benefit of managing statements with the literals removed. read more in my previous blog-post in the subject.

Enjoy!
/Karlsson

Finally MySQL gets World-class support!

2011-03-15T14:59:00.002+01:00

And have we been waiting for THAT puppy! Yeah, right, all over the years MySQL, Sun and Oracle has been providing real crappy support (tm), but now, finally, it is world class. And if you believe this, then may I interest you in a famous New York bridge that I can provide at a real bargain price? Or?

Fact is, MySQL already has world-class support. If you don't trust me, then look at the MySQL website, hosted by no one else than... Oracle! Like this from Mårten Mikkos or this, quoting Rich Green and Jonathan Schwartz (if you remember him).
Both claiming that now, finally, with Sun, is MySQL customers getting "world class support". Strange thing is, the same guys who are now to give world-class support are largely the same guys who has been providing much less than "world class" support.

And make no mistake, dudes, I'm not pointing fingers at the Support guys at MySQL, not by far, quite the opposite, they need some support from us now, when what they have been doing all the time is suddenly turned "world class" just because they have to use an Oracle developer crap support system. And I'm not pointing fingers to Mårten, Rich or Jonathan either at least not much. They were apparently wrong (as the support didn't actually turn World class until now), but we all had great expectations at that time, expectations which didn't turn into reality, but at least spirits were high.

In conclusion, I am much less upset with what Oracle, Sun and MySQL representatives said and how that was said, after all, they, and are most of us, stuck with a way of expressing our selves. In particular, Oracle has the problem of showing that they can add value to MySQL, they just have to, but that is difficult in most cases, so you have to use some meaningless terminology. What I DO worry about is the MySQL tech support guys at Oracle.

Come Oracle, give these guys some cred, they need it and deserve it. And just using Oracle support systems and being employed by Oracle doesn't make support World-class, but the people who make it World-class, and those have been there all the time, so really, it's been world class all the time.

/Karlsson

Slave Read-ahead 1.1 available now

2011-03-13T13:00:00.002+01:00

If you read about slave-readahead in my previous post on accellerating MySQL Replication, and if you got to the state of actually trying it out, then you really should download the 1.1 version that I just made available. Version 1.0 really was a pre-Alpha, and it had some memory allocation issues, as well as a simple multi-threading issue from my side, which had a really annying side-effect, mainly making the progrem not work at all in many circumstances.

The new version, 1.1, is in much better shape, really. I have run it for a while now, and except for the fact that it seems to do a better job, it also doesn't crash or consume enormous amount of memory after a while.

/Karlsson

Where is the Exit.. Pleeease!

2011-03-11T11:57:00.002+01:00

It might well be me not being in a good mood today. I really should be in a good mood, spring is here, sort of, the sun is shining, and I've started to look forward to waking up the MG and taking it for it's first round for the year!

But no, something got in the way of that. My #1 culprit is Skype but there are more of them, many of them these kinds of chat, social networking style applications. Msn is another of these annoying puppies, mainly on Windows, this is a disease that has yet to spread in the world of Linux that is has on Windows. Come on now, Microsoft, you put some many rules and regulations on a piece of software that is to be certified to run on Windows, why can't you require them to also have an Exit function? Is that something that is difficult?

In Skype you just cannot exit the application. I do not understand why. I could possibly accept it if there wasn't a reason to ever restart or exit skype, but that is not the case. Running Skype on a laptop where you move it from one network connection to the other (i.e. take the computer from the office and home into the docking station), means that Skype gets confused. It runs, but is confused, usually showing that it is live and connected, when it is not. A restart will fix that. But that restart has to be done by opening the Task Manager. If you, for the same reason, change audio equipment used for Skype (like a USB based headset in the office, and a normal Audio headset at home), the same thing happens.

Please get the Exit menu option back, or rather get us an Exit that will really Exit the application. In Skype, "Exit" means "Minimize the window". What the heck is THAT? I do not want more applications like this, I want ALL applications that are started manually by the user to also be able to shut down with a menu option. I don't mind using Task Manager, but I don't understand why I should have to. Is this a difficult thing to implement, an Exit function? Really?

/Karlsson
Exiting for now....

Want to accellerate the MySQL Slave? Here is something to try!

2011-03-10T15:16:00.004+01:00

I admit this wasn't my idea. Nor do I claim it will solve all problems. Or that it will always speed things up. But in principle, this should work in many cases.

The deal is this: For certain kind of Slave operations, UPDATE, DELETE and INSERT ... SELECT, the operation is both a read operation, as well as a write. To be frank (but I am not, I'm Anders), this is the case with just about any MySQL operation, but for the ones I list, there is a distinct set of rows, defined by the statement itself, that will have to be read.

The above is something we can take advantage on. As there is just one thread applying the changes from the master in the slave, as the slave operations are serialized, this thread is easily the bottleneck. But there is one aspect that doesn't necessarily need to be serialized, which is the read of the data to be modified. We can, to put it simple, pre-warm the MySQL cache with the data that is used by the statements above.

What is needed to make this work is to read from the MySQL relay log, ahead of the SQL Slave Replication thread, and convert the UPDATE, INSERT or DELETE statement to a SELECT, and then execute that SELECT statements. As I said, I didn't invent this, but I wanted to try it out. Hence you can now download slavereadahead 1.0 from Sourceforge. This is a really simple piece of software that just what I describe above. But before you get started, let me tell you some caveats:

I am NOT reading directly from the MySQL relay log files, rather, I use the output from the SHOW RELAYLOG EVENTS command, which means you have to be running MySQL 5.5 or up.
Because of the above, not all statements will be available to cache, as row-based events are not shown properly in this case.
Also, it might be the case, but I have not tested this, that the overhead of doing this through the server cause quite a bit of overhead. I do not know.

Now, I have not tested the effectiveness of the program much. I know it builds and works on Ubuntu 9.x on 64-bit Intel platforms, but that is about it for hard facts. What I have done to compensate for this is to make the program configurable in many ways. The SELECT statements are for example executed by separate threads, you can configure how many you want. If you run out of these threads, the SELECT will be executed by the "main" thread, which means that you can run with no specific SQL threads at all, if you wish. Also, how much ahead I read and how much I read at the time is also configurable.

The algorithm I use for which statements to pick up and execute is pretty simple, and the conversion from INSERT, UPDATE and DELETE into SELECT is also not very fancy. For us here at Recorded Future, it seems to work though. Any errors in the conversion to / execution of SQL statements are silently ignored.

I'd be really glad if someone would try out the usefulness or not of this program. It comes with the standard GNU autobuild setup, i.e. configure / make, and should be easy to get going with. There is also a basic documentation PDF included.

So, download from Sourceforge and try it out!

Cheers
/Karlsson

What? An iPad2? Me?

2011-03-07T21:05:00.002+01:00

I got my iPad about a year ago, long before they were available here in Sweden (I got mine in the US). The iPad is a brilliant piece of engineering, it's really beautiful, and the software pretty much matches it. So now, going back to the US for the MySQL UC again, will I get an iPad2? Kaj apparently IS getting an iPad2! Well, I'm not!

Why, you ask, unless you read my views on Apple and their take om media, in which case you know why. That Apple is cencoring smut isb't something that bothers me that much, really. It bothers me a lot more that they are cencoring perferclt legit media that is not to their liking (i.e. articles that doesn't suckup to Steve Jobs, articles that claim that Anroid rocks etc). Also, it bothers me that cetain technologies are not to their liking, such as Adobe Flash (now, I'm not discussing the merits or non-merits of Flash here, I'm nt debating it it really is Steves task to determine that).

And you might say that I should get over it, and accept that fact that Apple really can do it because it's their technology. Well, tell you what, the iPad I bought is mine! And it cae with the promoise of Apps and content. And noone was saying that certain content should be censored on it. Or rather again, censorship has a certain place, for content that is harmful to someone or to society as a whole, in many cases. What I am questioning is if it is Apples taks to do this censoring? I think not.

Democrazy and free speach is important to me, and however much I admore Apple hardware, software and technology as a whole, I don't really want them to touch my content. In particular, I don't want my daily morning newspaper (Dagens Nyheter here in Sweden) to provide me with a paid-for app where they don't even say WHAT has been censored, if anything.

So, sorry Apple, but Android really rocks. 3G Wifi router built in, Flash support and much more, at a lower price...

/Karlsson

How to explain things in the world of IT

2011-03-05T11:52:00.004+01:00

You see them all the time. The words and the expressions that are used all over the place, and which usually means something completely different from what that particular word means in real life (which is any life outside IT. Which in turn means any life at all). And it's not just Sales and Marketing folks that use these words, you see them used by any kind of IT professional, in particular when something just s*cks or plainly just doesn't work at all and the IT Professionals Dictionary is picked up and out comes a string of meaning less words, all with a fuzzy interpretation even when used correctly, strung together and mixed up with some technology term and a few acronyms.

Another thing here is that all terms used are fuzzy and non-committal, and typically passive terms are used. When something works, it is We created this exceptional groundbreaking SSD based RDBMS with world-leading performance! And when that groundbreaking technology fails, it's no longer "We created", rather it's "The system" or "The Technology" or some even more passive fuzzy terms is used, say The SSD part number X-561 671 has, due to conditions not under our control (Osama Bin Laden for example. But anyone but us. Despite the fact that when the product was released 2 months ago it was WE who had created just about every part of the product. But as the product actually s*cked, all responsibility is not with anyone else but us) show suboptimal performance under certain situations. Also the data quality has in some extreme tests shown signs of deteriorating. In short: The thing s*cks, data is corrupted, the product is ours and ours alone, but we are NOT responsible. Sort of.

Now, you can in an announcement usually find words that are meaningless, and at the same time positive, that are used in these situations. Words that make something look good, but not good in any specific kind of way, and above all are completely non-binding. Words and terms such as ground breaking, world class, excellent, seamless, high performance, user experience etc.

To these words you add a spice of acronyms of choice. These are a bit more difficult, and they have the role of making something look "modern" or "high end" or something like that, it means that these acronyms are more tied to the current times and to what is considered "HOT" when the something is written. And this changes with time of course. Todays acronym is SSD I think, at least when it comes to IT products. Every single product announcement has to mention SSD at least 10 times it seems, from razors to servers, from car radios to laptops, all benefit from SSD technology. Just HOW they benefit we don't know, but they do. It is these acronyms that makes an announcement look dated after a year or so, but in an announcement that is a few years old, say a 1980's software announcement where the writer wets his pants over SNA, you can do a search-and-replace for SNA-to-SSD and a new, modern, announcement is made.

You may think that a changing a software technology acronym like SNA for something that is a hardware one, such as SSD, isn't going to work, but that is not true. It will work just fine. The reason is that whatever is being announced, it is neither, hardware, nor software or anything substantial like that. All you know about it is that it is something that is Ground breaking and provides great Value for money! That the bl**dy thing doesn't even work, and was never intended to work, unless the provider had figured out a way to work around Einsteins Theory of Relativity (and make no mistake, this is something a marketeer or a Sales Engineer does on a daily basis) but now we are talking reality. I.e. the product is great and works perfectly, it's just that when applied in the real world it somehow fails. But real world is typically one of those things that are beyond our control. So again, besides the minor point that it doesn't at all and never will work in reality, it works just fine, What's you problem mr. Customer?

Take one particularly disgusting terms that has survived the world of IT for ages and ages, and which is still used (I had it replied to me the other day): Seamless integration. This is an interesting term in that it can be used both when something is announced, and when it is announced that it doesn't work. The reason for this aspect of this term, that makes it so useful, is that it introduces things beyond our control, i.e. when you are integrating, it typically means you are integrating with something, which is a good thing and a selling point.

On the other hand, when that integration fails miserably, which is always does, I have yet to see a product really achieving seamless integration, I guess one reason is that you really do not WANT integration (anyone using Windows and has seen the so called "ribbon" interface in MS Office knows what I am talking about. They introduce an interface which is distinctly worse and completely different from what already exists in Windows itself, which it really should integrate with. And both of these things which really SHOULD provide "seamless integration" doesn't, despite both coming from MS and despite the fact that they were once much BETTER integrated). So no, we don't integration, but that doesn't mean we can't use Seamless Integration to sell a product, as well as the major, highly important, reason to explain why it failed to deliver on the promises in certain specific customer situations. That last part can be interpreted by you newcomers to the world of IT as This huge pile of manure you call software really s*cks!

/Karlsson

SHOW or INFORMATION_SCHEMA tables? Can MySQL developers PLEASE make up their minds!

2011-02-26T11:20:00.002+01:00

Which one do you prefer?
SHOW TABLES
or
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE()
I know what I prefer these days! I have, I admit it, gotten used to the SHOW commands, and I find them useful in many cases. For example in the case above, if I don't have that many tables in the database and I only want to see what they are. a simple SHOW TABLES command does the trick nicely. And this is said by someone (myself) who really disliked the SHOW commands initially. And to be honest, I still dislike them in many cases, cases where the ability to filter, join, aggregate annd get an instance view (sic!) on things where only a proper SQL TABLE, such as the INFORMATION_SCHEMA.TABLES table, would do the trick. So all in all, they both have uses, the way I look at it is that SHOW commands are useful shortcuts for the INFORMATION_SCHEMA tables.

Regrettable, that is not how it works in real life. Rather, the INFORMATION_SCHEMA tables are largely based on what the SHOW commands return, which is a shame. Most it is a shame as many useful things with INFORMATION_SCHEMA tables, such as joins and aggregates, turns to being painfully slow even with a modest size instance.

And not only that. It was once the intention, IIRC, to make all SHOW commands available as INFORMATION_SCHEMA tables. But in recent MySQL versions, this is going in another, confusing, direction. MySQL 5.5 adds the SHOW RELAYLOG EVENTS command, for example. This returns data that would be really useful to do at least some filtering on, and possibly a join or two, but no, this is ONLY available as a SHOW command. You cannot open a cursor on a SHOW command either, which means that you cannot, in plain SQL, take data from it and store it in some other table, for example, so you cannot use MySQL, otherwise really useful, EVENTS here.

On the other hand, MySQL 5.5 introduces the PARAMETERS INFORMATION_SCHEMA table. This has no corresponding SHOW command? So if uou are to interactively call a procedure and want to look at the parameters, then you are back at the INFORMATION_SCHEMA tables again. Com one now, MySQL developers, make up your minds where you want stuff to be. And if you ask me, what I would want. No, correct that, require, at least in the long run:

All useful shortcuts to metadata available as SHOW commands, including some kind of filtering (the SHOW RELAYLOG EVENTS command lacks even the simple filtering along the lines of the SHOW TABLES and SHOW STATUScommands).
All available metadata available in INFORMATION_SCHEMA tables. Yes, that is ALL metadata.
And for the INFORMATION_SCHEMA tables, I don't really want a performance improvement, as currently there is no performance at all to improve. Can we please get SOME kind of performance on these tables, please.

In addition, a metadata repository would be nice. And if you ask me, I'd prefer to have that in a central repositiry, such as INFORMATION_SCHEMA. The whole idea of .frm files is hopelessly outdated I'm afraid. And yes, I understand the implications in terms of portability across versions and stuff, but you are smart guys, so I am sure you can fix that.

Cheerio
/Karlsson

VIEWS on INFORMATION_SCHEMA tables - Useful stuff

2011-02-15T15:44:00.003+01:00

Views in MySQL really has quite a bad reputation, for bad performance mainly, but also there were some stability issues at some point. Now they are pretty stable, but I don't see them used that much. One place where I like to use them myself is in combination with INFORMATION_SCHEMA tables. The I_S tables are really useful and contain a lot of information, and by using VIEWs we can massage the data a bit.

I often have a database specifcally for DBA needs, so that is what we will use here.
CREATE DATABASE IF NOT EXISTS dba;
USE dba;

OK, now we have a database to play with. Lets solve a minor problem first. Being able to use the TABLES table in INFORMATION_SCHEMA is great, as it allows standard SQL filtering and processing, in difference to the output from SHOW TABLES (largely at least, some filtering is available in the SHOW commands also of course). But the TABLES table contains the tables in ALL databases in the MySQL instance, not just the current database. Also, although the INFORMATION_SCHEMA tables are neat, typing INFORMATION_SCHEMA to access these tables all the time is tiresome. This is something that is easily fixed with a view. In the dba database, create a VIEW like this:
CREATE OR REPLACE VIEW dba.tables AS
SELECT * FROM information_schema.tables
WHERE table_schema = database();

Note that this is "dynamic", i.e. the database() function is evaluated when a query runs against the view, not when the view is created. So if we change to the test database and issues the command:
USE test;
SELECT * FROM dba.tables;

You will get the tables in the test database. Pretty neat and useful. Another issue with the I_S tables is that there is a whole lot of data in them, the data is often at a low level and sometimes they are not organized in a way to make using them easy. Case in point is the GLOBAL_STATUS table. This has a LOT of values, 300+ in the version of MySQL I use, and often you are only interested in some values, which is solved by filtering. But some values are missing, like cache hit ratios. Which is not to say that cache hit ratios cannot be computed from values in the GLOBAL_STATUS table. But we can make things easier with a view:
CREATE OR REPLACE VIEW dba.global_status(
variable_name, variable_value)
AS SELECT gs1.variable_name, gs1.variable_value
FROM information_schema.global_status AS gs1
UNION SELECT 'INNODB_CACHE_HIT_RATIO', ROUND((1 - (gs1.variable_value
/ gs2.variable_value)) * 100, 0)
FROM information_schema.global_status AS gs1
STRAIGHT_JOIN information_schema.global_status AS gs2
WHERE gs1.variable_name = 'INNODB_BUFFER_POOL_READS'
AND gs2.variable_name = 'INNODB_BUFFER_POOL_READ_REQUESTS'
UNION SELECT 'MYISAM_CACHE_HIT_RATIO', ROUND((1 - (gs1.variable_value
/ gs2.variable_value)) * 100, 0)
FROM information_schema.global_status AS gs1
STRAIGHT_JOIN information_schema.global_status AS gs2
WHERE gs1.variable_name = 'KEY_READS'
AND gs2.variable_name = 'KEY_READ_REQUESTS'
UNION SELECT 'QCACHE_CACHE_HIT_RATIO', ROUND((gs1.variable_value
/ gs2.variable_value) * 100, 0)
FROM information_schema.global_status AS gs1
STRAIGHT_JOIN information_schema.global_status AS gs2
WHERE gs1.variable_name = 'QCACHE_HITS'
AND gs2.variable_name = 'COM_SELECT'
ORDER BY variable_name;

Now, we have cache hit ratio values added to the global status. All the existing status values are still there. Also, note an interesting MySQL extension to VIEWs here: You can add an ORDER BY to them!

/Karlsson

Yet another MySQL fork - Hurra! Not...

2011-02-14T17:16:00.002+01:00

Announcing the KarlssonSQL MySQL fork versioon 5.2.7 based on MariaDB with patches from some guy at the local bar that seemed pretty cool, and also including the Facebook patch version 1.8.3 and some neat InnoDB multi-threading patches that I bought from a guy on eBay. The performance is great when it doesn't crash, and when it crashes, it crashes real fast, so performance is good even then!

Jokes aside, how many forks do we need, really? And what are the differences, really? With really here I mean in terms of real-world usefulness for real users? Also, it seems that different forks are focusing of different areas, based on their expertize, but on the other hand I find noone collecting the bits and pieces into a common Fork. The official Oracle MySQL distribution really is what I will stick to for now.

Which is not to say that there is anything wrong with the forks per se, I mean if someone outside Oracle has some brilliant idea of increasing the performance of InnoDB, and implements that and makes it available to us all, that is of course a good thing. But that is just the actions of that or those guys. If we assume it will also be maintained by those guys, then we have an enormous overlap here, where everyone is maintaining their own fork of MySQL, and this can hardly be what we wanted from Open Source: Source available to all, and everyone has their own version of the source. Again, I stick to Oracle for now. Probably not the hottest of "forks", but stable and reasonably reliable, in particular if you stay a version or two behind the latest and greatest release.

The same goes for Storage Engines. This is another thing that is inherently a good thing, but if it ends up with just too many or, as it might look right now, to specialized storage engines, what is the use? It's like we are back in the 1950's where everyone was running custom software. The difference is that then you had you, as there was no other option, whereas the reason these days seems to be that you can. Which is not a good reason, really, except that it is fun.

Fun is not the same as Common Good though.

/Karlsson
Who is possibly a bit grumpy again today...

Which is "The company we love to hate" today??

2011-02-10T13:21:00.003+01:00

WARNING! Sarcasm, irony and a few damn lies coming up!

Yeah, we all feel that at times. The big evil company that just acts like a bulldozer in the marketplace, or in your front yard. Boring, evil, moneymaking and bureaucratic. For a long time, that company was IBM. In those days, which lasted up until the late 1980's or so, before IBM posted the 1991 results, and we all laughed. Problem was then, we meeded some other company to hate with affection. And along came Microsoft, Bill Gates fit the Bill (!) perfectly here, a geek, a nerd and insanely rich. What an obvious target THAT was! And Bill wanted Windows on every desktop, hey that is just plain evil!

Today though, we have a problem. The thing is that to love to hate someone, there must, for some reason, be some kind of affection (admittedly, there was little to love with IBM also, except the alternatives were even worse). And although the stereotypical rich nerd that Gates is was something you could both hate and feel affection for, it is hard to feel affection for "Big Bull" Ballmer! Also, we are probably getting tired of the love-and-hate relationship with Microsoft by now, after close to 20 years with that.

So, which is the next company to take the leadership is this contest? I see three alternatives:

Apple - This is obvious. A charming Steve Jobs, that takes on the world of mobile phones and devices, and at the same time is also a rich dude and a founding father of Apple. And again insist on censoring your morning newspaper when feed through your iPad. Apple has grown big enough to take the helm here, and Steve is Charming enough to create the suspicison that maybe, just maybe, he and Apple is evil.
Google - This is an interesting contender. They get head to head with the former company-we-love-to-hate leader, Microsoft, which is sure way to into the contest. What Google lacks in a public likeable spokesperson, it compensates for by being the ubiquitous search engine. We like Google, maybe we love it, but maybe there is something with the results they give me? Any what are you doing,, censoring stuff in China?
Oracle - You have to give Larry credit for not being the most charming person on the planet, really, at least not in the way he is portrayed in the press. And Oracle as a company has a lot of something that we who love to hate really love-to-hate, lawyers. And those lawyers aren't just getting after anything, they are after the users and the user communities. There sure is evil enough within Oracle to qualify for the company-we-love-to-hate.

Of these three, only one remains, and here is my reasoning: Oracle, well they are evil enough, but they are not really known that well outside the world of IT professionals, and also, there should be something at least seen from the outside likeable, something that of the surface looks good that you can get affected to, but can also suspect that it is part of a big evil plan (like Windows when we first started loving to hate Microsoft). And Oracle lacks that, it is, in a way, just pure evil (not that anyone working there is. And not that Oracle hasn't been good to me when I worked there either.). But by far the biggest problem with Oracle is not that it is evil, but that it lacks good, and that it is lacks a wide public image. Sorry Larry.

As for Google, they are in much better shape than Oracle. The mix of good and evil is much better. Censorship of content is one evil thing, whereas the free search engine and apps is good. The feeling that you actually get too much paid for content when you search is just that feeling of an uncertain conspiracy that could get Google the #1 spot here. The problem is just that Google is too good. Noone really thinks that Google really is bad, and when Google acts bad, everyone seems to assume that Google was forced to. So where Oracle may be too evil, in the public eye, Google is too good.

This leaves us with Apple. And Apple sure fits the Bill. Steve Jobs is a great public spokesperson for a company that is after the company-we-love-to-hate Oscar, being both charming, unstable at times and sometime aggressive and very sure of himself. Brilliant! And Apple is trying real hard to win this prize, censoring content and applications on AppStore Apps, not allowing publications that are not "Apple friendly" and not allowing emulators or portability libraries. Yes, these are exactly the kind of things a winner of the company-we-love-to-hate prize show have. And Steve Jobs is a great spokesperson.

Congratulations Steve Jobs and Apple. And to Larry and Steve Ballmer: welcome back next year!

/Karlsson
PS. This is not serious. This I wrote for fun. This not the opinions of my employer or anyone else, actually, it is hardly my own opinion. I do not really think companies are evil. But I DO believe that we need a company to hate at times. Also, I don't have an iPhone!

EC2 - The E is for Elastic

2011-02-09T17:30:00.004+01:00

So, you are thinking about Cloud Computing? Is it a fad, along the lines of SOA, OOP, NoSQL, ORDBMS or is it a new paradigm when it comes to infrastructure? (not that a fad is bad, it's just that a fad, in my mind, is something that is grossly overblown in proportion. OOP is a good thing, but tell you what, OO-talibans out there, despite what you may think, OOP will not create peace in the middle east (if it did, I'd embrace it right now)).

But all that aside, what is in the Cloud, really? And from a technical standpoint, it seems simple enough: Your servers running across a number of virtual machines, with virtual disks and what have you not, where you pay for resource use and you share the environment with a bunch of other users. And that really is not that complicated. And from a pure technical view, that is it, sort of, but there is more to it than that, because when you come to run your stuff in a cloud, you realize that things aren't as simple, and that running in a VM in a cloud really is different from just running in a VM Ware / Zen / Zones environment, or something like that.

This is an easy mistake to do. When I started working here at Recorded Future, where we use Amazon EC2 for everything, that is what I thought. We run Ubuntu in a virtual environment+ Big deal. There are some EC2 integration tools and some GUI to administer the whole shebang, but except for that, this is no different than your server-room Linux box, but at a lower cost. And yes, I fully admit it, I was wrong.

What this is about, more than running in a Virtual environment, is the side effects of an environment shared with other, and how you set up your system to support that. Two important things I have learnt so far in some 5 months with Recorded Future:

Scalability is key! It really is. And I know, everyone want scalability, but in an environment such as EC2, with many different configuration options, but still a shared environment, you must be able to scale. And Scale horizontally. Even the very largest virtual machines at Amazon has the power for demanding applications in terms of disk I/O performance, Network throughput and latency and CPU performance.
It's not cheap. No, it's not, you are wrong. And that is not to say that it's bad. But it is different! If you manage that difference, you can run a very cost-efficient operation with EC2. But if you expect vertical scaling or have monolithic setups that runs on a single machine that has to scale, in a single instance, with your needs, then don't think this will save you and headaces or money.
But if you build your infrastructure in such a way that it scales nicely acress servers, and ensure that performance requirements on a single server are modest, and can be distributed if needed, then EC2 is for you.

So what about the Elastic aspect then? Well, elasticity in EC2 works both ways, on one side, the performance you get, in particular in terms of network latency, will vary over time. It just will, so get over it, accept the facts and create a system that can sustain it. On the other hand then, you can add resources as you need them. Sounds great, doesn't it? Well, yes, but there is a limit to WHAT resources you can add, and how.

EC2 allows you to add disks to your system as you please. There is a GUI (which is not very good) and there are command-line programs (not particularly good either, to be frank, but I am not, I'm Anders) to do this. But they do the job reasonably well. What you can NOT buy is more disk I/O throughput, just like that. You can get more disks and stripe them for sure, but that's about it. The same goes for CPU, you can get more of them (to a limit), but they are only so powerful. It's not like "Gimme a gazzillion of Petaflops" just like that, I'm afraid.

Above all, the network is only so fast, and regrettably that is not terribly fast. Also, the way EC2 manages DNS looks is just plain weird, I have to assume it is so for a reason, but the reason just has to be something you smoke, but not necessarily something you inhale.

So where are we now, then? EC2 has servers with limited performance, with limited disk I/O capacity and interconnected by what sometimes seems like 2400 baud modems. How can all this be useful? And by the way, at times both Disk I/O and Network performance is quite acceptable, but your milage may vary. Tell you what makes this stuff rock: There are MANY servers and many disks. How many as you want. And you can move them around, mount one disk on one machine, and then on another (like in a SAN, for example) just like that.

All in all then, the perfect infrastructure for EC2 then is something that scale with the number of servers. Scale I/O, Scale network performance, scale User connections, whatever the bottleneck is in your system, with the number of servers, each having as much disk as is necessary.

And having said that, you may have figured out where I am going with this, as in the Letterman Show, we are playing Will it scale?. Yes, that is a valid question, and the answer is, maybe. The less state a system holds, the easier it will scale, to make it really simple. A webserver that is just serving stuff off a disk will scale real nice, for example. Also, the less you persist, one way or the other, the better it will scale. The most common way of persisting data is of course writing it to disk, that is clear, but any kind of persisting data that is shared will cause some limit to scalability.

A simple Web-server scales easily, as we have already said. I more complex Web application, say a PHP application, may not scale as well, but still a lot, as long as the state is limited to a single PHP session or similar. The same goes for App-servers I guess. The one thing in most systems that is difficult to scale is the database, and the reason is of course that the database has a lot of state. A stateless database is not much of a database, to be honest.

MySQL has a means of scaling the database that has worked quite well for a number of years in Web-style applications. Scale-out is the way to go. And scale-out is simple enough: Asynchronously Replicated Slaves being fed from a Master. The Asynchronous nature of this Replication means that database writes (which all go to the Master) are not held up by replication to the Slaves. And you can have, in theory, any number of Slaves. But there is a drawback to all this: Only Reads may scale, not writes. Master-Master may help, to an extent, but not much. And a massive replication setup, with N slaves all Replicating from a different point in the Master. Another issue is that for the Slave to do it's job properly, the operations on the Slave are serialized (if this wasn't the case, think about what Foreign Key relationships might cause you), which means the Slaves are slower than the Master when it comes to Writes, which in turn means that unless you want the Slaves to become more and more behind, you better keep the write-load average at the level that the Slave can sustain, not the Master.

And no, by the way, I don't think MySQL Replication is something bad and awful. But I do think I know what it is good at and what it is NOT good at. And it will not help you scale your writes. Nope.

So what would a database that could scale in an Amazon EC2 Cloud look like? Above all, it would need:

Flexible configuration - Adding a node should be just that, adding a node. No restart, no optimizations, no reorg of data, no downtime, just Here is a node: Use it. And removal of a node should be the same. And management of data in the database as well. No more monolithic database configurations, please!
Scalable performance - And once you add those nodes, they really should be able to increase the performance of things, and not just to a small extent.
Data distribution - Yes, I want my data distributed. Replicated to where it is used. Distributed and persisted to where it is best persisted.
Transparent - Yes, all this should be transparent to the application.
SQL based RDBMS - Yes, I know, I am an old fashioned guy, but this what I want. SQL because it is ubiquitous, not because I think it's the best query language on the planet. And I want an RDBMS because I firmly believe that that is best for my data. Which is not to say that some application might prefer some other means of storage (if you want my full view on these matters, read this post).

So, is this just a wet dream? I hope not. One technology I am looking at and which I am eager to try later this year is NimbusDB. This is created by Jim Starkey, and if you think that the MySQL Falcon debacle was caused by him, and that NimbusDB is therefore not something to look seriously, think again. Having looked at a number of different technologies in the past 5 months or so, I have to say that NimbuxDB is the only one where they have at least understood what problems to solve in a Cloud environment, and that is not a bad start. And tell you what? I do think that to support such a setup as NimbusDB sets out to do, you really need to start from Scratch, I do not think that Oracle 13g or MySQL Clouse Storage Engine or something like that will be around to fulfill the requirements to run and scale properly in a Cloud. But you never know, there are interesting times ahead.

/Karlsson

Objections to Objects?

2011-02-03T15:52:00.003+01:00

A long time ago, there were no objects. There was clean, cold code, and the closer to the hardware the faster, not only because it is faster that way, but also because not just anyone would write code in those days. Then came programming languages in the 1950's and structured programming in the 1960's (the first commercial software offering was Auto-Flow, a program that would generate flowchart prints from existing programs written in Cobol, Fortran etc. Auto-Flow dates from the early 1960's).

The 1970's came around, and besides awful clothing styles and horrible interior decoration colour schemes, and things such as ABBA, we got Personal Computers, sort of, and the Relational Database (Oracle was first with an true software offering in this arena, in 1977).

In the 1980's Object Orientation, which had been rather esoteric and academic in the 1970's, turned into reality. (Also, Oracle got BIG). And if you ask me, this was a good thing, if it wasn't for what happened in the 1990's.

The 1990's saw, among other things, my (still) favorite hangout Akkurat open in Stockholm serving English Cask Conditioned Real Ale and Belgian Beers, among other things. One of the worst things that I think happened in the 1990's though was that Object Orientation, which had so far been a real cool way of writing organized code, which in itself also documented, in the code, relationships and interactions between objects, into a Religion. Oh my, among the most awful things was getting into a customer where an OO consultant had taken control. You couldn't even go to teh bathroom without having a crapper object with number one and number two methods. Awful stuff.

And in the midst of that came... Yes, you know what I am talking about, the ORDBMS and OODBMS, the Object relational and the Object Oriented Databases. I was out there, trying to convince people to buy the ORDBMS I was promoting at the time, something I was doing with some, but still limited, success. I admit it, I was wrong, this was, in the general case, not a good idea!

Looking back, I think I know what I think the problem is with the OO database things. At least I have an opinion (you expected that I guess) and here it is: OO is fine, it's a great way or organizing data in your code, but just because can make code good (it doesn't HAVE to, just as little as C automatically makes your code "portable"), it doesn't necessarily make your data any good.

One thing here is that there is lots of code that use your data, and there are many way to look at data at. So your code makes do with a view of data that fits whatever task your code is set out to do. But the same data may well be applicable to another use, using some other, completely different, structure, relationships and methods. The mistake, in my view, is to say that just because I can process data in an OO way, my data should be organized in an OO manner. I do not think so.

Another thing is that, when looking at data, the Relational model is brilliant. It allows you to view data from any angle, independent of how it will later be processed, which is the key here: Data is data, and processing of that data is processing of that data. And yes, these two things are linked, but that doesn't mean they have to have the same structure.

A third things is that OO from the start was a developer thing. It allowed you to write black-box style code, it allowed smarter code, and it encouraged structure to the code and made it easy to provide and enforce strict interfaces between different aspects of the code. But it was still about code. I think nearly every highly paid OO evangelist of the 1990 was looking at OO from the POV of code, and not data.

My view is a bit like this, when it comes to relational databases and different kinds of programming methods and whathaveyounot. The RDBMS is like a container, you can fill it with whatever you want, but it is just contents, it will not move or do anything, it will just stay there in the container, and the bookshelf you put in there is there the next time you open the container (hopefully). The transport mechanisms, the trucks, ships, air carriers, satellites and what have you not, is the processing we do. You pick the best processing for the job. There may be better ways of transporting your data than a container, but because all containers look the same, this creates so much of an advantage as the disadvantage of not filling up every square inch of the ship is minor.

And then you have the standard things that allows your container to be loaded, moved, fastened, unfastened, lifted and loaded again etc in a standard way. This is your query language. Of which SQL might well not be the best (I don't think it is), but that is beside the point, that everybody uses it is what the advantage is. It really is.

And when I pack my container to move it, I don't have to worry about if the container will fly, go by truck or be shipped. I really do not care, I just fill it with stuff. Just as I just provide my database tables.

And another things, looking at data in the shape of fixed named columns and variable number of rows is something that everyone, more or less, can understand. It's a universal language that goes WAY beyond the RDBMS.

To conclude, no, I don't think OO sucks, I think it rocks! But for code, not for data. And no, I don't think that your data is good just because it's in an RDBMS. And no, I do not even think an RDBMS is good for everything! There are cases, many of them, where all you need is an organized persistent storage for your programs data: No-SQL is great for that. But if data is data, and not just an extension of the RAM occupied by your program, then an SQL based RDBMS is often (but not always) your best bet!

/Karlsson

Speaking at the MySQL UC...

2011-01-26T09:16:00.002+01:00

I will be speaking at the MySQL Conference & Expo in April (The conference is on April 11 - 14 see more info here: http://en.oreilly.com/mysql2011). The subject for my speach is Large datasets in MySQL on Amazon EC2 and this is pretty appropriate. If you have been reading my blog before, you might know that we run all our systems on Amazon here at Recorded Future, and to be honest, at first I wasn't impressed. Then I learned to live with it, and now I'm a convert!

But I think Amazon might have done a bad job at selling and presenting this service to the world. The pro's are not what people think (cost, for example), and there are cons you didn't expect either. What the real issue is to get the right software that is at least to some extent EC2 aware. Yes, you run on your choise of operating system (we are on Ubuntu), and yes, it sure looks like your standard Linux box out there, but a bit more flexible. But this is not the whole truth.

The key in EC2 is the E as in Elastic. Here, in our EC2 environment, I want software that can scale. A lot. Here, I want software that is flexible (can be started, stopped, moved, extended, reconfigured). Here, I do NOT want a distributed, but static, clustered system.

So, see you in Santa Clara in April!

/Karlsson

Not so much on databases, more on Open Source and Democracy

2011-01-02T16:49:00.003+01:00

Those who read what I write here know that I am sometimes pretty opinionated, and that I have some strong feelings about some things. I am not to discuss politics here, but I will discuss one topic that that is one of the subjects closest to my heart, a thing that I think is crucial in todays world, and that has been a driving force in making the world what it is, both good and bad. That things is democracy.

Way back when I was 20-something I begun to appreciate what is sometimes called "Adult cartoons", then not Adult in the sense of being sexually explicit, but rather Adult in the sense that these cartoons are about things closer to us adults. I don't know about the rest of the world, but in Sweden this is pretty popular (you might have seen the "Rocky" cartoon for example, published in many languages, which is pretty typical of this genre).

You may think what you want about something like the "Rocky" cartoon, that is OK with me, but I still read it and enjoy it. Which is not to say that I don't read other books too, quite the opposite, I read newspapers, magazines, novels and fact-books every day. But if you disapprove Rocky, which you are free to do, that is OK, as long as you do not try to stop me reading Rocky just because you don't like it. That is censorship, plain and simple.

All this is not to say that we can publicly say or state just about anything. If what we state is considered harmful in some way to someone else, in a practical sense, then we should think again. But that is for the courts to decide. Just because someone dislikes this or that work of art, does not mean it should be banned. If you don't like a particular painting, book, cartoon or TV-show, then just don't view it. Simple. If a published material causes you harm in some way, for example if it humiliates someone personally or some ethnic etc. group, then let the courts decide. These are not simple thing, by any measure, but it is something we have to do as a democrcy: Decide what is harmful in such a way so that it really should be pubishable to state it publicly, and what is not.

Someone who should NOT decide this is some commercial entity with it's own agenda. To protect the innocent, I use the name of an appropriate fruit to name the specific company I am talking about here, so we call them Grapefruit Inc.

The company in question has allowed the Swedish magazine "Galago", which an Adult (not sexually explicit) cartoon magazine, to publish their "App" only under the condition that Grapefruit Inc can censor the contents. This is not the first time this company does this, and the affects, among other people, users of the products noPhone and noPad from Grapefruit Inc.

And before I go on. Yes, I do have a noPad. And I lov it, hardware is great, as is the software, it truly brilliant. But as the deals with publishers aren't open, I will not pay for any Apps, as I will not know if, and if so what, has been censored.

Another magaine with the same issue is a Danish IT-magazine that has published quite a few positive articles on Android. One thing with an Andriod phone is that it is not censored (again, if you ask, Flash is pretty crap technology, but it is out there and it's being used on sites I want to visit. Not so with my noPad, but my HTC Desire does it like a charm. As well as acting as a 3G WiFi router). What's next? Will they ban TCP/IP because IPX/SPX is better (which it probably is)?

In a way, this is the perfect mix of censorship as seen in, say, the old Eastern German, with modern day market economy: You get censorship, but you have to pay for it!

So, who comes to the rescue? Android, which is Open Source! No censoring, thank you! Open Source delivers! As for myself, who has spent 6 years with MySQL as a Sales Engineer, I have been on the look for the "killer reason" to go Open Source over all those years. To me, this is it, a better reason than any other I have seen: No censorship!

To conclude, I am aware that this is a very opinionated posting, and must be very clear that the opinions expressed here are my own only, and are not related to my day job. I would hope that my employer shares some of my views, but again, what is expressed here is my own thinking, and mine alone. Competely uncensored!

/Karlsson

Ubuntu Upstart for automatic MySQL start and stop - Part 2

2010-12-29T20:13:00.004+01:00

I've done a little change to the upstart script I published in a recent blogpost. The issue is with mysqld_safe which has a few issues it seems, but so far I haven't tracked them down exactly. Anyway, if mysqld_safe is not in your path and you run it with the full path (as I do in the Upstart script which is run by root that may well not have the mysql bin directory in it's path), then mysqld_safe will behave strange in at least 5.5 when called with the full path, i.e.
/mnt/mysql/bin/mysqld_safe --defaults-file=/etc/mysql/my.cnf &
will not work. If I then put the appropriate directory in my path, like this:
PATH=/mnt/mysql/bin:$PATH
/mnt/mysql/bin/mysqld_safe --defaults-file=/etc/mysql/my.cnf &
It still will not work as expected. In the first case, it complains that it cannot find my_print_defaults, and then just goes on, seemingly using some default values, error logging to /usr/local/mysql/data and trying to run /usr/local/mysql/bin/mysqld, and before you ask, yes I have set things like ledir and basedir in the [mysqld_safe] section in the .cnf file.

Anyway, the fix for all this is to run mysqld_safe with the current directory being the same as basedir (i.e. one step "above" where mysqld_safe is located). To tell Upstart to run in a specific directory, you use the chdir stanza. So my scripted ended up looking like this now:

#
# MySQL Service for Recorded Future
#
description     "MySQL Server"
author          "Anders Karlsson, Recorded Future"

start on (net-device-up
          and local-filesystems
          and runlevel [2345])
stop on runlevel [016]

expect fork
kill timeout 2

# Set variables.
env MYSQL_ETC=/etc/mysql
env MYSQL_PIDFILE=/var/run/mysql.pid
env MYSQL_HOME=/usr/local/mysql5.5
env MYSQL_INSTANCE=my
umask 007

chdir /usr/local/mysql5.5
exec $MYSQL_HOME/bin/mysqld_safe --defaults-file=$MYSQL_ETC/$MYSQL_INSTANCE.cnf >> /tmp/x.out &

post-start script
    loop=600
# Wait for MySQL to start.
    while [ $loop -gt 0 ]; do
        if $MYSQL_HOME/bin/mysqladmin --defaults-file=$MYSQL_ETC/$MYSQL_INSTANCE.cnf ping; then
            break
        fi
        loop=$(($loop - 1))
        sleep 1
    done
    exit 0
end script

# Send a soft SIGTERM to MySQL before Upstart will kill it.
# A Sigterm to mysqld will cause a controlled shutdown.
pre-stop script
    exec kill -SIGTERM `cat $MYSQL_PIDFILE`

# Wait for MySQL to end. Flushing buffers and all.
    loop=600
    while [ $loop -gt 0 ]; do
# If the pidfile is found, then continue waiting.
        if [ -e $MYSQL_PIDFILE ] ; then
            loop=$((loop - 1))
            sleep 1
            continue
        fi
        break
    done
end script

And lastly, again before you ask, don't try the easy way out here:
chdir $MYSQL_HOME
It will not work. And as usual, debugging this with Upstart is nearly impossible. If you try using the above syntax, all you get is an information that the script would not run (and, yes, the console Upstart stanza is more or less useless. Why I cannot tell Upstart to output to the current terminal is beyond me, and why there is no verbose mode, I do not know either).

I will check up the issue with mysqld_safe and send a bug report to MySQL (if it isn't there already). As for upstart, I am so annoyed by it at this point in time, that I'm not sure I have the energy to post a bug report, as I cannot even tell what so-called "feature" is most annoying. But again, this may be because of my limited upstart exposure. Upstart is sure different from working with chef though (which I am also new to). One single, minor, error in chef, and you get an error listing the size of Encyclopedia Britannica. Whereas Upstart on the other side would say something like "job failed", and nothing more, if it was managing the Titanic.

/Karlsson

Ubuntu Upstart for automatic MySQL start and stop

2010-12-25T16:04:00.006+01:00

Here at Recorded Future we use Ubuntu (running on Amazon EC2), but so far we have not explored Ubuntu Upstart that much. During the holidays I made an effort to get acquainted with Upstart and to implement proper MySQL start and stop with it.

If you do not know Upstart, this is the way you start and stop services in Ubuntu, and it serves the same purpose as the old /etc/init.d scripts, but are a bit more structured and powerful. That said, Upstart is regrettably far from complete, although the functionality is much better and Upstart has some cool features, some things do not work that well. For one thing, documentation, where it exists, is useless, at best. Secondly, there is very limited ability to test and develop Upstart scripts. And this is made worse by the fact that the documentation is so bad. Another thing is that Upstart insist on stopping services, by default, by sending a brutal kill signal. Not good for databases, mostly.

In the /etc/init directory are the Upstart scripts you have. In difference to the old init.d scripts, you cannot disable a service in Upstart curenntly. If it is in /etc/init it will be started at system start. That's it. And this is something that I am sure will be fixed, but for now, again, is something we have to live with. Upstart scripts have the suffix .conf (don't ask me why), so the default MySQL Upstart script, for example, is called /etc/init/mysql.conf.

In an Upstart script, there are Stanzas that determine what to do. Like the exec Stanza that runs a program for example. And you may then ask, when is it run? Startup? Shutdown? And the answer is startup. For shutting things down, as I said before, Upstart will by default just send a kill -9 signal.

The minimal startup script you can have, and this actually works in a reasonable way, is to just have one line with an exec stanza, like this:
exec /usr/bin/mydaemon
Which will start the daemon. For stopping the daemon, Upstart will send a -9 signal to the started process by default, and nothing more is needed in the Upstart script.

For MySQL, we need to make things a bit more complicated. The default mysql.conf Upstart script really is not good. For one thing, it will not do a controlled shutdown of MySQL (this is possible even if Upstart will eventually send a kill -9 anyway). Secondly, this script assumes that what we use is a standard Ubunty installed MySQL distribution, so if you have installed MySQL in /usr/bin/mysql5147 or somethings like that, you are out of luck.

So what I wanted to create was an Upstart script for MySQL that fullfilled these requirements:

Starts MySQL automatically.
Waits for MySQL to be available before exiting.
Be configurable to support different MySQL install locations, data directories etc.
Do a clean shutdown of MySQL when stopping the MySQL services.

Before I show you what I ended up with, I want to comment on the points 2 and 4 above. With Upstart, you can define a script or command to run just before or after a services has been started or stopped, and this is what I use to wait for MySQL to become available, and to send a SIGTERM to the MySQL Server when stopping (which will do a clean MySQL shutdown).

So here we go, a complete MySQL Upstart script, the way I want it to work:


#
# MySQL Service for Recorded Future
#
description     "MySQL Server"
author          "Anders Karlsson, Recorded Future"

start on (net-device-up
          and local-filesystems
          and runlevel [2345])
stop on runlevel [016]

expect fork
kill timeout 2

# Set variables.
env MYSQL_ETC=/etc/mysql
env MYSQL_PIDFILE=/var/run/mysql.pid
env MYSQL_HOME=/usr/local/mysql5.5
env MYSQL_INSTANCE=my
umask 007

exec $MYSQL_HOME/bin/mysqld_safe --defaults-file=$MYSQL_ETC/$MYSQL_INSTANCE.cnf >> /tmp/x.out &

post-start script
    loop=600
# Wait for MySQL to start.
    while [ $loop -gt 0 ]; do
        if $MYSQL_HOME/bin/mysqladmin --defaults-file=$MYSQL_ETC/$MYSQL_INSTANCE.cnf ping; then
            break
        fi
        loop=$(($loop - 1))
        sleep 1
    done
    exit 0
end script

# Send a soft SIGTERM to MySQL before Upstart will kill it.
# A Sigterm to mysqld will cause a controlled shutdown.
pre-stop script
    exec kill -SIGTERM `cat $MYSQL_PIDFILE`

# Wait for MySQL to end. Flushing buffers and all.
    loop=600
    while [ $loop -gt 0 ]; do
# If the pidfile is found, then continue waiting.
        if [ -e $MYSQL_PIDFILE ] ; then
            loop=$((loop - 1))
            sleep 1
            continue
        fi
        break
    done
end script

To be honest, this is not what I create for all our MySQL servers. Instead I used this to create a chef template, chef is what we use for configuration management here (see http://www.opscode.com/ for more on chef), and here it is put to good ude to generate an Upstart script for MySQL. The above is just an example.

/Karlsson

Using the right tool for the job at hand - MongoDB, MySQL and Sphinx

2010-12-20T18:05:00.003+01:00

You may have seen my posting regarding "eventual consistency" some months ago, and you may have come to the conclusion that I was insisting that a SQL based RDBMS is the way to go for just about anything. Tell you what, that is not so. And nether am I against using. say, MongoDB, where it is appropriate.

The whole deal with Eventual consistency is something that I am still opposed to, I want to know if my data is consistent. And I am not not sure that you cannot have a fully consistent, distributed system either. But I guess that debate goes on. And I still want my base data to be consistent. Like in RDBMS-SQL-Foreign-keys-all-over-the-place-and-not-a-bl**dy-bit-lost-in-the-MyISAM-swamp consistent. That is what I want the base data to look like. And if there are compromises with this, which it may well be, then I want to know about those too.

So, having covered that, what am I trying to say? Well, if you properly normalize your data, then the more you normalize and the more strict you are with data quality, the more troublesome management of that data is going to be, and that is something we have to live with I guess. But if you then are to ask some hefty queries on that data, where the data is organized in such a way to make data real consistent, and the queries just want to data, and the query-side of things really doesn't care about normalization at all, how do you deal with that? One way of course may be to replicate to something more query-friendly, possibly a second MySQL-server or possibly even a bunch of such servers in a scale-out scenario. But your data structures still look really complex, having being built to support storage, update, maintenance and consistency requirement foremost.

At Recorded Future we have taken a different path in our latest release: Choose the best tool for the job at hand. We use MySQL with InnoDB for our data loading and storage. And for that, MySQL worka real well. So we have the data we have collected and processed and organized, structed nicely in an RDBMS.

Now, on the other side of things, where queries are made, things look different, there we want to fulfil 2 needs, basically:

Fast quering for data, in out case these are instances.
Fast retrieval of attributes of the instances that was retrieved.

The above is fulfilled with 2 supporting technologies, Sphinx for instance querying and MongoDB for instance attribute (anwering the typical kind of key-value-store query: Here is a key, gimme all the data). This is what we are live with now, and so far we are really happy. Mind you, this didn't come that easy, both Sphinx and MongoDB has caused some problems.

Forgetting Sphinx for now, what we do with Sphinx is actually really simple, and concentrating on MongoDB, where we also do pretty simple things, but the requirements and the scale in the case of MongoDB is higher for us. MongoDB so far has performed well for us. We are running in an Amazon EC2 environment, and that has issues of it's own (in particular this seemed to be the case with Sphinx, but they are on the case). As for Mongo, this is so common in EC2 environments so I guess ot has been more tested.

I admit to liking MongoDB. It is lightweight, easy to manage and has some cool features, such as automatic, and reasonably transparent, sharding. MongoDB at first seems to have good documentation, there is a lot of examples and samples. What is lacking is a good reference though, and a good administration guide is also missing and the Sharding support is still not well documented from admin point-of-view. The JavaScript orientation in MongoDB at first was annoying to me, but increasingly I find it useful. One thing to note about MongoDB is that it needs it's memory: the more the better. For good performance with MongoDB you really want to avoid disk accesses (in particular in EC2 environments). What we do to avoid disk-accesses as much as we can, we have gzipped the data items. We store data in JSON format (yes, we are a JSON shop, sorry XMLers out there) and compressing data fast and having a fast JSON parser is crucial. And yes, we DID think about the case with frequently accessed data: we have the option, on an instance-by-instance basis, to store data as plain JSON or compressed. Plain JSON has advantages, such as as MongoDB is using JavaScript, JSON is pretty much Native to MongoDB, making certain operations real easy.

We are always on the lookout for new technologies, and we do try many things, but the current setup is really useful and we do get much better performence and scalability. And yes, we do get both, with the same number of servers, we get better performance, and much better distributed load of the machines. Now we are waiting for Amazon to fix their disk IO and Network issues.

Your truly
/Karlsson
Hope to see you in Santa Clara in April at the MySQL UC!

Working with MySQL on Windows - Part 1

2010-12-02T19:08:00.003+01:00

This is the first post in an attempt to write about how to get along with MySQL on Windows. Before I start, I want to stress that the focus here is seasoned Windows users getting started with MySQL. As for MySQL, I am prett much Operating System agnostic, having used Linux / Unix for some 25 - 30 years ( started using Unix as of Version 6. That's OLD). But I have also been a Windows user and developer since Windows 3.0 days.

Also, when I say I'm a Windows developer, let me tell you where I come from. I come from learning Win16 in C. Hardcoded message loops and message handlers and that kind of stuff. And that is how I still develop on Windows, but these days with Win32 / Win64 and exposing more Windows APIs. So I am no C# or .NET expert by any means.

So, you are ready to get started with MySQL on Windows? OK, first you should download it, possibly from here: http://dev.mysql.com/downloads/mysql/. And no, you don't have to pay anything, MySQL is free for us developers. And no, you will not need to compile anything from source or something, quite the opposite, MySQL comes with a very nice installer. Just download the MSI installer package for your platform (32 or 64 bit that is), and get started.

Usually when Installing MySQL, you can choose most of the defaults. After the installation, a configuration application starts, and you can usually choose most of the defaults for a starter here also. One things that your might want to look at is the character set selection. Latin1 is one option that is usually OK and that is similar odl and tried Windows CP1252. It's good enough as a start. If you plan to develop Web applications, then you might want to choose UTF8.

OK, assuming all went well, where do a get started with really using MySQL? Well, there isn't much in terms of a Windows GUI for MySQL installed as part of MySQL. And not only that, if you are a Visual Studio user, possibly developing in C#, there is very little in Visual Studio that shows that there is a new database server installed? Well, this we will fix, all this stuff sure is available from MySQL, you just have to know where to look.

First, as for a MySQL GUI for Windows, you have a few choices. If you want a complete MySQL design and SQL tool, then look no further than MySQL Workbench from MySQL themselves. This is a database design tool for MySQL that is loaded with cool features. Again, you can download it and use it at no cost, it is available here: http://dev.mysql.com/downloads/workbench/5.2.html

There are alternatives to MySQL Workbench, and many of them are also free and Open Source. HeidiSQL is one such option, and I have a tool myself called MyQuery. Both of these are a little less ambitious than MySQL Workbench, but also has features of their own. HeidiSQL is very much DBA focused and has loads of DBA style functions. My own MyQuery is focused on working with MySQL Scripts and shines when it comes to extensibility among other things. MyQuery can be downloaded here: http://sourceforge.net/projects/myquery/

But what about your applications? How is a database connection to MySQL achieved? Well, there are several ways to connect to MySQL. When you download the MySQL Server, it comes with a C API. If you are an old man like me, that might well be the way to go. The C API is OK and is well documented here: http://dev.mysql.com/doc/refman/5.5/en/c.html. The C API comes in static and dynamic shape. If you don't know the the difference or don't really care, then use the dynamic build. Using the static library is more demanding. MySQL itself, including the C API, is built with Visual Studio, so if that is what you use for your application (and I guess you are), that is OK. And you can do with the Express editions of Visual Studio.

Now, chances are you are not an old C hacker like myself, but a more modern person, using C#, Visual Basic or Java. If that is the case, MySQL provides help even here. If you are using Visual Studio with any of these languages, you want to download and install MySQL Connector/Net. A Connector is what MySQL calls their database drivers. And yes, even if you are on Java you want the .Net connector. The reason is that the .Net connector also includes the Visual Studio integration, although here it doesn't seem that the Express editions are enough. Connector/.Net can be downloaded here: http://dev.mysql.com/downloads/connector/net/.

And then for Java, you need Connector/J, available here: http://dev.mysql.com/downloads/connector/j/

Now, one word of caution before I leave you for this time: Both Connector/Net and Connector/J does some cool things for you. If you look at how MySQL works, or have a look at the MySQL C API, you realize that MySQL has some very particular ways of dealing with some things, like array inserts, prepared statements and things like that. To make life as a MySQL developer easier, both of these connectors do some very cool things to isolate you from that. Also, these connectors to an extent address some things beyond MySQL itself, such as high availability. And thirdly, tuning the Connectors is often just as important as tuning the server! What I am saying here is, in short, something as unknown and arcane as read the documentation. In particular this is true for Connector/J, which has many parameters, is very advanced, but also very fast and can help you speed up your application if you know how to work it.

Bye for now, I'll be back in a jiffy with some more Windows specific MySQL notes
/Karlsson

More on the sqlstats SQL Statement monitor plugin

2010-11-30T16:03:00.002+01:00

There was some questions on how sqlstats really works, so I'll provide some more details here. Before I begin though, if you want to play with it, it can be downloaded from Sourcefore here: http://sourceforge.net/projects/sqlstats/

OK. So lets start then. SQLStats is a plugin for MySQL 5.5 and up, it will not work at all with previous versions. What it does is reasonable simple, the reason it hasn't been done before is that MySQL just wasn't instrumented to support it, but the effect is really usuful anyway. The 4 things it does, in short, are:

Pick up all SQL Statements executing in MySQL. This is done using the MySQL Audit plugin interface.
The SQL Statement is then normalized, which in this case means that all literals in the statements are taken away, so that SELECT * FROM orders WHERE orderid = 57 becomes SELECT * FROM orders WHERE orderid = ?. Then we place this statement in a list of most recently executed statements, if the normalized version is already in the list, the execute counter is incremented and the statement is popped to the top (as it is most recently executed), else it is inserted into the list (and popped to the top), in which case, if the list of full, the least recently executed statement leaves the list.
Then we check a second list, the toplist of most executed statements. If the execute count of the normalized statement will make it appear on this list, it is added there in the same way.
As a sidenote, both the recently executed list as well as the top executed list are simple in-memory datastructures. They are not stored on disk or anything, they are just lists of C-structs allocated dynamically.
The last thing the Plugin do is to expose two INFORMATION_SCHEMA plugins, so that the recently executed list and the toplist can be examined as normal MySQL tables.

All three plugins (the Audit plugin that will pick up and process the statements, and the two INFORMATION_SCHEMA tables) are in the same plugin library, so that they can share the same data: the lists of SQL statements. The lists are protected by an internal mutex, so that multiple threads can use the plugin simultaneously without breaking anything.

One magic aspect here is the Audit plugin API? What is that you ask? It is actually pretty well documented these days (see http://dev.mysql.com/doc/refman/5.5/en/audit-plugins.html). Basically how I use it in this instance is to pick up the statements every time they are written to, or would be written to, the general log. Note that the general log does NOT have to be enabled anywhere for the plugin to work! That's a releif, right?

So, once the plugin is installed, if you follow the documentation, you can see the most recently executed statements in the SQLSTATS_LASTSQL tables in the INFORMATION_SCHEMA schema, like this for example:
SELECT * FROM INFORMATION_SCHEMA.SQLSTATS_LASTSQL;
The columns are described in the documentation, but the most important ones are:

STATEMENT - The normalized statement.
SAMPLE_STATEMENT - The first real statement that was turned into the normalized statement in the STATEMENT column.
NUM_EXECUTES - The number of times the statement has been executed.
NUM_ROWS_TOTAL - The total number of rows retrieved by all NUM_EXECUTES executions of the statement.

The SQLSTATS_TOPSQL table looks the same, but has different rows in it of course.

You can turn collection of statements on and off by setting the sqlstats_enable global variable:
SET GLOBAL sqlstats_enable=1; -- Turn SQLSTATS on
The installation script will turn it on. You may also adjust the size of the lists using the global variables sqlstats_lru_stmts and sqlstats_top_stmts global variables. The way these work now, they will not really be adjusted down below the current size of the list.

To install the plugin, just follow the documentation. As you have to link with the MySQL sourcecode, the build process is slightly iffy, but once built, the plugin can be installed and deinstalled and recinfigured, without shuting down the server or anything.

I think that the Audit API is good enough reason to go with MySQL 5.5, if you ask me. My testing, and some semi-production testing here at RF, has shown that the overhead of the plugin is hardly noticable.

/Karlsson

What? Me Grumpy? Me?? or MySQL on Windows, what's the deal?

2010-11-30T07:22:00.003+01:00

So you have seen my writings on MySQL on Windows. Most recently regarding MySQL 5.5 and before that on Cluster and 5.1. So, what is the deal? Am I a mean person attacking MySQL? Am I a Windows hater trying to show how badly Windows works? Or maybe a Linux / Unix hater who want to keep those bearded 1970's terminal junkies out of my GUI Windows world?

Actually none of those. My ambition is to point out this that should be addressed in MySQL for Windows to make it more Windows friendly, for Windows users and developers. As MySQL on Windows stands right now,, it is OK for the Linux / Unix user user who also wants to run on Windows, and for those with cross-platform knowledge and ambition who want to run MySQL across the range. And I put myself among those. But I also want the Windows hard-core users to find out about, use and love MySQL. And MySQL sure could do much more in that area, but before that happens, we (yes we. We who are cross platform folks, we who who can take the time to check things out a bit and has somewhat of an understanding of what those rough edges are and we who care about MySQL and it's adoption on ALL platforms).

I really do not think that a Windows user, who is not used to Linux / Unix, whould have to learn some Linux stuff just to use MySQL on Windows. Come on, MySQL is a database system, using the SQL standard and that runs fine on Windows. Yes it DOES! And there are some good Windows integration, like the VS plugin, .NET connector and much more. MySQL 5.5 really does do away with many MySQL on Windows limitations also.

I recently reported some bugs on this matter, really minor bugs, but things that could easily be fixed and that would make MySQL on Windows a bit more polished. One issue I reported was that there are still Perl-scripts distributed with MySQL for Windows, even in 5.5. Now, installing Perl just to use MySQL is bad enough. But the thing is that many Windows users / developers don't even know what a .pl file is, and that a Perl runtime is needed! If MySQL on Windows really needs Perl to be fully functional (I don't think this should be the case. If Oracle can do without Perl, then MySQL should be able to do this also, but this is a different issue), then ship a Perl runtime with MySQL!

The reason I am reporting these minor bugs on MySQL on Windows is not that I dislike or hate this or that technology. I am waaay too old to get that emotional about technology in and of itself. The reason I am doing it is that this is low-hanging fruit on the way to make MySQL work, look and behave really good on Windows.

/Karlsson

Announcement: MyQuery 3.4.1 Released

2010-11-29T14:10:00.003+01:00

MyQuery 4version 3.4.1 is now released on Sourceforge: http://sourceforge.net/projects/myquery/. This is a pretty minor release with just a few fixes, but the first of them pretty significant:

Windows 7 User Access Control (UAC) issue fixed - When running on Windows 7, the Windows UAC would prohibit MyQuery from starting, complaining about registry access. For once, this was not yet another UAC annoyance, but an actual bug that I hadn't caught before. Now, this should work OK even on Windows 7.
SQL Statement normalization issues - The SQL Statements dialog got statements wrong at times, and could cause a crash. This was due to a bug in the normalization of SQL Statements and this is now fixed, including refining the normalization a bit.
Optionally Show SQL SELECT tool output vertically - This will turn the output from the SQL command in a SELECT tool around. This is for running statements that will produce multiple columns instead of multiple rows, such as SHOW SLAVE STATUS and SHOW MASTER STATUS. The output from these commands is weird, the multi-column layout really isn't useful, but this little feature fixes that.

Happy SQL Coding!
/Karlsson

Perl is probably a fine scripting language, but on Windows...

2010-11-27T15:21:00.004+01:00

Please do not assume Perl is always installed on all platforms. In particular not on Windows! Just as it is probably a bad idea to assume that Windows DOS commands work on Linux! Come on now!

Why am I making a fuzz you ask, do I have a problem with you writing stuff in Perl (personally, I don't even like Perl, but that's another issue)? Nope. But somewhat modern software really should not assume that you have Perl, or DOS, or some other arcane, not terribly standardized piece of software around for a server to do it's job. And MySQL still have a way to go to be reasonably good on Windows. Downloading the lastest RC, 5.5.7, MySQLhas managed to screw up at least two things, at least, in my mind. But maybe that's just me:

The optimzied. non debug libraries, are now in /lib under the distribution, not in /lib/opt where they used to be. I see no good reason for this, to be honest (place the libraries where you want, of course, but once a release is out, don't move them around at random in upcoming releases). This has been reported as bug #58532
To make it realhard for us in the community to test this release Candidate version of MySQL, to iron out the bugs, MySQL decided not to include dynamic debug client libraries, only the static ones. Why this makes things difficult, you have to do some development on Windows to userstand. For a bunch of reasons (not necessarily good reasons, but if you are Microsoft, you are allowed to do whatever you choose, no reason needed, except that maybe it's the 7th hour on the 7th month and it's afull moon coming up or something), Micosoft compiles stuff so that a static library depends on stuff included by the compiler itself. So if the compiler version changes, the library will not work properly anymore. In short, you need to build your applications with the same version of Visual Studio as the MySQL Client library was built with. Yikes! (For more Yikes, see MySQL bug #58531)

None of this is any fun. And to add insult to injury, in the bin directory of the MySQL distribution on Windows, in 5.5.7, I find the following files: mysql_config.pl, mysqld_multi.pl and 4 more. Yes, Perl programs. No, I do not have Perl install on my Windows boxes. I'm about to test this and see how it worls aout. Above all, I want to test if mysql_config works as expected.

All in all: Please remove Perl depenent client programs from the MySQL distribution! And mysqld_multi seems rather superflous on Windows in general, if you ask me (not that it even works, check bug #58553).

/Karlsson

Monitoring MySQL SQL statements the way it SHOULD be done!

2010-11-23T17:11:00.004+01:00

You may have read a previous post of mine, back in April this year, where I wrote about using the MySQL 5.5 Audit interface to SQL Statement monitoring. There was a bunch of comments and some ideas, but not much happened. Until now that is.

Hereby I release the first version of SQLStats, which is a much enhanced version of what I described in the previous post. This is a MySQL Plugin for MySQL 5.5 that allows you to monitor the most recently executed, and the most frequently executed statements using 2 INFORMATION_SCHEMA tables. The thing is not very complex, to be honest, but it does do the job. So what was the job then? Well, looking at what this plugin does, it goes something like this:

Allows you to monitor ALL SQL statements executed by the server.
The SQL statements are "normalized", meaning that literals / constants are removed before comparison.
Data is saved in memory. No disk access and very little overhead.
Data is retrieved from INFORMATION_SCHEMA tables, just a simple SELECT and you know what is going on in the server.

I have done some basic testing of the overhead of this plugin, and it wasn't even noticable. If you have used the general query log for this stuff before, know what overhead I'm talking about. With SQLStats, the overhead is close to 0.

There is no need for MySQL Proxy or anything like that. There is no need to change something in the Client or in the Connector. To be honest, there are a couple of things I want to add to the plugin eventually, but this is a starting point at least. To use it: download it, install the tomcat / mysql monitoring server, install the monitoring agent and ... No wait, that was MySQL Enterprise Monitor, this is how you do it: download, build, install and use it. That's it.

So where can I get it, you ask (or maybe you don't, but I'm gonna tell you anyway). It's on sourceforge, and you can download it from here:
http://sourceforge.net/projects/sqlstats/

There are two files to download: A simple PDF documents with some basic usage and configuration tips, and a source-code package (which also includes the same PDF).

What would I want from you? Ideas for future development, bug reports and a few beers, that's it, not too much to ask, eh?

/Karlsson
Also, did I mention that the overhead is VERY low...

MyMonitor 1.1 released. Finally!

2010-11-19T17:14:00.003+01:00

I know some people have used mymonitor on and off over the years, but it was a long ago since I created it, and things has moved on and changed. Among the things that didn't work well was the build process, and the fact that there were a few bugs in the normalization of SQL statements. Now this is fixed.

If you have NOT used MyMonitor, it is a small ncurses based tool for monitoring SQL statements in your server, and work by polling the PROCESSLIST and the normalizing and sorting and grouping the result. What is "normalizing" then? It's the term I use to describe the process where a statement has all the literals replaced with ? in them, so I can see that:
SELECT * FROM mytable WHERE id = 861
is actually the same statement as
SELECT * FROM mytable WHERE id = 387
And by the way, this is also the same statement as
SELECT * FROM mytable WHERE id = 177

MyMonitor can look like this in action:

You get the point I think. MyMonitor has a few more tricks up it's sleeve, like the ability to write out the monitored statements to a file and to do some simple timing on statements. All in all, this is a simple tool that can be real helpful at times. Many times less powerful than, say, MySQL Enterprise Monitor or MONYog, but still useful.

If you are wondering: No GUI needed. What IS needed though is ncurses. And Windows support? Nope, but have a look at my MyQuery project instead, it has many more features, including staement monitoring.

Download mymonitor from Sourceforge here: http://sourceforge.net/projects/mymonitor/

Cheers
/Karlsson

MySQL configuration management using Chef

2010-11-14T18:13:00.003+01:00

Here at Recorded Future we use Chef for Configuration Management, and I am a complete newcomer to this. Chef is, like Puppet, built in Ruby, Ruby sure seems popular for Configuration Management things.

As for the so called "recipes", which is what Chef names a things used for installing some software package, such as MySQL, is really not complete in the case of MySQL at least, but that is really not a big deal. I actually am beginning to like Chef, there are many good points to it, although I can't escape the feeling that I am spending more time writing Ruby than actually installing stuff and getting MySQL running. But I beleive that this most a result of the fact that we are still a startup and do not yet have that many MySQL servers running.

I my opinion, and I can be pretty opinionated as you know, I think the one big thing with Chef that I do not like is Ruby. I have to admit I really have to work hard to get used the idiosycratic means that this language works in. Mind you, I'm no big fan of scriping languages in general, but I have written fair amounts of PHP, for example is most of my website papablues built by myself in PHP as Joomla modules, components, templates and plugins.

In many ways, I think Ruby started of pretty well, the basics of it are promissing. But the way they have complicated Object Orientation seems to me to be not just an overkill, but an overkill without really good uses. There are so many different means and syntaxes for the same thing that you just get confused. And then you do what I do. Learn one tecnique and use that and nuthin' else (which means the code will be hard to read for someone using some other tecnique: Tough sh*t!)

That said, all this may be just because I'm not used to it. I used to hate PHP also, but sort of got used to it (I still don't like it that much, to be frank). But my one and only favourite programming language remains C. Which you can learn by reading the thin "The C programming language", not 1000 pages of "Idiots introduction to the basics of PHP" or some similar utterly useless publication.

All this though, Chef is beginning to be fun, and I will share some more experiences with you regarding the Chef work we are doing here at recorded Future. Also, I was thinking that we should share some experiences with other MySQL Chefs, maybe as a BoF session at the upcoming MySQL UC (It can't be a full blown talk, the deadline for CFP is over).

Cheers for now
/Karlsson
Who sticks to his old way of working, even with Chef: Code should be commented! And yes, I am also aware I am old fashioned.... And by the way, Chef documentation leaces a bit to be desired (and that way todays gross understatement).

MySQL 5.5.7 - Can we trust it being RC, or?

2010-11-10T22:00:00.002+01:00

I just saw that MySQL 5.5.7 RC had been released, and reading the releasenotes made me more than a fair bit suspicious. In some kind of general agreement on what constitutes a "beta" release, this is when the software has reached a level of maturity when no more major features are to be introduced. MySQL (and many others) has broken that rule at times, and the rule is not enforced or something.

What constitutes an RC release though, in my mind, but I really want to know what you think, is software that is really 100% feature complete. There may be, but hopefully there aren't, even any major bugs to iron out. In short, it is "A Candidate to Release", and as close to GA as you can get. I have not seen this rule broken much, really.

With MySQL 5.5.7, this is an rc, as was the previous release, 5.5.6, and this time there is a really major feature introduced between these two release, pluggable authentication. And before I go on, let me stress that this feature per se is not what I am questioning here, quite the opposite, this is a very useful feature.

What I am questioning though is:

Why is MySQL introducing new major features in an RC release, even in between 2 RC releases? This means, if I am not mistaken, that this very important feature (authentication deal with security, mind you), that it might go live (the C is for Candiate) without having been beta tested?
What made MySQL 5.5.6 a Release Candidate? What I mean here is that if we assume that this major new features was conceived, written and performed in just a few weeks between 5.5.6 and 5.5.7, MySQL knew that 5.5.6 wasn't feature complete, and hence in no way a release candidate (C is for Candidate, if you had forgotten that little fact).
Why does MySQL insist on having major important changes to the security setup be tested the least in the server before GA? Fact is, what MySQL is telling us here is that there may not be any testing at all (as 5.7.7 is RC (where C is for Candidate) which means is could possibly be GA.

My conclusion is that MySQL 5.5 is not to be treated as GA (is MySQL even considering a GA release as feature complete, or are they about to introduce more features again in that line of releases) just not yet. Which is a problem for me personally I I just recommended us to go with 5.5.6, hey, it's RC (you know what the C in RC is for now, right?), and that it would be as close to solid for production use as you can get.

To be clear, I will stick with 5.5.6 for now. Not 5.7.7 or even 5.5 GA for a while, until I have tested that pluggable authentication is secure and solid for production use. I really want 5.5, and I am not alone, so I do not understand why MySQL had to screw around with this. I do understand why pluggable authentication should go into MySQL, for sure, but not in 5.5 or at least not in the midst of a RC cycle.

/Karlsson
Who is not saying he will now change to Postgres. Nope, I will not not act stupid. And watch me run with 5.5.6 for a while yet, I will not be alpha testing MySQL security in a live production site, no way, José.

It's about time

2010-10-24T11:25:00.003+02:00

TIMESTAMP and DATETIME is how dates and times, or temporal, data is represented ion MySQL. These types are combined with a bunch of operators on these types, such as ADDATE and TIME_FORMAT and such. So is this enough to effectively manage temporal data then? Nope, in my mind, far from it.

To begin with, time isn't such a simple type of data as we might look at it. Is a date just a count of the number of seconds since 00:00:00 on Jan 1 1970 or something like that? And we then convert it to a calendar and all is fine? Is time data really linear in that sense? No, it's not. Dec 24 2010 is Christmas Eve and when you get your Christmas gifts here in Sweden. Is this significant? Also, it's a Friday. That this data is a Friday can be calculated from the date or the seconds-since-jan-1-1970 counter, but that is a different thing. Looking at that counter, you cannot see that there is no way to look at that number and figure out that this day is special, but it is.

To manage this, we have the date and time functions in MySQL, which help us figure things like the above out, but there are issues with it still. To begin woth, the constructs quickly turn difficult. Secondly, you cannot really do an indexed search on temporal data, except pretty simple cases, such as searching for a data and time, before or later than a given data and things like that. A search for "all saturdays" will sure not use an index.

There are more issues though, like that fact that, as I have shown above, a temporal value as we know them above, contains hidden data, something that is considered bad (like the day of the week, the month, the fact that a data is a leap day etc).

And it doesn't end there, there are different calendars in use, in different parts of the world and in different inductries. If you have a mortage on your house, chances are that the interest you pay is the same each month, despite the fact that the interest in calculated over the full year and different months have different number of days. The reason is that interest uses a "360 day" calendar, where each month is assumed to have 30 days. And different financial instruments use diferent calendars (for example the one called 366 day, which is our normal calendar and the 365 day calendar. which is the normal calendar with the exception that there is no leap day). If you try to match one financial instrument to another, when they use different calendars, is not a lot of fun (I've done it, so I know).

But chances are that many of you now think that this is not a big problem for you, as you don't see people asking questions on data with complex temporal relationships. And to that I say that, there is a big chance that this is because:

Temporal data is difficult. More so than simple integers and strings, disregarding the simple before date X or something like that. But we just don't ask the questions like "how much of the sales per quarter happen on the last day of the quarter" and things like that. Which doesn't mean the information isn't interesting, but the questions is difficult to formalize and even more so to execute in a database. Not to mention checking for overlapping dates and things like that.
We are used to not being able to ask these kinds of questions.

But really we should be able to ask, query and get data with a lot more temporal information. When was the last time you asked a temporal data query in Google (not that you CAN do this). Like, how has the matching or IBM and Pressrelease changed over the last 10 years, i.e. how many Press releases did IBM post in 2001, in 2002 etc. Yes, the data is there somewhere, querying it is difficult. And using temporal data combined with data you get on the web should allow you to determine more than what you can today (i.e. IBM and Press release matches X number of documents in Google right now). Adding a temporal axis to web data should allow you to determine what might happen.

One issue with all this is that databases are hardly geared up to support this, and a second is that the data to support this isn't available in large quantities yet. This is one of the things we are looking at at Recorded Future, but there is more stuff coming. The web is there with masses of data, now we need to make sense of all that data. One aspect which is there but which hasn't yet been explored much is just the temporal aspect of data.

/Karlsson

MyQuery 3.4.0 Released!

2010-10-19T13:49:00.002+02:00

I have not finished MyQuery 3.4.0, after a long time of weekend and spare-time development. This version adds some features that are basically Scintilla featires that I have integrated with. This includes error indicators, margin markers and some other things. In particular the margin markers changes a few things in the interface: When running up to, or continue from a point in teh script, then use the margin marker instead of the current line! If you don't like this, then don't set the margin marker and the current line will be used just like in old releases.

Another addition this release is Online help. I get the help contents from the database that you connect to, which is installed with most MySQL installations in the tables called help_xxx in the mysql schema.

External program has a few more options, and in this release the installer installs a few of those, just to show how it works, like the MySQL schema in an editor window and direct access to the MySQL commandline.

In addition to this, and a few more minor features, there is a whole bunch of bugfixes in this release, which doesn't mean that some doesn't remain. Above all, I have reengineered how I handle multiple access to the MySQL connection.

Download it from Soureforge! I will follow up this post with some more on how to use MyQuery, how to build extensions and plugins and some things like that!

Happy SQLing
/Karlsson
PS. If you have issues with MyQuery, drop me an email, but note that I am now at anders@recordedfuture.com!

Moving on, but not far

2010-10-18T13:02:00.003+02:00

As of today I have a new employer, and I no longer work for MySQL / Sun / Oracle, instead I am now Database Architect at Recorded Future (http://www.recordedfuture.com). This is exciting stuff, really so. RF pulls data from teh Web, but instead of just allowing searching, RF will analyze what it find to spot trends and things like that.

It is still early days, we need to collect a lot more data than today, and managing that will be one of my challenges. But an interesting and fun challenge!

This doesn't mean I will not maintain MyQuery anymore, I sure will. And Recorded Future is by the way MySQL users, so I will probably work even more with MySQL related stuff than today. I will also continue to blog about what we are doing, and about other database related things as well. In particular, I will work with and blog about maintaining very large databases!

So watch this space, and see you soon
/Karlsson

MyQuery 3.4 soon to be released

2010-10-14T22:46:00.003+02:00

I've been spending some cycles now and then on MyQuery 3.4. One thing I wanted to do with this release was to fix some long-standing bugs I knew about, but hadn't identified (Are you seeing ReleaseSemaphore errors? That's one of them). Also, I had a couple of issues with the new Plugins, as well as some ideas for new features, such as OnLine Help.

In addition to that, I also felt that the Scintilla editor controls tht I was using wasn't used to it's full potential.

As I have the new 3.4 in front of me now, I have done something about all those things. I have cleaned up some of the more recent code, I have added OnLine help, in this case I get that from the MySQL help tables. I am pretty happy with how a few more steps to integration with Scintilla could add so much more usability, Scintilla really is a great editor control with a lot of potential, I haven't reallly explored it fully yet, and there is more to do. Also, Scintilla is really solid.

What remains to be done is to test, package and publish MyQuery 3.4. This will be a Beta, but I will release a GA based on it real soon. Before that, I have one or two plugins I want to add (Replication monitoring for example). Also, I have a plan to add some more advanced error handling, using Scintilla to show erroneous text in a better way than today. But all in all, we are now close to GA.

Cheers and see you soon!
/Karlsson

More on MySQL Cluster monitoring with MyQuery 3.3

2010-09-13T23:20:00.007+02:00

A few days ago, I posted a blog on monitoring MySQL Cluster / NDB with the aid of MyQuery accessing the ndbinfo tables. Now, there are more ways than that to query the status of MySQL Cluster / NDB, you can use the MGMAPI, which is the low level API used to monitor and manage a NDB Cluster setup.So, if this API is available, and is accessible from C, and MyQuery has the ability to be extended with DLLs written in C or C++, then why not create a MyQuery plugin for managing a NDB Cluster? Tell you what, that is already part of MyQuery, which has by the way had a few bugfixes and is now up to version 3.3.4.

To get started, if you haven't done so already, download MyQuery and install it, and make sure to install the plugins / User defined tools. Once you have done that we are sort-of ready to go, but first a few words pf caution regarding the sourcecode.

If you haven't used any of the NDB low-level APIs, i.e. NDBAPI and MGMAPI, let me fill you in on a few details. NDBAPI is used for normal database DML and DDL, but it is much more low level than SQL, but also much MUCH faster. NDBAPI is strictly a C++ interface, although there are other bindings (Java) on top of it. MGMAPI on the other hand, which is used for monitoring and managing an NDB Cluster, is C on the surface, meaning the the interface used no classes, no overloading no nothing, and most MGMAPI code I have seen uses straght C. Bit this isn't the whole truth. Some NDBAPI includefiles that are also needed by MGMAPI still have no C++ specifics, but one thing it uses, which is valid in C++ but not in C, is to define structs with no members (don't ask me why), like this:

struct ndb_logevent_STTORRYRecieved { };

So even though the MyQuery plugin that I will show later is written in C and is contained in the file NdbMonitor.c, it still must be compiled as C++ code. In Visual Studio 2008, this is done by right clicking on the file in Solution Explorer (yes, the file NdbMonitor.s, nit the project, that will not work!) select the Properties menu option and in the dialog go to C7C++ and then Advanced and then change Compile as to Compile as C++ Code. Also, when you look at the MyQuery Solution, note that the NdbMonitor project is compiled and linked against MySQL Cluster, and in fact, this is not the binary MySQL Cluster that you download from MySQL, rather this is built from source, see more in my previous blog postings on the subject.

OK, so that's it for the sourcecode, now let's look at what this little puppy can do when we run it (and it's part of the biinary MyQuery download by the way, so you do not have to use MGM API yourself or anything). To use this tool, after installing MyQuery, connect to an SQL node in a NDB Cluster and then select the Tools->NDB Monitor menu option. This will show a dialog like this:
If this is the first time you use NDB Monitor, the default Cluster connection string is filled in the connect string edit box, and adjust this to reflect your NDB Cluster setup. You may connect to NDB Cluster on any platform here, not just Windows. Then click Connect and the current Cluster status will be shown when you have connected to the Cluster:Most of the information here should be pretty obvious. The traffic lights are green if all datanodes are up, yellow if one is down and red if two or more are down. To refresh the status click Refresh and you may automatically refresh by turning on the Autorefresh checkbox and enter the number of seconds between refreshing.

There are two tabs, one for the nodes themselves and one for the node groups. When the node view is active, you can right click on a node to bring up a menu that allows you to do one of three things:

Stop a node - This will bring up a dialog that allows you to stop a node, gracefully or just kill it.
Restart node - This will bring down a node and then restart it. You have the option of leving it in "non-started" mode also.
Start node - If you have done a restart and left a node in non-started mode, then you can start it using this menu option.

And that's about it, NdbMonitor isn't more fancy than that just now, but I am planning a few more things:

Cluster backup - This is not that hard to implement, I just haven't gotten through to it yet.
Cluster log management - This is a bit more difficult, but it involves subscribing to the cluster log and showing log contents in a new tab.

If you have more ideas for future versions of NdbMonitor, let me know!

/Karlsson

MySQL Cluster users - Check out MyQuery 3.3.2

2010-09-10T13:16:00.004+02:00

In MyQuery 3.3, where I just released version 3.3.2 which includes some MySQL Cluster NB specific fixes, there is some support for accessing MySQL Cluster / NDB beyond what is available as ndbinfo table and such things. MyQuery is a Windows based MySQL GUI, if you didn't know that already, and just because you run this tool on Windows (it is only supported on Windows), doesn't mean you cannot monitor a Cluster that runs on, say, Linux. Or Windows, should you so wish.

There are 2 predefined tools that support MySQL Cluster there, one that uses the ability to predefine a SQL statement that runs in a non-modal dialog and is optionally updated and one that uses the feature that allows a DLL, using a sepcfic MyQuery API, to run as a tool, and in this case, this tool is linked up with the NDBAPI to allow monitoring and a certain amount of control overthe Cluster.

Monitoring NDB Cluster memory usage
In MySQL Cluster / NDB 7 and up, there are tables in a schema specific to NDB, called ndbinfo that includes a bunch of tables with NDB specific information.
Defining a tool in MyQuery that monitors memory usage, like this tool. by selecting from a table is real easy. The Memory usage monitor is included with MyQuery 3.3 and up, so you can just download MyQuery and then connect to the Cluster and use it, but possibly you want to create tools of your own for other ndbinfo tables or something completely different.
Assuming we didn't have the NDB Menory tool already, and wated to create it, this is how you wold do that:

First, test the SQL statement you want to use in the MyQuery editor. Always use fully qualified table names (i.e. prefixing them with the database name), unless you have a specific reason not to do this. In this case, we end up with this statement:
select * from ndbinfo.memoryusage
The next step is to create the tool. Select the Settings -> Configure user tools menu option, and you will get a dialog that shows all the tools that are currently defined. We are to create a new tool, so click the new button.
In the dialog that pops up, give the tool an appropriate name and select a type. In this case, the type is SQL SELECT. Then click OK.
A new tool is now created for you, but it will not be saved until we have added some required info, in this case teh SQL statement. In this case, if you have the SQL text in the editor, click the Copy button and select Copy from editor, else just tpe in the SELECT statement you want to run.
We probably want to tool to be shown in the Tool menu, and hence be accessible, only when it will work properly, in this case, the ndbinfo plugin is required. For this, select the Enabled for plugin radio button and then select the ndbinfo plugin in the listbox.
The last thing we want to do is select the connection to use. There are three options here: Use the connection used to execute normal SQL in MyQuery (Use execute connection), use the connection normally used by MyQuery tools (if MyQuery isn't connected with dual connections, then these two options are the same of course), which is the default (Use main connection) or use a completely separate connection. In our case, just use the default.
For tools like this, you would usually not enable the Modal dialog option. This is an option only used in certain special cases.
Finally, you might want to add some Help text.
That's it, now we have a new tool! Click OK and you are done!

Now, in the Tools menu, your newly created tool should appear. Select it, and a non-Modal dialog will appear, just like the one for the NDB Memory info tool. It should look something like this:
As you can see, there is the ability to refresh the contents automatically, to refresh every 2 seconds, just check the Autimatic refresh checkbox and set the time to 2 and you are all set. Let me know if you find out any really cools ways of using this feature for tools, and if they are generic enough, I will include them in a future release of MyQuery.

Happy Clustering!
/Karlsson

MyQuery 3.3.1 releases - Fixes MyQuery 3.3.0 platform issues

2010-09-09T23:10:00.002+02:00

I have released MyQuery 3.3.1 now. This is a minor release in many ways, but still significant in others. MyQuery 3.3.0 had issues on platforms where the runtime library I used wasn't installed. Also, there was a minor (but crashing) bug that caused Ndb Monitor to fail. All this, and a few other minor things, are fixed now.

I will eventually blog a bit more on the new features in MyQuery 3.3, such as the plugins, the InnoDB Lock monitor and how to easily define your own monitor dialogs with a simple SELECT statement.

Til then, download MyQuery 3.3 from Sourceforge.

Cheers
/Karlsson
And by the way, I'm sorry that I failed some QA with version 3.3.0, but it's just me coding away here, and I have a limited amount of machines and above all, time.

MyQuery 3.3.0 problems about to be fixed.

2010-09-09T15:40:00.004+02:00

If you have tested MyQuery 3.3.0 and have problems with a dialog poping up saying that the "application configuration is incorrect", then I am working on this. The reason for this problem is that I now use Visual Stuio 2008 for building, and now Microsoft insists on things such as manifests and stuff. To be frank, insisting on manifests isn't necessarily a bad thing, actually it's pretty cool. The issue is that when there is a problem with them, the messages you get from Windows are, well, less helpful.
In the message you get when you try to run MyQuery probably says nothing about the manifest or anything. In my mind, it should. Also, if you link dynamcically with the Microsoft redistributable DLLs, then you want manifests for those also. Ah well. I am working on it anyway.
I will move to a new VS 2008 generated project, instead of migrating the old as I did before, and build a new distribution. As far as I can see, this will fix these problems and will also create a better, more clean, project. I'll release a 3.3.1 version later today with these fixes in it.

/Karlsson

Whoa! MyQuery 3.3.0 Beta Released at last!

2010-09-07T07:12:00.004+02:00

After a long wait, today I release MyQuery 3.3.0. For you who haven't seen MyQuery before, this is a Windows based interactive query tool for MySQL (sorry no Mac or Linux support this time) . The emphasis is much on script editing and running, and on the needs of the DBA, more than on the end user. Also, for the database designer, there is some cool stuff in here.

MyQuery supports running scripts up to a specific point in the script, or starting from a specific point and supports a kind of "edit and continue", so you can develop a script, run it til it breaks, correct the error, and then continue where you left off.

That is not all, after all these releases MyQuery has gained quite a few useful features. Script editing is done with the Scintilla color coded editor for examples, and there are tools for managing database objects such as tables, views and events.

Every release of MyQuery has a theme, and MyQuery 3.3.0 has a very distinct theme: Extensibility. There is now support for adding your own favoutite SQL SELECT or Scripts to a user defined tools menu. And that is not all, there are many more types of tool that you can define, from opening a web link in a browser to running code in a specified DLL, using the new MyQuery Plugin API.

The main new features in summary are:

The User defined tools feature
The MyQuery Plugin API
A login dialog which now supports saved login settings for different servers
MySQL Cluster / NDB monitoring through User tool plugin
InnoDB Lock tree monitoring through a User tool plugin
Several bugfixes

This is still a beta version. It is hence feature complete, but I know there is a bug or two, in particular the plugin API (which is an advanced feature which required a fair amount of refactoring to work). Feedback is welcome!

I will write a few more blogposts here to describe some 3.3 details, such as the InnoDB Lock tree monitor, the NDB Monitor, the Plugin API and things like this, but for now, just download the thing and enjoy! As usual it's available on Sourceforge:
http://sourceforge.net/projects/myquery/files

/Karlsson

Querying for InnoDB Lock contention

2010-08-25T17:13:00.002+02:00

In the previous post, I showed how the "virtual tables" that the InnoDB Plugin use for finding lock contention works, and what they look like. I also showed what an InnoDB lock graph looks will look like when monitored from MyQuery 3.3, which is not yet, but soon, released.

So, we are looking at three tables, the transaction table, where each transaction has no, one or more locks. For our intents and purposes, we will disregard transactions without locks, so we can join transactions table to the locks table. Then we need to link up the locks table to the lock waiters, to see if the lock in question is waiting on another lock, Each lock is either not waiting on one, or, as we have seen, more, locks, or it is not waiting at all. As a lock might not be waiting at all, we need an OUTER join here.

So the query I am using, the result of which is massaged a fair bit before I shown the lock tree, but this is the query I use to get the data:

SELECT t.trx_id, l.lock_id, w.blocking_lock_id, l.lock_table, l.lock_index,
t.trx_query, l.lock_data
  FROM information_schema.innodb_trx AS t
  JOIN information_schema.innodb_locks AS l ON t.trx_id = l.lock_trx_id
  LEFT OUTER JOIN information_schema.innodb_lock_waits AS w
  ON l.lock_id = w.requested_lock_id

/Karlsson

Monitoring InnoDB Lock contention

2010-08-25T14:27:00.005+02:00

When you use InnoDB, or any other RDBMS or storage engine that supports row level locking and transactions, you get to realize a few things:

The chance of lock contention is less, as the lock is much more granual that page or table level locking.
On the other hand, when you get into lock contention, maybe because you have structured your data in such a way so there may well be as many locks as before (for example if one small table with very few rows is updated frequently by many threads), then the effect is worse.
With transactions, which are a good thing by the way, I'm not promoting not transactions datastores here and assuming auto commit isn't used, then locks are held longer, and as each transaction may well hold several locks, because of multiple rows being updated or multiple statements are part of one transaction for example.
With InnoDB then, fining the root cause when you end up with lock contention, with several transactions locking each other out in a complex manner, is HARD!

Luckily, finding locks and waiters for them, and the relationship between these, is A LOT easier when you use the InnoDB Plugin instead of the builtin InnoDB (are you still using the built-in? Dont. Just don't! The plugin has better performance, is easier to monitor, has online-operations and is GA, even in 5.1, you just enable it with a few settings and you are ready to go).

With the InnoDB Plugin and the InnoDB Plugin Information schema tables installed, the latter by installing the plugins using the plugin_load my.ini setting, for example:

plugin_load="innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;innodb_locks=ha_innodb_plugin.dll;innodb_lock_waits=ha_innodb_plugin.dll"

we are ready to go.

There are 3 new tables in the INFORMATION_SCHEMA schema now, one for each object we need for this exercise:

INNODB_TRX - This keeps track of the InnoDB transactions. Each transaction may have no, one or more locks and may also be waiting on locks (locks I say? What? More about that later)
INNODB_LOCKS - This keeps track of all locks for all transactions. Locks are unique for transactions, so if two transactions are waiting for one lock, we will see a total of three locks, one holding the lock, and two other locks, for the same row, waiting for it.
INNODB_LOCK_WAITS - This table has one row for each lock that is waiting for another lock.

In InnoDB, only locks that are actually interesting are show in these tables, so if a transactions holds a lock that noone is waiting for, this lock will not show up.

The are some intersting things to note about these tables. If we have 3 transactions:

A holds a lock on a row
B is waiting for the lock A holds.
C is waiting for the same lock that A holds and that B is waiting for.

We will see 3 locks! Why? A holds a lock, right, so that is fine. B holds a lock and is waiting for A, so that is fine too. But does C hold a lock? Yes, it holds a lock and s waiting for the one that B holds. I told you that locks, as seen in this table, are unqiue to each transaction.
If we now assume that A just holds the lock, and isnät waiting on anything, we have how many waiters? Well, we have 2 waiting transactions, but 3 waiters! Transaction C, to complete, needs both the lock that A holds AND the lock that B holds (as we saw above, just because B is waiting for a lock on a row, doesn't maen it can also HOLD a row on that row. A different loc, but the same row). B waits for A and C waits for A and B, whch menas we have a total of three waiters! But if you look at the individual transactions, as represented by the requesting_trx_id column in the INNODB_LOCK_WAITS table, we see only two unique transactions, which is right, as although we have 3 lock waiting, only 2 transactions are actually blocked (B and C). And if we look at the column blocking_trx_id column, then we again see only 2 unique transactions blocking something, A and B this time, as C isn't really blocking something!
The actual data may look something like this, in this example:
SELECT * FROM innodb_locks;

lock_id    lock_trx_id lock_mode lock_type lock_table  lock_index lock_space lock_page lock_rec lock_data
B87:0:52:6 B87         X         RECORD    `test`.`t1` `PRIMARY`  0          52        6        5
B86:0:52:6 B86         X         RECORD    `test`.`t1` `PRIMARY`  0          52        6        5
B85:0:52:6 B85         X         RECORD    `test`.`t1` `PRIMARY`  0          52        6        5

And the the waiters:SELECT * FROM innodb_lock_waits;

requesting_trx_id requested_lock_id blocking_trx_id blocking_lock_id
B87               B87:0:52:6        B86             B86:0:52:6
B87               B87:0:52:6        B85             B85:0:52:6
B86               B86:0:52:6        B85             B85:0:52:6

One interesting column that has not been discussed so far is the column lock_data in the INNODB_LOCKS table. This shows the value that is being locked, in this case I have an integer column with the value 5 (and this is the PRIMARY KEY also, as you can see from the lock_index column).
Does this help? Yes, it is much better than using the built-in InnoDB, but there is still some way to go. A simple SELECT from these tables will be a good help, but as this data is actually in a hierarchy, it would be real nice to see this as a lock graph? Right? Well that is something I am working on for MyQuery 3.3, which is due soon. It will look something like this:
This is the same lock tree as in the example above, and how it will look, but there is something that is not right here, right? The C lock is just shown as waiting on A not B? Well, for the sake of clarity, I have cleaned that up in the GUI itself, so only relevant locks and waits are really shown.

/Karlsson