The coolest feature of MySQL/MariaDB, in my opinion, is the concept of the storage engine, or “motor” as we commonly call it here in Brazil. I like to compare the storage engine to those famous watches with interchangeable bands. The core of the watch remains the same, but the look and feel can be completely different. And anyone can create their own watch band and attach it to their watch.
That’s how storage engines work. Companies, communities, groups, or even individuals can create a specialized storage engine with new features, better performance, or tailored for specific tasks, and simply plug it into MySQL/MariaDB.
The practical result is that you continue using standard ANSI SQL commands and the entire range of MySQL/MariaDB features, but the data is read and written differently—this task is handled by the storage engine. This can translate into new functionality or, ultimately, a performance boost.
PBXT
PBXT was developed by the company PrimeBase Technologies, led by Paul McCullagh. Its goal is to be a high-performance transactional storage engine, designed for OLTP (Online Transaction Processing) operations. It is free of charge and serves as an alternative to InnoDB. Paul’s main promise—being always very helpful and accessible—was to deliver a conceptually unique storage engine, with high performance as a core focus.
Warming Up the Engines
Forget about using PBXT on machines with limited memory. If InnoDB loves memory, PBXT “loves it muuuch more!” And the default configuration it comes with will only frustrate you — you’ll be more than disappointed with its performance. To make proper use of PBXT, pay special attention to the following configurations (magic variables) before you start playing around:
– pbxt_record_cache_size: I love these self-explanatory names… This cache (or buffer) stores record pages from all PBXT tables on a given mysqld server. Set it as large as possible (within your physical resource limits). The larger your tables are, the larger this setting should be. And the larger this setting is, the better performance you’ll get. The default is 32MB, and believe me — you won’t be happy with that size.
– pbxt_log_cache_size: This is the transaction cache (or buffer). Therefore, the larger your transactions and/or the higher the volume, the larger this cache should be to accommodate active transactions. This cache primarily affects writes. Its default size is 16MB. Paul himself advised me to use a fixed size of 128MB when I told him about the benchmark I was going to run.
– pbxt_index_cache_size: This stores the index pages—roughly speaking, it’s similar to key_buffer_size in MyISAM. The default is a dismal 16MB. Still, according to the “father of the engine,” you should use something between 1/3 and 1/2 the size of the record cache. Naturally, this will greatly depend on the number of indexes involved.
I always like to remind people of MySQL’s motto (so that Oracle never forgets it): “to be the best database, and to be accessible and available to everyone.” I find it fantastic when I come across websites developed by teenagers that use MySQL. Just like sites such as Wikipedia that use exactly the same database. This is digital inclusion accessibility in the best concept and definition of the words.
MySQL/MariaDB is easy, simple, and efficient. Detractors aside. It works for those who know everything, or for those who pretend to know, like me writing this. It’s unacceptable (sorry Paul, sincerely) that a storage engine ships with default settings that will not function properly. That’s homework for the folks at PrimeBase Technologies.
The Rally
Anyone who has read a benchmark or attended my performance tuning classes will be familiar with my trusty test table. Here is the structure used:
CREATE TABLE `telpbxt` (
`cpf` bigint(20) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`log` varchar(100) DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
`comp1` varchar(100) DEFAULT NULL,
`comp2` varchar(100) DEFAULT NULL,
`neighborhood` varchar(100) DEFAULT NULL,
`city` varchar(100) DEFAULT NULL,
`cep` varchar(100) DEFAULT NULL,
`ddd` varchar(100) DEFAULT NULL,
`phone` varchar(100) DEFAULT NULL,
KEY `idx2` (`name`(10))) ENGINE=PBXT DEFAULT AVG_ROW_LENGTH=94 CHARSET=latin1;
Special attention to AVG_ROW_LENGTH, which represents the average length of your rows (or records). It is essential to tell PBXT the average size of your rows if you want to move faster.
This table has 10,300,000 rows. Nice, huh? Hey, no complaining about all those varchars. It’s a table for didactic purposes.
Disk usage: In MyISAM the same table consumed 960MB, in InnoDB 680MB (using Barracuda). In PBXT it was 1.1GB. That’s practically double the storage space used by the latest version of InnoDB. Keep in mind that the default InnoDB uses a less efficient data compression method (Antelope).
Some people criticized this test for the size and type of the table. Well, my commitment is to my community colleagues. Does it make sense to test a transactional storage engine with tiny tables of 1 million rows? I don’t think so. So, it will take some hits!!!
It’s important to emphasize that the tests were carried out on my good old MacBook, not on server-class hardware. However, during the tests, everything possible was cleared from memory. And the full majesty and plenitude of my endless 4GB of RAM were dedicated to the tests.
The Performance
The comparison was made against the MyISAM and InnoDB engines, both “tuned” to excellence (if I ever managed to achieve that). This means that when I tested MyISAM, I configured the server to run 100% MyISAM, the same for InnoDB and PBXT. This way, I ensured that each storage engine was well supported regarding memory and hardware requirements.
As expected, MyISAM outperformed PBXT by a wide margin in all aspects. InnoDB was also faster than PBXT in every aspect—except for inserts, surprisingly.
For inserts, it was on average 12% faster than InnoDB. Here are some numbers:
250,000 rows were inserted in 4.8 seconds, 500,000 in 16.1 seconds, 1 million in 39.3 seconds, and 3 million in 161.4 seconds. For a MacBook, isn’t that good? I think it’s excellent. This proves that PBXT, as a transactional storage engine, is on a good path.
A simple SELECT COUNT(0) was answered in 5.48 seconds compared to 6.5 seconds for InnoDB. However, a SELECT cep, COUNT(0)… GROUP BY 1 took 11 minutes versus 7 minutes for InnoDB. That’s a big difference. For context, both tables had no indexes.
Creating the index took 5 hours on PBXT compared to 20 minutes on InnoDB.
Reading is not the strong point of this storage engine, at least not on a MacBook with no more than 2.5GB available. Talking with Paul about the results, he assured me that on a more powerful machine with plenty of memory, PBXT would perform better. Hey, but I ran the same tests on MyISAM and InnoDB on the same Mac, and the results were much better!!!
If your case is write-intensive, this is probably the storage engine of your dreams. I keep thinking of two dear friends, one from Porto Alegre and another from Boa Vista, who have applications that love to write. This is their storage engine.
Stability
Again, I ran the tests on my super mainbook or noteframe, which is quite stable. I do tests and benchmarks all the time, and rarely experience any kind of instability.
PBXT has a serious problem with users (like me) who love to issue Kill -9 on everything. By the way, if anyone has discovered some kind of Kill command to wipe out all the bills and expenses for the month, please let me know. Dirty shutdowns will cost you dearly. So, if you plan to run PBXT in an unstable environment, rethink everything. To run PBXT, I recommend a solid machine (hardware), very well configured, stress-tested to exhaustion before going into production, and above all, a very reliable uninterruptible power supply (UPS).
Simulating power loss and OS instability, I endured a reboot time of 1 hour and 55 minutes with the only table used in the tests! Scared? Me too. For a simple 1GB table, the crash recovery time is approximately 2 hours. For comparison, I had to be quite creative to get InnoDB into recovery mode (RMS – Recovery Management System)… and it took only 10 minutes to check if everything was OK.
It’s worth remembering that storage engines with some kind of automatic verification and/or recovery trigger do not release the RDBMS for use until their protocols are complete. Therefore, in my case, PBXT “punished” me for almost 2 hours every time it “crashed” (transitive verb… whoever crashes something, hey, it’s in the dictionary, you can check…).
PROS
Accessibility and availability of Paul and PrimeBase Technologies. Writing speed. 100% ACID compliance. Simplicity and analogy of configuration variables. More comprehensive CHECK TABLES (writes some important details in the error log).
CONS
Default configuration. Excessive logging of promotional messages in the error log during mysqld startup. Ease of entering recovery mode (even without any writes to the table). Startup time after a dirty shutdown.
I didn’t like the read performance or the time required for index maintenance at all.
Recommendation
If it were good, I’d be selling it, right?
PBXT is still under development. Those who used it last year will notice significant improvements. However, it’s not yet ready for production. Now, if we analyze the documentation, we’ll see that for much smaller tables—at most 200M or 300M rows—the performance would be much better. PBXT relies heavily on its caches, so for tables that can fit mostly into the record cache, performance improves greatly.
I do not recommend using it until a new benchmark is conducted.
Visit our Blog
Learn more about databases
Learn about monitoring with advanced tools
Have questions about our services? Visit our FAQ
Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!
Discover the History of HTI Tecnologia
