openGauss
开源数据库
openGauss社区官网
开源社区
openGauss内存优化表最佳实践
2020-12-17openGauss最佳实践
openGauss 内存优化表最佳实践
适用版本
openGauss 1.0.1 build 和更高版本。
用途
概述 MOT（内存优化表）的特性，如何在 openGauss 数据库中创建和使用 MOT。
MOT 概述
openGauss 引入了 MOT 存储引擎，它是一种事务性行存储，针对多核和大内存服务器进行了优化。MOT 是 openGauss 数据库最先进的生产级特性（Beta 版本），它为事务性工作负载提供更高的性能。MOT 完全支持 ACID 特性，并包括严格的持久性和高可用性支持。企业可以在关键任务、性能敏感的在线事务处理（OLTP）中使用 MOT，以实现高性能、高吞吐、可预测低延迟以及多核服务器的高利用率。
使用 MOT
授予用户权限
postgres=# select usename,usesysid from pg_user; usename | usesysid ---------+---------- omm | 10 (1 row) 要使omm用户能够创建和访问MOT（DDL、DML、SELECT），以下语句只执行一次 GRANT USAGE ON FOREIGN SERVER mot_server TO omm;创建/删除 MOT
检查参数
postgres=# show enable_incremental_checkpoint; enable_incremental_checkpoint ------------------------------ on如果 enable_incremental_checkpoint 显示为 on，则修改为 off，并重启数据库
alter system set enable_incremental_checkpoint='off'; gs_om -t stop gs_om -t start postgres=# show enable_incremental_checkpoint; enable_incremental_checkpoint ------------------------------- off (1 row)创建 MOT 表 mot_1，并创建索引
postgres=# create FOREIGN table mot_1 postgres-# (o_w_id integer not null, postgres(# o_d_id integer not null, postgres(# o_id integer not null, postgres(# o_c_id integer not null, postgres(# o_carrier_id integer, postgres(# o_ol_cnt integer, postgres(# o_all_local integer, postgres(# o_entry_d timestamp, postgres(# primary key (o_w_id, o_d_id, o_id)); NOTICE: CREATE FOREIGN TABLE / PRIMARY KEY will create constraint "mot_1_pkey" for foreign table "mot_1" CREATE FOREIGN TABLE postgres=# create index mot_1_index1 on mot_1(o_w_id, o_d_id, o_c_id, o_id) ; CREATE INDEX插入 200000 随机数据。
postgres=# insert into mot_1 select ceil(random()*(10000-1)+1),ceil(random()*(10000-10)+10),ceil(random()*(10000-100)+100),ceil(random()*(10000-1)+1),ceil(random()*(1000-1)+1),ceil(random()*(1000-1)+1),ceil(random()*(1000-1)+1),sysdate postgres-# FROM generate_series(1,200000); INSERT 0 200000 Time: 4202.304 ms通过执行计划查看 MOT 表
说明：执行计划中->Memory Engine 表示从内存中获取数据
postgres=# explain select * from mot_1; QUERY PLAN ------------------------------------------------------------------- Foreign Scan on mot_1 (cost=0.10..20000.00 rows=200000 width=36) -> Memory Engine returned rows: 0 (2 rows)select 所有列数据不包含 where 条件
postgres=# select * from mot_1 limit 5; o_w_id | o_d_id | o_id | o_c_id | o_carrier_id | o_ol_cnt | o_all_local | o_entry_d --------+--------+------+--------+--------------+----------+-------------+--------------------- 2 | 291 | 8858 | 4033 | 274 | 681 | 655 | 2020-11-26 11:03:34 2 | 962 | 7972 | 1996 | 684 | 646 | 399 | 2020-11-26 11:03:34 2 | 1750 | 1415 | 1597 | 758 | 508 | 163 | 2020-11-26 11:03:34 2 | 2315 | 2449 | 1049 | 418 | 585 | 61 | 2020-11-26 11:03:34 2 | 2444 | 4531 | 274 | 461 | 120 | 503 | 2020-11-26 11:03:34 (5 rows)select 所有列数据包含索引列
说明：内存表可以通过索引访问数据__ Index Scan on: mot_1_pkey1
postgres=# explain select * from mot_1 where o_w_id = 2 limit 4; QUERY PLAN -------------------------------------------------------------------- Limit (cost=0.00..4.00 rows=4 width=36) -> Foreign Scan on mot_1 (cost=0.00..211.00 rows=211 width=36) -> Memory Engine returned rows: 0 -> Index Scan on: mot_1_pkey1 Index Cond: (mot_1.o_w_id = 2) postgres=# select * from mot_1 where o_w_id = 2 limit 4; o_w_id | o_d_id | o_id | o_c_id | o_carrier_id | o_ol_cnt | o_all_local | o_entry_d --------+--------+------+--------+--------------+----------+-------------+--------------------- 2 | 291 | 8858 | 4033 | 274 | 681 | 655 | 2020-11-26 11:03:34 2 | 962 | 7972 | 1996 | 684 | 646 | 399 | 2020-11-26 11:03:34 2 | 1750 | 1415 | 1597 | 758 | 508 | 163 | 2020-11-26 11:03:34 2 | 2315 | 2449 | 1049 | 418 | 585 | 61 | 2020-11-26 11:03:34两张表关联查询
新建 MOT 表 mot_2
postgres=# create FOREIGN table mot_2 postgres-# (o_w_id integer not null, postgres(# o_d_id integer not null, postgres(# o_id integer not null); CREATE FOREIGN TABLE Time: 3.359 ms查看执行计划（mot_2 表无索引），通过 Hash Join 的方式做表关联
postgres=# explain select a.o_w_id,b.o_id from mot_2 a,mot_1 b where a.o_w_id = b.o_w_id; QUERY PLAN -------------------------------------------------------------------------------- Hash Join (cost=11250.10..1156750.00 rows=500 width=8) Hash Cond: (b.o_w_id = a.o_w_id) -> Foreign Scan on mot_1 b (cost=0.10..20000.00 rows=200000 width=8) -> Memory Engine returned rows: 0 -> Hash (cost=10000.00..10000.00 rows=100000 width=4) -> Foreign Scan on mot_2 a (cost=0.10..10000.00 rows=100000 width=4) -> Memory Engine returned rows: 0 (7 rows)查看执行计划（mot_2 有单列索引），通过访问索引 Nested Loop 的方式访问数据
postgres=# create index mot_2_index1 on mot_1(o_w_id) ; CREATE INDEX Time: 437.916 ms postgres=# explain select a.o_w_id,b.o_id from mot_2 a,mot_1 b where a.o_w_id = b.o_w_id; QUERY PLAN -------------------------------------------------------------------------- Nested Loop (cost=0.10..111000.00 rows=500 width=8) -> Foreign Scan on mot_2 a (cost=0.10..10000.00 rows=100000 width=4) -> Memory Engine returned rows: 0 -> Foreign Scan on mot_1 b (cost=0.00..1.00 rows=1 width=8) Filter: (a.o_w_id = o_w_id) -> Memory Engine returned rows: 0 -> Index Scan on: mot_2_index1 Index Cond: (b.o_w_id = $0) (8 rows)update 单表非索引字段
将 update o_w_id=2 的非索引字段
postgres=# select count(*) from mot_1 where o_w_id=2; count ------- 21 (1 row)已更新 21 条
postgres=# update mot_1 set o_all_local = 2020,o_ol_cnt=9090 where o_w_id=2; UPDATE 21 Time: 0.672 msupdate 单表索引字段
将 update o_w_id=2 的索引字段
postgres=# select count(*) from mot_1 where o_w_id=2; count ------- 21 (1 row)无法更新
postgres=# update mot_1 set o_w_id=2020 where o_w_id=2; ERROR: Update of indexed column is not supported for memory table Time: 0.302 msupdate 含有子查询
mot_2 o_w_id = 2 的数据(2000 条)
postgres=# select count(o_w_id) from mot_2; count ------- 2000 (1 row)更新 mot_1 中 o_w_id in（select o_w_id from mot_2 where o_w_id = 2）的数据
postgres=# update mot_1 set o_all_local = 2020,o_ol_cnt=9090 where o_w_id in (select o_w_id from mot_2 where o_w_id = 2); UPDATE 21 Time: 13.429 ms查询 mot_1 中 o_all_local = 2020 and o_ol_cnt=9090 的数据
postgres=# Select * from mot_1 where o_all_local = 2020 and o_ol_cnt=9090; o_w_id | o_d_id | o_id | o_c_id | o_carrier_id | o_ol_cnt | o_all_local | o_entry_d --------+--------+------+--------+--------------+----------+-------------+--------------------- 2 | 291 | 8858 | 4033 | 274 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 962 | 7972 | 1996 | 684 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 1750 | 1415 | 1597 | 758 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 2315 | 2449 | 1049 | 418 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 2444 | 4531 | 274 | 461 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 2761 | 2627 | 7693 | 169 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 3300 | 3947 | 167 | 997 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 3921 | 424 | 4765 | 557 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 4216 | 2304 | 4065 | 829 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 5401 | 8904 | 8009 | 856 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 5416 | 9478 | 2939 | 959 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 5696 | 9749 | 7707 | 330 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 5935 | 2938 | 324 | 210 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 6680 | 4998 | 5906 | 439 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 6983 | 2343 | 18 | 87 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 7274 | 3141 | 6374 | 941 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 7292 | 6505 | 9174 | 989 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 7740 | 3702 | 8976 | 958 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 7828 | 7520 | 1161 | 387 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 8223 | 9573 | 7540 | 622 | 9090 | 2020 | 2020-11-26 11:03:34 2 | 8327 | 4427 | 8846 | 519 | 9090 | 2020 | 2020-11-26 11:03:34 (21 rows) Time: 110.945 msdelete 单表（删除 o_w_id = 3 的所有数据）
postgres=# select count(*) from mot_1 where o_w_id = 3; count ------- 26 (1 row) postgres=# delete from mot_1 where o_w_id = 3; DELETE 26 postgres=# commit; COMMIT postgres=# select count(*) from mot_1 where o_w_id = 3; count ------- 0 (1 row)delete 带有子查询（删除在 mot_2 表中 o_w_id = 5 的 mot_1 表的所有数据）
postgres=# delete from mot_1 where o_w_id in (select o_w_id from mot_2 where o_w_id = 5 ); DELETE 15 postgres=# commit; COMMITtruncate 操作（truncate mot_2 表）
postgres=# truncate table mot_2; TRUNCATE TABLE事务的 commit 和 rollback
truncate（commit）
结论：
1、truncate 操作可以 rollback。
2、当会话 1 执行 truncate 之后（没有 commit），会话 2 对该表执行 select 操作处于等待状态。
3、会话 1 执行 commit 或者 rollback 之后，其它会话可以正常 dml 操作。
会话 1
postgres=# truncate table mot_2; TRUNCATE TABLE postgres=# select count(*) from mot_2; count ------- 0 (1 row) postgres=# commit; COMMIT postgres=#会话 2
postgres=# select * from mot_2; 等待状态… 等待会话1 commit之后 postgres=# select * from mot_2; o_w_id | o_d_id | o_id --------+--------+------ (0 rows)truncate（rollback）
insert mot_2 表 2000 条数据
postgres=# insert into mot_2 select ceil(random()*(10000-1)+1),ceil(random()*(10000-10)+10),ceil(random()*(10000-100)+100) FROM generate_series(1,2000); INSERT 0 2000 postgres=# postgres=# commit; COMMIT postgres=# select count(*) from mot_2; count ------- 2000 (1 row)postgres=# truncate table mot_2; TRUNCATE TABLE postgres=# select count(*) from mot_2; count ------- 0 (1 row) postgres=# rollback; ROLLBACK postgres=# select count(*) from mot_2; count ------- 2000 (1 row)update(commit 和 rollback)
结论：
1、会话 1 做 update 操作（commit 之前），会话 2 依然可以对同一批数据做出不同的更改，不会出现悲观锁等待。
2、最终的数据以最先 commit 的操作为准。
会话 1
select * from mot_2 where o_w_id = 5573; postgres=# select * from mot_2 where o_w_id = 5573; o_w_id | o_d_id | o_id --------+--------+------ 5573 | 8453 | 7777 5573 | 2162 | 7777 (2 rows)会话 2
select * from mot_2 where o_w_id = 5573; postgres=# select * from mot_2 where o_w_id = 5573; o_w_id | o_d_id | o_id --------+--------+------ 5573 | 8453 | 7777 5573 | 2162 | 7777 (2 rows)会话 1
postgres=# update mot_2 set o_id = 9999 where o_w_id = 5573; UPDATE 2会话 2
postgres=# update mot_2 set o_id = 3333 where o_w_id = 5573; UPDATE 2--在会话 1 没有 commit 或者 rolllback 时，会话 2 可以修改同一批数据，修改成功
会话 1 --看到的是当前会话已修改 o_id=9999 的数据
select * from mot_2 where o_w_id = 5573; postgres=# select * from mot_2 where o_w_id = 5573; o_w_id | o_d_id | o_id --------+--------+------ 5573 | 8453 | 9999 5573 | 2162 | 9999 (2 rows)会话 2 --看到的是当前会话已修改 o_id=3333 的数据
postgres=# select * from mot_2 where o_w_id = 5573; o_w_id | o_d_id | o_id --------+--------+------ 5573 | 8453 | 3333 5573 | 2162 | 3333 (2 rows)会话 1 --先提交
postgres=# commit; COMMIT postgres=# select * from mot_2 where o_w_id = 5573; o_w_id | o_d_id | o_id --------+--------+------ 5573 | 8453 | 9999 5573 | 2162 | 9999 (2 rows)会话 2 --不提交，查询依然 o_id=3333 的数据
postgres=# select * from mot_2 where o_w_id = 5573; o_w_id | o_d_id | o_id --------+--------+------ 5573 | 8453 | 3333 5573 | 2162 | 3333 (2 rows)会话 2 --后提交
postgres=# commit; WARNING: AbortTransaction while in COMMIT state ERROR: Commit: could not serialize access due to concurrent update(0) postgres=# select * from mot_2 where o_w_id = 5573; o_w_id | o_d_id | o_id --------+--------+------ 5573 | 8453 | 9999 5573 | 2162 | 9999 (2 rows)insert(commit 和 rollback)
结论：
1、会话 1 做 insert 操作（commit 之前），会话 1 可以查看新插入的数据，会话 2 无法查看到新插入的数据。
2、等会话 1 做了 commit 之后，会话 2 可以看见新插入的数据。
会话 1
postgres=# \set AUTOCOMMIT off postgres=# select count(*) from mot_2; count ------- 2000 (1 row) postgres=# insert into mot_2 select ceil(random()*(10000-1)+1),ceil(random()*(10000-10)+10),ceil(random()*(10000-100)+100) FROM generate_series(1,2000); INSERT 0 2000 postgres=# postgres=# select count(*) from mot_2; count ------- 4000 (1 row) postgres=# commit; COMMIT会话 2
postgres=# \set AUTOCOMMIT off postgres=# select count(*) from mot_2; count ------- 2000 (1 row) postgres=# select count(*) from mot_2; count ------- 4000 (1 row)update(commit 和 rollback)
结论：和 insert 原理相同
会话 1
postgres=# select count(*) from mot_1 where o_w_id=2; count ------- 21 (1 row) postgres=# postgres=# update mot_1 set o_all_local = 5555 where o_w_id=2; UPDATE 21 postgres=# select count(*) from mot_1 where o_w_id=2 and o_all_local =5555; count ------- 21 (1 row) postgres=# commit; COMMIT会话 2
postgres=# select count(*) from mot_1 where o_w_id=2 and o_all_local =5555; count ------- 0 (1 row) postgres=# select count(*) from mot_1 where o_w_id=2 and o_all_local =5555; count ------- 21 (1 row)数据库 stop、start 之后 MOT 表的数据是否存在。
结论：数据库 MOT 表的数据依然存在
postgres=# select count(*) from mot_1; count -------- 199959 (1 row) postgres=# select count(*) from mot_2; count ------- 4000 (1 row) postgres-# \q [omm@bms-f408-0001 ~]$ gs_om -t stop Stopping cluster. ========================================= Successfully stopped cluster. ========================================= End stop cluster. [omm@bms-f408-0001 ~]$ gs_om -t start Starting cluster. ========================================= ========================================= Successfully started. [omm@bms-f408-0001 ~]$ gsql -d postgres -p26000 gsql ((openGauss 1.0.1 build 3a33fded) compiled at 2020-11-04 15:58:51 commit 0 last mr ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. postgres=# select count(*) from mot_1; count -------- 199959 (1 row) postgres=# select count(*) from mot_2; count ------- 4000 (1 row)通过 create table as 从实体表创建 MOT 表。
结论：不支持
postgres=# create table tbl_1(aid integer not null,bid integer); CREATE TABLE postgres=# insert into tbl_1 values(1001,9001); INSERT 0 1 postgres=# create FOREIGN table mot_from_tbl_1 as select * from tbl_1; ERROR: syntax error at or near "as" LINE 1: create FOREIGN table mot_from_tbl_1 as select * from tbl_1; ^ postgres=# create table tbl_2 as select * from tbl_1;通过 create forrign table as 从 MOT 表创建 MOT 表
结论：不支持
postgres=# create FOREIGN table mot_3 as select * from mot_2; ERROR: syntax error at or near "as" LINE 1: create FOREIGN table mot_3 as select * from mot_2; ^ postgres=# create FOREIGN table mot_3 select * from mot_2; ERROR: syntax error at or near "select" LINE 1: create FOREIGN table mot_3 select * from mot_2;MOT 表相互通过 insert into select 的方式插入数据。
结论：支持
postgres=# create FOREIGN table mot_3 postgres-# (o_w_id integer not null, postgres(# o_d_id integer not null, postgres(# o_id integer not null); CREATE FOREIGN TABLE postgres=# insert into mot_3 select * from mot_2; INSERT 0 4000 postgres=# commit;通过 insert into select 的方式从普通表向 MOT 表插入数据。
结论：不支持
postgres=# create table tbl_1(aid integer not null,bid integer); ERROR: relation "tbl_1" already exists postgres=# insert into tbl_1 values(1001,9001); INSERT 0 1 postgres=# create FOREIGN table mot_tbl_1(aid integer not null,bid integer); CREATE FOREIGN TABLE postgres=# select count(*) from mot_tbl_1; count ------- 0 (1 row) postgres=# insert into mot_tbl_1 select * from tbl_1; ERROR: Cross storage engine query is not supportedopenGauss MOT 和其它数据库相关特性对比
说明：
在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。