Read/write Analysis and Comparison of TokuDB and InnoDB

We know that the most popular MySQL standalone version is INNODB, the only engine that supports full transactions. Its characteristic is that the data itself is organized by B-TREE, the data itself is a huge B-TREE index based on the primary key clustering. Therefore, the write speed is somewhat reduced, because I/O is required for every write operation to re-arrange the index tree. Especially when the data size is much larger than the memory size, the CPU itself is entangled by disk IO and cannot do other things. At this time, we should consider how to reduce the disk I/O to solve the CPU situation, so what should we do? (Of course, if there is enough data in the memory, you don't have to consider these things .)
1. Can I increase INNODB pages? (16 KB by default) but the increase also brings some defects. For example, the CHECKPOINT time for the disk will be delayed.
2. Put the log file on a faster disk? Such as SSD?


In fact, we can consider using another well-known engine TokuDB. Who is MySQL born to support unplugging at will!
TokuDB actually uses its own data storage to use the deformation version of The B-TREE Fractal-Tree. Fractal-Tree adds a cache to the original non-leaf node of B-Tree. No matter how the Tree is operated, it is a mode: the cache at Father's Day is full, it flows to the son node, and after the son node's cache is full, it flows to the son node again, and so on a series of final to the leaf node, and then when the PAGE of the leaf node is large enough, check point. Of course, no matter how the cache is implemented, you must first refresh the REDO log after each transaction, or it is difficult to guarantee data consistency.


Next, we will test the performance difference between InnoDB and TokuDB in the same environment. Of course, I did not do a stress test, but simply executed several SQL statements manually.
(5.6.10-enterprise-Internal cial-advanced-log MySQL Enterprise Server-Advanced Edition (commercial ))
The file used for import is about 35 MB.

1. INNODB. corresponding parameter: innodb_buffer_pool_size = 32 M bulk_insert_buffer_size = 20 M query_cache_size = 0 import performance: (InnoDB has been busy with IO replacement in the CPU .) Mysql> load data infile '/tmp/t3_push.csv' into table t3_push; Query OK, 955527 rows affected (30 min 44.03 sec) Records: 955527 Deleted: 0 Skipped: 0 Warnings: 0 read performance: (The read performance is still good. Here we use the 5.6 ICP and MRR features .) Mysql> select count (*) from t3_push where rank1 <20 and rank2 <30; + ---------- + | count (*) | + ---------- + | 49 | + ---------- + 1 row in set (0.06 sec) Increase innodb_buffer_pool = 128 Mmysql> load data infile '/tmp/t3_push.csv' into table t3_push; Query OK, 955527 rows affected (38.72 sec) Records: 955527 Deleted: 0 Skipped: 0 Warnings: 0, the import performance is good.

2. tokuDB. (5.5.30-tokudb-7.1.0-e-log TokuDB Enterprise Server (GPL) parameters: tokudb_cache_size = 32 M tokudb_loader_memory_size = 20 M query_cache_size = 0 Write Performance :( IO times here, so the import speed is very fast .) Mysql> load data infile '/tmp/t3_push.csv' into table t3_push; Query OK, 955527 rows affected (19.73 sec) Records: 955527 Deleted: 0 Skipped: 0 Warnings: 0 read performance: (The read speed is a little slower than that of INNODB .) Mysql> select count (*) from t3_push where rank1 <20 and rank2 <30; + ---------- + | count (*) | + ---------- + | 49 | + ---------- + 1 row in set (0.54 sec) mysql> select count (*) from t3_push where rank1 <200 and rank2 <300; + ---------- + | count (*) | + ---------- + | 5759 | + ---------- + 1 row in set (4.13 sec), but TokuDB can change the clustering of secondary indexes, therefore, if read-only, it will be faster than InnoDB. Add a clustered index to the column rank2, mysql> alter table t3_push add clustering index idx_rank2 (rank2); Query OK, 0 rows affected (6.79 sec) Records: 0 Duplicates: 0 Warnings: 0 now all index-based idx_rank2 queries are instantaneous. Mysql> select count (*) from t3_push where rank1 <20 and rank2 <30; + ---------- + | count (*) | + ---------- + | 49 | + ---------- + 1 row in set (0.00 sec) mysql> select count (*) from t3_push where rank1 <200 and rank2 <300; + ---------- + | count (*) | + ---------- + | 5759 | + ---------- + 1 row in set (0.01 sec)