TokuDB的安裝及注意事項
TokuDB的安裝及注意事項
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（注意安裝順序）
安裝介質下載:
安裝步驟:
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 () 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 () 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
The latest information about Percona Server is available on the web at
/software/percona-server
Support Percona by buying support at
/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 /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 /doc/percona-server/5.6/tokudb/tokudb_installation.html for more installation details
* See /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 () 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
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> 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
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> 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 操作。
吐槽：
該引擎現在一般用於大量歷史資料的歸檔儲存，但是最致命的還是沒有一款合適的備份工具，只能使用mysqldump 備份或者停機物理備份，貌似官方的物理備份工具要收費。
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（注意安裝順序）
安裝介質下載:
安裝步驟:
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 () 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 () 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
The latest information about Percona Server is available on the web at
/software/percona-server
Support Percona by buying support at
/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 /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 /doc/percona-server/5.6/tokudb/tokudb_installation.html for more installation details
* See /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 () 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
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> 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
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> 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 操作。
吐槽：
該引擎現在一般用於大量歷史資料的歸檔儲存，但是最致命的還是沒有一款合適的備份工具，只能使用mysqldump 備份或者停機物理備份，貌似官方的物理備份工具要收費。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/543979/viewspace-2096453/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SVN安裝配置及安全注意事項
- 教程：MySQL 8安裝與配置及注意事項MySql
- 解壓縮版mysql安裝及注意事項MySql
- TokuDB安裝
- 詳解Linux(Centos)之安裝Nginx及注意事項LinuxCentOSNginx
- Linux原始碼包安裝過程及注意事項Linux原始碼
- Micro/Mamba 安裝與注意事項
- PbootCMS怎麼安裝？注意事項boot
- phpstudy 的安裝後需要注意事項PHP
- mongoDB安裝與啟動的注意事項MongoDB
- hadoop 3.x安裝注意事項Hadoop
- 安裝sybase12.5 for linux注意事項(zt)Linux
- mysql 5.7.14 免安裝版注意事項(精)MySql
- Z-Blog的安裝與升級注意事項
- 安裝 Nuxt.js 的步驟和注意事項UXJS
- Python eval的用法及注意事項Python
- 安裝並使用 Ubuntu Server 的一些注意事項UbuntuServer
- 搭建 nuget 私服及注意事項
- Guava HashMultimap使用及注意事項Guava
- 介面開發文件及注意事項
- 開發及上線中的注意事項
- 小米遊戲本安裝ubuntu 雙系統注意事項遊戲Ubuntu
- 4.Rxjs介紹及注意事項JS
- 段合併優化及注意事項優化
- 如何搭建伺服器及注意事項伺服器
- 膜結構車棚安裝時的注意事項你清楚嗎JOQ
- RandomAccessFile注意事項randomMac
- @Lombok注意事項Lombok
- 換工作的注意事項
- Oracle使用*的注意事項Oracle
- 整合環信IM SDK及使用注意事項
- ERP選型準備、方法及注意事項
- TransactionScope事務處理方法介紹及.NETCore中的注意事項NetCore
- Go語言中 defer 使用場景及注意事項，你是要注意的！Go
- 阿里雲初次備案全流程及注意事項阿里
- 新手使用ABP框架及注意事項--純後端框架後端
- Python命令列引數定義及注意事項Python命令列
- SQL 語句的注意事項SQL
- C++ queue的注意事項C++