三千字教你使用MOT

使用MOT非常简单，以下几个小节将会进行描述。openGauss允许应用程序使用MOT和基于标准磁盘的表。MOT适用于最活跃、高竞争和对吞吐量敏感的应用程序表，也可用于所有应用程序的表。以下命令介绍如何创建MOT，以及如何将现有的基于磁盘的表转换为MOT，以加速应用程序的数据库相关性能。MOT尤其有利于已证明是瓶颈的表。工作流程概述以下是与使用MOT相关的任务的简单概述：本小节还介绍了如何执行各种

Gauss松鼠会

4099人浏览 · 2022-04-27 10:22:56

Gauss松鼠会 · 2022-04-27 10:22:56 发布

上一期我们介绍了MOT特性及部署。说了为什么要用，接下来就是如何使用MOT。使用MOT非常简单，以下几个小节将会进行描述。

openGauss允许应用程序使用MOT和基于标准磁盘的表。MOT适用于最活跃、高竞争和对吞吐量敏感的应用程序表，也可用于所有应用程序的表。

以下命令介绍如何创建MOT，以及如何将现有的基于磁盘的表转换为MOT，以加速应用程序的数据库相关性能。MOT尤其有利于已证明是瓶颈的表。

工作流程概述

以下是与使用MOT相关的任务的简单概述：

本小节还介绍了如何执行各种与MOT相关的附加任务，以及MOT SQL覆盖和限制。

目录

1.授予用户权限

2.创建/删除MOT

3.为MOT创建索引

4.将磁盘表转换为MOT

4.1前置条件检查

4.2转换

4.3转换示例

5.查询原生编译

6.查询编译：PREPARE语句

6.1 运行命令

6.2 轻量执行支持的查询

6.3 轻量执行不支持的查询

7. 重试中止事务

8. MOT外部支持工具

9. MOT SQL覆盖和限制

9.1 不支持的特性

9.2 MOT限制

9.3 不支持的DDL操作

9.4 不支持的数据类型

9.5 不支持的索引DDL和索引

9.6 不支持的DML

9.7 原生编译和轻量执行不支持的查询

1.授予用户权限

以授予数据库用户对MOT存储引擎的访问权限为例。每个数据库用户仅执行一次，通常在初始配置阶段完成。

说明： MOT通过外部数据封装器（Foreign Data Wrapper，FDW）机制与openGauss数据库集成，所以需要授权用户权限。

要使特定用户能够创建和访问MOT（DDL、DML、SELECT），以下语句只执行一次：

GRANT USAGE ON FOREIGN SERVER mot_server TO <user>;

所有关键字不区分大小写。

2.创建/删除MOT

创建MOT非常简单。只有MOT中的创建和删除表语句与openGauss中基于磁盘的表的语句不同。SELECT、DML和DDL的所有其他命令的语法对于MOT表和openGauss基于磁盘的表是一样的。

创建MOT：

create FOREIGN table test(x int) [server mot_server];

以上语句中：
- 始终使用FOREIGN关键字引用MOT。
- 在创建MOT表时，[server mot_server]部分是可选的，因为MOT是一个集成的引擎，而不是一个独立的服务器。
- 上文以创建一个名为test的内存表（表中有一个名为x的整数列）为例。在下一节（创建索引）中将提供一个更现实的例子。
- 如果postgresql.conf中开启了增量检查点，则无法创建MOT。因此请在创建MOT前将enable_incremental_checkpoint设置为off。
删除名为test的MOT：
```
drop FOREIGN table test;
```

3.为MOT创建索引

支持标准的PostgreSQL创建和删除索引语句。

例如：

create index text_index1 on test(x) ;

创建一个用于TPC-C的ORDER表，并创建索引：
create FOREIGN table bmsql_oorder ( 
  o_w_id       integer      not null, 
  o_d_id       integer      not null, 
  o_id         integer      not null, 
  o_c_id       integer not null, 
  o_carrier_id integer,          
  o_ol_cnt     integer, 
  o_all_local  integer, 
  o_entry_d    timestamp, 
  primary key (o_w_id, o_d_id, o_id) 
); 
create index  bmsql_oorder_index1 on bmsql_oorder(o_w_id, o_d_id, o_c_id, o_id) ;

说明： 在MOT名字之前不需要指定FOREIGN关键字，因为它仅用于创建和删除表的命令。

有关MOT索引限制，请参见“MOT SQL覆盖和限制”的索引部分内容。

4.将磁盘表转换为MOT

磁盘表直接转换为MOT尚不能实现，这意味着尚不存在将基于磁盘的表转换为MOT的ALTER TABLE语句。

下面介绍如何手动将基于磁盘的表转换为MOT，如何使用gs_dump工具导出数据，以及如何使用gs_restore工具导入数据。

4.1前置条件检查

检查待转换为MOT的磁盘表的模式是否包含所有需要的列。

检查架构是否包含任何不支持的列数据类型，具体参见“不支持的数据类型”章节。

如果不支持特定列，则建议首先创建一个更新了模式的备磁盘表。此模式与原始表相同，只是所有不支持的类型都已转换为支持的类型。

使用以下脚本导出该备磁盘表，然后导入到MOT中。

4.2转换

要将基于磁盘的表转换为MOT，请执行以下步骤：

暂停应用程序活动。
使用gs_dump工具将表数据转储到磁盘的物理文件中。请确保使用data only。
重命名原始基于磁盘的表。
创建同名同模式的MOT。请确保使用创建FOREIGN关键字指定该表为MOT。
使用gs_restore将磁盘文件的数据加载/恢复到数据库表中。
浏览或手动验证所有原始数据是否正确导入到新的MOT中。下面将举例说明。
恢复应用程序活动。

须知： 由于表名称保持不变，应用程序查询和相关数据库存储过程将能够无缝访问新的MOT，而无需更改代码。请注意，MOT目前不支持跨引擎多表查询（如使用Join、Union和子查询）和跨引擎多表事务。因此，如果在多表查询、存储过程或事务中访问原始表，则必须将所有相关的磁盘表转换为MOT，或者更改应用程序或数据库中的相关代码。

4.3转换示例

假设要将数据库benchmarksql中一个基于磁盘的表customer迁移到MOT中。

将customer表迁移到MOT，操作步骤如下：

检查源表列类型。验证MOT支持所有类型，详情请参阅“不支持的数据类型”章节。

benchmarksql-# \d+ customer 
                       Table "public.customer" 
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+------------- 
 x      | integer |           | plain   |              | 
 y      | integer |           | plain   |              | 
Has OIDs: no 
Options: orientation=row, compression=no

请检查源表数据。

benchmarksql=# select * from customer; 
 x | y 
---+--- 
 1 | 2 
 3 | 4 
(2 rows)

只能使用gs_dump转储表数据。


$ gs_dump -Fc benchmarksql -a --table customer -f customer.dump -p 16000
gs_dump[port='15500'][benchmarksql][2020-06-04 16:45:38]: dump database benchmarksql successfully 
gs_dump[port='15500'][benchmarksql][2020-06-04 16:45:38]: total time: 332  ms

重命名源表。

benchmarksql=# alter table customer rename to customer_bk; 
ALTER TABLE

创建与源表完全相同的MOT。

benchmarksql=# create foreign table customer (x int, y int); 
CREATE FOREIGN TABLE 
benchmarksql=# select * from customer; 
 x | y 
---+--- 
(0 rows)

将源转储数据导入到新MOT中。

$ gs_restore -C -d benchmarksql customer.dump -p 16000
restore operation successful 
total time: 24  ms 
Check that the data was imported successfully. 
benchmarksql=# select * from customer; 
 x | y 
---+--- 
 1 | 2 
 3 | 4 
(2 rows) 
      
benchmarksql=# \d 
                                List of relations 
 Schema |    Name     |     Type      | Owner  |             Storage 
--------+-------------+---------------+--------+---------------------------------- 
 public | customer    | foreign table | aharon | 
 public | customer_bk | table         | aharon | {orientation=row,compression=no} 
(2 rows)

5.查询原生编译

MOT的另一个特性是，在预编译的完整查询需要执行之前，能够以原生格式（使用PREPARE语句）准备并解析这些查询。

这种原生格式方便后续更有效地执行（使用EXECUTE命令）。这种执行类型速度要快得多，因为原生格式在执行期间绕过多个数据库处理层，从而获得更好的性能。

这种分工避免了重复的解析分析操作。查询和事务语句可以交互执行。此功能有时称为即时（Just-In-Time，JIT）查询编译。

5.1 查询编译：PREPARE语句

若要使用MOT的原生查询编译，请在执行查询之前调用PREPARE客户端语句。MOT将预编译查询和（或）从缓存预加载先前预编译的代码。

下面是SQL中PREPARE语法的示例：

PREPARE name [ ( data_type [, ...] ) ] AS statement

PREPARE在数据库服务器中创建一个预处理语句，该语句是一个可用于优化性能的服务器端对象。

5.2 运行命令

发出EXECUTE命令时，将解析、分析、重写和执行预处理语句。这种分工避免了重复的解析分析操作，同时使执行计划依赖于特定的设置值。

下面是在Java应用程序中调用PREPARE和EXECUTE语句的示例。

conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword); 

// Example 1: PREPARE without bind settings 
String query = "SELECT * FROM getusers";  
PreparedStatement prepStmt1 = conn.prepareStatement(query); 
ResultSet rs1 = pstatement.executeQuery()) 
while (rs1.next()) {…} 

// Example 2: PREPARE with bind settings 
String sqlStmt = "SELECT * FROM employees where first_name=? and last_name like ?"; 
PreparedStatement prepStmt2 = conn.prepareStatement(sqlStmt); 
prepStmt2.setString(1, "Mark"); // first name “Mark” 
prepStmt2.setString(2, "%n%"); // last name contains a letter “n” 
ResultSet rs2 = prepStmt2.executeQuery()) 
while (rs2.next()) {…}

MOT编译支持的特性和不支持的特性见下文。

5.3 轻量执行支持的查询

以下查询类型适合轻量执行：

简单点查询
- SELECT (including SELECT for UPDATE)
- UPDATE
- DELETE
INSERT查询
引用主键的完整前缀的范围UPDATE查询
引用主键的完整前缀的范围SELECT查询
JOIN查询，其中一部分或两部分重叠为点查询
引用每个连接表中主键的完整前缀的JOIN查询

5.4 轻量执行不支持的查询

任何特殊的查询属性都不适用于轻量执行。特别是如果以下条件中的任何一项适用，则该查询不适合轻量执行。有关更多信息，请参阅“原生编译和轻量执行不支持的查询”。

需要强调一点，如果查询语句不适用原生编译和轻量执行，不向客户端报告错误，查询仍以正常和规范的方式执行。

有关MOT原生编译功能的详细信息，请参阅 “查询原生编译”或“查询原生编译（JIT）”的有关内容。

6. 重试中止事务

在乐观并发控制（OCC）中，在COMMIT阶段前的事务期间（使用任何隔离级别）不会对记录进行锁定。这是一个能显著提高性能的强大优势。它的缺点是，如果另一个会话尝试更新相同的记录，则更新可能会失败。所以必须中止整个事务。这些所谓的更新冲突是由MOT在提交时通过版本检查机制检测到的。

说明： 使用悲观并发控制的引擎，如标准Postgres和openGauss基于磁盘的表，当使用SERIALIZABLE或REPEATABLE-READ隔离级别时，也会发生类似的异常中止。

这种更新冲突在常见的OLTP场景中非常少见，在使用MOT时尤其少见。但是，由于仍有可能发生这种情况，开发人员应该考虑使用事务重试代码来解决此问题。

下面以多个会话同时尝试更新同一个表为例，说明如何重试表命令。有关更多详细信息，请参阅“OCC与2PL的区别举例”部分。下面以TPC-C支付事务为例。

int commitAborts = 0; 

while (commitAborts < RETRY_LIMIT) { 

    try {                         
        stmt =db.stmtPaymentUpdateDistrict; 
        stmt.setDouble(1, 100); 
        stmt.setInt(2, 1); 
        stmt.setInt(3, 1); 
        stmt.executeUpdate(); 

        db.commit();                      

        break; 
    }               
    catch (SQLException se) { 
        if(se != null && se.getMessage().contains("could not serialize access due to concurrent update")) { 
            log.error("commmit abort = " + se.getMessage()); 
            commitAborts++; 
            continue; 
        }else { 
            db.rollback(); 
        } 

        break; 
    } 
}

7. MOT外部支持工具

为了支持MOT，修改了以下外部openGauss工具。请确保使用的工具是最新版本。下面将介绍与MOT相关的用法。有关这些工具及其使用方法的完整说明，请参阅openGauss工具参考。

gs_ctl（全量和增量）

此工具用于从主服务器创建备服务器，以及当服务器的时间线偏离后，将服务器与其副本进行同步。

在操作结束时，工具将获取最新的MOT检查点，同时考虑checkpoint_dir配置值。

检查点从源服务器的checkpoint_dir读取到目标服务器的checkpoint_dir。

目前MOT不支持增量检查点。因此，gs_ctl增量构建对于MOT来说不是以增量方式工作，而是以全量方式工作。Postgres磁盘表仍然可以增量构建。

gs_basebackup

gs_basebackup用于准备运行中服务器的基础备份，不影响其他数据库客户端。

MOT检查点也会在操作结束时获取。但是，检查点的位置是从源服务器中的checkpoint_dir获取的，并传输到源数据目录中，以便正确备份。

gs_dump

gs_dump用于将数据库模式和数据导出到文件中。支持MOT。

gs_restore

gs_restore用于从文件中导入数据库模式和数据。支持MOT。

8. MOT SQL覆盖和限制

MOT设计几乎能够覆盖SQL和未来特性集。例如，大多数支持标准的Postgres SQL，也支持常见的数据库特性，如存储过程、自定义函数等。

下面介绍各种SQL覆盖和限制。

8.1 不支持的特性

MOT不支持以下特性：

跨引擎操作：不支持跨引擎（磁盘+MOT）的查询、视图或事务。计划于2021年实现该特性。
MVCC、隔离：不支持没有快照/可序列化隔离。计划于2021年实现该特性。
原生编译（JIT）：SQL覆盖有限。此外，不支持存储过程的JIT编译。
本地内存限制为1GB。一个事务只能更改小于1GB的数据。
容量（数据+索引）受限于可用内存。未来将提供Anti-caching和数据分层功能。
不支持全文检索索引。
不支持逻辑复制特性。

此外，下面详细列出了MOT、MOT索引、查询和DML语法的各种通用限制，以及查询原生编译的特点和限制。

8.2 MOT限制

MOT功能限制：

按范围分区
AES加密
流操作
自定义类型
子事务
DML触发器
DDL触发器
“C”或“POSIX”以外的排序规则

8.3 不支持的DDL操作

修改表结构
创建including表
创建as select表
按范围分区
创建无日志记录子句（no-logging clause）的表
创建可延迟约束主键（DEFERRABLE）
重建索引
表空间
使用子命令创建架构

8.4 不支持的数据类型

UUID
User-Defined Type (UDF)
Array data type
NVARCHAR2(n)
NVARCHAR(n)
Clob
Name
Blob
Raw
Path
Circle
Reltime
Bit varying(10)
Tsvector
Tsquery
JSON
HSTORE
Box
Text
Line
Point
LSEG
POLYGON
INET
CIDR
MACADDR
Smalldatetime
BYTEA
Bit
Varbit
OID
Money
无限制的varchar/character varying
HSTORE
XML

8.5 不支持的索引DDL和索引

在小数和数值类型上创建索引
在可空列上创建索引
单表创建索引总数>9
在键大小>256的表上创建索引

键大小包括以字节为单位的列大小+列附加大小，这是维护索引所需的开销。下表列出了不同列类型的列附加大小。

此外，如果索引不是唯一的，额外需要8字节。

下面是伪代码计算键大小：
```
keySize =0; 
      
for each (column in index){ 
      keySize += (columnSize + columnAddSize); 
} 
if (index is non_unique) { 
      keySize += 8; 
}
```
列类型

列大小

列附加大小

varchar

N

4

tinyint

1

1

smallint

2

1

int

4

1

longint

8

1

float

4

2

double

8

3

列类型	列大小	列附加大小
varchar	N	4
tinyint	1	1
smallint	2	1
int	4	1
longint	8	1
float	4	2
double	8	3

上表中未指定的类型，列附加大小为零（例如时间戳）。

8.6 不支持的DML

Merge into
Select into
Lock table
Copy from table

8.7 原生编译和轻量执行不支持的查询

查询涉及两个以上的表
查询有以下任何一个情况：
- 非原生类型的聚合
- 窗口功能
- 子查询子链接
- Distinct-ON修饰语（distinct子句来自DISTINCT ON）
- 递归（已指定WITH RECURSIVE）
- 修改CTE（WITH中有INSERT/UPDATE/DELETE）

以下子句不支持轻量执行：