Monty Rants or Yet Another Case Of Developers vs Users

I saw Monty's latest rant and I told myself "here we go again." I blogged before about deaf developers, and here's another symptom of the same disease.
This is another case of a developer, a brilliant one, mind you, one of the brightest in the open source arena, but a developer nonetheless, who has a non-user oriented mindset and is inflicting his view on the users.
I will stay clear of the reasons. Why the co-founder of MySQL decides to spoil the party on release day and ambushes his colleagues with a truckload of blame is beyond my understanding.
What concerns me is the patronizing plea that Monty uses against the users. "Don't use MySQL 5.1"? Really? What about the ones who have already been using it for months, if not years? Don't they count for anything? Has he ever used it?
I look around, and I find plenty of stories from people who have been using MySQL 5.1 in production for long time, starting from the people at MySQL.com itself. And then there are the ones of the Use Case competition, who have shown that 5.1 in production is not anathema. And then there is me. I haven't participated to the competition, but I have been using MySQL 5.1 with partitions for 8 months already, and it suits my needs perfectly.
I don't know of the other features, but for partitioning I have something to say. Let's look at Monty's claims:

Partitioning in MySQL 5.1 should be regarded as a step to a full partitioning feature with parallel query. Parallel query is however not scheduled even for MySQL 6.0.

Why should I care? Yes, parallel queries would help, but with partitioning I got performance gains from 10x to 100x, and I couldn't care less if the feature is not as perfect as it would satisfy a stellar developer.

For now partitioning is mainly useful in the case where you need to frequently drop a well defined part of a table (like one month of data) and when MERGE tables are too cumbersome to use.

I strongly disagree. MERGE tables were a crude hack that I used as long as there was nothing better. Partitions solve my problems nice and square. My insertion and retrieval speed increased, and that's all I need.

If one partitioned table crashes it's very hard (sometimes impossible to repair it.

Probably Monty wasn't paying attention when Bug#20129 was fixed.

If you get a server crash during ALTER TABLE of a partitioned table you may loose all your data for that table.

I would like to see proof of this.

Partitioning is very slow and can become unusable if you have a large number of partitions. This happens even if you only use a few of the underlying tables in your query.

My experience tells me otherwise.

So, it looks like Monty has never used partitioning, probably because he doesn't think that the implementation is good enough. Is this a good reason to scare away everyone else? I think not.

Cracking the Tokutek contest with MySQL 5.1

Hello, blog! Long time without posting. Here I am again.
My attention was caught a few days ago by a contest posted by Tokutek.
This is a company that makes a proprietary engine for MySQL. In their contest, they claim that it takes 20 days to insert 1 billion records into a InnoDB table, and that MyISAM is not even up to the task. In their example, InnoDB inserts 404 rows per second and MyISAM died after a few days of inserting 93 rows per second.
It looks farfetched to me. Surely you can do better than that.
Now, I don't have a quad 3.16Ghz Xeon server with 16GB RAM available like the one used by Tokutek, and so I tried with a modest quad 2.7Ghz AMD, 8GB RAM server. The problem is mostly the same. Once your index becomes bigger than the available RAM, you are in the glue. The only difference is that my server was slower.
I didn't try with InnoDB, but I tackled the MyISAM case at once. Using MySQL 5.1, I just added 400 partitions to the contest table, partitioning by range of 50 million records. And I made a simple Perl script that produces INSERT statements with 50,000 rows each.
The result? I inserted the rows into a MyISAM table at an average rate of 8,800 rows per second, with peaks of 25,000 rps. The speed did never drop below 5,000 rps anyway. The whole operation took 31.5 hours. That's much less than 20 days, and surely a hell of a lot less than the projected 167 days that should be needed for MyISAM according to the contest slides.
Should I claim the ridiculous contest prize? (a $100 Starbucks card? Gimme a break!)
No. I just wanted to make a point. The above instructions are enough for anyone savvy to reproduce the case. If the prize changes, and becomes a $2,000 donation to the FSF, for example, I will release the code that I used. In the meantime, enjoy your own tests!

While I was testing, I saw that MySQL released 5.1.30-GA. Great!. I used 5.1.29 for my experiment. Had I known about the release, I would have waited two days. Anyway, if you believe TokuTek benchmarks, using 5.1 partitions will result in a performance 200 times better than the original. There must be something wrong in their method.

Events and backup - two good features - an awful combination

The cool features coming with MySQL 5.1 and 6.0 are the event scheduler and so called online backup.
Both of them implement something that you can do outside the database server. The event scheduler frees the DBA from the operating system dependency and the database backup and restore makes mysqldump redundant.
So far, the good news. What's wrong with this picture?
A look at the manual tells you all. You can backup and restore a database with an explicit SQL statement, but you can't use it in a prepared statement or in an event.
What?
WHAAAAT?
What do I use the event scheduler for, then? Ask any DBA about the first thing that comes to mind associated to a time scheduled event, and 9 times out of 10 the answer is "backup".
So, why is the most logical action not allowed in events?
And don't try to cheat the system with a stored routine that may free you from the details of stating which backup file to use. The backup database and restore database commands are not supported in prepared statements. Ergo, even if you could use "backup database" in a stored routine, it won't do you any good, because the MySQL server can't overwrite an existing file.
Sure, you can circumvent this prohibition with some hacks (MySQL Proxy could be helpful, for instance), but why the user has to suffer for such a small issue?
MySQL developers, if you are listening, please think like users!

Mapping the MySQL community

I was intrigued by this survey about MySQL today, and I took it.
Some of the questions made me think about the status of MySQL community. Unlike other free/open source projects, MySQL community people are not direct contributors to the project, but just users. Then there are the more advanced ones who keep an active role, and the majority who are just content to use it and don't even care to participate in blogs or forums.
Seen throrugh the articles in PlanetMySQL, the MySQL community has three components, with sub components:

• Sun/MySQL employees, who link between the noisy users and the company.
• The ones who produce or advocate closed source
• The ones who only deal with open source
• The ones who tell interesting stories without taking sides.
• The interested parties, i.e. the ones who have gained some expertise in MySQL and sell their services or products.
  
• The positive vibes, who promote their business and contribute constructive criticism
• The negative vibes, who are always complaining about something, trying to weakening MySQL in order to sell their stuff, even when they contribute good technical stuff.
• The enthusiasts, who just write about cool things.
  

Maybe the distinction is wider, but this is how I see it after browsing planetmysql archives for a while.
I like to collocate myself in the third group. And I am thinking of taking a few notes and write a Whos' who of MySQL community when I have time.

MySQL sought by politics

In the hot US political campaign, something of interest for MySQL is happening. The Obama camp is looking for developers in the LAMP stack, asking for MySQL experience. They also ask specifically for deep knowledge of MySQL performance and query optimization. The interesting bits about this request is that it is
It would be interesting to know what the McCain camp is going to use to counter this move. But it is not going to be so difficult to guess ...
$ wget -O /dev/null -S http://www.johnmccain.com/
...
Content-Location: http://www.johnmccain.com/Home.htm
Last-Modified: Sun, 01 Jun 2008 13:39:58 GMT
Accept-Ranges: bytes
Server: Microsoft-IIS/6.0
X-Powered-By: ASP.NET
...
I can already foresee many geeks converting a political debate into a technological challenge.
As, for me, I don't discuss my political views. Go LAMP!

Sun, the stock market lottery, and the road ahead

Seeing the recent stock market disaster, I wonder if it makes sense to hire capable managers, when your company is hostage of the stockholders mob.
The news delivered by Sun did not seem that bad, except for the USA salesforce. Sun is growing everywhere else in the world, and a slow US economy has punished the whole company out of proportion. The reaction from the crowd is really unbelievable.
On a related matter, Sun has announced the layoff of 2500 people. What does it mean? Sun's strategy is based on growth, or so they say. How can they grow if they start firing people?
Bah!

MySQL and Ubuntu - a perfect match

I like Ubuntu's philosophy. Among the Debian derived Linux distros, it's the one that appeals to me the most. The first live CDs (Knoppix, Mepis) were a revolution, but Ubuntu has perfected the trend by adding a quality that was missing from these early ones.
I especially like the ease of installation. Plug to the net, apt-get install package_name, and presto! you got what you want.
MySQL server comes with just one line:

apt-get install mysql-client mysql-server

This will get you the latest server and client binaries, ready to use.
Yesterday I wanted to build MySQL 5.1 from source. The latest one (5.1.24) that has been released is missing the Federated engine, and I wanted the complete thing. So I installed Ubuntu in a spare machine, and got the source code from the development tree.
By default, Ubuntu does not ship with a compiler, and the manual lists quite a lot of requirements to get the ball rolling. In Ubuntu, installing the recommended building tools is as easy as:

apt-get install build-essential autoconf automake libtool bison byacc libncurses5-dev

After this command, to compile a complete server, you type:

cd where/you/downloaded/the/source/tree
./BUILD/compile-pentium-max

And it works without a glitch. Could it be easier than that?
On a side, but not entirely unrelated note, after Sun's acquisition, I suspect that Solaris will play a more important role with MySQL. I have little experience with Solaris, but surely it isn't as easy as Ubuntu. I wonder if there is an equivalent in Solaris to the above apt-get command. Any takers?

backup by slave, oh yes!

A well thought backup saved my skin last Saturday.
It's a simple setup: many copies. Using MySQL replication, the master is for writes.
Four slaves for reads. One slave for backups only (*). In a different server room. In a different building. (**)
The backup slave has a  cron job, which stops the slave, makes a dump, removes the oldest one, and resumes replication.
The same job works hourly (keeps 30 dumps), daily (keeps 7 dumps), and weekly (keeps 8 dumps).
The disaster occurred yesterday. A colleague who was working too much (***) made a destructive query on the wrong server. He thought he was using the development server, but it turned out to be the master. Fortunately, nobody else was working on a Saturday, so there weren't any changes, besides his. I zeroed the database on the master and reloaded the latest hourly dump. No suffering. No bad blood, and my morning coffee is paid till the end of the quarter!

(*) Replication is not a backup.
In this particular case, if you rely only on replication, your data is gone forever. 
You have five copies, all of them FUBAR.
(**) Keep the backup slave in the same room, and a flooding will make both master and slave useless at once.
(***) Seriously, Mel! You should have been holding a beer in a pub downtown instead of being in the office.