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:
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.