Saturday, March 15, 2008

PBXT, or how to really exploit the plugin architecture

MySQL 5.1 introduces the plugin interface, a series of API that allow the load and unload of server components at runtime.
The idea behind this architecture is to make MySQL server really expandable.
Surprisingly, the latest engines being developed in house (Falcon and Maria) are not implemented using the plugin technology. The best example of how to take advantage of this interface comes from the outside. Paul McCullagh's PBXT is a transactional engine that can be loaded at run time.
To use it, you can either download the pre-compiled plugin library, and load it into your server, or get the source code and build the whole server from scratch.
Of course, the most interesting option is the pre-compiled binary. I downloaded the latest 5.1.23 binaries for Mac OS X, and the corresponding plugin library for my system.
I installed the server using MySQL Sandbox, and then ran this command:
show variables like 'plugin_dir';
+---------------+----------------------------------+
| Variable_name | Value |
+---------------+----------------------------------+
| plugin_dir | /Users/gmax/opt/mysql/5.1.23/lib |
+---------------+----------------------------------+
This command tells me in which directory I should copy the library I just downloaded from primebase site.
$ cp ~/Downloads/libpbxt.so /Users/gmax/opt/mysql/5.1.23/lib
Now I can load the component.
INSTALL PLUGIN pbxt SONAME 'libpbxt.so';
Query OK, 0 rows affected (0.03 sec)

select * from information_schema.engines where engine='pbxt'\G
*************************** 1. row ***************************
ENGINE: PBXT
SUPPORT: YES
COMMENT: High performance, multi-versioning transactional engine
TRANSACTIONS: YES
XA: NO
SAVEPOINTS: NO
That's it! The additional engine is up and running! No compilation needed.

A rather unorthodox test follows. With the help of Patrick Crews I created a test employees database with about 4 million records in 6 tables. Not huge, but not trivial either.
I loaded this database using MyISAM, InnoDB and PBXT, and compared the loading times. Not surprisingly, MyISAM and InnoDB are much faster than PBXT.
Engine          Loading time
------------- ------------
MyISAM 1m08.546s
Innodb 1m46.622s
PBXT 3m20.842s
However, the database includes a test suite (If you have been following my blog, you shouldn't be surprised about that), which calculates the number of records and a global CRC for all the tables. This check includes queries like the following:
SET @crc= '';
INSERT INTO tchecksum
SELECT @crc := SHA1(CONCAT_WS('#',@crc,
emp_no,birth_date,first_name,last_name,gender,hire_date))
FROM employees ORDER BY emp_no;
INSERT INTO found_values VALUES ('employees', (SELECT COUNT(*) FROM employees), @crc);
Heavy stuff. I don't know how much relevant these queries are in real world applications, but that is what I had at hand without squeezing my brains, and this is the result of running the whole test that calculates the global CRC of 4 million records:
Engine          Checking time
------------- -------------
MyISAM 0m26.312s
Innodb 0m31.627s
PBXT 0m26.230s
In this particular test (repeated three times with two different operating systems), PBXT is faster than both MyISAM and InnoDB.
The engine is still green, and perhaps my test is silly, but this start is promising!

No comments: