MySQL storage engines comparison, include TokuDB and PBXT
Updated: 2011-01-10
Updated: 2011-01-12
Abstract:
About week ago we decided to evaluate MariaDB with TokuDB. Then we decided to do some performance testing on different storage engines, to see which one performs better for different conditions. Finally we decided to do some more tests on PBXT as well; even it is not GA yet.All test was performed on Dual Processor Dual Core Intel Xeon 3210 @ 2.13GHz, 4 cores total, 8 GB RAM, HDD with no RAID, Ext3 file system.
Comparison is available here:
http://nmmm.nu/mariadb/tokudb_engine_compare.xls
Some information about different storage engines we tested:
TokuDB:
TokuDB is MySQL storage engine developed from Tokutek and is closed source. TokuDB supports transactions, but from what we see lock model was table lock - just like MyISAM. When we tested with two SQL queries in parallel they executed one after other and not in parallel. It also blocks reads when there is table lock.According Tokutek, TokuDB utilize fractal-tree indexes, and it can speedup MySQL server about 30-50x just by changing the storage engine.
TokuDB also utilize hi compression the information. This is good improvement, because the information needs to be retrieved from disk and this operation take less time if data is compressed.
TokuDB is not free software (in fact is quite expensive), but is available for free evaluation. It can be downloaded from http://www.tokutek.com/. Current version is 4.1
TokuDB Installation:
Tokutek gives you patched source versions of MySQL and MariaDB or 64 bit Linux binaries. The fractal-tree index code is in 64 bit Linux stripped shared library and you get no source code for it.There is no 32 bit version.
First we decided to compile MariaDB from the patched source. We did not succeed due to lots of errors we got from the compiler.
Then we got binary version and installation was very easy. We had some problem with ld.so.conf, but it was probably not related to TokuDB itself. Then we decided to place the fractal-tree shared library in MySQL lib directory, and everything worked smoothly.
Another thing I like on TokuDB is how they handle show processlist:
MariaDB [test]> show processlist;
+----+------+-----------+------+---------+------+--------------------------------------------------------+...+
| Id | User | Host | db | Command | Time | State |...|
+----+------+-----------+------+---------+------+--------------------------------------------------------+...+
| 34 | root | localhost | test | Query | 57 | Queried about 1364080 rows, Updated about 1364000 rows |...|
| 35 | root | localhost | test | Query | 0 | NULL |...|
+----+------+-----------+------+---------+------+--------------------------------------------------------+...+
2 rows in set (0.00 sec)PBXT:
PBXT is new engine, developed from PrimeBase Technologies http://www.primebase.org/. It supports transactions, it is multi version and it is not blocking reads when there is lock. Lock model is row level locking. PBXT is free and is emerging competitor of InnoDB.Current version of PBXT is 1.0.11-6 Pre-GA, and is included in MariaDB by default.
InnoDB / XtraDB:
Instead of InnoDB storage engine we tested Percona’s XtraDB. It is basically patched version of InnoDB-Plugin – the “next” version of InnoDB, which is not yet included in MySQL.InnoDB have transactions, have multi version and it is not blocking reads when there is lock. Lock model is row level locking.
InnoDB-plugin can be downloaded from http://www.innodb.com/.
XtraDB can be downloaded from http://www.percona.com/. XtraDB is also included in MariaDB by default.
Maria (Aria):
This is storage engine developed by MontyProgram, authors of MariaDB. It is crash safe MyISAM and at least for the moment does not support transactions. Lock type is table lock.We tested “page” row format only, because other two formats supposed to be exactly the same as MyISAM.
Maria has been renamed to Aria, but in the document I still will use reference "Maria" for the storage engine and "MariaDB" for the database system.
MyISAM:
This is default storage engine of MySQL. We tested both “dynamic” and “fixed” row formats.Some people, including me claimed that "fixed" format is faster because every record occupy same space on the disk.
How tests were performed:
Data structure:
We used following table with 10,875,440 records:CREATE TABLE `video` ( `id` int(4) NOT NULL AUTO_INCREMENT, `artist` varchar(100) NOT NULL, `title` varchar(100) NOT NULL, `video` varchar(20) NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`), KEY `video_artist_ix` (`artist`), KEY `video` (`video`), KEY `date_id` (`date`,`id`) );We created same table in “blackhole” engine called “video_bh”.
Then we created following emty tables:
| Table | Storage engine | Row Format |
|---|---|---|
| video_inno | XtraDB | |
| video_ma | Maria, Page format | Page |
| video_my | MyISAM, Dynamic row format | Dynamic |
| video_myfix | MyISAM, Fixed row format | Fixed |
| video_pbxt | PBXT | |
| video_toku | TokuDB |
- video_pbxt1
- video_toku1
What tests were performed:
Test 1: Bulk insert
insert into video_xxx select * from video;Description:The query requires full scan of video table and inserting each row in video_xxx table with updating all indexes in video_xxx table.
Expectations:We may expect that MyISAM will have advantage because it “bundles” the records prior the insert.
Results:
- TokuDB made the insert in less than 2 min.
- Second try of TokuDB made it in 4:30 min.
- Maria and MyISAM made it in 5 to 7 minutes.
- InnoDB made it in 1h 20 min.
- PBXT made it in 5+ hours, then after the operation finished, the table was under “system lock” for several hours. We decided to kill -9 the process. After the restart we waited for data recovery about 1 hour 30 min.
- Second attempt PBXT made it in 8+ hours. It crashed again, we kill -9 again and data was recovering 10+ hours.
Test 1a: Data size on the HDD
- Toku DB used about 600 MB.
- Maria / MyISAM used about 1.4 GB
- Fixed MyISAM used 2.8 GB
- InnoDB – 3 GB
- PBXT – 3.7 GB
Test 2: Bulk update
update video_xxx set artist=concat(artist,' ok')Description:This requires full table scan, retrieving each record and then storing it back.
Expectations:We may expect that PBXT will be probably fast here, because it simply create new “version” of the record.
Also we may expect that Fixed MyISAM will be fast because is fixed and probably data can be retrieved easily
Results:
- TokuDB – less 9 min.
- InnoDB / Maria – 49 min – probably because of log file maintenance
- MyISAM / Fixed MyISAM – 20 min
- PBXT – 43 min
- Second PBXT try – over 5 hours.
Test 3: Bulk delete
delete from video_xxx where artist like 'ni%'Description:Because we can not afford to delete all data, we decided to delete 68,155 records. The test combine index scan (artist like 'ni%) and delete and updating any necessary indexes.
Expectations:We may expect PBXT to be faster because it not really deleting the records.
Results:
- TokuDB / PBXT – About 30 sec
- InnoDB / Maria / MyISAM – 3 - 4 min
Test 4: Key lookup + Table scan
insert into video_bh select * from video_xxx where date = '2010-11-11'Description:
This test combine key lookup (date = '2010-11-11') plus all 10,853 records must be retrieved from the disk and inserted into “blackhole” table.
Results:
- TokuDB / PBXT: 20 sec
- InnoDB / Maria / MyISAM: less 1 sec
Test 4a: Key lookup + Table scan
insert into video_bh select * from video_xxx where artist like 'a%'Description:This is basically the same as previous test, but index lookup is from part of the index.
Results:
- TokuDB: 20 sec
- PBXT: 9 sec
- MyISAM Dynamic: 4 min
- InnoDB / Maria / MyISAM Fixed: 2 min
Test 5: Aggregate + Key lookup + using index
select count(*) from video_xxx where date = '2010-11-11'Description:This test is similar to previous, but does not require retrieving of any data. Instead data needed is “inside” the index.
Results:
- All: less 1 sec
Test 5a: Aggregate + Key lookup + using index
select count(*) from video_xxx where artist like 'a%'Description:This is similar to test 4a.
Results:
- TokuDB / PBXT / InnoDB – less 1 sec.
- Maria – 3 min
- MyISAM – 8 sec
Test 6: Count(*)
select count(*) from video_xxxExpectations:We may expect MyISAM and Maria to do this in less 1 sec, because this information is maintained externally for this table formats.
We may expect InnoDB to be slow.
Results:
- TokuDB – 7 sec
- PBXT – 20 sec
- InnoDB – 36 sec
- MyISAM / Maria – 0 sec
Test 6a: Agregate all
select min(date), max(date) from video_xxxDescriotion:Even this is completely different query; it is similar to previous test.
Expectations:We may expect ALL engines to do it very fast because we do the query immediately after the previous one ( select count(*) from video_xxx ) and the optimizer probably have the min() / max() already. Even if there is no such values, they should be retrieved from the index by forward and backward scan.
Results:
- All – less 1 sec.
Test 7: Concurrent bulk operations
create table video_temp as select * from video video where artist like 'ni%'
...then...
insert into video_xxx select * from video_temp
...in parallel with...
delete from video_xxx where artist like 'ma%'
...in parallel with...
select * from video_xxx limit 5Description:This test show how the engine handle parallel operations. Results are not that much important here, more important is if operations are done. If the engine has table level lock, then the engine does not support concurrent operations.
This is what we write originally:
Note that TokuDB have table lock and it is n_o_t really ACID complaint
Correction 2011-01-10:
TokuDB have row level locking and it is ACID complaint. However if you try to update / delete same rows, there is a conflict and if you use TokuDB you need to wait the operations one after the other. This is the explanation why We were unable to do the test. Soon we will retest and will try to choose "different" rows and will see what the results are.
InnoDB and PBXT support concurrent operations; also they do not block selects.
Correction 2011-01-12:
We were unable to do concurrent queries with TokuDB 4.1.3, this is probably because the lock type is pessimistic, but yet is strange, because we tried to update / delete different rows.
Bug in TokuDB
Using this last test we realized major bug in TokuDB. It was described here:http://nmmmpic.blogspot.com/2010/12/bug-in-tokudb.html
Another interesting fact is that TokuDB have built-in lock timeout and when we tested, one of the query executions was terminated due to this timeout. Because of the bug we did not tried to tune-up this parameter.
Correction 2011-01-10:
We received word from Tokutek that the bug will be fixed within a week. Then we will re-test everything again.
Correction 2011-01-12:
Bug is now fixed in version 4.1.3
Conclusions:
- PBXT is still not ready for production environment, but some of the results are very good. May be some day it will be great competitor of InnoDB.
- TokuDB 4.x is still not ready for production environment. We need to do same tests with TokuDB 3.0 or to wait Tokutek to fix the bug.
- MyISAM with fixed rows have no advantages over MyISAM with dynamic rows.
- Maria is almost as fast as MyISAM. This must be expected, because Maria have MyISAM heritage.
