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.
MyISAM is frequently described and marketed as providing fast
reads when it really provides fast index and table scans. This is
a more narrow use case as fast reads implies great performance
for most queries while fast scans implies great performance for
single-table queries that are index only or do a full table
MyISAM caches index blocks but not data blocks. There can be a lot of overhead from re-reading data blocks from the OS buffer cache assuming mmap is not used. InnoDB and PBXT are 20X faster than MyISAM for some of my tests. However, I suspect that mutex contention on the key cache is also a factor in the performance differences.
While there are many claims about the great performance of MyISAM. There are not as many examples that explain when it is fast. Alas, the same marketing technique is being repeated with NoSQL to the disadvantage of MySQL.
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 …[Read more]
(note: give the mirrors some hours to sync up)
- 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.
- 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 | +------+ | …[Read more]
I have just released PBXT 1.0.09 RC3. Besides bug fixes (details
in the release notes), this version includes 2 Beta
- 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
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! …