> 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 ```