其他
抢鲜体验 GreatSQL 的 AP 引擎 Rapid
The following article is from 少安事务所 Author 少安事务所
GreatSQL 及 Rapid 存储引擎简介
GreatSQL 数据库是一款开源免费数据库，可在普通硬件上满足金融级应用场景，具有高可用、高性能、高兼容、高安全等特性，可作为 MySQL 或 Percona Server for MySQL 的理想可选替换。
从 GreatSQL 8.0.32-25 版本开始，新增 Rapid 存储引擎，该引擎使得 GreatSQL 能满足联机分析（OLAP）查询请求。Rapid 引擎不会直接面对客户端和应用程序，用户无需修改原有的数据访问方式。它是一个无共享、内存化、混合列式存储的查询处理引擎，其设计目的是为了高性能的处理分析型查询。
GreatSQL 安装
以下内容为 GreatSQL 在 CentOS 7 的安装步骤。
1. 下载 GreatSQL 8.0.32-25 安装包
这里下载的是适配 el7 的 RPM 包。
wget https://product.greatdb.com/GreatSQL-8.0.32-25-Rapid/greatsql-8.0.32-25.1.el7.x86_64.rpm-bundle.tar.xz
2. 解压压缩包
解压后得到 6 个 RPM 包，这里没有 test 包，如需使用，需要另行下载。
[root@shawnyan ~]# tar xf greatsql-8.0.32-25.1.el7.x86_64.rpm-bundle.tar.xz
[root@shawnyan ~]# ls -alh greatsql-*
-rw-r--r-- 1 root root 121M Feb 4 03:02 greatsql-8.0.32-25.1.el7.x86_64.rpm-bundle.tar.xz
-rw-r--r-- 1 root root 19M Feb 2 15:51 greatsql-client-8.0.32-25.1.el7.x86_64.rpm
-rw-r--r-- 1 root root 1.9M Feb 2 15:51 greatsql-devel-8.0.32-25.1.el7.x86_64.rpm
-rw-r--r-- 1 root root 2.1M Feb 2 15:51 greatsql-icu-data-files-8.0.32-25.1.el7.x86_64.rpm
-rw-r--r-- 1 root root 5.0M Feb 2 15:51 greatsql-mysql-router-8.0.32-25.1.el7.x86_64.rpm
-rw-r--r-- 1 root root 93M Feb 2 15:51 greatsql-server-8.0.32-25.1.el7.x86_64.rpm
-rw-r--r-- 1 root root 1.5M Feb 2 15:51 greatsql-shared-8.0.32-25.1.el7.x86_64.rpm
[root@shawnyan ~]#
3. 安装 RPM 包
使用 YUM 命令直接安装即可，YUM 会自行分析依赖。
[root@shawnyan ~]# yum install greatsql-*.rpm
...
Install 6 Packages (+46 Dependent packages)
Upgrade ( 3 Dependent packages)
Total size: 624 M
Total download size: 19 M
Is this ok [y/d/N]: y
...
Installed:
greatsql-client.x86_64 0:8.0.32-25.1.el7 greatsql-devel.x86_64 0:8.0.32-25.1.el7 greatsql-icu-data-files.x86_64 0:8.0.32-25.1.el7 greatsql-mysql-router.x86_64 0:8.0.32-25.1.el7 greatsql-server.x86_64 0:8.0.32-25.1.el7 greatsql-shared.x86_64 0:8.0.32-25.1.el7
Dependency Installed:
groff-base.x86_64 0:1.22.2-8.el7 keyutils-libs-devel.x86_64 0:1.5.8-3.el7 krb5-devel.x86_64 0:1.15.1-55.el7_9 libaio.x86_64 0:0.3.109-13.el7 libcom_err-devel.x86_64 0:1.42.9-19.el7 libkadm5.x86_64 0:1.15.1-55.el7_9
libselinux-devel.x86_64 0:2.5-15.el7 libsepol-devel.x86_64 0:2.5-10.el7 libverto-devel.x86_64 0:0.2.5-4.el7 make.x86_64 1:3.82-24.el7 net-tools.x86_64 0:2.0-0.25.20131004git.el7 numactl-libs.x86_64 0:2.0.12-5.el7
openssl.x86_64 1:1.0.2k-26.el7_9 openssl-devel.x86_64 1:1.0.2k-26.el7_9 pcre-devel.x86_64 0:8.32-17.el7 perl.x86_64 4:5.16.3-299.el7_9 perl-Carp.noarch 0:1.26-244.el7 perl-Data-Dumper.x86_64 0:2.145-3.el7
perl-Digest.noarch 0:1.17-245.el7 perl-Digest-MD5.x86_64 0:2.52-3.el7 perl-Encode.x86_64 0:2.51-7.el7 perl-Exporter.noarch 0:5.68-3.el7 perl-File-Path.noarch 0:2.09-2.el7 perl-File-Temp.noarch 0:0.23.01-3.el7
perl-Filter.x86_64 0:1.49-3.el7 perl-Getopt-Long.noarch 0:2.40-3.el7 perl-HTTP-Tiny.noarch 0:0.033-3.el7 perl-PathTools.x86_64 0:3.40-5.el7 perl-Pod-Escapes.noarch 1:1.04-299.el7_9 perl-Pod-Perldoc.noarch 0:3.20-4.el7
perl-Pod-Simple.noarch 1:3.28-4.el7 perl-Pod-Usage.noarch 0:1.63-3.el7 perl-Scalar-List-Utils.x86_64 0:1.27-248.el7 perl-Socket.x86_64 0:2.010-5.el7 perl-Storable.x86_64 0:2.45-3.el7 perl-Text-ParseWords.noarch 0:3.29-4.el7
perl-Time-HiRes.x86_64 4:1.9725-3.el7 perl-Time-Local.noarch 0:1.2300-2.el7 perl-constant.noarch 0:1.27-2.el7 perl-libs.x86_64 4:5.16.3-299.el7_9 perl-macros.x86_64 4:5.16.3-299.el7_9 perl-parent.noarch 1:0.225-244.el7
perl-podlators.noarch 0:2.5.1-3.el7 perl-threads.x86_64 0:1.87-4.el7 perl-threads-shared.x86_64 0:1.43-6.el7 zlib-devel.x86_64 0:1.2.7-21.el7_9
Dependency Updated:
krb5-libs.x86_64 0:1.15.1-55.el7_9 openssl-libs.x86_64 1:1.0.2k-26.el7_9 zlib.x86_64 0:1.2.7-21.el7_9
Complete!
4. 启动 GreatSQL 数据库
安装时会自行创建 mysql 用户，安装完成后，初始化数据目录。
id mysql
/usr/bin/mysqld_pre_systemd --user=mysql
[root@shawnyan ~]# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)
使用 mysqld_safe 启动数据库。
/usr/bin/mysqld_safe &
5. 连接 GreatSQL 数据库
mysql -uroot -p
查看状态和版本信息。
[root@shawnyan ~]# mysql -uroot -p'Greatsql@123'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.32-25 GreatSQL (GPL), Release 25, Revision 79f57097e3f
Copyright (c) 2021-2023 GreatDB Software Co., Ltd
Copyright (c) 2009-2023 Percona LLC and/or its affiliates
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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> \s
--------------
mysql Ver 8.0.32-25 for Linux on x86_64 (GreatSQL (GPL), Release 25, Revision 79f57097e3f)
Connection id: 15
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.32-25 GreatSQL (GPL), Release 25, Revision 79f57097e3f
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 55 min 51 sec
Threads: 2 Questions: 462 Slow queries: 0 Opens: 366 Flush tables: 3 Open tables: 279 Queries per second avg: 0.137
--------------
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32-25 |
+-----------+
1 row in set (0.00 sec)
6. 查看第二引擎系统变量
这个版本的数据库提供了 9 个第二引擎的系统变量。
mysql> show variables like '%secondary%';
+--------------------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------------------+----------------------+
| secondary_engine_cost_threshold | 100000.000000 |
| secondary_engine_parallel_load_workers | 4 |
| secondary_engine_read_delay_gtid_threshold | 100 |
| secondary_engine_read_delay_level | TABLE_START_INC_TASK |
| secondary_engine_read_delay_time_threshold | 60 |
| secondary_engine_read_delay_wait_mode | WAIT_FOR_TRX |
| secondary_engine_read_delay_wait_timeout | 60 |
| show_create_table_skip_secondary_engine | OFF |
| use_secondary_engine | OFF |
+--------------------------------------------+----------------------+
9 rows in set (0.00 sec)
7. 安装 Rapid 存储引擎
查看 Rapid 相关系统变量，由于尚未安装插件，所以查询结果为空。
mysql> show variables like '%rapid%';
Empty set (0.00 sec)
安装 Rapid 存储引擎插件。
mysql> INSTALL PLUGIN Rapid SONAME 'ha_rapid.so';
Query OK, 0 rows affected (0.05 sec)
再次查看相关系统变量，可以看到有 5 条记录。
mysql> show variables like '%rapid%';
+-------------------------------+-----------------+
| Variable_name | Value |
+-------------------------------+-----------------+
| rapid_checkpoint_threshold | 16777216 |
| rapid_hash_table_memory_limit | 10 |
| rapid_memory_limit | 1073741824 |
| rapid_temp_directory | duckdb.data.tmp |
| rapid_worker_threads | 4 |
+-------------------------------+-----------------+
5 rows in set (0.01 sec)
查看 Rapid 插件信息。
mysql> show plugins;
+----------------------------------+----------+--------------------+-------------+---------+
| Name | Status | Type | Library | License |
+----------------------------------+----------+--------------------+-------------+---------+
...
| Rapid | ACTIVE | STORAGE ENGINE | ha_rapid.so | GPL |
+----------------------------------+----------+--------------------+-------------+---------+
51 rows in set (0.00 sec)
mysql> select * from information_schema.plugins where PLUGIN_NAME = 'rapid'\G
*************************** 1. row ***************************
PLUGIN_NAME: Rapid
PLUGIN_VERSION: 0.1
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: STORAGE ENGINE
PLUGIN_TYPE_VERSION: 80032.0
PLUGIN_LIBRARY: ha_rapid.so
PLUGIN_LIBRARY_VERSION: 1.11
PLUGIN_AUTHOR: Oracle Corporation
PLUGIN_DESCRIPTION: Rapid storage engine
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
1 row in set (0.00 sec)
8. 启用 Rapid 引擎，并加载数据
创建一个测试表，默认存储引擎为 InnoDB。
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
将该表的第二存储设置为 Rapid。
mysql> alter table t secondary_engine = rapid;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=rapid
1 row in set (0.00 sec)
查看表结构，描述里出现 SECONDARY_ENGINE=rapid 字样。
也可以通过参数 show_create_table_skip_secondary_engine 进行控制展示内容，跳过第二引擎展示。
mysql> set show_create_table_skip_secondary_engine = on;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
加载数据到 Rapid 引擎。
mysql> alter table t secondary_load;
Query OK, 0 rows affected (0.02 sec)
需要注意的是，需要先进行 load 再查询数据，否则会报错。
mysql> explain analyze select /*+ set_var(use_secondary_engine=forced) */ * from t\G
ERROR 3889 (HY000): Secondary engine operation failed. use_secondary_engine is FORCED but query could not be executed in secondary engine.
9. Rapid 的使用
使用 Hint 强制使用 Rapid 引擎，并查看执行计划。
mysql> explain select /*+ set_var(use_secondary_engine=forced) */ * from t;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using secondary engine RAPID |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select /*+ SET_VAR(use_secondary_engine='forced') */ `sbtest`.`t`.`a` AS `a` from `sbtest`.`t`
不过，目前不支持 explain analyze 语法。
mysql> explain analyze select /*+ set_var(use_secondary_engine=forced) */ * from t;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE with secondary engine'
在官方文档中，还有描述了一些限制：
“在GreatSQL 8.0.32-25版本中，Rapid引擎支持的语句范围如下：
表类型：InnoDB引擎的normal表。 SELECT stmt：不包含 SELECT INTO, SELECT locking clause 等语法。 PREPARE stmt：仅支持 PREPARE SELECT查询。 其余类型的SQL语法暂时还不支持。
Rapid引擎暂时不支持表分区（partition），不支持外键（foreign key）。
举例：
mysql> CREATE TABLE partitioned_table (
-> id INT,
-> name VARCHAR(50),
-> date_created DATE
-> )
-> PARTITION BY RANGE (YEAR(date_created)) (
-> PARTITION p0 VALUES LESS THAN (1991),
-> PARTITION p1 VALUES LESS THAN (1992)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> alter table partitioned_table secondary_engine = rapid;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table partitioned_table secondary_load;
ERROR 3877 (HY000): The partition table is not supported for RAPID
此外，也不支持整个库同时加载到 Rapid 引擎。
mysql> alter database sbtest secondary_engine = rapid;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'secondary_engine = rapid' at line 1
10. 查看 Rapid 引擎表的系统信息
在系统表中可以查看到 Rapid 相关信息，具体示例如下。
mysql> select TABLE_NAME,ENGINE,CREATE_OPTIONS from information_schema.tables where TABLE_SCHEMA = 'sbtest';
+-------------------+--------+---------------------------------------------------------+
| TABLE_NAME | ENGINE | CREATE_OPTIONS |
+-------------------+--------+---------------------------------------------------------+
| partitioned_table | InnoDB | partitioned SECONDARY_ENGINE="rapid" SECONDARY_LOAD="0" |
| t | InnoDB | SECONDARY_ENGINE="rapid" SECONDARY_LOAD="1" |
+-------------------+--------+---------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> desc information_schema.SECONDARY_ENGINE_INCREMENT_LOAD_TASK;
+--------------------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+----------------+------+-----+---------+-------+
| DB_NAME | varchar(192) | NO | | | |
| TABLE_NAME | varchar(192) | NO | | | |
| START_TIME | varchar(192) | NO | | | |
| START_GTID | varchar(65535) | NO | | | |
| COMMITTED_GTID_SET | varchar(65535) | NO | | | |
| READ_GTID | varchar(192) | NO | | | |
| READ_BINLOG_FILE | varchar(1024) | NO | | | |
| READ_BINLOG_POS | bigint | NO | | | |
| DELAY | bigint | NO | | | |
| STATUS | varchar(192) | NO | | | |
| END_TIME | varchar(192) | NO | | | |
| INFO | varchar(1024) | NO | | | |
+--------------------+----------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
总结
GreatSQL 于 2/4 正式推出 AP 引擎 Rapid，并提供下载试用。本文简要介绍了如何安装 GreatSQL，及 Rapid 引擎的基础用法。
GreatSQL数据库是一款开源免费数据库，可在普通硬件上满足金融级应用场景，具有高可用、高性能、高兼容、高安全等特性，可作为MySQL或Percona Server for MySQL的理想可选替换。
⏩GitHub: https://github.com/GreatSQL/
🆙BiliBili: https://space.bilibili.com/1363850082
（对文章有疑问或见解可去社区官网提出哦~）
| 加入微信交流群 | 加入QQ交流群 |
想看更多技术好文，点个"在看"吧！