Shulou(Shulou.com)05/31 Report--

How to carry out Tokudb installation testing, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

A preface

TokuDB is a high-performance storage engine that supports MVCC's MySQL and MariaDB. The main feature of TokuDB is excellent data compression and support for high write pressure, which was developed by TokuTek (http://www.tokutek.com/)), which was acquired by Percona in April 2015 and, of course, provided the TokuDB version of Percona Server. This article uses Peronca server version 5.6.30 for test installation.

Preparation before installation

Please refer to the official documentation Percona Server YUM source, Percona Server tokudb installation documentation

Three installation steps

3.1 close the large page of the system

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

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

Echo never > / sys/kernel/mm/redhat_transparent_hugepage/enabled

Echo never > / sys/kernel/mm/redhat_transparent_hugepage/defrag

3.2 disable SELINUX

Root@rac2:~# > vim / etc/selinux/config set SELINUX=disabled

Root@rac2:~# > setenforce 0

Root@rac2:~# > getenforce

Undefined3.3 installs using percona's yum source

Yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Yum list | grep percona # check whether there are tokudb-related rpm packets in the yum source

Yum install Percona-Server-tokudb-56.x86_64-y

3.4 initialize the database instance and start the database

/ usr/bin/mysql_install_db-user=mysql-datadir=/srv/my3306/data-basedir=/usr/-defaults-file=/srv/my3306/my.cnf &

/ usr/bin/mysqld_safe-defaults-file=/srv/my3306/my.cnf-user=mysql &

3.5 use ps_tokudb_admin to install the tokudb storage engine, remembering that the instance must be in a startup state.

Root@rac2:/srv/my3306/data# > ps_tokudb_admin-- enable-uroot-h227.0.0.1-P 3306

Checking SELinux status...

INFO: SELinux is in permissive mode.

Checking if Percona Server is running with jemalloc enabled...

INFO: Percona Server is running with jemalloc enabled.

Checking transparent huge pages status on the system...

INFO: Transparent huge pages are currently disabled on the system.

Checking if thp-setting=never option is already set in config file...

INFO: Option thp-setting=never is not set in the config file.

(needed only if THP is not disabled permanently on the system)

Checking TokuDB engine plugin status...

INFO: TokuDB engine plugin is not installed.

Adding thp-setting=never option into / etc/my.cnf

INFO: Successfully added thp-setting=never option into / etc/my.cnf

Installing TokuDB engine...

INFO: Successfully installed TokuDB engine plugin. -- > indicates that the Tokudb storage engine plug-in was installed successfully

3.6 Log in to the instance to check

Root@rac2:/srv/my3306/data# > my3306

Server version: 5.6.31-77.0-log Percona Server (GPL), Release 77.0, Revision 5c1061c

Mysql > show engines

+-- +

| | Engine | Support | Comment | Transactions | XA | Savepoints |

+-- +

| | TokuDB | YES | Percona TokuDB Storage Engine with Fractal Tree (tm) Technology | YES | YES | YES |

+-- +

10 rows in set (0.02 sec)

Mysql > SELECT @ @ tokudb_version

+-+

| | @ @ tokudb_version |

+-+

| | 5.6.31-77.0 | |

+-+

1 row in set (0.00 sec)

Four tests

4.1 Compression ratio

Create tables for the innodb and tokudb storage engines, and test inserts and file sizes.

Mysql > create table t_tokudb (id int,val varchar) engine=tokudb default charset utf8

Query OK, 0 rows affected (0.05 sec)

Mysql > insert into t_tokudb (val) select val from t_tokudb

Query OK, 262144 rows affected (2.32 sec)

Records: 262144 Duplicates: 0 Warnings: 0

Mysql > CREATE TABLE `t _ innodb` (

-> `id` int (11) DEFAULT NULL

-> `val` varchar (256) DEFAULT NULL

->) ENGINE=InnoDB DEFAULT CHARSET=utf8

Query OK, 0 rows affected (0.13 sec)

Mysql > insert into t_innodb select * from t_tokudb

Query OK, 1048576 rows affected (10.40 sec)

Records: 1048576 Duplicates: 0 Warnings: 0

View the size of the innodb and tokudb tables

Root@rac2:/srv/my3306/data# > du-sm _ yang_t_tokudb_main_5_2_1d.tokudb

8 _ yang_t_tokudb_main_5_2_1d.tokudb

Root@rac2:/srv/my3306/data/yang# > du-sm t_innodb.ibd

149 t_innodb.ibd

Root@rac2:/srv/my3306/data/yang# >

Innodb 149M

Tokudb 8M

The compression ratio reached an astonishing 149 gambit 8 = 18:1. Because the values of val in the test examples are all the same, there will be more different vales in the production environment, and the compression ratio will be reduced.

4.2 Storage engine conversion

Mysql > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (10) NOT NULL AUTO_INCREMENT

`val` varchar (10) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

1 row in set (0.01 sec)

Mysql > alter table T1 engine=tokudb

Query OK, 4 rows affected (0.25 sec)

Records: 4 Duplicates: 0 Warnings: 0

4.3 online ddl Test

Mysql > alter table t_tokudb add name varchar (30) default 'a'

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table t_tokudb add key idx_a (name)

Query OK, 0 rows affected (4.79 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table t_tokudb drop key idx_a

Query OK, 0 rows affected (0.57 sec)

Records: 0 Duplicates: 0 Warnings: 0

Summary of five TokuDB

Characteristics of TokuDB

1 High compression ratio. Zlib is used for compression by default, especially for strings (varchar,text, etc.), which is more suitable for storing logs, raw data, etc. It generally has a compression ratio of 5-10 times.

2 supports online field addition, deletion, expansion and renaming operations.

3 support complete ACID features and transaction mechanism

4 support fast write scenarios, Fractal-tree has advantages in transaction implementation, no undo log

Shortcomings of TokuDB

1. The response time is relatively long

2Magna online ddl is not suitable for fields such as text,blob.

3. There is no perfect hot backup tool. At present, there are backup schemes and logical backups recommended by Ali Cloud Database team.

It is recommended that scenarios apply:

1 archiving of frequently accessed data or historical data

2 the table is very large and requires ddl operation from time to time

How to choose the compression algorithm of TokuDB

Tokudb_zlib: zlib compression is enabled for the table, the compression effect is moderate, and the CPU consumption is moderate. It is recommended to use (default)

Tokudb_quicklz: table with quicklz compression enabled, poor compression effect and low CPU consumption

Tokudb_lzma: lzma compression is enabled for the table with good compression effect and high CPU consumption.

The default compression algorithm for TokuDB is zlib, and it is recommended that you do not modify it, because zlib compression is very cost-effective.

After reading the above, have you mastered the method of how to test Tokudb installation? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.