TokuDB v6.5 adds the ability to expand certain column types without downtime. Users can now enlarge char, varchar, varbinary, and integer columns with no interruption to insert/update/delete statements on the altered table. Prior to this feature, enlarging one of these column types required a full table rebuild. InnoDB blocks all insert/update/delete operations to a table during column expansion as it rebuilds the table and all indexes.
We recently added SSDs to our existing servers (HP DL380 servers with internal RAID arrays). I opted for LSI controllers (LSI 9285-8e and LSI 9280-4i4e), Samsung 830 SSDs, and a Sans Digital AccuRaid AS108X enclosure. I was unable to locate much in the way of optimizing Linux for SSD/RAID performance and plan on blogging my findings in the future. However, the out of the box performance is seriously fast.
I was curious to see how much downtime occurs when issuing a column expansion during a sysbench benchmark run on my new SSD arrays. I configured a 16 table 25 million rows per table sysbench database on InnoDB (MySQL 5.5.24) and TokuDB (v6.5 on MySQL 5.5.24) and started sysbench with 32 clients. This is a custom version of sysbench that I modified to run rate limited, the total transactional throughput is limited to 50 transactions per second. Ten minutes into the benchmark I issue the column expansion command as follows:
alter table sbtest1 modify column c char(200) not null default '';
As the below graph shows InnoDB blocks all modifications to the table for over 7 minutes, bringing it’s transactional throughput to 0 for the duration of that operation. TokuDB throughput is unaffected by the operation. While SSDs bring amazing IOPs and read/write throughput to servers, a full table rebuild is expensive, especially as you tables get large. And if you have large tables you might see hours or even days of unavailability for a particular table when expanding a column.
Can’t afford downtime? TokuDB enables zero-downtime when adding indexes and adding/deleting/expanding/renaming columns.
To learn more about TokuDB: