MySQLBlog

As the years go by, data pages on disk have to get bigger. 16 KB pages were good for databases in the late 1990's, but today's data pages should probably be 64 KB. Page sizes go up over time because memory gets cheaper, and disks get much larger, but disks do not get very much faster.

In 1997, a Megabyte of memory cost $15, but today it costs 10 cents. A new SCSI drive held 4 GB then but 146 GB today. If cost is held constant, today's machine has 150 times the memory and 36 times the storage of a machine from ten years ago, but the performance of disks is only about 3 times better. Disks are still about 3 inches across and turn at about 10K RPM; today's best disk might deliver about 180 I/Os per second (IOPS), compared to 60 IOPS in 1997. And since the disk doesn't move much faster, it makes sense -- given the cheap storage both on disk and off -- to transfer a bigger chunk of data back and forth with every disk access.

There's a rule of thumb that data engineers can use to judge cache sizes; it says a system should have enough RAM to cache any data that will be reused within about five minutes.

It's Summer of Code time again -- a chance for students to do some real-world programming, contribute to an open source project, and earn $4500 over the summer. You have just one week, March 24 - 31, to submit an application.

Over on MySQL's Summer of Code Ideas page, I have listed some projects around MySQL Cluster and mod_ndb. They require some experience with C and C++. I'm hoping they will be great summer of code projects:

• You get to work with Apache httpd -- the world's most popular web server since 1996, and a classic example of modular and portable software architecture.
  
  
• You get to work with cutting-edge technologies (like JSON, REST, MySQL Cluster, and Memcached) that will be more important a few years down the road than they are today.
  
  
• You get to make an important contribution to a project that's still fairly young and small.
  

I'm looking forward to the summer!

A quick note on mod_ndb: the official 1.0 release, and the first beta of 1.1, are now available for download at mod-ndb.googlecode.com. Mod_ndb is an Apache web server module that provides a Web Services gateway into MySQL Cluster. Its documentation is at MySQL Forge.

Mod_ndb supports MySQL 5.0 and 5.1 along with Apache 1.3, 2.0, and 2.2, and it is distributed in source code form only – you build it for your particular versions of Apache and MySQL. One nice improvement in 1.1 is that it is easier to build mod_ndb for Apache 2.0, as the build system no longer has any dependencies on the old version of apxs.

The mod_ndb 1.0 release candidate is now available from mod-ndb.googlecode.com. mod_ndb is a "web services node" for MySQL Cluster: an NDB API node that runs as an Apache web server module and handles requests over HTTP. It supports MySQL Cluster 5.0 and 5.1, and Apache 1.3, 2.0, and 2.2.

A few months ago, I felt that mod_ndb's configuration parameters were getting too complex to remember, and realized that a SQL-like configuration language ("N-SQL") would be more intuitive. It's not quite as simple as SQL -- it does not have an optimizer, so it still requires you to dictate an access plan -- but it is more concise and readable than the strict Apache-style configuration that mod_ndb started with.

A lot of other details have fallen into place in the last two months, especially regarding error handling, HTTP response codes, and documentation, so the newest release is the release candidate and will hopefully become mod_ndb 1.0.

Often enough, it seems like you can begin working on software project with the assumption that you won't need to, say, design a little language and build a parser for it -- and yet, before long, that's exactly what you end up doing.

In the case of mod_ndb, my web services gateway for MySQL Cluster, the little language turns out to involve page-description -- it describes how to take a table of results from a database query and present it within an HTTP response. Inside an httpd.conf file, it looks something like this:

<OutputFormat XML> 

   Table  scan = '<NDBScan>\n$row$\n...\n</NDBScan>\n' 

   Row    row  = ' <NDBTuple> $attr$ \n  ...  </NDBTuple>'

   Record attr = '<Attr name=$name/Q$ value=$value/Qx$ />' or '<Attr name=$name/Q$ isNull="1" />'

</OutputFormat> 



<OutputFormat JSON> 

   Table  array  = '[\n $object$,\n ... \n]\n'

   Row    object = ' { $pair$ , ... }' 

   Record pair   = '$name/Q$:$value/qj$' or '$name/Q$:null'

</OutputFormat>

The three data types are a Table, representing a result table, a Row of data, and a Record, which (though it's probably not a very good term for it) is a column name/value pair.

The record description breaks down to $name$ and $value$, with some flags: $value/Q$ means to put the value in quotes; $value/q$ means to quote it only if it's character (not numeric); $value/j$ means to encode the value JSON-style with backslash escapes, while $value/x$ means to encode it XML-style using HTML entities (> and so forth).

A record description contains both non-null and null varieties. A row description describes how to loop over the columns in the row, and a table description describes how to loop over the rows of the table. Everything except the $variables$, the elipses, and the \n is plain text.

The format is (I hope) concise and intuitive, and I can parse it with a simple little two-pass compiler. First read each line and build a symbol table; then walk the table and parse each format. It's not exactly how I had intended to spend the weekend, but it mostly works. Now I've got three weeks to test it and get the bugs out before the MySQL Conference.

Last august, I started wanting a new API for MySQL Cluster that could solve a few problems. Frist, if you were to use MySQL Cluster in a LAMP application, you would be turning a two-layer architecture (Apache and MySQL) into three layers (Apache, MySQL, and NDB). Adding that extra tier requires more hardware and means slower response times. Second, the one way around this was to write native NDB API code in C++, but learning the API and developing low-level code can require a big commitment of time. I wanted an easier way to eliminate the extra layer.

My solution is called mod_ndb, and it is an Apache module that runs inside a web server and connects directly to the MySQL Cluster data nodes. It supports a "RESTful" API -- applications make GET, POST, and DELETE requests over HTTP, and mod_ndb provides appropriate HTTP responses. Because it is HTTP, responses can be cached by proxies in a well-defined way, and data can be delivered "straight from the database to the browser" in an AJAX application.

The code is now available at http://code.google.com/p/mod-ndb, with documentation hosted on the MySQL Forge Wiki at http://forge.mysql.com/wiki/Mod_ndb.

While I don't have lots of performance measurements, the benchmarks so far show that if you simply replace a mysqld with an httpd and query it using HTTP instead of SQL, mod_ndb has performance very similar to or slightly better than mysql. (However, you have to use persistent HTTP, so that if you are making five queries you can make them all in one TCP connection, rather than five). And if mod_ndb allows you to improve the overall architecture, a very big performance improvement can be gained -- maybe even a factor of ten.

The current release is not quite beta: some HTTP response codes need to be corrected, along with some formatting details of particular data types. Also, for now, mod_ndb can provide output either in JSON or in a simple XML format, but the big improvement I hope to make before the MySQL Conference next month is to support user-defined output formats. I'll be presenting mod_ndb at the conference on Tuesday, April 24th at 5:30 PM.

Next Monday's MySQL Meetup in San Francisco should be lots of fun. We'll meet at JasperSoft (303 2nd Street, Suite 450) at 7:00 PM, Mon 11 Sep., and have special guest Teodor Danciu, the creator and architect of Jasper Reports. The guys from JasperSoft will also show off some freely available reports for Bugzilla, and I'm going to give a quick 5-slide presentation on the MySQL Roadmap.

Most web applications use something like five languages. One of these is a big programming language, like Java or PHP, running on a server, and the other four are HTML, CSS, Javascript, and SQL.

SQL and HTML are "declarative": they let you state what you want, and the browser (for HTML) or database (for SQL) has to decide how to cook it up for you. This makes them easy languages for people to grasp; it also ensures that the browser and database server will be exceptionally large and complex pieces of software. (And apparently programmers are never really satisfied with declarative languages, so database servers grew stored procedures and browsers got Javascript.) CSS is a "little language," a vocabulary for design, more lexicon than grammar. Over the last ten years, a lot of presentation code has moved out of HTML into CSS, while today's HTML is full of DOM signposts ("id" attributes and "div" tags) that didn't used to be there.

The full-fledged language at the center of a web app -- maybe PHP, Java, or Ruby -- is the one language in the bunch that is well-suited for a large software engineering project. Ironically, with AJAX, the user interface on the browser gets better, but the role of this central language in the whole scheme of the application gets smaller and smaller.

Web apps still aren't as good as desktop apps. They are tooday's dominant software platform (or at least the one with the most momentum.) People are throwing out perfectly good CRM systems so they can pay per-employee, per-month, for Salesforce.com -- which, on it's own part, sees some big advantages: nobody there has to press CDs, help customers install the software, or worry about people who run old versions and refuse to upgrade. These advantages are all fortunate side effects of the design of the Web, but they are not fundamental to it. The World Wide Web was intended to be a big network of hyperlinked global public information. If it also revolutionized the distribution of data-driven business software, that's just an afterthought. Out of all those languages, only HTML was in the original plan.

If, today, you really set out to create an internet application framework on top of HTTP, would you build it around a hodgepodge of dissimilar interpreted languages? As web UIs get better, can't you imagine that the web development stack will get even messier? Netfrastruture -- Jim Starkey's last project, which MySQL AB now owns -- was an interesting experiment in simplifying the server side of web apps. Maybe the rest of the stack can be cleaned up some, too.

My talk at the MySQL Users Conference is officially called "Advanced User Defined Functions in MySQL 5," but if it weren't too late I'd change the name and call it "SQL as a Glue Language."

Sometimes logic should be as close to the data as possible; this is why triggers exist in SQL. MySQL 5 gives you a full-fledged programming language (SQL/PSM), lots of utility functions, and (this is where the "glue" comes in) the ability (using UDFs) to plug into existing C and C++ libraries with ease.

I hope to see you all there Tuesday at 4:30, and then at Jeremy & Hartmut's UDF Hackfest Tuesday night.

The Spread Toolkit is one of my favorite Open Source projects. Spread lets you send multicast group messages between applications. It provides reliable, ordered, asynchronous message delivery, and it's efficient, works on large wide-area networks, and can survive and recover from network failures.

What kind of things could you do if a MySQL server could send and receive messages using Spread? I started a new project, the MySQL Message API, to find out. For a few ideas to get started with: if you want to notify a bunch of application servers whenever a particular table changes, you can write a simple trigger to send them all a message. Or if you have lots of different specialized database servers powering your 30-million-user social networking site, you might want to send messages from one server process them (with stored procedure code) on another.

The Message API is a set of MySQL UDFs that enable you to join message groups and to send and receive messages. The first beta release is available now on MySQL Forge. I have been working with the code and testing it pretty extensively for the last three months -- but only on Mac OS X and FreeBSD. I wouldn't be surprised to hear about problems compiling and linking the code on Linux, Solaris, and other platforms, so I'm especially hoping to get some feedback about those.

I mentioned a few weeks ago that Jan Kneschke has posted some MySQL user-defined functions -- available here -- for communicating with memcache. I want to outline an example of how to automate cache maintenance by calling these functions from triggers. In a big web application, you have critical high-performance code (which, if you are running memcache, is no doubt already cache-aware), but you probably also have a bunch of little scripts for performing sundry administrative tasks. With triggers in the database, you can keep these scripts the way they are, and even run ad hoc SQL statements, without worrying about leaving inconsistent records in memcache.

This all requires running MySQL 5 with dynamically loaded UDF code -- but of course you don't have to upgrade the whole database farm to MySQL 5, just a single replication slave to run the triggers. On this one slave, create AFTER DELETE and AFTER UPDATE triggers that will delete a record from memcache whenever it gets changed or deleted in the database, like this:

CREATE trigger cache_expire_delete AFTER DELETE on table1 FOR EACH ROW DO memcache_delete("memcache_host:11211",OLD.pk) ;

Here are the details.

MySQL's User-Defined Functions (UDFs) allow you to link code libraries into the database server, and I was excited to learn this week that Jan Kneschke has published a UDF which uses Sean Chittenden's libmemcache library to connect to memcached. My next post will be all about using the memcache UDFs along with triggers in MySQL 5. First, though, I wanted to clarify a little bit about how to compile UDFs on Mac OS X.

OS X is based on the Mach executable format, and Apple's compiler is a slightly different version of gcc than anyone else's. The MySQL manual tells you to use gcc -shared to compile a UDF, like this:

gcc -shared -o udf_example.so udf_example.cc

However, Apple's gcc doesn't support the -shared option. So how do you create the shared library? On OS X, I use one command to compile the UDF source file, and then a second command to link it as a loadable shared object. You can compile Jan's memcache UDF using gcc, like this:

cc -c -DDBUG_OFF -O2 `mysql_config --cflags` udf_memcache.c

And then, to create the shared object file, use Apple's libtool utility:

libtool -dynamic -o udf_memcache.so udf_memcache.o -lmemcache -lc

This creates a udf_memcache.so file that can be loaded into the "max" version of a MySQL server.