Scaleout and Tuning

According to my findings in Bug #31876, MySQL does not commit data to disk in Windows using the same method MS SQL Server and DB/2 are using. The method MySQL uses appears to be seven times slower in pathological scenarios.

The bug report contains a patch - thanks to the MySQL WTF (The Windows Task Force) and the lab provided by the customer for helping me to find that.

Does this work for you? I want to hear about your test results.

Lately, I have had opportunity to evaluate a very large Ruby installation that also was growing very quickly. A lot of the work performed on site has been specific to the site, but other observations are true for the platform no matter what is being done on it. This article is about Ruby On Rails and its interaction with MySQL in general.

This article does not even contain the words database or MySQL. I still believe it is somewhat interesting.

Mail has, for some reason, always been playing a big role in my life. I have been running mail for two, my girlfriend and me, in 1988. I have been running mail for 20 and 200 people in 1992, setting up a citizens network. Later I designed and built mail systems for 2 000 and 20 000 person corporations, and planned mail server clusters for 200 000 and 2 million users. And just before I became a consultant at MySQL I was working for a shop that did mail for a living for 20 million users.

Mail is a very simple and well defined collection of services. You accept incoming messages to local users, you implement relaying for your local users with POP-before-SMTP and SMTP AUTH, you build POP, IMAP and webmail accesses, and you deploy spam filter systems and virus scanners for incoming and outgoing messages. This services collection does hardly change when you go from 2 to 20 million users – maybe the larger systems will also provide additional services such as portal services, a news server or other more directed stuff, but that is just fluff outside of the scope of the mail system. The solutions, though, are very different, and very much dependent on the scale of your operations.

In Semi-Dynamic Data, Sheeri writes about Semi-Dynamic Data and content pregeneration. In her article, she suggests that for rarely changing data it is often adviseable to precompute the result pages and store them as static content. Sheeri is right: Nothing beats static content, not for speed and neither for reliability. But pregenerated pages can be a waste of system ressources when the number of possible pages is very large, or if most of the pregenerated pages are never hit.

An intermediate scenario may be a statification system and some clever caching logic.

Statification is the process of putting your content generation code into a 404 page handler and have that handler generate requested content. The idea is that on a second request the content will be there and thus a static file is being served with a 200 OK, using the fast path of the web server.

A typical example for this kind of task would be a script that generates its name as a PNG: By requesting http://vvv.k6p.de/statify/example.png, we are returning a PNG image that contains the text "example" - the number of potential PNGs is infinite, and we cannot possibly precalculate all of them. But a few PNGs are requested over and over - most likely because we are referencing them via some IMG tags in some HTML pages. It would be pointless to generate them again and again for each requester, because these images never change.

Here is how to do it, using PHP:

This is a translation of a german language article I wrote two weeks ago for my german language blog.

In 2004, when I was still working for web.de, I gave a little talk on Scaleout on Linuxtag. Even back then one major message of the talk was "Every read problem is a cache problem" and "Every write problem is a problem of distribution and batching":

To scale, you have to partition your application into smaller subsystems and replicate the data. The replication should be asynchronous, not using two phase commit (2pc), or the gain will be smaller to nothing. Writes must be delayed and batched, so they can be handled more efficiently. To avoid bottlenecks, data storage should be decentralized and a central database should be avoided.

That is often contradictory to what traditional database classes at university teach: It is insecure, you are working with false or outdated data and you don't even know at all time if everything worked out the way you imagined it should have.

It has an important advantage: It works. And it scales.

In Serving Images from a Database, Sheeri responded to me:

I appreciate your ideas, but you haven't read what I've written: "the sheer number of files is enough to kill any os."

I'm serious about that. we're talking 36 million images -- that's 36 million files (no thumbnails, they're all reduced size) plus directories to store them in. We have cacheing tuned very well, and it's still kills the OS.

Here is what I tried to simulate that:

I took my new Strato MR2. It is currently running tor, INN and MySQL. There are two HDS722516VLSA80 160 GB SATA HDDs. These disks are rated at 8.8ms avg seek time, so there are 113 seeks in a second with this disk.

Sheeri wrote:

So, most of the “I want images in MySQL” conversations are terminated with “Don’t.”

People do say this for a reason. Here is the story of your request going through the system:

In Is MySQL simple or complex (or both)?, Mike Kruckenberg asks:

So the question is . . . What happens to the people who really just wanted a simple, no frills, easy to use database. Based on the message, MySQL is no longer the database for them. Right?

MySQL 5.0 has gained a lot of features, and some of these features such as the procedural extensions, are as often a benefit for a project as they are creating problems for a project due to feature abuse. Having more features makes the product larger and more complex as well. So, yes, 5.0 is a lot more complex than older versions, and it carries around a lot of baggage that many traditional MySQL users don't need - and yet the same features are crucial for others.

This dilemma one reason why I look forward to the beginnings of modularization that we see with 5.1 - loadable fulltext parsers are only a beginning. Ultimately, I hope to see loadable storage engines together with loadable SQL syntax extensions (loading a storage engine also extends at least CREATE TABLE and ALTER TABLE syntax with the options needed for these extensions), and perhaps even loadable everything such as loadable stored procedure interpreters - CREATE SP LANGUAGE AS "/usr/lib/libperl.so" or similar, if you like it.

Growing in such a direction would make MySQL at the same time a simpler tool than before - in an ideal world, the core would have no features at all besides a DSO loader. At the same time it would turn MySQL into something more flexible and complex than ever before - a data storage and retrieval framework that can ultimately mix and match extensions as you need them for your project and contains only the code and functionality that you need. And if something is missing, just write it yourself. Write only what is missing and merge it with the rest within the framework.

(Disclaimer: /me is a MySQL employee, but the above is my vision of MySQL, and it may or may not differ from the company vision. :-)
(title with apologies to Murray Gell-Mann)