MariaDB 使用TokuDB存储引擎

MariaDB 使用TokuDB存储引擎

一.安装TokuDB引擎

INSTALL SONAME 'ha_tokudb.so';

SHOW ENGINES;

FYI: In the MariaDB binary tarballs, only the ones labeled "glibc_214" have TokuDB.

+-------------------------------+----------+--------------------+--------------+---------+

二.默认使用TokuDB存储引擎

https://mariadb.com/kb/en/tokudb/

SET GLOBAL default_storage_engine = TokuDB;

[mysqld]

default-storage-engine = TokuDB

*************************** 5. row ***************************

Engine: TokuDB

Support: DEFAULT

Comment: Percona TokuDB Storage Engine with Fractal Tree(tm) Technology

Transactions: YES

XA: YES

Savepoints: YES

三.自定义相关TokuDB变量

[mysqld]

tokudb-cache-size = 9GB #不设置是默认会设置为系统内存的1/2

tokudb-data-dir = /mnt/sdb1

tokudb-log-dir = /mnt/sdb1

tokudb-tmp-dir = /mnt/sdc1

禁用write cache

 hdparm -W0 /dev/sdb1

hdparm -W0 /dev/sdc2

四.使用 TokuDB存储引擎

先导入示例数据库http://www.isfdb.org/wiki/index.php/ISFDB_Downloads

wget http://isfdb.s3.amazonaws.com/backups/backup-MySQL-55-2016-03-26.zip

unzip backup-MySQL-55-2016-03-26.zip

CREATE DATABASE isfdb CHARACTER SET utf8;

mysql -ujlive -p isfdb

1.建表

USE isfdb;

CREATE TABLE authors_tokudb (

author_id int NOT NULL AUTO_INCREMENT,

author_canonical mediumtext,

author_lastname varchar(128),

author_birthplace mediumtext,

author_birthdate date DEFAULT NULL,

author_deathdate date DEFAULT NULL,

PRIMARY KEY (author_id),

KEY (author_lastname),

KEY (author_birthdate),

KEY (author_deathdate)

) ENGINE=TokuDB;

2.插入数据

INSERT authors_tokudb

SELECT

author_id, author_canonical,

author_lastname, author_birthplace,

author_birthdate, author_deathdate

FROM authors;

3.查看索引

SHOW INDEXES FROM authors_tokudb;

4.调优

OPTIMIZE TABLE authors_tokudb;

五.转换存储引擎到TokuDB

ALTER TABLE pub_authors ENGINE=TokuDB;

ALTER TABLE pub_content ENGINE=TokuDB;

ALTER TABLE pub_series ENGINE=TokuDB;

ALTER TABLE publishers ENGINE=TokuDB;

ALTER方式将某张表转换存储引擎，实际上做的事情如下，

CREATE TABLE notes_tokudb LIKE notes;

ALTER TABLE notes_tokudb ENGINE=TokuDB;

SELECT * FROM notes INTO OUTFILE '/tmp/notes.tmp';

LOAD DATA INFILE '/tmp/notes.tmp' INTO TABLE notes_tokudb;

(jlive)[isfdb]>CREATE TABLE notes_tokudb LIKE notes;

Query OK, 0 rows affected (0.15 sec)

(jlive)[isfdb]>ALTER TABLE notes_tokudb ENGINE=TokuDB;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

(jlive)[isfdb]>SELECT * FROM notes INTO OUTFILE '/tmp/notes.tmp';

Query OK, 417609 rows affected (0.42 sec)

(jlive)[isfdb]>LOAD DATA INFILE '/tmp/notes.tmp' INTO TABLE notes_tokudb;

Query OK, 417609 rows affected, 51940 warnings (4.32 sec)

Records: 417609 Deleted: 0 Skipped: 0 Warnings: 51940

六.添加index到TokuDB表

SHOW INDEXES FROM authors;

ALTER TABLE authors

DROP KEY canonical,

ADD CLUSTERING KEY canonical (author_canonical(50)),

ENGINE=TokuDB;

CREATE CLUSTERING INDEX birthdate ON authors (author_birthdate);

七.压缩TokuDB表

相较其它存储引擎，TokuDB最大的特点就是在数据高压缩率的情况下仍然具有非常高的性能

ALTER TABLE titles ENGINE=TokuDB

ALTER TABLE titles ENGINE=TokuDB ROW_FORMAT=default;

在没有指定压缩率时，压缩率默认为default

ALTER TABLE pub_content ENGINE=TokuDB ROW_FORMAT=tokudb_small;

TokuDB官方建议

<=6核压缩率选default

>6核压缩率选tokudb_small

tokudb_fast是tokudb_quicklz的别名，默认的压缩选项目前是tokudb_fast

ALTER TABLE canonical_author ENGINE=TokuDB ROW_FORMAT=tokudb_fast;

tokudb_small是tokudb_lzma的别名

ALTER TABLE notes ENGINE=TokuDB ROW_FORMAT=tokudb_lzma;

不压缩

ALTER TABLE pubs ENGINE=TokuDB ROW_FORMAT=tokudb_uncompressed;

OPTIMIZE TABLE titles, pub_content, canonical_author, notes, pubs;

(jlive)[isfdb]>OPTIMIZE TABLE titles, pub_content, canonical_author, notes, pubs;

+------------------------+----------+----------+-------------------------------------------------------------------+

+------------------------+----------+----------+-------------------------------------------------------------------+

+------------------------+----------+----------+-------------------------------------------------------------------+

7 rows in set (10.22 sec)