Is your MySQL database experiencing slow write speeds or high disk usage? TokuDB, a high-performance storage engine, can be the solution! This blog post will guide you through the installation process of TokuDB on Percona Server, addressing a common pain point for database administrators.
Unleashing Speed: TokuDB for Faster MySQL Write Performance
The TokuDB engine must be separately downloaded and then enabled as a plug-in component in previous versions. This package can be installed alongside with standard Percona Server 5.6 releases starting with 5.6.19-67.0 and does not require any specially adapted version of Percona Server. After acquisition Percona has integrated the TokuDB with their standard Percona Server distribution.
How TokuDB Can Save Your MySQL Database
The TokuDB storage engine is a scalable, ACID and MVCC compliant storage engine that provides indexing-based query improvements, offers online schema modifications, read free replication and reduces slave lag for both hard disk drives and flash memory. This storage engine is specifically designed for high performance on write-intensive workloads which is achieved with Fractal Tree indexing. Which make the writes faster and has a very good level of compression with ZLIB, QuickLZ and LZMA.
TokuDB on Percona Server
TokuDB has some good improvements while comparing the older versions. Many critical bugs were fixed and features like lock visualization were added in the latest. The TokuDb Version is merged with Percona Server version.
Download the latest version of percona servers.
Here we have used Percona-Server-5.7.11-4 here.
Temporary password will be displayed using the –initialize option.
[root@labs1 percona]$ ./bin/mysqld --defaults-file=/usrdir/root/percona/my.cnf --initialize --user=root
...
2016-03-17T22:05:16.763995Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-03-17T22:05:17.671445Z 0 [Warning] CA certificate ca.pem is self signed.
2016-03-17T22:05:17.827422Z 1 [Note] A temporary password is generated for root@localhost: ))uxoJ
[root@labs1 percona]$ mysql --user=root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.11-4
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \s
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user root@localhost identified by 'Root@123';
Query OK, 0 rows affected (0.01 sec)
By Default Tokudb was not enabled we have install the plugin manually.The steps are as follows:
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql> INSTALL PLUGIN tokudb SONAME 'ha_tokudb.so';
ERROR 1123 (HY000): Can't initialize function 'tokudb'; Plugin initialization function failed.
This is happen due to transparent huge pages.
It should be disabled while installing the tokudb plugins.
In Error log we can find out the exact scenario about this issue.
[root@labs1 percona]$ cat data/labs1.local.mydbops.com.err
....
2016-03-17T22:17:13.249566Z 2 [ERROR] TokuDB will not run with transparent huge pages enabled.
2016-03-17T22:17:13.249570Z 2 [ERROR] Please disable them to continue.
2016-03-17T22:17:13.249574Z 2 [ERROR] (echo never > /sys/kernel/mm/transparent_hugepage/enabled)
2016-03-17T22:17:13.249578Z 2 [ERROR]
2016-03-17T22:17:13.249594Z 2 [ERROR] ************************************************************
2016-03-17T22:17:13.249640Z 2 [ERROR] Plugin 'TokuDB' init function returned error.
2016-03-17T22:17:13.249648Z 2 [ERROR] Plugin 'TokuDB' registration as a STORAGE ENGINE failed.
2016-03-17T22:17:13.249666Z 2 [Note] Shutting down plugin 'TokuDB'
2016-03-17T22:33:35.988207Z mysqld_safe Number of processes running now: 0
2016-03-17T22:33:36.024939Z mysqld_safe mysqld restarted
...
To disable the transparent huge pages we should use this command
[root@labs1 percona]$ echo never > /sys/kernel/mm/transparent_hugepage/enabled
Installing the plugins which is need for tokudb:
For more details : Link
mysql> INSTALL PLUGIN tokudb SONAME 'ha_tokudb.so';
Query OK, 0 rows affected (0.76 sec)
mysql> INSTALL PLUGIN tokudb_file_map SONAME 'ha_tokudb.so';
Query OK, 0 rows affected (0.06 sec)
mysql> INSTALL PLUGIN tokudb_fractal_tree_info SONAME 'ha_tokudb.so';
Query OK, 0 rows affected (0.04 sec)
mysql> INSTALL PLUGIN tokudb_fractal_tree_block_map SONAME 'ha_tokudb.so';
Query OK, 0 rows affected (0.04 sec)
mysql> INSTALL PLUGIN tokudb_trx SONAME 'ha_tokudb.so';
Query OK, 0 rows affected (0.04 sec)
mysql> INSTALL PLUGIN tokudb_locks SONAME 'ha_tokudb.so';
Query OK, 0 rows affected (0.04 sec)
mysql> INSTALL PLUGIN tokudb_lock_waits SONAME 'ha_tokudb.so';
Query OK, 0 rows affected (0.04 sec)
mysql> INSTALL PLUGIN tokudb_background_job_status SONAME 'ha_tokudb.so';
Query OK, 0 rows affected (0.03 sec)
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| TokuDB | YES | Percona TokuDB Storage Engine with Fractal Tree(tm) Technology | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
The installed tokudb version is 5.6.27-76.0
mysql> SELECT @@tokudb_version;
+------------------+
| @@tokudb_version |
+------------------+
| 5.7.11-4 |
+------------------+
1 row in set (0.00 sec)
These are the tokudb variables:
mysql> show global variables like '%tokudb%';
+-----------------------------------------+-------------------------+
| Variable_name | Value |
+-----------------------------------------+-------------------------+
| tokudb_alter_print_error | OFF |
| tokudb_analyze_delete_fraction | 1.000000 |
| tokudb_analyze_in_background | ON |
| tokudb_analyze_mode | TOKUDB_ANALYZE_STANDARD |
| tokudb_analyze_throttle | 0 |
| tokudb_analyze_time | 5 |
| tokudb_auto_analyze | 30 |
| tokudb_block_size | 4194304 |
| tokudb_bulk_fetch | ON |
| tokudb_cache_size | 2017665024 |
| tokudb_cachetable_pool_threads | 0 |
| tokudb_cardinality_scale_percent | 100 |
| tokudb_check_jemalloc | 1 |
| tokudb_checkpoint_lock | OFF |
| tokudb_checkpoint_on_flush_logs | OFF |
| tokudb_checkpoint_pool_threads | 0 |
| tokudb_checkpointing_period | 60 |
| tokudb_cleaner_iterations | 5 |
| tokudb_cleaner_period | 1 |
| tokudb_client_pool_threads | 0 |
| tokudb_commit_sync | ON |
| tokudb_compress_buffers_before_eviction | ON |
| tokudb_create_index_online | ON |
| tokudb_data_dir | |
| tokudb_debug | 0 |
| tokudb_directio | OFF |
| tokudb_disable_hot_alter | OFF |
| tokudb_disable_prefetching | OFF |
| tokudb_disable_slow_alter | OFF |
| tokudb_empty_scan | rl |
| tokudb_enable_partial_eviction | ON |
| tokudb_fanout | 16 |
| tokudb_fs_reserve_percent | 5 |
| tokudb_fsync_log_period | 0 |
| tokudb_hide_default_row_format | ON |
| tokudb_killed_time | 4000 |
| tokudb_last_lock_timeout | |
| tokudb_load_save_space | ON |
| tokudb_loader_memory_size | 100000000 |
| tokudb_lock_timeout | 4000 |
| tokudb_lock_timeout_debug | 1 |
| tokudb_log_dir | |
| tokudb_max_lock_memory | 252208128 |
| tokudb_optimize_index_fraction | 1.000000 |
| tokudb_optimize_index_name | |
| tokudb_optimize_throttle | 0 |
| tokudb_pk_insert_mode | 1 |
| tokudb_prelock_empty | ON |
| tokudb_read_block_size | 65536 |
| tokudb_read_buf_size | 131072 |
| tokudb_read_status_frequency | 10000 |
| tokudb_row_format | tokudb_zlib |
| tokudb_rpl_check_readonly | ON |
| tokudb_rpl_lookup_rows | ON |
| tokudb_rpl_lookup_rows_delay | 0 |
| tokudb_rpl_unique_checks | ON |
| tokudb_rpl_unique_checks_delay | 0 |
| tokudb_strip_frm_data | OFF |
| tokudb_support_xa | ON |
| tokudb_tmp_dir | |
| tokudb_version | 5.7.11-4 |
| tokudb_write_status_frequency | 1000 |
+-----------------------------------------+-------------------------+
62 rows in set (0.00 sec)
Things To be considered:
1) TokuDB Do not support Foreign keys.
2) Needs TokuDB Plugin for Hot backup ( Xtrabackup do not support TokuDB ).
3) Full text index is not supported.
4) It depends on both OS memory and allocate memory ( Toku cache ) where as InnoDB do not depends on the OS memory much.
5) With high Compression level there is a trade off in CPU usage.
While TokuDB offers significant performance advantages, managing its intricacies can be challenging. Mydbops' MySQL experts can help you seamlessly integrate TokuDB, optimize its configuration, and ensure your database runs at peak efficiency.
{{cta}}
.png)
.png)
.svg)
