Saturday, January 2, 2016

Installing MariaDB and TokuDB in Ubuntu Trusty


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 (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
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
   echo never > /sys/kernel/mm/transparent_hugepage/defrag

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 :


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 :


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


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


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.


  • 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

No comments: