In our March DBJ article we talked about some of the storage engines to choose from with MySQL.  With it’s plugin storage engine architecture, you have a range of options.  In our April article we continue to discuss a further selection of storage engines, and what features they offer to the DBA and database architect.

Database Journal – Exotic Storage Engines

I have been talking about this for a while, now at last I have found the time to get started! Below is a picture from my 2008 MySQL User Conference presentation. It illustrates how engine level replication works, and also shows how this can be ramped up to provide a multi-master HA setup.


What I now have running is the first phase: asynchronous replication, in a master/slave configuration. The way it works is simple. For every slave in the configuration the master PBXT engine starts a thread which reads the transaction log, and transfers modifications to a thread which applies the changes to PBXT tables on the slave.

Where to get it

I have pushed the changes that do this trick to PBXT 2.0 on Launchpad. The branch to try out is …

Martin Scholl (@zeit_geist) has started a new project based on the PBXT storage engine: EPBXT - Embedded PBXT! In his first blog he describes how you can easily build the latest version: Building Embedded PBXT from bzr.

The interesting thing about this project is that it exposes the "raw" power of the engine. Some basic performance tests show this really is the case.

At the lowest level, PBXT does not impose any format on the data stored in tables and indexes. When running as a MySQL storage engine it uses the MySQL native row and index formats. Theoretically it would be possible to expose this in an embedded API. The work Martin …

Following on from our earlier announcement, Paul McCullagh has responded with the answers to your questions - as well as a few I gathered from other Percona folks, and attendees of OpenSQL Camp. Thank you Paul!

What’s the "ideal" use case for the PBXT engine, and how does it compare in performance?  When would I use PBXT instead of a storage engine like MyISAM, InnoDB or XtraDB?

Unfortunately it is not possible to point to a specific category of applications and say, "PBXT will be better here, so try it".  PBXT is a general purpose transactional storage engine, designed to perform well on a broad range of tasks, much like InnoDB.  However, PBXT's log-based architecture makes performance characteristics different to both MyISAM and InnoDB/XtraDB. Tests show that PBXT's performance is similar to InnoDB but, depending on your database designed and the application, it can be faster.

PBXT is a …

You can now yum (RPM) or apt-get (DEB) MariaDB 5.1.39, courtesy of OurDelta and in close cooperation with Monty Program Ab. Simply follow the info on the CentOS, Debian or Ubuntu pages.

(note: give the mirrors some hours to sync up)

Quick overview

• See the Release Notes for relevant bugfixes.
• Drop-in replacement for stock MySQL 5.0 or 5.1 (If you’re coming from stock MySQL, see the MariaDB extras/differences overview). …

See the OurDelta blog for details of this release. RHEL/CentOS packages also coming.

You can now apt-get your way to MariaDB 5.1, courtesy of OurDelta and in close cooperation with Monty Program Ab. To get started, simple follow the info on the Debian and Ubuntu pages.

Quick overview

• For MariaDB we use different repository directories to ensure that you can’t accidentally upgrade or revert major versions without you explicitly choosing to do so.
• At this point we have Ubuntu Hardy, Intrepid, Jaunty and Karmic for you, as well as Debian 4 (Lenny). Etch (Debian 4) is waiting on a small fix (thanks to Antony Curtis for helping with that).
• The package names start with mariadb*, except …

We’ve been able to do MySQL 5.1 binary tarballs for a bit now (great working together with Kristian Nielsen of Monty Program), but packages are bit more tricky. Peter has been working on Debian/Ubuntu while I’ve focused on RH/CentOS. The following is from an OurDelta (trial build run) RPM install on CentOS 5 x64:

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.38-maria-beta1-ourdelta (OurDelta - http://ourdelta.org/)

mysql> CREATE TABLE test.t1 (i int) ENGINE=PBXT;
Query OK, 0 rows affected (0.10 sec)

mysql> SHOW CREATE TABLE test.t1\G
*************************** 1. row ***************************
Table: test.t1
Create Table: CREATE TABLE `test.t1` (
`i` int(11) DEFAULT NULL
) ENGINE=PBXT DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO test.t1 values (1);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM test.t1;
+------+
| i    |
+------+
| …

I have just released PBXT 1.0.09 RC3. Besides bug fixes (details in the release notes), this version includes 2 Beta features:

• XA/2-Phase Commit support
• Native online backup Driver

XA support has been around MySQL for quite a while, and we all know of it usefulness, for example when sharding. So I was surprised to find a bug in the XA recovery: Bug #47134. Contrary to what is reported, the crash can also occur when using XA with just the default engines installed, so watch out for that one (the good news: the bug fix is simple).

Online backup is really cool! I have heard that it may soon be released in a coming version of 5.4, so lets hope that this is true.

In a little test, I did a backup of a 10GB database in 49.26 seconds! …

Vladimir will be giving a presentation on PBXT at the FrOSCon 2009 in St. Augustin, near Bonn in Germany tomorrow:

PBXT: Technology trends that affect your Database
Room: C120/OpenSQLCamp
Time: 22 Aug 2009, 18:15 - 18:45

The talks is packed with interesting information about how the design of PBXT handles the major technological challenges of the future, including multiple cores, lots of RAM and solid state drives.

If you are in the area, check it out! :)