抢鲜体验 GreatSQL 的 AP 引擎 Rapid

原创严少安 2024-02-05

1350

图片.png

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)