Percona-MyRocks安装

Percona-MyRocks

简介

MyRocks是与Facebook开源项目RocksDB集成的MySQL存储引擎。通过提高读取，写入和存储数据的效率，它提供了改进的闪存存储性能。针对快速，低延迟的存储进行了优化，非常适合当今的大容量，写密集型应用，例如数据聚合，IoT，交易记录，系统监控，支付处理和计费系统。MyRocks需要更少的SSD存储空间，提供更多的存储耐用性，并确保更好的IO容量。

MyRocks引擎没有单独的二进制发行版，Percona将MyRocks集成到了Percona发行版的MySQL中。

规划

IP	系统版本	数据库版本
192.168.240.40	CentOS7.6	Percona-Server-8.0.21-12-Linux.x86_64.glibc2.12-minimal

Percona-Server二进制安装包下载地址：https://www.percona.com/downloads/Percona-Server-LATEST/， 安装方式为二进制包安装方式。

安装Percona-Server-8.0.21-12

将二进制安装包上传到/usr/local/src目录下

cd /usr/local/src
tar xf Percona-Server-8.0.21-12-Linux.x86_64.glibc2.12-minimal.tar.gz
mv Percona-Server-8.0.21-12-Linux.x86_64.glibc2.12-minimal /usr/local/Percona-Server-8.0.21-12
echo 'export  PATH=/usr/local/Percona-Server-8.0.21-12/bin:$PATH' >> /etc/profile
. /etc/profile

创建目录结构(所有节点)

useradd -M -s /sbin/nologin  mysql
mkdir -pv  /datadir/{temp,log,data}
touch /datadir/log/err.log
chown -R mysql:mysql /datadir

初始化数据库(所有节点)

mysqld --initialize-insecure --datadir=/datadir/data --user=mysql
chown -R mysql:mysql /datadir

修改配置文件(所有节点)

[mysqld]
basedir=/usr/local/Percona-Server-8.0.21-12/
user=mysql
port=3306
mysqlx_port=33060
datadir=/datadir/data
log-error=/datadir/log/err.log
pid-file=/datadir/temp/mysqld.pid
socket=/datadir/temp/mysqld.sock
mysqlx_socket=/datadir/temp/mysqlx.sock
symbolic-links=0
server_id=40
gtid-mode=on
enforce-gtid-consistency=true
skip_ssl
log_bin=/datadir/log/binlog
binlog_format=ROW

# 参考https://www.percona.com/blog/2018/04/30/a-look-at-myrocks-performance/文档
# 请根据实际情况进行调整，非固定
rocksdb_max_open_files=-1
rocksdb_max_background_jobs=8
rocksdb_max_total_wal_size=4G
rocksdb_block_size=16384
rocksdb_table_cache_numshardbits=6
 
# rate limiter
rocksdb_bytes_per_sync=16777216
rocksdb_wal_bytes_per_sync=4194304
 
rocksdb_compaction_sequential_deletes_count_sd=1
rocksdb_compaction_sequential_deletes=199999
rocksdb_compaction_sequential_deletes_window=200000
 
rocksdb_default_cf_options="write_buffer_size=256m;target_file_size_base=32m;max_bytes_for_level_base=512m;max_write_buffer_number=4;level0_file_num_compaction_trigger=4;level0_slowdown_writes_trigger=20;level0_stop_writes_trigger=30;max_write_buffer_number=4;block_based_table_factory={cache_index_and_filter_blocks=1;filter_policy=bloomfilter:10:false;whole_key_filtering=0};level_compaction_dynamic_level_bytes=true;optimize_filters_for_hits=true;memtable_prefix_bloom_size_ratio=0.05;prefix_extractor=capped:12;compaction_pri=kMinOverlappingRatio;compression=kLZ4Compression;bottommost_compression=kLZ4Compression;compression_opts=-14:4:0"
rocksdb_max_subcompactions=4
rocksdb_compaction_readahead_size=16m
 
rocksdb_use_direct_reads=ON
rocksdb_use_direct_io_for_flush_and_compaction=ON

[client]
socket=/datadir/temp/mysqld.sock

只需要修改server_id这一项即可(保证所有MySQL服务器都不同)

启动数据库

mysqld_safe  --defaults-file=/etc/my.cnf --daemonize

开启MyRocks存储引擎

# ps-admin --enable-rocksdb -uroot -h127.0.0.1 -P3306
Checking if RocksDB plugin is available for installation ...
INFO: ha_rocksdb.so library for RocksDB found at /usr/local/Percona-Server-8.0.21-12/lib/plugin/ha_rocksdb.so.

Checking RocksDB engine plugin status...
INFO: RocksDB engine plugin is not installed.

Installing RocksDB engine...
INFO: Successfully installed RocksDB engine plugin.

查看MyRocks存储引擎是否安装成功

# mysql -uroot -h127.1 -e "show plugins"
+---------------------------------+----------+--------------------+---------------+---------+
| Name                            | Status   | Type               | Library       | License |
+---------------------------------+----------+--------------------+---------------+---------+
.....
| ROCKSDB                         | ACTIVE   | STORAGE ENGINE     | ha_rocksdb.so | GPL     |
| ROCKSDB_CFSTATS                 | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
| ROCKSDB_DBSTATS                 | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
| ROCKSDB_PERF_CONTEXT            | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
| ROCKSDB_PERF_CONTEXT_GLOBAL     | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
| ROCKSDB_CF_OPTIONS              | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
| ROCKSDB_GLOBAL_INFO             | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
| ROCKSDB_COMPACTION_STATS        | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
| ROCKSDB_DDL                     | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
| ROCKSDB_INDEX_FILE_MAP          | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
| ROCKSDB_LOCKS                   | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
| ROCKSDB_TRX                     | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
| ROCKSDB_DEADLOCK                | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
+---------------------------------+----------+--------------------+---------------+---------+

输出上述信息时表示安装MyRocks存储引擎安装成功。

测试

mysql> DROP DATABASE IF EXISTS t1;
Query OK, 1 row affected (0.09 sec)

mysql> CREATE DATABASE t1;
Query OK, 1 row affected (0.00 sec)

mysql>  CREATE TABLE t1.t1(id int primary key ,name varchar(20)) engine=rocksdb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1.t1(id,name) values(1,'li');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t1.t1;
+----+------+
| id | name |
+----+------+
|  1 | li   |
+----+------+
1 row in set (0.00 sec)

mysql> show create table t1.t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

参考文档

• MyRocks官方文档：http://myrocks.io/
• percona-server官方文档：https://www.percona.com/software/mysql-database/percona-server
• 调优文档参考：https://www.percona.com/blog/2018/04/30/a-look-at-myrocks-performance/