Monday, August 07, 2006

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.