Recently I encountered a specific situation in which a customer was forced to stay with the MyISAM engine due to a legacy application using tables with over 1000 columns. Unfortunately InnoDB has a limit at this point. I did not expect to hear this argument for MyISAM. It is usually about full text search or spatial indexes functionality that were missing in InnoDB, and which were introduced in MySQL 5.6 and 5.7, respectively, to let people forget about MyISAM. In this case though, InnoDB still could not be used, so I gave the TokuDB a try.

I’ve created a simple bash script to generate a SQL file with …

New to TokuDB® v7.5 is a feature we’re calling “Read Free Replication” (RFR). RFR allows TokuDB replication slaves to process insert, update, and delete statements with almost no read IO. As a result, the slave can easily keep up with the master (no lag) as well as brings all the read IO capacity of the slave for read-scaling your workload.

The goal of this blog is two-fold: (1) to cover why RFR is important and how RFR works and (2) to run a simple before/after benchmark showing the impact of RFR on a well known workload. Later this week I’ll post another blog showing other interesting use-cases for RFR beyond this first benchmark.

Read Free Replication: The Why and How

In MySQL, a replication slave does less work than the master because there is no need for a slave to execute SELECT statements (only INSERT, UPDATE, and DELETE). However, a MYSQL slave can struggle to keep up with the master because replication is …

Today we released TokuDB® v7.5, the latest version of Tokutek’s storage engine for MySQL and MariaDB.

I’ll be publishing two blogs next week to go into more details about our new “Read Free Replication”, but here are high level descriptions of the most important new features.

Read Free Replication

TokuDB replication slaves can now be configured to process the binary logs with virtually no read IO. This is accomplished via two new server parameters: one to allow the skipping of uniqueness checks (for inserts and updates), the other to eliminate read-before-write behavior (for updates and deletes). The two other conditions are that the slave must be in read-only mode and replication must be row based.

Hot Backup Now Supports Multiple Directories (Enterprise Edition)

The original implementation of our Hot Backup functionality was only capable of …

In my last post, “A closer look at the MySQL ibdata1 disk space issue and big tables,” I looked at the growing ibdata1 problem under the perspective of having big tables residing inside the so-called shared tablespace. In the particular case that motivated that post, we had a customer running out of disk space in his server who was looking for a way to make the ibdata1 file shrink. As you may know, that file (or, as explained there, the set of ibdata files composing the shared tablespace) stores all InnoDB tables created when innodb_file_per_table is disabled, but also other InnoDB structures, such as undo logs and data dictionary.

For example, when you run a transaction involving InnoDB tables, MySQL will first write all the changes it triggers in an undo log, for the case you later …

In my last post, “A closer look at the MySQL ibdata1 disk space issue and big tables,” I looked at the growing ibdata1 problem under the perspective of having big tables residing inside the so-called shared tablespace. In the particular case that motivated that post, we had a customer running out of disk space in his server who was looking for a way to make the ibdata1 file shrink. As you may know, that file (or, as explained there, the set of ibdata files composing the shared tablespace) stores all InnoDB tables created when innodb_file_per_table is disabled, but also other InnoDB structures, such as undo logs and data dictionary.

For example, when you run a transaction involving InnoDB tables, MySQL will first write all the changes it triggers in an undo log, for the case you later decide to …

Covering indexes can result in orders of magnitude performance improvements for queries. Bradley’s presentation on covering indexes describes what a covering index is, how it can effect performance, and why it works. However, the definition of a covering index can get cumbersome since MySQL limits the number of columns in a key to 16 (32 on MariaDB).

Tokutek introduced multiple clustering indexes into MySQL to address these problems. Zardosht describes the multiple clustering indexes feature and how clustering indexes differ from covering indexes. Zardosht also describes the …

I recently ran across some TokuDB table corruption, which was not easily identifiable at first, and the error log entry was not too verbose either, so I wanted to share that experience here.

Basically, TokuDB crashed, and then mysqld had problems restarting afterward. Just for reference, the error log had the following in the stack trace:

/usr/lib64/mysql/plugin/ha_tokudb.so(+0x71c48)[0x7fb25be75c48]
/usr/lib64/mysql/plugin/ha_tokudb.so(+0x71cbd)[0x7fb25be75cbd]
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z29toku_deserialize_bp_from_disk...
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z23toku_ftnode_pf_callback...
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z30toku_cachetable_pf_pinned_pair..
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z24toku_ft_flush_some_child...
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z28toku_ftnode_cleaner_callback...
/usr/lib64/mysql/plugin/ha_tokudb.so(_ZN7cleaner11run_cleaner...
/usr/lib64/mysql/plugin/ha_tokudb.so(+0xcfeee)[0x7fb25bed3eee] …

When setting up TokuDB, you may encounter error 1126.

I’ve seen 2 recent invocations of it, so I wanted to share them both here in case you run into this issue:

MariaDB [(none)]> install soname 'ha_tokudb';
ERROR 1126 (HY000): Can't open shared library
'/usr/lib/mysql/plugin/ha_tokudb.so' (errno: 2, undefined symbol: bitmap_free)

MariaDB [(none)]> install soname 'ha_tokudb';
ERROR 1126 (HY000): Can't open shared library 'ha_tokudb.so'
(errno: 8, API version for STORAGE ENGINE plugin TokuDB not
supported by this version of the server)

The latter is a bit more descriptive, but the former is fairly cryptic.

Given the latter, as you may have guessed it, if you run into either of these, you have the wrong version of ha_tokudb.so in your plugin directory – that is, it is not the correct version for your MariaDB/MySQL.

In both cases, 10.0.11 was used. In the first case, the ha_tokudb.so was from …

If you want to use TokuDB with MariaDB, MySQL, or Percona Server, you will need to disable support for transparent hugepages in Linux.

Fortunately, this is very easy to check, and to change.

An easy way to check is with:

cat /sys/kernel/mm/transparent_hugepage/enabled

This will return something like:

[always] madvise never

Note the word surrounded by “[]” is what this option is set to. So the above is set to “always”. To disable it, we want it set to “never”.

I’ve found the easiest way to change/set this is to add the below to your /etc/rc.local file (and then reboot your system):

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 …

TokuDB is gaining more and more popularity and many people are finding it very helpful for certain cases.

Using/enabling it in some distributions can be quite a pain, but enabling it in MariaDB is easy as 1-2-…, well, only 1-2, since that’s all there is to it!

1. Ensure you have the correct MariaDB version:

• MariaDB 5.5.36+
• MariaDB 10.0.9+
• Note: Linux 64-bit systems only – specific packages include: Ubuntu, Debian, Fedora, CentOS, Red Hat
• Note: If using the Linux tarball – it must be the version built with glibc 2.14+

2. Run this command:

INSTALL SONAME 'ha_tokudb';

or update my.cnf file with:

[mysqld]
plugin-load=ha_tokudb

There is one requirement from TokuDB, which is to have transparent …