Last month my colleague Rich Prohaska covered the technical details of our “Fast Update” feature which we added to TokuDB in version 6.6. The message based architecture of Fractal Tree Indexes allows us to defer certain operations while still maintaining the semantics that MySQL users require.
In the case of Fast Updates, TokuDB is avoiding the read-before-write requirement that the existing MySQL update statement imposes on storage engines. We can simply inject an update message into the Fractal Tree Index, and apply that message at a later time. The message is dynamically applied if a user selects that specific row, and permanently when the message buffers overflow and push it down into the leaf node.
There are trade-offs for not performing a read for every update:
A single Sysbench OLTP transaction performs a series of point and range queries, an indexed update, a non-indexed update, a delete, and an insert. I ran two tests, one with a single update per transaction and a second with 25 updates per transaction. These Sysbench tests used 16 tables, 10 million rows per table, and 64 client threads. The server was started with 8GB of cache for each storage engine, each was using Direct IO, and the InnoDB on-disk size was 36GB. The performance shown is the average transactional throughput for the 15 minute benchmark run.
The performance of InnoDB dropped 66.5% percent when we increased the number of updates per transaction from 1 to 25. This was to be expected, as each of the 25 updates is likely to perform an IO.
In TokuDB using traditional updates, the transactional throughput dropped 54.9% (179.5 to 80.9) when increasing the number of updates per transaction from 1 to 25. As with InnoDB, this is expected behavior as each requires an IO. However, TokuDB’s fast updates only dropped 13.3% (179.5 to 155.5). Fractal Tree Index messaging for the win!
TokuDB’s Fast Updates are just part of our SQL optimization story. We have similar optimizations for INSERT … ON DUPLICATE KEY UPDATE, INSERT IGNORE, and REPLACE INTO. If you rely heavily on these statements and need to increase your MySQL throughput then you should seriously consider evaluating TokuDB.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.