Installing MariaDB and TokuDB in Ubuntu Trusty

Background

In this post I would tell a story about installing MariaDB in Ubuntu Trusty, and the process that I went through to enable TokuDB engine. I need to experiment using the engine as an alternative to the Archive engine, to store compressed table rows. It have better performance than InnoDB tables (row_format=compressed), and it was recommended in some blog posts (this post and this) 

Packages for Ubuntu Trusty

In order to be able to use TokuDB, I seek the documentation and find out that Ubuntu version 12.10 and newer for 64-bit platform requires mariadb-tokudb-engine-5.5 package.  Despite the existence of mariadb-5.5 packages, I found no package containing tokudb keyword in the official Ubuntu Trusty repositories. The mariadb 5.5 server package also doesn't contain ha_tokudb.so (see file list). 

The solution is to use the repository from this online wizard.

Installing mariadb-server-10.1, we have many storage engines available, tokudb and cassandra being the more interesting ones. 

Preparation - disable Hugepages

Kernel hugepages are not compatible with TokuDB Engine. I disabled it by inserting some rows in /etc/rc.local :

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then

   echo never > /sys/kernel/mm/transparent_hugepage/enabled

fi

if test -f /sys/kernel/mm/transparent_hugepage/defrag; then

   echo never > /sys/kernel/mm/transparent_hugepage/defrag

fi

Enabling TokuDB

I enabled tokudb by running this command in mariaDB's SQL prompt as root:

INSTALL SONAME 'ha_tokudb';

Upon retrospect, maybe I supposed to uncomment the plugin load line in /etc/mysql/conf.d/tokudb.conf .

Using TokuDB

Having enabled tokuDB, check by show engines :

Cool.

The syntax to use it from MariaDB is a bit different from Percona or Tokutek distribution :

CREATE TABLE xxx (columns .., PRIMARY KEY pk_name(pk_field1,pk_field2..)) ENGINE = TokuDB COMPRESSION=TOKUDB_SNAPPY;

We also could transform existing InnoDB table (or other kind of tables) into TokuDB table, but beware that this will recreate the entire table in TokuDB engine :

ALTER TABLE xxx ENGINE =TokuDB COMPRESSION=TOKUDB_SNAPPY;

There are two ways of optimizing TokuDB tables, the first one is to do light 'maintenance' :

OPTIMIZE TABLE xxx;

But if you want to free some space you need to recreate (reorg?) the table :

ALTER TABLE xxx ENGINE =TokuDB COMPRESSION=TOKUDB_SNAPPY;

The compression options (refer here but beware of syntax differences) is as follows : 

• tokudb_default, tokudb_zlib: compress using zlib library, medium CPU and compression ratio
• tokudb_fast, tokudb_quicklz: Use the quicklz library, the lightest compression with low CPU usage,
• tokudb_small, tokudb_lzma: Use the lzma library. the highest compression and highest CPU usage
• tokudb_uncompressed: No compression is used.
• tokudb_snappy: compression using Google's snappy algorithm, reasonable compression and fast performance.

Caveats

• Currently I still cannot enable InnoDB/XtraDB page level compression
• Syntax differences confused me sometimes, some information that are not clear in MariaDB's website can be read in Percona's website.
• Xtrabackup doesn't work for TokuDB tables, you need plain mysqldump or mydumper to backup TokuDB tables
• Mydumper in Ubuntu Trusty repository doesn't work with MariaDB 10.1
• I still unable to compile recent Mydumper version in Ubuntu Trusty - MariaDB 10.1 combination