TokuDB安装及注意事项有哪些

51次阅读

没有评论

共计 17037 个字符，预计需要花费 43 分钟才能阅读完成。

这篇文章将为大家详细讲解有关 TokuDB 安装及注意事项有哪些，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

TokuDB 的特点：
1、高压缩比，官方宣称可以达到 1：12。
2、高 insert 性能，官方称至少比 innodb 高 9 倍。
3、可以在线添加索引和字段，速度快。

TokuDB 的缺点：
1、cpu usr 态消耗高
2、响应时间变长。

安装环境:
OS: centos 6.5
IP:192.168.0.103
TokuDB:5.6.29

安装介质:
Percona-Server-shared、Percona-Server-client、Percona-Server-server、Percona-Server-tokudb（注意安装顺序）

安装介质下载:
https://www.percona.com/downloads/Percona-Server-5.6/LATEST/

安装步骤:
1、先安装 Percona-Server-shared
[root@linfytest3 Downloads]# rpm -ivh Percona-Server-shared-56-5.6.29-rel76.2.el6.x86_64.rpm
warning: Percona-Server-shared-56-5.6.29-rel76.2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing… ########################################### [100%]
1:Percona-Server-shared-5########################################### [100%]

2、再安装 Percona-Server-client
[root@linfytest3 Downloads]# rpm -ivh Percona-Server-client-56-5.6.29-rel76.2.el6.x86_64.rpm
warning: Percona-Server-client-56-5.6.29-rel76.2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing… ########################################### [100%]
1:Percona-Server-client-5########################################### [100%]

3、再安装 Percona-Server-server
[root@linfytest3 Downloads]# rpm -ivh Percona-Server-server-56-5.6.29-rel76.2.el6.x86_64.rpm
warning: Percona-Server-server-56-5.6.29-rel76.2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing… ########################################### [100%]
1:Percona-Server-server-5########################################### [100%]
2016-05-08 19:36:57 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2016-05-08 19:36:57 0 [Note] /usr/sbin/mysqld (mysqld 5.6.29-76.2) starting as process 3039 …
2016-05-08 19:36:57 3039 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-05-08 19:36:57 3039 [Note] InnoDB: The InnoDB memory heap is disabled
2016-05-08 19:36:57 3039 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-05-08 19:36:57 3039 [Note] InnoDB: Memory barrier is not used
2016-05-08 19:36:57 3039 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-05-08 19:36:57 3039 [Note] InnoDB: Using Linux native AIO
2016-05-08 19:36:57 3039 [Note] InnoDB: Using CPU crc32 instructions
2016-05-08 19:36:57 3039 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-05-08 19:36:57 3039 [Note] InnoDB: Completed initialization of buffer pool
2016-05-08 19:36:57 3039 [Note] InnoDB: Highest supported file format is Barracuda.
2016-05-08 19:36:57 3039 [Note] InnoDB: 128 rollback segment(s) are active.
2016-05-08 19:36:57 3039 [Note] InnoDB: Waiting for purge to start
2016-05-08 19:36:57 3039 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.29-76.2 started; log sequence number 1600607
2016-05-08 19:36:57 3039 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2016-05-08 19:36:57 3039 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2016-05-08 19:36:58 3039 [Note] Binlog end
2016-05-08 19:36:58 3039 [Note] InnoDB: FTS optimize thread exiting.
2016-05-08 19:36:58 3039 [Note] InnoDB: Starting shutdown…
2016-05-08 19:36:59 3039 [Note] InnoDB: Shutdown completed; log sequence number 1625987

2016-05-08 19:36:59 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2016-05-08 19:36:59 0 [Note] /usr/sbin/mysqld (mysqld 5.6.29-76.2) starting as process 3064 …
2016-05-08 19:36:59 3064 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-05-08 19:36:59 3064 [Note] InnoDB: The InnoDB memory heap is disabled
2016-05-08 19:36:59 3064 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-05-08 19:36:59 3064 [Note] InnoDB: Memory barrier is not used
2016-05-08 19:36:59 3064 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-05-08 19:36:59 3064 [Note] InnoDB: Using Linux native AIO
2016-05-08 19:36:59 3064 [Note] InnoDB: Using CPU crc32 instructions
2016-05-08 19:36:59 3064 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-05-08 19:36:59 3064 [Note] InnoDB: Completed initialization of buffer pool
2016-05-08 19:37:00 3064 [Note] InnoDB: Highest supported file format is Barracuda.
2016-05-08 19:37:00 3064 [Note] InnoDB: 128 rollback segment(s) are active.
2016-05-08 19:37:00 3064 [Note] InnoDB: Waiting for purge to start
2016-05-08 19:37:00 3064 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.29-76.2 started; log sequence number 1625987
2016-05-08 19:37:00 3064 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2016-05-08 19:37:00 3064 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2016-05-08 19:37:00 3064 [Note] Binlog end
2016-05-08 19:37:00 3064 [Note] InnoDB: FTS optimize thread exiting.
2016-05-08 19:37:00 3064 [Note] InnoDB: Starting shutdown…
2016-05-08 19:37:02 3064 [Note] InnoDB: Shutdown completed; log sequence number 1625997

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password new-password
/usr/bin/mysqladmin -u root -h linfytest3 password new-password

Alternatively you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

Please report any problems at
https://bugs.launchpad.net/percona-server/+filebug

The latest information about Percona Server is available on the web at
http://www.percona.com/software/percona-server

Support Percona by buying support at
http://www.percona.com/products/mysql-support

WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
–defaults-file argument to mysqld_safe when starting the server

Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit.
Run the following commands to create these functions:
mysql -e CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME libfnv1a_udf.so
mysql -e CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME libfnv_udf.so
mysql -e CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME libmurmur_udf.so
See http://www.percona.com/doc/percona-server/5.6/management/udf_percona_toolkit.html for more details

4、安装 jemalloc
[root@linfytest3 Downloads]# rpm -ivh jemalloc-3.6.0-2.el5.x86_64.rpm
Preparing… ########################################### [100%]
1:jemalloc ########################################### [100%]

5、安装 Percona-Server-tokudb

[root@linfytest3 Downloads]# rpm -ivh Percona-Server-tokudb-56-5.6.29-rel76.2.el6.x86_64.rpm
warning: Percona-Server-tokudb-56-5.6.29-rel76.2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing… ########################################### [100%]
1:Percona-Server-tokudb-5########################################### [100%]

* This release of Percona Server is distributed with TokuDB storage engine.
* Run the following script to enable the TokuDB storage engine in Percona Server:

ps_tokudb_admin –enable -u mysql_admin_user -p[mysql_admin_pass] [-S socket] [-h host -P port]

* See http://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_installation.html for more installation details

* See http://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_intro.html for an introduction to TokuDB

6、启动 MySQL
/usr/sbin/mysqld

root@linfytest3 sbin]# ./mysqld -uroot
2016-05-08 20:21:47 0 [Warning] Ignoring user change to root because the user was set to mysql earlier on the command line

2016-05-08 20:21:47 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2016-05-08 20:21:47 0 [Note] ./mysqld (mysqld 5.6.29-76.2) starting as process 3848 …
2016-05-08 20:21:47 3848 [Note] Plugin FEDERATED is disabled.
2016-05-08 20:21:47 3848 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-05-08 20:21:47 3848 [Note] InnoDB: The InnoDB memory heap is disabled
2016-05-08 20:21:47 3848 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-05-08 20:21:47 3848 [Note] InnoDB: Memory barrier is not used
2016-05-08 20:21:47 3848 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-05-08 20:21:47 3848 [Note] InnoDB: Using Linux native AIO
2016-05-08 20:21:47 3848 [Note] InnoDB: Using CPU crc32 instructions
2016-05-08 20:21:47 3848 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-05-08 20:21:47 3848 [Note] InnoDB: Completed initialization of buffer pool
2016-05-08 20:21:48 3848 [Note] InnoDB: Highest supported file format is Barracuda.
2016-05-08 20:21:48 3848 [Note] InnoDB: 128 rollback segment(s) are active.
2016-05-08 20:21:48 3848 [Note] InnoDB: Waiting for purge to start
2016-05-08 20:21:48 3848 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.29-76.2 started; log sequence number 1626017
2016-05-08 20:21:48 3848 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2016-05-08 20:21:48 3848 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2016-05-08 20:21:48 3848 [Note] Server hostname (bind-address): * port: 3306
2016-05-08 20:21:48 3848 [Note] IPv6 is available.
2016-05-08 20:21:48 3848 [Note] – :: resolves to ::
2016-05-08 20:21:48 3848 [Note] Server socket created on IP: :: .
2016-05-08 20:21:48 3848 [Note] Event Scheduler: Loaded 0 events
2016-05-08 20:21:48 3848 [Note] ./mysqld: ready for connections.
Version: 5.6.29-76.2 socket: /var/lib/mysql/mysql.sock port: 3306 Percona Server (GPL), Release 76.2, Revision ddf26fe

7、登录 mysql
[root@linfytest3 sbin]# /usr/bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-76.2 Percona Server (GPL), Release 76.2, Revision ddf26fe

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 show engines;
+——————–+———+—————————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————————-+————–+——+————+
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| CSV | YES | CSV storage engine | 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 |
| 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 |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+——————–+———+—————————————————————————-+————–+——+————+
9 rows in set (0.01 sec)

8、安装 tokuDB 插件

[root@linfytest3 bin]# /usr/bin/ps_tokudb_admin –enable -u root -P 3848 -p
Enter password:

Checking SELinux status…
ERROR: SELinux is in enforcing mode and needs to be disabled (or put into permissive mode) for TokuDB to work correctly.

[root@linfytest3 bin]# /usr/sbin/sestatus
SELinux status: enabled
SELinuxfs mount: /selinux
Current mode: enforcing
Mode from config file: enforcing
Policy version: 24
Policy from config file: targeted
[root@linfytest3 bin]# getenforce
Enforcing

[root@linfytest3 bin]# setenforce 0
[root@linfytest3 bin]# getenforce
Permissive

root@linfytest3 bin]# /usr/bin/ps_tokudb_admin –enable -u root -P 3848 -p
Enter password:

Checking SELinux status…
INFO: SELinux is in permissive mode.

Checking if Percona Server is running with jemalloc enabled…
ERROR: Percona Server is not running with jemalloc, please restart mysql service to enable it and then run this script…

关闭 mysql server
/usr/bin/mysqladmin shutdown -uroot -p

启动 Mysql server
root@linfytest3 bin]# ./mysqld_safe
[1] 5579
[root@linfytest3 bin]# 160508 23:42:51 mysqld_safe Adding /usr/lib64/libjemalloc.so.1 to LD_PRELOAD for mysqld
160508 23:42:51 mysqld_safe Logging to /var/log/mysqld.log .
160508 23:42:51 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@linfytest3 bin]# pwd
/usr/bin

[root@linfytest3 bin]# /usr/bin/ps_tokudb_admin –enable -u root -P 5579 -p
Enter password:

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.

9、登录 mysql
[root@linfytest3 bin]# /usr/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.29-76.2 Percona Server (GPL), Release 76.2, Revision ddf26fe

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 show engines;
+——————–+———+—————————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————————-+————–+——+————+
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| CSV | YES | CSV storage engine | 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 |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| TokuDB | YES | Percona TokuDB Storage Engine with Fractal Tree(tm) Technology | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+——————–+———+—————————————————————————-+————–+——+————+
10 rows in set (0.01 sec)

10、建表、索引
mysql use test
Database changed
mysql CREATE TABLE timeline_1009 (
– uid bigint(16) unsigned not null,
– vflag tinyint(4) unsigned not null default 0,
– status_id bigint(16) unsigned not null,
– source int(6) unsigned not null default 0,
– fflag int(6) unsigned not null default 0,
– mflag tinyint(4) not null default 0,
– primary key (uid)
– ) engine=TokuDB DEFAULT CHARSET=utf8 ROW_FORMAT=TOKUDB_LZMA;
Query OK, 0 rows affected (0.07 sec)

mysql create index idx_flag on timeline_1009(mflag);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql drop index idx_flag on timeline_1009;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql alter table timeline_1009 add column test_flag tinyint;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql alter table timeline_1009 drop column test_flag;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

11、

一些注意事项
1、alter table xxx engine=tokudb 的情况会加 metadata 锁阻塞插入更新，而 myql-5.6.17 innodb 引擎则不会导致阻塞。
2、只有 char, varchar, varbinary, and integer 字段类型的扩展才支持 online ddl【缩减不支持 online 操作】，注意修改的时候字段属性不能发生变化，同时也不能是任何主键或二级索引的字段【二级索引的话，可以先删除二级索引，不过 tokuDB 创建索引二级索引并不快】，否则会导致 copy 表。
3、修改列名的时候必须是单个列修改，如果是一条语句同时修改多条语句则会 copy 表。
4、这些字段 TIME, ENUM, BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB 类型不支持 online ddl。
其他的一些限制详情可参考官网

12、tokuDB 事务、锁信息查看
查看事务情况：
SELECT * FROM information_schema.tokudb_trx,
information_schema.processlist
WHERE trx_mysql_thread_id = id;

查看锁的情况
SELECT id FROM information_schema.tokudb_locks,
information_schema.processlist
WHERE locks_mysql_thread_id = id;

警告：
不要移动任何 TokuDB 相关的文件、否则会导致数据库崩溃并且需要使用备份才能进行恢复。

(6)、关于 TokuDB 行格式
tokudb_row_format 可以设置如下的几个值：
tokudb_default: 设置默认的压缩行为。在 TokuDB 7.1.0 版本，默认使用 zlib 库进行压缩，未来版本可能会改变。
tokudb_fast: 使用 quicklz 库的压缩模式。
tokudb_small: 使用 lzma 库的压缩模式。
tokudb_zlib: 使用 zlib 库的压缩模式，提供了中等级别的压缩比和中等级别的 CPU 消耗。

tokudb_quicklz: 使用 quicklz 库的压缩模式，提供了轻量级的压缩比和较低基本的 CPU 消耗。
tokudb_lzma: 使用 lzma 库压缩模式，提供了高压缩比和高 CPU 消耗。
tokudb_uncompressed: 不使用压缩模式。
可以使用如下命令修改行格式：
ALTER TABLE table_name ROW_FORMAT=row_format;
注意修改行格式的操作也是 online 操作。