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
| 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
|
+-------------------------------+----------+--------------------+--------------+---------+
二.默认使用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存储引擎
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;
+------------------------+----------+----------+-------------------------------------------------------------------+
|
Table
| Op
| Msg_type
| Msg_text
|
+------------------------+----------+----------+-------------------------------------------------------------------+
|
isfdb.titles
| optimize
| note | Table does not
support optimize, doing recreate + analyze instead |
|
isfdb.titles
| optimize
| status | OK
|
|
isfdb.pub_content
| optimize | note
| Table does not support optimize, doing
recreate + analyze instead |
|
isfdb.pub_content
| optimize | status |
OK
|
|
isfdb.canonical_author | optimize | status |
OK
|
|
isfdb.notes
| optimize
| status | OK
|
|
isfdb.pubs
| optimize
| status | OK
|
+------------------------+----------+----------+-------------------------------------------------------------------+
7 rows in
set (10.22 sec)