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

This article will explain in detail about the installation of TokuDB and what matters needing attention. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Characteristics of TokuDB:

1, high compression ratio, officially claimed to be able to reach 1:12.

2. High insert performance, which officials say is at least 9 times higher than innodb.

3. Indexes and fields can be added online with high speed.

Disadvantages of TokuDB:

1. High consumption of cpu usr state.

2. The response time is longer.

Installation environment:

OS: centos 6.5

IP:192.168.0.103

TokuDB:5.6.29

Installation media:

Percona-Server-shared, Percona-Server-client, Percona-Server-server, Percona-Server-tokudb (note the installation sequence)

Download the installation media:

Https://www.percona.com/downloads/Percona-Server-5.6/LATEST/

Installation steps:

1. Install Percona-Server-shared first

[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. Install Percona-Server-client again

[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. Install Percona-Server-server again

[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: 128rollback 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: 128rollback 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. Install jemalloc

[root@linfytest3 Downloads] # rpm-ivh jemalloc-3.6.0-2.el5.x86_64.rpm

Preparing... # [100%]

1:jemalloc # # [100%]

5. Install 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-p [MySQL _ admin_pass] [- S] [- h-P]

* 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. Start 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: 128rollback 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. Log in to 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. Install the tokuDB plug-in

[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...

Close mysql server

/ usr/bin/mysqladmin shutdown-uroot-p

Start 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. Log in to 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. Build tables and indexes

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 、

Some points for attention

1. In the case of alter table xxx engine=tokudb, the metadata lock will block the insertion of updates, while the myql-5.6.17 innodb engine will not cause blocking.

2. Only the expansion of char, varchar, varbinary and and integer field types supports online ddl [reduction does not support online operation]. Note that the field property cannot be changed when modified, and it cannot be any primary key or secondary index field [secondary index can be deleted first, but it is not fast for tokuDB to create secondary index], otherwise it will lead to copy table.

3. The column name must be modified by a single column. If it is a statement that modifies multiple statements at the same time, it will copy the table.

4. Online ddl is not supported for these fields TIME, ENUM, BLOB, TINYBLOB, MEDIUMBLOB and LONGBLOB.

Details of other restrictions can be found on the official website

12. View tokuDB transaction and lock information

View the transaction:

SELECT * FROM information_schema.tokudb_trx

Information_schema.processlist

WHERE trx_mysql_thread_id = id

Check the condition of the lock

SELECT id FROM information_schema.tokudb_locks

Information_schema.processlist

WHERE locks_mysql_thread_id = id

Warning:

Do not move any TokuDB-related files, otherwise it will cause the database to crash and need to use a backup to restore.

(6) about TokuDB line format

Tokudb_row_format can set several values as follows:

Tokudb_default: sets the default compression behavior. In TokuDB version 7.1.0, the zlib library is used by default for compression, and future versions may change.

Tokudb_fast: uses the compression mode of the quicklz library.

Tokudb_small: uses the compression mode of the lzma library.

Tokudb_zlib: uses the compression mode of the zlib library, providing a medium compression ratio and medium CPU consumption.

Tokudb_quicklz: uses the compression mode of the quicklz library to provide a lightweight compression ratio and low basic CPU consumption.

Tokudb_lzma: uses lzma library compression mode to provide high compression ratio and high CPU consumption.

Tokudb_uncompressed: do not use compression mode.

You can modify the line format using the following command:

ALTER TABLE table_name ROW_FORMAT=row_format

Note that the operation of changing the row format is also an online operation.

This is the end of this article on "TokuDB installation and precautions". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

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.