EmergencyEMERGENCY? Get 24/7 Help Now!

TokuDB impacts InnoDB Performance?

 | March 22, 2016 |  Posted In: MySQL

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST
Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

3 Comments

  • Thanks Peter. We hit “Two-phase transaction commit.” issue and finally I understand the real reason behind this odd behaviour. In our case the slave was 4 times slower with bin-log disabled and performance was “fixed” with bin-log enabled.

    I would mention one more thing related to the Toku performance. Performance with tokudb_commit_sync = 1 is not great and tokudb_commit_sync = 0, tokudb_fsync_log_period = 1000 is much better option on a busy server. There is an issue though if you decide to disable tokudb_commit_sync. TokuDB Hot backup will not create a consistent backup. https://bugs.launchpad.net/percona-server/5.6/+bug/1533174

  • Igor,
    Is there something different in the performance difference in the tokudb_commit_sync=0|1 vs innodb_flush_at_trx_commit=0|1

    The issue with Tokubackup is indeed something which needs attention

  • Peter,
    I think quite comparable, but this is just my feeling.
    I hardly ever used innodb_flush_log_at_trx_commit=1 , always innodb_flush_log_at_trx_commit=2

    tokudb_commit_sync=1 slower to 0 considerably. It’s enough for me to reject tokudb_commit_sync=1 usage in the same way as innodb_flush_log_at_trx_commit=1. Working with data 3-10T loosing potentially upto 1 sec of transactions in case of MySQL failure is acceptable.

    One thing I want to mention is InnoDB scales better. We had a table with need to update simultaneously by number of clients (5-20) and that table become regularly corrupted every 2 weeks . Table corruption in TokuDB causes all other threads to queue up to the point when server needs to be killed and started up. The only thing that worked for us was to convert that table to InnoDB. Overall TokuDB performance better than InnoDB. Our 3T dataset was struggled on InnoDB to process more than 3K QPS after migrating to TokuDB + adding few Clustering indexes we can process 15-25K QPS on the same hardware.

Leave a Reply