General Information

1.    What is TokuDB?

• TokuDB is an ACID compliant storage engine for MySQL that is specifically designed for high performance on write-intensive workloads. It achieves this via Fractal Tree^® indexing, which is based on cache-oblivious algorithmics. This approach to building memory-efficient systems was originally jointly developed by researchers at the Massachusetts Institute of Technology, Rutgers University, and the State University of New York (SUNY) at Stony Brook.

2.    What are the key advantages for TokuDB vs. other MySQL storage engines?

• Unmatched Speed

o Insert up to 80x faster in actual field conditions

o Accelerate queries with better indexing

o Immunity to database aging (a.k.a. index fragmentation)

• Replication Performance

o Eliminate Slave Lag

o Read Scalability

o Non-disruptive backups

o Improved Disaster Recovery

• Maximum Scalability

o Grow to multiple terabytes

o Deploy without partitions

o Never fragment

o Up to 25x data compression for reduced disk use and lower storage costs

• Exceptional Agility

o Hot Column Addition/Deletion/Rename

o Hot Indexing

o No dump/reload downtime

o MVCC

3.    What are typical uses for TokuDB? When would I consider using it?

• TokuDB continues to be the ideal choice for complex / high-volume applications that must have fast response times and that must simultaneously store and query large volumes of rapidly arriving data:

o   Social Networking

o   Online Advertising

o   Logfile Analysis

o   Metadata for Cloud Storage

o   Fiction Publishing on the Web

o   eCommerce Personalization

o   Copyright Surveillance

• By making IO operations sequential, TokuDB can maintain a rich set of covering and clustering indexes, enabling fast queries to improve application performance and responsiveness. TokuDB typically helps applications with the following characteristics:

o   Tables with more than 50M to 100M rows

o   Substantial insert, update or delete requirements – TokuDB scales to tens of thousands of rows per second

o   Writes are usually the bottleneck for replication, resulting in slave lag. TokuDB addresses this by handling high ingestion rates (tens of thousands of inserts per second)

o   A desire to query new data in “interactive time,” without waiting hours or days for batch insertions

o   “Range queries” – queries that must scan multiple rows and benefit from a rich set of indexes.

4.    What is a B-Tree?

• Virtually all of today’s commercial relational databases use indexes to increase query performance. Databases use indexes to significantly reduce the amount of data they examine while responding to queries. Indexes are commonly implemented with B-trees, a data structure first described in 1970. Besides allowing for searches, the B-tree data structure also allows for operations like inserting data and sorted order iteration, the primary operation used by an index. Depending on the workload and implementation, B-tree performance can be limited by the random I/O characteristics of disks. In addition, while freshly loaded databases tend to have good sequential behavior, this behavior becomes increasingly difficult to maintain as a database grows, resulting in more random I/O and degraded performance.

5.    What is a Fractal Tree? Why would I use it over a B-tree?

• Fractal Tree indexes implement the same operations as a B-tree, and thus are a drop-in replacement for B-trees. The difference is in performance. Fractal Tree indexes effectively replace random I/O with sequential I/O, which is faster on spinning disks. By converting random I/O into sequential I/O, Fractal Trees index data at near disk bandwidth rates, regardless of the structure of the primary and secondary keys, and have range queries that stream data off disk at near disk bandwidth rates, even as the database grows. As a result, more indexes can be maintained without a drop in performance. This is because adding data to indexes tends to stress the performance of B-trees, but performs well in Fractal Tree indexes.

6.    Is TokuDB’s speed from being an “in memory” or a “read only engine”?

• No. TokuDB is a disk-based, read-write transactional storage engine that is based on Fractal Tree indexes. TokuDB is designed for very large (terabyte-plus) tables. In fact, the bigger the table the greater the TokuDB advantage over B-tree storage engines. Even on large tables, TokuDB provides exceptional performance without partitioning or sharding.

7.    How does TokuDB compare to InnoDB?

• For a feature by feature comparison, see here.
• For performance benchmark comparisons, see here.

8.    How do faster inserts improve my query performance?

• DBAs tend to maintain few indexes in databases because keeping more than a few indexes up to date can reduce insertion rates to unacceptably low levels. They often make trade-offs, either waiting for all the data to come in (and get stale), or insert at limited rates, extending the timeframe. Sometimes sub-optimal indexes that happen to insert sequentially are used, because the optimal index is too slow. However, sub-optimal indexes results in slow queries. The difference in speed between a query against an index and the same query when no index is available can be many orders of magnitude. Any clustered index would enjoy the same query-performance boost by maintaining the right indexes, but other solutions simply can’t keep up with the insertion load. Thus, fast indexing means more indexes, which means fast queries.

9.    What are clustering indexes?

• In general, a clustered index or a clustering index is an index that stores all of the data for the rows. Quoting the MySQL Reference Manual:

o   Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record.

• Most storage engines allow at most one clustered index for each table. For example, MyISAM does not support clustered indexes at all, whereas InnoDB allows only the primary key be a clustered index.
• In TokuDB, we have added functionality and grammar syntax to define multiple clustered indexes. As a result, users can get the performance advantages of clustered indexes for multiple indexes.

10. Hot Indexing and Hot Column Addition became available with TokuDB v5.0. Can you explain those features?

• Hot indexing means that concurrent DB read/write operations are allowed while the index is being created. The problem in the real world is that data models are constantly changing with market needs. Without a hot solution, any changes to the database typically require that the database be taken offline and rebuilt; this takes days for large databases. A hot index solution non-disruptively and dynamically allows for the creation of ad hoc indexes in seconds to minutes.
• In a similar fashion, columns can be added, deleted or renamed while ongoing operations and updates are being performed.

11. TokuDB v6.0 introduced Replication Performance. Can you explain?

• TokuDB v6.0 eliminates the common and persistent problem of “slave lag” in which a replication server is unable to keep up with the query load borne by the master server. TokuDB v6.0 solves this by offering high ingestion rates at the slave with best in class compression.
• In addition, TokuDB v6.0 introduced two phase commit support for XA, insuring more robust support for replication environments .

12. What does it take to implement TokuDB? Is it drop-in?

• TokuDB is designed to be a drop in replacement. Installation is as easy as plugging in a storage engine. TokuDB requires a patch to the MySQL source code, so you can either patch the source code yourself or download an already-patched version from Tokutek that contains TokuDB as well. Tuning of TokuDB is much easier than InnoDB as there are only few parameters to change, and TokuDB runs well “out of the box”. For full details, see our TokuDB for MySQL Evaluation Guide

13. Does TokuDB help on solid state disk (SSD) drives (aka flash drives)?

• Yes. Fractal tree indexes are efficient at insertions on SSDs because they transform random I/O into sequential I/O. They use data structures that are fast for any block size. See the Performance Model for SSDs for more detail and information. That said, in many cases TokuDB may perform well enough on spinning disks that a user may not need to resort to (more expensive) SSD hardware.
• Furthermore, with SSDs, data compression – which TokuDB excels at – becomes crucial since reduced storage requirements translate into reduced cost of expensive SSD hardware. TokuDB’s aggressive compression allows customers to cut their SSD costs by 50%-80%.

14. When do I need to buy a license?

• TokuDB for MySQL is free for up to 50GB of user data and costs $2,500 / 100GB / year above 50GB. “User data” means uncompressed user data, excluding indexes. The level of support depends on the license type. Note that there are discounts for replication slaves and BLOBs. See TokuDB for MySQL Pricing for more details and limitations.

15. So TokuDB really improves performance. Does it save me any money?

• Absolutely. Some examples include saving administrative costs otherwise spent on partitioning or sharding, reducing outlays by purchasing rotating disks instead of SSDs, and mitigating hardware expenditures by using smaller disks given the compression that is enabled. And if your application does require SSD storage, TokuDB’s superior compression can cut SSD hardware costs 50%-80%.

16. What does ACID compliant mean?

• ACID (atomicity, consistency, isolation, durability) is a set of properties that insure reliable database transactions.  Databasejournal.com defines ACID as the following:

o   Atomicity:

§  An atom is meant to be the smallest particle, or something that cannot be divided. Atomicity applies this principle to database transactions. The queries that make up the transaction must either all be carried out, or none at all.

o   Consistency:

§  This refers to the rules of the data. For example, an article body may have to have an associated article heading. During the transaction, this rule may be broken, but this state of affairs should never be visible from outside of the transaction.

o   Isolation:

§  Simply put, data being used for one transaction cannot be used by another transaction until the first transaction is complete.

o   Durability:

§   Once a transaction has completed, its effects should remain, and not be reversible.

• TokuDB has been ACID compliant starting with v3.0

17. What does MVCC compliant mean?

• Multi-Version Concurrency Control (MVCC) is a technique for improving multi-user database performance. It does this by eliminating row-level locking and table locking. In doing so, it ensures that locks acquired for querying (reading) data don’t conflict with locks acquired for writing data and so reading never blocks writing and writing never blocks reading.
• TokuDB has been MVCC compliant starting with version 5.0.

Technical Information

18. What is the API interface and SE handler for TokuDB?

• For TokuDB, we chose to program to the Berkeley DB API, and then adapted the Berkeley DB SE handler for our purposes. The main difference between the InnoDB embedded API and the Berkeley DB API that we use is that InnoDB understands that rows are made of columns, whereas Berkeley DB treats the rows as undifferentiated items.

19. What SQL versions does TokuDB support?

• TokuDB 6.0 currently supports MySQL 5.5 and MariaDB 5.2

20. How big of an implementation is supported?

• TokuDB can support up to 32 columns per index, with row size of up to 32MiB.

21. What are the minimum system and hardware requirements?

• Operating Systems:

o   TokuDB is supported on Linux; CentOS 5.1 through CentOS 5.5 (64 bit).

o   Please contact Tokutek at support@tokutek.com for Windows support or for other Linux platforms

• Processor Architecture:

o   TokuDB runs on X86 64

o   Please contact Tokutek at support@tokutek.com if you are interested in other architectures.

• Memory:

o   TokuDB requires at least 1GB of main memory.

Best Practices and Tools

22. What should I be aware of to insure a successful trial and to maximize my performance?

• The following steps can help insure an accurate assessment of the benefits of TokuDB for your production application:

1.     Most Important Test on large tables (results from small tables are not indicative of performance on large tables).  It is absolutely critical that the table be larger than main memory to generate a meaningful test result. If you need help with this or any other part of your evaluation, please contact support@tokutek.com.

2.     Load an initial data set and measure disk use.

3.    Run queries several times on TokuDB for data structures to be automatically optimized.

4.    Define covering and clustering indexes for large gains in query performance.

5.     Test incremental inserts, updates and especially deletes into large tables with indexes defined.

• TokuDB’s compression benefits will show up on step 2, query performance benefits become clear on step 4, and the ability to maintain indexes necessary to deliver improved query performance over time on large, growing tables is demonstrated in step 5. Data gathered during all these steps should be evaluated against production requirements to assess TokuDB for your application
• For more details and an evaluation guideline, see our TokuDB for MySQL Evaluation Guide.

23. What performance measurement tools are available for TokuDB?

• Our Benchmark Portal contains information on testing done for TokuDB vs. InnoDB for TPCC, SysBench, compression, loading times, and Hot Schema modifications. It also has information on testing performed with iiBench, a popular open-source benchmark developed by Tokutek with various improvements from online users.
• iiBench stresses storage engine performance for inserting data while maintaining secondary indexes. Use of indexes provides significantly higher query performance. The schema consists of short rows that model a retail point- of-sale transaction system.