Mar
22
2016
--

TokuDB impacts InnoDB Performance?

TokuDB impacts InnoDB performance

TokuDB impacts InnoDB performanceThis blog discusses how TokuDB impacts InnoDB performance when the two run in the same environment.

You would think MySQL storage engines are fairly independent of each other, even in the same environment. Enabling one, or changing its configuration, logically should have no impact on the performance of other engines (such as InnoDB) when they are accessing tables. The reality, however, is more complicated than that!    

Now that we’ve shipped TokuDB, we’ve been getting feedback from our community and customers that enabling TokuDB might negatively affect performance – even for queries that don’t touch TokuDB tables (and in some cases, even when TokuDB is kept completely idle).

After investigating these reports, we found that the following issues could be contributing to this performance loss:

  • Memory allocation. By default, TokuDB allocates 50% of the system memory to its cache. If you already allocate a significant amount of memory to the 
    innodb_buffer_pool

    , you’re likely overcommitting. Check

    tokudb_cache_size

     and set it to the amount that is appropriate for your system.

  • Caching. Many users run InnoDB with
    innodb_flush_method=O_DIRECT

    . This ensures that the OS file cache is not polluted with heavy database IO, and is available for binary log files, MyISAM temporary tables and other OS needs. TokuDB uses buffered IO by default, which can pollute your OS cache with heavy use. You can change this behavior by enabling

    tokudb_directio

    . Note though, TokuDB performs better without

    tokudb_directio

     for some workloads.

  • Two-phase transaction commit. This is a tricky one. To coordinate between multiple transactionally capable storage engines and the binary log, MySQL uses a two-phase transaction commit and transaction coordinator. If you only run the InnoDB storage engine, and have the binary log disabled (typical in a test environment), the transaction coordinator won’t be used. If, however, you enable both InnoDB and TokuDB – with the binary log disabled – the TC_MMAP transaction coordinator gets used. The TC_MMAP transaction coordinator has very poor performance and scalability. Since MySQL can’t predict which storage engines are going to be part of a transaction, overhead takes place for all transactions – even for the transactions that only touch InnoDB tables. To “fix” this problem, you need to enable the binary log. The binary log gets used as the transaction coordinator, and it performs and scales much better. This related bug describes the other effect of transaction coordinator issue.

Storage engine configuration causes most of the performance issues related to running TokuDB and InnoDB together, and the proper MySQL configuration settings resolve them.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com