MySQL Schema Agility on SSDs

Posted on:



Share Button

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:

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.

Benchmark Results


Benchmark Environment

  • 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.
Share Button


, , , ,

Tokutek, TokuView