Testing TokuDB’s Group Commit Algorithm Improvement

The MySQL 5.6 Release has introduced some changes to how two phase commit works and is managed.  In particular, the commit phase of transactions to the binary log is now serialized and this behavior is something we identified fairly immediately.  We implement a group commit algorithm that needed to be altered so that TokuDB’s group commit to its recovery log would function effectively.

As part of our effort to verify the new Binary Log Group Commit functionality introduced in TokuDB 7.5.4 for Percona Server, we wanted to demonstrate the substantial increase in throughput scaling but also show the bottleneck caused by the skewed interaction between the binary log group commit algorithm in MySQL 5.6 and the transaction commit mechanism used in TokuDB 7.5.3 for Percona Server.  During our testing, we noticed that the throughput scaling was diminished when we turned on the binlog.

Here are the relevant system specifications from our test environment:

  • Ubuntu 13.10 64-bit
  • Intel(R) Core(TM) i7-2600 CPU @ 3.40GHz
  • Intel 330 Series SSD, 120GB

Sysbench was the application we decided to use once we configured our test environment.  In particular, we chose a fairly simple workload from sysbench called update_non_index.lua as our workload because of its simple nature and determinism as it could be applied to TokuDB before and after the introduction of the Binlog Group Commit enhancement.

We created a single table that contained 1M rows and kept the table small enough to fit in memory so the test could isolate the measurement of I/O written to the logs.

Here is what the function looks like inside lua:

function event(thread_id)
local table_name
local c_val
local query
table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
c_val = sb_rand_str("###########-###########-##########...-###########")
query = "UPDATE " .. table_name .. " SET c='" .. c_val .. "' WHERE id=" .. sb_rand(1, oltp_table_size)
rs = db_query(query)

I wrote a small bash script that would essentially take the prepare phase of sysbench and create a table that would be the target of this workload.  It is important to reemphasize as well that the dataset would need to be kept in memory and as a result, the sysbench parameters and my.cnf would need to be configured such that these requirements would be met.

The relevant parameters for MySQL and TokuDB are as follows:

#log_bin=mysql-bin (used to turn the binlog on or off)

Within the bash script that calls sysbench, I decided to use a table with 1 million rows to keep the dataset in memory and a simple for loop to increment the thread count by a factor of 2.  One of the nice features of sysbench is that as a C program it uses a MySQL connection with autocommit=ON which means that each statement is executed in its own transaction.

Here is the aforementioned block that executes the sysbench run portion of the workload:

for i in 1 2 4 8 16 32 64 128 256; do
sysbench --test=${SYSBENCH_DIR}/tests/db/update_non_index.lua --mysql-table-engine=${MYSQL_STORAGE_ENGINE} --oltp_tables_count=${NUM_TABLES} --oltp-table-size=${NUM_ROWS} --mysql-socket=${MYSQL_SOCKET} --mysql-user=${MYSQL_USER} --mysql-password=${MYSQL_PASSWORD} --num_threads=$i run | grep 'threads|write requests'

I have also piped the output to grep for the thread count and read/write request metrics and used gnuplot to show the results from our testing.

Initially, we needed to demonstrate the throughput of TokuDB 7.5.3 for Percona Server with the binlog turned OFF (see red line in the graph shown).   Next, we can show what happens when the Binlog is turned on with TokuDB 7.5.3 for Percona Server (see orange line in the graph shown).  As you can see, throughput clearly suffers.  And finally, as expected with the enhancement to TokuDB’s group commit algorithm in TokuDB 7.5.4 for Percona Server, the performance shows a 17x improvement (see blue line in the graph shown).


As a result, with the binlog turned on AND the optimization as part of TokuDB 7.5.4 for Percona Server, we can see the bottleneck has been reduced and the throughput can scale accordingly.

In this case, one can see that the improvement is about 17x more read/write throughput.

Share this post

Comments (4)

  • Vlad

    I think you need to regenerate this graph – look at the orange line – it’s constant zero.

    December 26, 2014 at 11:59 pm
    • Dave Rosenlund

      Take a closer look, Vlad. It’s ~1K TPS

      December 29, 2014 at 3:52 pm
  • Vlad

    Thanks for the clarification.

    Now though, I am far more concerned by the massive performance hit caused by having the binlog enabled. I wonder if it’s as severe as this when using MariaDB, which uses its own group commit algorithm, as was outlined in the last blog post.

    I really hope that the next release of MariaDB will have your improved algorithm changes, as well as enabled support for the no affected rows (NOAR) keyword for INSERTs and UPDATEs – both of these will remove a bottleneck preventing maximum throughput when using TokuDB in MariaDB.

    January 11, 2015 at 6:39 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.