Benchmarking is a tricky thing, especially when it comes to compression. Some data compresses quite well while other data does not compress at all. Storing jpeg images in a BLOB column produces 0% compression, but storing the string “AAAAAAAAAAAAAAAAAAAA” in a VARCHAR(20) column produces extremely high (and unrealistic) compression numbers.
This week I was assisting a TokuDB customer understand the insertion performance of TokuDB versus InnoDB and MyISAM for their actual data. The table contained a single VARCHAR(50), multiple INTEGER, one SET, one DECIMAL, and a surrogate primary key. To support a varied query workload they needed 6 indexes.
Here is an obfuscated schema of the table:
col1 varchar(50) NOT NULL, col2 int(40) NOT NULL DEFAULT '0', col3 int(10) NOT NULL DEFAULT '0', col4 int(10) NOT NULL DEFAULT '0', col5 int(10) NOT NULL DEFAULT '0', col6 set('val1', 'val2', ..., ‘val19’, 'val20',) NOT NULL DEFAULT …[Read more]