New PBXT Beta release includes a pluggable storage engine for MySQL 5.1

I have just released the first Beta version of PrimeBase XT. Besides MySQL 4.1.21 support it is also available as a pluggable storage engine for MySQL 5.1. As far as I know, PBXT is the first full featured engine to be released in this form.

The engine can be downloaded (at http://www.primebase.com/xt) and built separately from MySQL. Currently, a reference to a MySQL 5.1 source tree is required to build the plug-in. However, in the future things will be even easier. Lenz Grimmer has told me that the plan is to include the required headers in the mysql-devel package. Then it will only be necessary to install the mysql-devel package in order to build the engine.

Still further in the future, once 5.1 is released, I will be able to make the PBXT engine available as a binary download for a number of platforms.

Once you have built the PBXT engine, you can install it by copying the binary, libpbxt.so, to the MySQL plug-in directory. Then enter the following command:

mysql> INSTALL PLUGIN pbxt SONAME 'libpbxt.so';

After this, you can create tables that use the PBXT engine by setting ENGINE=PBXT in the table declaration as usual. It's that easy!

I think this makes PrimeBase XT a great demo of the ease and power of the new pluggable storage engine API for MySQL 5.1.

PBXT Presentation at the Hamburg MySQL September Meetup

My thanks to Lenz Grimmer for the opportunity to present PrimeBase XT at the Hamburg MySQL September Meetup last Monday night. The meeting took place in a well equipped conference room belonging to the Chinese restaurant "Ni Hao" in Wandsbek. With over 20 attendees the venue was filled to capacity.

I must admit, I was a bit surprised (although I probably should not have been) by the highly technical nature of the questions and comments during the presentation and afterwards. As a result we had great time and spent quite a while discussing the various algorithms and implementation strategies used by XT.

I have posted the presentation as PDF on the PrimeBase XT home page. Among the topics of the presentation were: our motivation, a brief history of XT, future plans, design aspects and the results of some preliminary performance testing.

Of particular interest to many was the garbage collection mechanism. There was some concern as to just how much load the garbage collector would place on the server. When I presented a graphic showing the data throughput rate vs the record size written, someone asked why there was no dropoff in the performance when the garbage collector kicks in.

Indeed, performance is not affected adversely by the garbage collector. This is mainly due to the fact that the garbage collector is a low-priority thread, so it is immediately descheduled when it would affect foreground activity. This is not a problem as long as the system has enough disk space to be able to delay garbage collection until the peak access times are over. This can be contrasted with a conventional RAM garbage collector which is forced to run due to the fact that it manages a relatively scarce resource.

But besides this, the XT garbage collector generally does not require much CPU time. For example, it does not need to search for garbage, this is marked as such by the "sweeper" thread (see my white paper). I plan to discuss this topic in more details in a future blog, so stay tuned...

PBXT on schedule for Beta in September

Its a dirty job, but somebody's got to do it! I am talking about testing and debugging, of course, which is what I have been doing for the last month. But at least I have the satisfaction of knowing that XT is a lot more stable.

Over 95% of the "mysql-test-run" scripts now run through correctly with PBXT as the default engine. I have documented the changes made to the test scripts here: pbxt-test-run-changes.txt. This file also explains the major differences between MyISAM and PBXT, and lists all features are not yet implemented.

If you have compiled XT and would like to run the tests, then enter the following commands:

cd mysql-test
./mysql-test-run --force --mysqld=--default-storage-engine=pbxt

I have also compiled and tested XT on a multi-processor, 64-bit machine. I used sysbench 0.4.7 for multi-threaded, stability and performance testing. If you have downloaded and build sysbench, then you can measure the speed of XT using the following commands:

To create a test table containing 1000000 rows:

sysbench --test=oltp --oltp-table-size=1000000 --mysql-table-engine=pbxt --mysql-engine-trx=yes --mysql-user=root --mysql-socket=/tmp/mysql.sock prepare

Then, to perform a test with READ-ONLY queries:

sysbench --test=oltp --oltp-table-size=1000000 --init-rng=on --mysql-engine-trx=yes --oltp-table-name=sbtest --oltp-test-mode=complex --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on --oltp-read-only=on --max-requests=0 --num-threads=4 --oltp-dist-type=special --init-rng=on --mysql-db=sbtest --max-time=60 --mysql-user=root --mysql-socket=/tmp/mysql.sock run

But more interesting is a test with update transactions, as follows:

sysbench --test=oltp --oltp-table-size=1000000 --oltp-read-only=off --oltp-test-mode=complex --num-threads=2 --mysql-engine-trx=yes --init-rng=on --oltp-point-selects=0 --oltp-simple-ranges=0 --oltp-sum-ranges=0 --oltp-order-ranges=0 --oltp-distinct-ranges=0 --mysql-user=root --mysql-socket=/tmp/mysql.sock --oltp-table-name=sbtest --oltp-dist-type=special --max-requests=50000 run

In this test, each transaction performs 3 UPDATEs, a DELETE and an INSERT. According to my tests, SELECT speed is similar to InnoDB, but the update queries are significantly faster. It would be great if someone could confirm these results.

Judging by the stability of the current version, it looks like I will be able to achieve my goal of a Beta release next month. As I mentioned before, the only feature still to be completed for the Beta version is a pluggable storage engine for MySQL 5.1.

New PBXT Release 0.9.5: Smaller, Faster, Better...

The new release of PBXT is, in fact, smaller than the previous version. This is due to a major re-structuring of the code. The result is a more compact and direct implementation. With less code to execute and some major changes this version is also a lot faster.

The most significant change is that all files (except the transaction logs) are now associated with a particular table. All table related files begin with the name of the table, and can be easily identified. If a database is not being accessed you can copy a table by copying the associated files, or drop a table by deleting the files. However, the main reason for this change was to improve the speed of sequentially reading and writing.

Records are now divided into a fixed and a variable length component. The fixed length component is stored together with the handle in the record data file. The variable component is referenced by the handle and is stored in one of a number of data log files associated with the table.

This new format reduces the number of disk accesses needed to read, create and update a row significantly. Furthers details of changes in this version can be found in the release notes.

In this release I have also resolved almost all of the issues on my to-do list. This includes the items regarding auto-increment, caching, unique key checking and all the bugs reported so far.

The only feature still due for the Beta release in September is the 5.1 implementation which I will be working on next.

I would much appreciate any feedback, bug reports and performance test results!

PBXT featured on the MySQL home page

Last week I spoke to Lenz Grimmer, MySQL Community Relations Manager for Europe, ME and Africa, about the Users Conference, PBXT and the Community.

The interview has been featured under Articles on the MySQL home page.

For those of you that missed it, here is the link to the interview again:

Interview with Paul McCullagh, developer of the PrimeBase XT Storage Engine.

PBXT mentioned by CEO Mårten Mickos at the MySQL Users Conference Keynote

Ok, so here is a story:

I was standing with Giuseppe Maxia waiting to go into this mornings keynote when a smartly dressed man approached Giuseppe to congratulate him. The reason was that Giuseppe had won an award the day before for service to the MySQL community. So Giuseppe introduced the guy as Marten (the rest of the name I don't get, and he was not wearing a name tag).

Marten seemed to recognize my name and asked if I was a frequent visitor to the conferences. I said, "no, I am the PBXT guy". Thats when he realized why he had heard my name.

So then we chatted a bit, and he asked me how difficult it had been to write the engine, how long it had taken me and what my engine can do well. I said I was more concerned about what it could not do so well then what it could do well, but then I told him what was good in my performance tests, etc.

A little later Giuseppe and I moved into the hall for the keynote. The CEO was introduced and I started thinking, hey, isn't this the guy I just spoke to.

Well, I soon realized it was Mårten Mickos the CEO of MySQL whom I had just spoken to because he included a few details of our conversation in his keynote!

When discussing innovation in the MySQL community, this is what he said (at the risk of being immodest):

"I just spoke to the guy that programmed the PBXT engine: He developed the engine in the 6 months. I thought we had hired all the best database guys. Well there are still a lot of very clever people out there in the community."

(The last sentence is very true, of course).

And later he mentioned that PBXT is particularly good at handling variable length records.

Then on the slide which listed the storage engines: Falcon (by MySQL themselves), Solid (new partner) and InnoDB (new partner Oracle), he mentioned PBXT again, even though it was not on the slide.

So this is very encouraging because, with Falcon, Solid and InnoDB around, in any other conventional company PBXT would be sidelined. I know this from experience. This is because Falcon, Solid and InnoDB are built on existing code developed by well established companies.

So why are things different with MySQL. Because the community is extremely important to MySQL! I mean, just think about it: MySQL was build by the community!

PBXT and the Community

I would just like to thank all those who have downloaded and tested PBXT for their help and suggestions. I have updated my to-do list to include all the issues raised so far.

Although my own performance tests indicate that PBXT has great potential, the more general MySQL performance tests show that the implementation is still in its early days.

Of course, there is still a lot to be done. Everybody knows there is a big difference between a running program and a production system. It is the most challenging part of writing a program. But also the most rewarding because it will eventually see the program being put to useful work.

Some people at MySQL have expressed surprise that an effort to develop a new engine has come out of the community. Personally I think I was more surprised that I am already part of the community!

I guess I am used to the turf wars that pervade most other large companies, so I really was not expecting the warm welcome I have received from everybody.

Thanks for making PBXT part of the community!

PBXT: May the source be with you!

Now that I have PBXT running under both Mac OS X (Power PC) and Linux, I have posted the source code. Here is the download URL:

http://www.primebase.com/xt/download/mysql-4.1.16-pbxt-0.9.1.tar.gz

As the name suggests, the engine has been integrated into MySQL 4.1.16 (the nightly build of November 4, 2005). I have labeled this version Alpha since it is almost feature complete, but please check my to-do list before you build and run it:

http://www.primebase.com/xt/download/pbxt-to-do.txt

Here are some brief instructions for building MySQL with PBXT:

http://www.primebase.com/xt/download/how-to-build.txt

You can send any questions, suggestions or bug reports to me directly: paul.mccullagh(a)primebase.com.

My initial tests show that the performance of PBXT under Linux/Intel is very promising, to put it modestly. More about this later.

Concerning: PBXT, MySQL and Mountains

Is PrimeBase XT (PBXT) just another transactional engine for MySQL? I think (hope) not! But, even if it was, it would still be justified considering the current lack of an independent (i.e. not owned by a monopolist) transactional engine. But I started developing PBXT at the beginning of last year for a much more basic reason, after all I had no idea what Oracle was about to do.

I started programming PBXT simply because it was there!

But wait, you say, "we climb mountains because they are there, we don't program software because it is there", because it isn't (before we start). Well I think many programmers can relate to that statement anyway. Programming is much like climbing a mountain. You start out with great enthusiasm, wanting to do things better and quicker than before. But as you get higher, the air gets thinner. You struggle on and as you approach the top it gets tougher and tougher (we all know how long it takes to finish that last 10%)!

But when you reach the peak, the summit, there is nothing like it! You have conquered the beast of chaos and doubt, the air is fresh and you can see for miles. Inspiring, isn't it? Well I think so.

OK, so programming is like climbing a mountain, but how can you climb a mountain that isn't there? Well, it is there, actually, isn't it? Right there, in your head.

And so it was with PBXT. After quite a few years of programming databases I had some ideas (questions actually), for example: what would it be like if the database wrote only sequentially? Must a database always write twice? Can we eliminate read locks? Is it possible to commit or rollback without doing any work? Is it possible to startup without recovery?

Using some known techniques and combining them with a few new ideas, I have found some answers to these questions. You can read about it in my White Paper at http://www.primebase.com/xt.

So have I reached the top? Of course not, nobody ever reaches the top! Seriously though, I have a few things on my to-do list, but mostly PBXT is untested. So I am thankful that testing is the first area in which I have been offered help (thanks Ronald) and also from MySQL themselves (thanks Brian). Any more help would be welcome and we'll turn this into an expedition.

I plan to keep the address above posted with the latest information, versions, etc. and we'll just take it from there, one step at a time...