Monthly Archives: April 2017

MariaDB 10.0: How to migrate from InnoDB to TokuDB

This is only to remember what I did to migrate from InnoDB to TokuDB on a MariaDB cluster.
TokuDB settings are specific to my environment, so please don't ask me what/how/why.

root@dbrfoo:~# mysql -u root -p
MariaDB [(none)]> INSTALL SONAME 'ha_tokudb';
Query OK, 0 rows affected (0.07 sec)

MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| TokuDB             | YES     | Tokutek TokuDB Storage Engine with Fractal Tree(tm) Technology             | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

MariaDB [(none)]> show plugins;
+-------------------------------+----------+--------------------+--------------+---------+
| Name                          | Status   | Type               | Library      | License |
+-------------------------------+----------+--------------------+--------------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL         | GPL     |
| mysql_old_password            | ACTIVE   | AUTHENTICATION     | NULL         | GPL     |
| MEMORY                        | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| MyISAM                        | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| CSV                           | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| MRG_MyISAM                    | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| PERFORMANCE_SCHEMA            | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| InnoDB                        | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| XTRADB_READ_VIEW              | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| XTRADB_INTERNAL_HASH_TABLES   | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| XTRADB_RSEG                   | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_TRX                    | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_LOCKS                  | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_LOCK_WAITS             | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMP                    | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMP_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMPMEM                 | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMPMEM_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMP_PER_INDEX          | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMP_PER_INDEX_RESET    | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_BUFFER_PAGE            | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_BUFFER_PAGE_LRU        | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_BUFFER_POOL_STATS      | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_METRICS                | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_DEFAULT_STOPWORD    | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_DELETED             | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_BEING_DELETED       | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_CONFIG              | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_INDEX_CACHE         | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_INDEX_TABLE         | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_TABLES             | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_TABLESTATS         | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_COLUMNS            | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_FIELDS             | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_FOREIGN            | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_FOREIGN_COLS       | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_TABLESPACES        | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_DATAFILES          | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CHANGED_PAGES          | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| ARCHIVE                       | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| Aria                          | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| FEDERATED                     | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| BLACKHOLE                     | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| FEEDBACK                      | DISABLED | INFORMATION SCHEMA | NULL         | GPL     |
| partition                     | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| TokuDB                        | ACTIVE   | STORAGE ENGINE     | ha_tokudb.so | GPL     |
| TokuDB_trx                    | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so | GPL     |
| TokuDB_lock_waits             | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so | GPL     |
| TokuDB_locks                  | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so | GPL     |
| TokuDB_file_map               | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so | GPL     |
| TokuDB_fractal_tree_info      | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so | GPL     |
| TokuDB_fractal_tree_block_map | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so | GPL     |
+-------------------------------+----------+--------------------+--------------+---------+
54 rows in set (0.00 sec)

MariaDB [(none)]> \q

root@dbrfoo:~# vim /etc/mysql/conf.d/tokudb.cnf

[mariadb]
# See https://mariadb.com/kb/en/how-to-enable-tokudb-in-mariadb/
# for instructions how to enable TokuDB
#
# See https://mariadb.com/kb/en/tokudb-differences/ for differences
# between TokuDB in MariaDB and TokuDB from http://www.tokutek.com/

#plugin-load=ha_tokudb.so

tokudb_cache_size=300GB
tokudb_commit_sync=off
tokudb_fsync_log_period=1000
tokudb_directio=ON
tokudb_disable_slow_alter=ON
tokudb_disable_hot_alter=OFF
tokudb_load_save_space=ON
tokudb_row_format=tokudb_fast


root@dbrfoo:~# /etc/init.d/mysql stop
root@dbrfoo:~# /etc/init.d/mysql start


----------------
150615 14:35:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
150615 14:36:09 mysqld_safe Starting mysqld daemon with databases from /data/db/mysql
150615 14:36:10 [Note] /usr/sbin/mysqld (mysqld 10.0.19-MariaDB-1~wheezy-log) starting as process 20812 ...
150615 14:36:10 [Note] InnoDB: Using mutexes to ref count buffer pool pages
150615 14:36:10 [Note] InnoDB: The InnoDB memory heap is disabled
150615 14:36:10 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
150615 14:36:10 [Note] InnoDB: Memory barrier is not used
150615 14:36:10 [Note] InnoDB: Compressed tables use zlib 1.2.7
150615 14:36:10 [Note] InnoDB: Using Linux native AIO
150615 14:36:10 [Note] InnoDB: Using CPU crc32 instructions
150615 14:36:10 [Note] InnoDB: Initializing buffer pool, size = 100.0G
150615 14:36:16 [Note] InnoDB: Completed initialization of buffer pool
150615 14:36:17 [Note] InnoDB: Highest supported file format is Barracuda.
150615 14:36:20 [Note] InnoDB: 128 rollback segment(s) are active.
150615 14:36:20 [Note] InnoDB: Waiting for purge to start
150615 14:36:20 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.23-72.1 started; log sequence number 38210935651348
150615 14:36:20 [Note] Plugin 'FEEDBACK' is disabled.
150615 14:36:20 [Note] Server socket created on IP: '::'.
150615 14:36:20 [Note] Event Scheduler: Loaded 0 events
150615 14:36:20 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.0.19-MariaDB-1~wheezy-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
----------------

root@dbrfoo:~# mysql -u root -p

MariaDB [(none)]> use xxxxxxxx_ugc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [xxxxxxxx_ugc]> alter table user_data engine=tokudb;
Stage: 1 of 2 'Fetched about 161900000 rows, loading data still remains'   3.49% of stage done