MySQL Schema Agility on SSDs
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.
- HP DL380; 2 x Xeon 5520; 72GB RAM; LSI MegaRaid 9285; 2 x Samsung 830; RAID 0; Centos 5.8
Can’t afford downtime? TokuDB enables zero-downtime when adding indexes and adding/deleting/expanding/renaming columns.
To learn more about TokuDB:
- Download a free trial of TokuDB.
- Read the press release here.
- See news coverage here.
- Attend a Webinar overview of TokuDB v6.5.
- Come to our booth #6202 at MySQL Connect Sept. 29 & 30 in San Francisco
- Come to our table October 1 & 2 at Percona Live NYC.
- Catch Bradley Kuszmaul’s presentation “Solving the Challenges of Big Databases with MySQL” at 5:45 pm on Sunday, Sep. 30, at MySQL Connect and at 1:30 p.m. on Tuesday, Oct. 2, at Percona Live NYC.