Thursday, December 18, 2008

PBXT goes RC!

With all the booha about MySQL not being ready for GA, it makes me almost afraid to announce, ahem, ... and PBXT is, ehr, RC.

It has been just over a year now since I started developing the fully durable version of PBXT. Before that, PBXT was Beta. After that, it was Alpha again.

Now we have 2 solid Beta versions behind us, Vladimir and I have fixed all known bugs for this version, including quite a number of foreign key bugs. We have all 259 mysql-test-run tests that were adapted for PBXT (and a bunch of our own) running through without any errors on 4 platforms: Mac OS X, Linux 32-bit and 64-bit, and Windows. Our buildbot is giving us a green light, at last!

Besides this we have done crash tests, load tests and crash and load tests (I mean recovery)! And maybe most important, we have it ticking away in a very demanding OEM product called TeamDrive. And it is doing it 20% to 30% faster than the "most commonly used" transactional storage engine.

Then we also have PBXT 1.0.07 RC compiling and running with MySQL 5.1.30, MySQL 6.0.8 and Drizzle! And it compiles on Linux, Windows, Mac OS X, FreeBSD, netbsd, OpenSolaris and Solaris (last patch pending on this one), whew!

So what's next?

Well next stop is GA, and I would like to have it done before the MySQL conference. Heard that one before? Nah ;)

Seriously though, we are not planning to add anymore features to this version so there is only one way to stop us: by testing and reporting bugs! Right here: https://bugs.launchpad.net/pbxt

Would be much appreciated! :)

BTW, the version is available, as usual, from http://www.primebase.org/download, or get it straight from Launchpad.net:

bzr branch lp:pbxt/1.0.07-rc

Monday, December 15, 2008

xtstat: Tells you exactly what PBXT is doing!

I have created a new tool, called xtstat, for analyzing the performance of the PBXT storage engine.

The way it works is simple. PBXT now counts all kinds of things: transactions committed and rolled back, statements executed, records read and written, tables and indexes scanned, bytes read, written and flushed to various types of files: record, index, data logs, transaction logs, and so on.

A SELECT on the system table PBXT.STATISTICS (or INFORMATION_SCHEMA.PBXT_STATISTICS if PBXT was built inside the MySQL tree) returns the current totals of all these counters. xtstat does a SELECT every second on this table and prints the difference. In this way, you can see how much work PBXT is doing in each area.

There are currently 48 different statistics:


To ensure all this counting does not itself cost any performance, each thread counts for itself, so no locking is required. The SELECT on STATISTICS then sums over all running threads.

The default output of xtstat is 201 characters wide (281 characters are required to display all statistics), but using the --display option you can specify exactly which statistics you would like to look at.

Here is an example, of the default output with some of the middle columns removed:


To display large byte values, such as data read from the data log files (data-in column), xtstat uses K (Kilobytes), M (Megabytes) and G (Gigabytes) to ensure the values don't overflow the column space. Counts like the number of rows inserted (row-ins column) use, t (thousands), m (millions) and b (billions) to keep things lined up.

Using xtstat it is possible to ask questions like:

How efficiently is group commit working?

Notice here that the number of transaction commits in this example (xact-commt column), is larger than the number transaction log flushes (xlog-syncs).

Is their enough index cache?

Displaying the index stats shows that index cache misses (ind-miss column) are at about 30%, and that the server is reading 8MB per second from the index file. So increasing the index cache would result in better performance.

How much time is being spent flushing files?

In the example above you can see that xtstat displays the number of flushes (syncs) and the time spent in flushing in milliseconds (ms and msec columns).

When you build PBXT with make install, xtstat will be installed in the same directory as other MySQL tools such as mysqladmin, mysqldump, etc.

For more information on xtstat, just enter:

$ bin/xtstat --help

All in all, I think xtstat will be very useful in analyzing and tuning the performance of the engine.