1-2 Percona-5.7 + TokuDB 部署

> Percona官方文档 [原文地址](https://www.percona.com/doc/percona-server/LATEST/tokudb/tokudb_installation.html "原文地址") >TokuDB与InnoDB对比 [感谢billy鹏的分享](http://www.cnblogs.com/billyxp/p/3567421.html "感谢billy鹏的分享") > #### TokuDB可以大大缓解插入性能的瓶颈，并且存储空间大大减少，结合分区表效果更佳 #### 环境初始化 ##### 安装jemalloc TokuDB依赖 jemalloc 3.3.0以上版本 ```shell yum install jemalloc-devel.x86_64 -y ``` ##### 建立MySQL用户 ```shell useradd -M -s /sbin/nologin mysql ``` ##### （弃用）关闭Transparent Huge Pages(THP) Percona安装TokuDB已经默认配置，不需要单独配置 ```shell if test -f /sys/kernel/mm/transparent_hugepage/enabled; then echo never > /sys/kernel/mm/transparent_hugepage/enabled fi if test -f /sys/kernel/mm/transparent_hugepage/defrag; then echo never > /sys/kernel/mm/transparent_hugepage/defrag fi ``` ##### 安装Percona MySQL 注意：此时不添加TokuDB配置，默认没有启用引擎 ```shell mkdir /data/mysql3306 cd /opt/ tar xzf Percona-Server-5.7.18-16-Linux.x86_64.ssl101.tar.gz mv Percona-Server-5.7.18-16-Linux.x86_64.ssl101 /usr/local/ cd /usr/local/ && ln -s Percona-Server-5.7.18-16-Linux.x86_64.ssl101 mysql #编辑/etc/my.cnf，配置文件见结尾附件 cd /usr/local/mysql/bin/ && ./mysqld --initialize --user=mysql cp /usr/local/mysql/support-files/m ysql.server /etc/init.d/ /etc/init.d/mysql.server start ``` ##### /etc/my.cnf中 增加TokuDB配置文件 ```shell [mysqld] # TokuDB settings # skip-host-cache tokudb_data_dir=/data/mysql3306 tokudb_log_dir=/data/mysql3306 tokudb_row_format=tokudb_fast tokudb_cache_size = 2G tokudb_commit_sync = 0 tokudb_directio = 1 tokudb_read_block_size = 128K tokudb_read_buf_size = 1M tokudb_commit_sync=OFF tokudb_fsync_log_period=1000 [mysqld_safe] thp-setting=never malloc-lib= /usr/lib64/libjemalloc.so ``` ##### 启用TokuDB 过程会检查THP，Selinux，jemalloc ```shell /usr/local/mysql/bin/ps_tokudb_admin --enable -uroot -p -S /tmp/mysql3306.sock ``` ##### （完整数据导入）导入Zabbix数据库，并修改引擎 - 导出时设置 --no-autocommit（待测试） - 设置sync_binlog = 0 以及 tokudb_commit_sync = 0 提高插入速度 - 编辑备份文件，替换部分表的存储引擎，具体看下节 ##### (全新）导入Zabbix数据库，并修改引擎 ```shell alter table history engine = 'TOKUDB'; alter table history_log engine = 'TOKUDB'; alter table history_str engine = 'TOKUDB'; alter table history_text engine = 'TOKUDB'; alter table history_uint engine = 'TOKUDB'; alter table trends engine = 'TOKUDB'; alter table trends_uint engine = 'TOKUDB'; ``` ##### 配置文件附件 >配置源自姜成尧老师的分享，感谢姜成尧老师 ```shell [mysql] prompt = [\\u@\\h][\\d]>\\_ [mysqld] # base dir basedir = /usr/local/mysql/ socket = /tmp/mysql3306.sock datadir = /data/mysql3306 pid_file = /data/mysql3306/mysql3306.pid innodb_undo_directory = /data/mysql3306/undolog/ ## basic settings # user = mysql autocommit = 1 character_set_server=utf8mb4 explicit_defaults_for_timestamp = 1 max_allowed_packet = 16777216 # connection # interactive_timeout = 30 wait_timeout = 30 lock_wait_timeout = 5 skip_name_resolve = 1 max_connections = 1024 # session memory settings # read_buffer_size = 8M read_rnd_buffer_size = 16M sort_buffer_size = 32M tmp_table_size = 16M join_buffer_size = 32M thread_cache_size = 64 ## log settings # log_error = error.log slow_query_log = 1 slow_query_log_file = slow.log log_queries_not_using_indexes = 1 log_slow_admin_statements = 1 log_slow_slave_statements = 1 log_throttle_queries_not_using_indexes = 10 expire_logs_days = 90 long_query_time = 2 min_examined_row_limit = 100 binlog-rows-query-log-events = 1 log-bin-trust-function-creators = 1 log-slave-updates = 1 log-bin=mysql-bin #general_log=on general_log_file=/data/mysql3306/general_log ## innodb settings # innodb_page_size = 16384 innodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 1 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_flush_method = O_DIRECT innodb_log_file_size = 536870912 innodb_log_files_in_group = 2 innodb_log_buffer_size = 16777216 innodb_purge_threads = 1 innodb_sort_buffer_size = 33554432 innodb_write_io_threads = 2 innodb_read_io_threads = 2 innodb_file_per_table = 1 # replication settings # master_info_repository = TABLE relay_log_info_repository = TABLE sync_binlog = 1 binlog_format = ROW server-id=237 ```