Lenz Grimmer's blog

Oracle/InnoBase announced the availability of the embedded version of InnoDB at this year's MySQL Conference & Expo, but I have not seen a lot of comments or reviews about it so far. Which surprises me, because I think this is a very interesting piece of technology!

In my opinion it might actually hit the sweet spot for application developers seeking an alternative embedded database solution. SQLite is nice and popular, but it seems to have concurrency issues when used in multi-threaded applications. An embedded MySQL server would be an alternative - this is what the Amarok developers decided to go with, for example. But this approach has its issues, too, especially the lack of a shared library version of libmysqld poses some challenges when distributing binaries.

This is where I think the embedded version of InnoDB might have an edge. It's pretty lightweight in comparison to a full-blown MySQL server, provides excellent crash-recovery (which is essential for desktop applications), transactions (useful in environments with high concurrency) and foreign key constraints. I'm not sure how important these are for embedded use cases, it probably depends on the complexity of the data to be stored. On the downside, Embedded InnoDB does not "speak" SQL. In order to store and retrieve values, you need to use the InnoDB API. See the chapter Concepts and Architecture for more details and an overview.

Another possible reason for the low popularity might be that it's currently not part of any Linux distribution (yet) and that Oracle only provides binary tarball packages for Linux and a Windows binary for download from the web site.

Therefore I've now created a spec file to build RPMs of Embedded InnoDB and added it to my repository on the openSUSE Build Service, which now provides Embedded InnoDB packages for a wide range of RPM-based Linux distributions. I hope that the spec file will be included in the next source distribution. I've posted it (and a patch to fix a few problems with the examples) to the newly created InnoDB mailing list, but to be sure I added a note to the Embedded InnoDB Forum as well.

We've now concluded our call for papers for the MySQL Developer Room at FOSDEM 2009 in Brussels, Belgium, which will be open on Sunday, 8th of February from 09:00-17:00.

We received some excellent proposals and I am very excited about the schedule. Here's the quick summary of the talks:

• Vladimir Kolesnikov: Practicing DBA's Guide to the PBXT Storage Engine
• Kris Buytaert: Monitoring MySQL
• Geert Vanderkelen: MySQL Cluster
• Roland Bouman: MySQL 5.1 Plugins
• Kaj Arnö: MySQL, powering and using Social Networks
• Ewen Fortune: Percona MySQL patches and the XtraDB storage engine
• Giuseppe Maxia: Boost performance with MySQL 5.1 partitions
• Jurriaan Persyn: Database Sharding

See the Schedule page on the MySQL Forge for the detailed agenda, including the detailed session abstracts and speaker bios. These talks will soon appear in the general FOSDEM schedule, too. If you are interested in MySQL and any of the topics above, consider visiting us in Room AW1.126! Participation and attendance is totally free, though the organizers happily accept donations and sponsorships.

In addition to the Developer Room, MySQL will share a project desk with the OpenSolaris community. We are still looking for at least one more volunteer that would help us with manning the desk! If you are interested in helping out (2 hours at a minimum), please drop me a line!

This article describes how to install the Drupal 6.2 CMS on MySQL 6.0, using the Falcon Storage Engine. The operating system is a default Ubuntu 8.04 "Hardy Heron" (x86) installation.

I will make a few assumptions here, in order to keep the instructions simple: a fresh OS install, no other MySQL databases or web services are running or have already been installed. Both MySQL and the web server are installed on the same host. You should be able to become root to install packages and to have access to the local file system and the system configuration.

This article will explain how to install and configure Apache/PHP, MySQL 6.0 and Drupal 6.2.

IBM plans a residency to write a Redbook about the IBM DB2 for i5/OS as a Database Engine for MySQL:

Seize this opportunity to be among the first, elite IBM System i professionals worldwide to gain hands-on and practical experience with the MySQL on i5/OS. Working alongside and exchanging knowledge with some of the top database experts in the System i, you will develop a competitive edge that will serve you well in successful service delivery to System i clients. MySQL is used extensively with opensource PHP applications. As our customers deploy PHP web applications, many of them are looking to run MySQL as well.

The residency will take place in Rochester, MN from April 21st to May 16th. This book is going to be a nice followup to the first Redbook titled "Discovering MySQL on IBM i5/OS", which was finished in December last year. If you are curious to learn how to install MySQL on this platform (or would like to prepare yourself for participating in this upcoming residency), you can get a PDF from there, too!

I initially wanted to attend this residency myself, as I have been involved in writing a Redbook before and really enjoyed the experience. However, recent changes forced me to retract my offer to volunteer. IBM covers the travel expenses and will take care of accomodation for the time of the residency. If you are interested and think you have the time and skills and you would like to expand your knowledge about MySQL on the i5/OS Platform, please contact me at firstname at MySQL dot com!

Even though MySQL is used to power a lot of web sites and applications that handle large binary objects (BLOBs) like images, videos or audio files, these objects are usually not stored in MySQL tables directly today. The reason for that is that the MySQL Client/Server protocol applies certain restrictions on the size of objects that can be returned and that the overall performance is not acceptable, as the current MySQL storage engines have not really been optimized to properly handle large numbers of BLOBs. To work around these limitations, these projects usually just store a reference to the object (e.g. a path name in a regular file system). This approach works around the limitations applied by the MySQL Server, but results in a disconnection and potential source of inconsistency between the database and the file system content. There was an interesting discussion about that topic on Sheeri's blog some time ago, with an excellent reply by Kristian, if you are curious to read more about this.

Thanks to the pluggable storage engine architecture of MySQL 5.1, we now see many independent storage engines emerging, one of them being PBXT by Paul McCullagh from SNAP Innovation. One of its strengths is going to be the handling of large binary objects in a performant way, so data objects can be stored in the database along with the other information related to it. To compliment this feature and to work around the limitations applied by the Client/Server Protocol, SNAP has now launched the Scalable BLOB Streaming Architecture for MySQL project, which intends to develop an architecture that accompanies the MySQL Client/Server protocol by providing an API to access and stream BLOBs stored in a MySQL database. See their press release (PDF) for the official announcement..

Paul will hold a BOF about this at the MySQL Conference and Expo this Tuesday at 19:30 in room San Thomas. So if storing and streaming large binary objects out of a MySQL Database is an essential requirement for your project, make sure to attend this session and provide Paul with your feedback and ideas!

Now that the source tree for the new Falcon Storage Engine is finally public, here's a quick HOWTO on how to compile the server from source. This procedure is described in more detail in the MySQL Manual. I assume you use Linux and have the required development toolchain installed.

You first should get the free BK client from http://www.bitmover.com/bk-client2.0.shar, unpack and install it:

$ wget http://www.bitmover.com/bk-client2.0.shar
--17:34:34--  http://www.bitmover.com/bk-client2.0.shar
           => `bk-client2.0.shar'
Resolving www.bitmover.com... 192.132.92.2
Connecting to www.bitmover.com|192.132.92.2|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 17,676 (17K) [application/x-shar]

100%[========================================================>] 17,676        33.56K/s

17:34:35 (33.50 KB/s) - `bk-client2.0.shar' saved [17676/17676]

$ sh ./bk-client2.0.shar
x - creating lock directory
x - creating directory bk-client2.0
x - extracting bk-client2.0/Makefile (text)
x - extracting bk-client2.0/bkf.c (text)
x - extracting bk-client2.0/demo.sh (text)

$ cd bk-client2.0
$ make
cc -O2 -Wall -Wno-parentheses    bkf.c   -o bkf
$ sudo install -m755 bkf /usr/local/bin

Now that the BK client is installed, we can create a local clone of the Falcon development tree:

$ bkf clone bk://mysql.bkbits.net/mysql-5.2-falcon mysql-5.2-falcon

This will now perform a check out the most recent source tree from the mysql-5.2-falcon repository (~83 MB of source code). You can now enter the newly created directory and start the build:

$ cd mysql-5.2-falcon
$ ./BUILD/compile-pentium-debug-falcon

If you have a x86_64 Linux system, use compile-amd64-debug-falcon instead. Depending on the performance of your system, this step will take a while, as it performs a full compile of the MySQL server and all related tools. Once the build completed with no errors, you can create a binary tarball distribution:

./scripts/make_binary_distribution

This should leave you with a tarball mysql-5.2.0-falcon-alpha-linux-i686.tar.gz that you can now install to, say, /usr/local:

$ tar zxvf mysql-5.2.0-falcon-alpha-linux-i686.tar.gz -C /usr/local
$ cd /usr/local
$ ln -s mysql-5.2.0-falcon-alpha-linux-i686 mysql
$ cd mysql
$ ./configure

The last command will install the privilege tables and start up the mysql server. You can now fire up the mysql client:

$ ./bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.2.0-falcon-alpha-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Now make sure that falcon has indeed been included:

mysql> SHOW VARIABLES LIKE 'have_falcon';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_falcon   | YES   |
+---------------+-------+
1 row in set (0.01 sec)

Looks good! Now it's time to actually create a table that uses the Falcon engine:

mysql> use test;
Database changed
mysql> CREATE TABLE names (id INT, fname VARCHAR (20), lname VARCHAR (20)) ENGINE=Falcon;
Query OK, 0 rows affected (2.84 sec)

mysql> describe names;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| fname | varchar(20) | YES  |     | NULL    |       |
| lname | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> INSERT INTO names VALUES (0, 'Lenz', 'Grimmer');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO names VALUES (1, 'Monty', 'Widenius');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM names WHERE lname='Widenius';
+------+-------+----------+
| id   | fname | lname    |
+------+-------+----------+
|    1 | Monty | Widenius |
+------+-------+----------+
1 row in set (0.00 sec)

Congratulations, you are ready to further test and explore this new storage engine! Have fun with testing it - we would appreciate your feedback and experiences! Please share your impressions via the Falcon Forum or report bugs via our Bug Database. Make sure to read the bug reporting guidelines on the MySQL Forge Wiki first!

UPDATE: The people at Bitkeeper updated the free BK client to version 2.0 after I wrote this. I updated the described procedure above accordingly. Currently, the repository seems to have a locking problem that only appears when using the free BK client. We are working with the BitKeeper team on resolving this issue.