Installation of Percona Server and installation notes for TOKUDB engine

Installation of Percona Server and installation notes for TOKUDB engine

July 7, 2017

To install Percona server:

Yum Localinstall percona-server-client-57-5.7.18-15.1.el6.x86_64.rpm percona-server-shared-57-5.7.18-15.1.el6.x86 _64.rpm percona-server-server-57-5.7.18-15.1.el6.x86_64.rpm percona-server-tokudb-57-5.7.18-15.1.el6.x86_64.rpm

The Jmelloc component is automatically installed during the installation process.

/ETC/MY.CNF content is as follows:

[Client]

Port = 3306

Socket =/tmp/mysql.sock

[Mysqld]

Port = 3306

Socket =/tmp/mysql.sock

DataDir =/var/lib/mysql

Innodb_file_per_table=on

Character-set-server = UTF8

server_id=1000

Join_buffer_size = 256K

Thread_stack = 256K

Default_storage_engine = InnoDB

max_connections = 2000

#slave-skip-errors = 1032,1062

#性能相关调节

#跳过外部锁定 for locking MyISAM data tables for multi-process conditions

Skip-external-locking

Sort_buffer_size = 4M

Read_buffer_size = 4M

Read_rnd_buffer_size = 4M

Myisam_sort_buffer_size = 64M

#自适应哈希索引, in high concurrency complex SQL scenario, it is possible to cause system resources to be low, a lot of spin waits, lock waits lock wait, cause crash

Skip-innodb_adaptive_hash_index

#为所有线程打开表的数量

Table_open_cache = 2048

#索引块的缓冲区大小

Key_buffer_size = 128M

#MySQL查询结果缓冲区的大小

Query_cache_type = OFF

Query_cache_size = 0

#处理大的信息包量

Max_allowed_packet = 16M

#1G =>8 2g=>16 3g=>32 >3g=>64

#缓存可重用的线程数

Thread_cache_size = 64

Event_scheduler = 1

Long_query_time = 1

Slow_query_log=1

Slow_query_log_file =/var/lib/mysql/log-slow-queries.log

Log-error =/var/lib/mysql/error.log

Max_binlog_size = 256m

Transaction_isolation = read-committed

#innodb_io_capacity =1000

Master_info_repository = TABLE

Relay_log_info_repository = TABLE

Relay_log_recovery = 1 #crash safe

Log-bin=mysql-bin

Binlog_format=row

Sync_binlog =1

Log-slave-updates

Log_bin_trust_function_creators = 1

Binlog_rows_query_log_events=on

Expire_logs_days = 5

Auto_increment_increment=1

Auto_increment_offset=1

Skip-slave-start

Skip-name-resolve

Binlog_cache_size = 16M

binlog_stmt_cache_size=16m

####### #innodb settings########

Innodb_buffer_pool_instances = 4 #小于逻辑CPU个数, as large as possible

Innodb_log_buffer_size = 16777216

Innodb_read_io_threads = 4 #小于逻辑CPU个数/2, as large as possible

Innodb_write_io_threads=4 # <= (number of logical CPUs-read_io_threads), as large as possible

Innodb_purge_threads = 2

Innodb_flush_method=o_direct

# Timeout

Connect_timeout = 20

Wait_timeout = 14400

Interactive_timeout = 14400

Net_write_timeout = 180

Lock_wait_timeout = 120

tmp_table_size=200m

max_heap_table_size=200m

Sql_mode = ' No_engine_substitution,no_auto_create_user '

[Mysqldump]

Quick

Max_allowed_packet = 16M

[MySQL]

No-auto-rehash

# Remove The next comment character if you is not a familiar with SQL

#safe-updates

prompt = ' [\\[email protected]\\h:\\d] > '

[Myisamchk]

Key_buffer_size = 512M

Sort_buffer_size = 512M

Read_buffer = 8M

Write_buffer = 8M

[Mysqlhotcopy]

Interactive-timeout

Initialize the database:

/usr/sbin/mysqld--initialize-insecure--basedir=/usr/sbin/--user=mysql--datadir=/var/lib/mysql/

# Description: Use the--initialize-insecure parameter, do not let mysqld initialization time generate random password, keep blank password

Start the database:

/etc/init.d/mysql start

To set the database password:

Mysql

> Set Password=password (' [email protected] ');

> Flush Privileges;

> Exit

Then, start installing the TOKUDB engine:

Official detailed installation steps: https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_installation.html

1 Disabling transparent huge pages

Cat/sys/kernel/mm/transparent_hugepage/enabled

[Always] madvise never

Always represents enabled

echo Never >/sys/kernel/mm/transparent_hugepage/enabled

echo Never >/sys/kernel/mm/transparent_hugepage/defrag

# Write settings to a file to prevent the restart configuration from being lost

Echo ' echo never >/sys/kernel/mm/transparent_hugepage/enabled ' >>/etc/rc.local

Echo ' echo never >/sys/kernel/mm/transparent_hugepage/defrag ' >>/etc/rc.local

2. To install and enable the TOKUDB engine:

# # can be installed using the Install plugin method, or it can be configured using the Ps_tokudb_admin tool (Ps_tokudb_admin is recommended)

Executive Ps_tokudb_admin-uroot [email protected]--enable

will automatically be configured in/ETC/MY.CNF in the following 1 lines

[Mysqld_safe]

Thp-setting=never

Then execute ps_tokudb_admin-uroot [email protected]--enable-backup

will automatically be configured in/ETC/MY.CNF in the following 1 lines

[Mysqld_safe]

Preload-hotbackup

This completes the installation of the TOKUDB engine plugin and Tokubackup plugin plug-in.

Restart the mysqld process.

/etc/init.d/mysql restart

mysql-uroot [email protected]

> Show engines;

> Show plugins;

> SELECT @ @tokudb_version;

You can see that the TOKUDB is enabled.

In this case, if we restart the mysqld or the entire server, the TOKUDB engine will automatically load again when MySQL restarts.

This article is from the "Vegetable Chicken" blog, please be sure to keep this source http://lee90.blog.51cto.com/10414478/1945419

Installation of Percona Server and installation notes for TOKUDB engine