GaussDB(DWS)时序表建表最佳实践cid:link_0HStore简介cid:link_1列存储优势cid:link_6小CU问题cid:link_2CU合并cid:link_7提升数据聚簇性cid:link_3HStore表的背景cid:link_4HStore 表的特点cid:link_8HStore 的视图与函数cid:link_9GaussDB(DWS) 锁管理详解cid:link_10表锁cid:link_11其他锁cid:link_5用户自定义锁cid:link_12查看锁等待cid:link_13GaussDB(DWS)有哪些锁https://bbs.huaweicloud.com/forum/thread-0294191082093980066-1-1.html

常规锁：常规锁主要用于业务访问数据库对象的加锁，保护并发操作的对象，保持数据一致性；常见的常规锁有表锁（relation）和行锁（tuple）。 表锁：当对表进行DDL、DML操作时，会对操作的对象表加锁，在事务结束释放； 行锁：使用select for share语句时持有该模式锁，后台会对tuple加5级锁；使用select for update, delete, update 等操作时，后台会对tuple加7级锁（ExclusiveLock）。 轻量级锁：轻量级锁主要用于数据库内部共享资源访问的保护，比如内存结构、共享内存分配控制等。常规锁按照粒度通常可分为1~8个等级，DWS在8.2.1(及以上)版本中新增了第9级锁（UPDATE EXCLUSIVE）。ACCESS SHARE与ACCESS EXCLUSIVE锁冲突例子：session 1 在事务内对表进行truncate，且 lockwait_timeout参数设置为10s；session 2 查询该表，此时会一直等到session 1 释放锁，直到等锁超时；ROW SHARE（行锁冲突的例子）：并发insert/update/copy；session 1在事务内对有主键约束的行存表进行更新；session 2 对同一主键的行进行更新，会一直等待session 1释放锁，直到行锁超时。

locktype 列表示锁类型，包括表锁、事务锁、扩展锁、自定义锁等； relation 列表示表的oid，如果是表锁，relation列会显示表的oid； transactionid 表示事务号，如果是事务锁，transactionid列会显示session的事务号； mode列表示锁级别，级别1-8级； pid 列表示session的线程号； granted 列表示是否持有锁，‘t’表示持有锁，‘f'表示等待锁；持有表t1的3级锁RowExclusiveLock，表t1的oid是16384； 持有session2的事务锁，事务号是2415630； 持有记录（1,1）的记录锁； 等待session1事务结束释放事务锁，granted为’f'，申请 session1的事务号对应的5级锁（ShareLock），与 session1 持有 7 级事务锁冲突，需要锁等待。 

用户自定义锁也叫咨询锁(advisory lock），用户可以通过调用GaussDB(DWS) 提供的函数来自定义锁，自定义锁按照作用范围分为两类： 1) 事务级自定义锁 pg_advisory_xact_lock(key bigint) pg_advisory_xact_lock(key1 int, key2 int) pg_advisory_xact_lock_shared(key bigint) pg_advisory_xact_lock_shared(key1 int, key2 int) pg_try_advisory_xact_lock(key bigint) pg_try_advisory_xact_lock(key1 int, key2 int) pg_try_advisory_xact_lock_shared(key bigint) pg_try_advisory_xact_lock_shared(key1 int, key2 int) 2) session级自定义锁 pg_advisory_lock(key bigint) pg_advisory_lock(key1 int, key2 int) pg_advisory_lock_shared(key bigint) pg_advisory_lock_shared(key1 int, key2 int) pg_try_advisory_lock(key bigint) pg_try_advisory_lock(key1 int, key2 int) pg_try_advisory_lock_shared(key bigint) pg_try_advisory_lock_shared(key1 int, key2 int) 带shared后缀的相关函数会申请5级锁ShareLock，不带 shared后缀的会申请7级锁ExclusiveLock； 带try 标识的相关函数表示尝试申请锁，如果申请不到，直接返回，不需锁等待。

1) 事务锁 写事务会获取一个事务号，并且会以这个事务号申请一个事务锁，锁级别是7级锁ExclusiveLock。 事务锁用于控制记录的并发修改，比如，两个事务先后修改同一条记录，在前一个事务未结束之前，后一个事务会等待在前一个事务的事务锁上。 2) 记录锁 当出现并发更新冲突时，冲突的事务会申请记录锁，锁级别是 7 级锁ExclusiveLock。 记录锁主要是提高等待事务的优先级，在更新事务结束后，让持有记录锁的事务第一个被唤醒。 3) 扩展锁 文件扩展时，会申请扩展锁，扩展锁的锁级别是7级锁ExclusiveLock。 表、索引、fsm、vm等文件扩展时都会申请扩展锁。 4) 分区锁 分区锁是专门针对分区表的，分区锁的意义与表锁差不多，锁级别从 1-8 都有。

GaussDB(DWS)支持的表锁级别很多，原生PG支持从最低的1级到最高的8级，另外GaussDB(DWS)在8.2.1（及以上）版本中还增加了9级锁，各锁级别含义如下： 1级锁，AccessShareLock SELECT语句申请AccessShareLock，只与8级锁冲突，只会阻塞DDL等语句； 2级锁，RowShareLock SELECT FOR SHARE/UPDATE语句申请RowShareLock，与7/8级锁冲突； 3级锁，RowExclusiveLockINSERT/UPDATE/DELET语句申请RowExclusiveLock，与 6-8级锁冲突； 4级锁，ShareUpdateExclusiveLockVACUUM/ANALYZE语句申请ShareUpdateExclusiveLock，与 5-8级锁冲突； 5级锁，ShareLock CREATE INDEX语句申请ShareLock，与4/6/7/8级锁冲突，与5级锁不冲突，同一个表的多个CREATE INDEX可以同时执行不阻塞； 6级锁，ShareRowExclusiveLock 在 GaussDB(DWS) 中，ShareRowExclusiveLock 目前只在 ALTER SEQUENCE 中用到，阻塞表的增删改以及更高级别操作，该锁与3-8级锁冲突； 7级锁，ExclusiveLock VACUUM FULL，MERGE PARTITION等语句申请ExclusiveLock级锁，ExclusiveLock 只与SELECT兼容，与2-8级锁冲突； 除了表锁外，事务锁，扩展锁、记录锁都是使用的ExclusiveLock，后面会进行详细介绍； 8级锁，AccessExclusiveLock DDL 等语句会申请AccessExclusiveLock，包括 ALTER TABLE，DROP TABLE，TRUNCATE，REINDEX，VACUUM FULL等，8级锁与所有锁都冲突； 9级锁，UpdateExclusiveLock 仅autovacuum中使用，为了支持与autoanalyze操作并行。 

LWLock 称为轻量级锁，只存在两种锁类型（LWLockMode）：共享锁(Shared)和排它锁（Exclusive），当无法获取到锁时，会进行阻塞等待状态； 原生PG不提供死锁检测，DWS自己支持了死锁检测； 加锁解锁对外部用户透明，使用完一般应该立即主动释放，不等事务结束。 SpinLock 称为自旋锁，一般依赖于硬件支持，需要CPU支持TAS（test-and-set）指令集； 由于没有cpu上下文切换，性能高，主要用于临界区很短的场景，能快速释放锁，如几个指令内的内存状态修改和计算； 最多只有一个持有者，如未能获取到锁，则cpu busy做轮询检查直到拿到（超时后会PANIC退出）； 没有等待队列和死锁检测。 RegularLock 称为常规锁，底层使用hash 表，持锁时间可以较长，有死锁检测和等待队列，事务结束时会自动释放； 可细分为表锁，事务锁，文件扩展锁，咨询锁等。

当前HStore并发更新同一行仍然是不支持的，其中同一行上并发update/delete操作会先等锁然后报错，同一行上的并发upsert操作会先等锁然后继续执行。由于等待开销也是会影响业务的入库性能，甚至可能产生死锁，所以需要在入库时保证不会并发更新到同一行或者同一个key。索引相关 索引会占用额外的空间，同时带来的点查性能提升有限，所以HStore表只建议在需要做 Upsert 或者有点查（这里指唯一性与接近唯一的点查） 的诉求下创建一个主键或者btree 索引。 MERGE相关 由于HStore表依赖后台autovacuum来将操作MERGE到主表，所以入库速度不能超过MERGE速度，否则会导致Delta表的膨胀，可以通过控制入库的并发来控制入库速度。同时由于Delta表本身的空间复用受oldestXmin的影响，如果有老事务存在可能会导致Delta空间复用不及时而产生膨胀。 UPSERT性能 HStore 表虽然相比普通列存，并发upsert入库性能得到了很大提升，但相比行存还是有差距，大概只有行存的1/3。所以在不追求压缩率以及批量查询性能、只追求单点查询性能的场景下，还是推荐行存表入库。

虽然列存老Delta表解决了小批量入库产生的小CU问题，但是没有解决同一个 CU 上的并发更新产生的锁冲突问题。而实时入库的场景下，需要将insert+upsert+update 操作实时并发入库，数据来源于上游的其他数据库或者应用，同时要求入库后的数据要能及时查询，且对于查询的效率要求很高。 目前的列存表由于锁冲突的原因无法支持并发upsert/update入库，导致这些有需要的局点只能使用行存表，但是行存表因为格式的天然劣势，在AP查询场景下一方面性能较慢，另一方面由于压缩差导致占用了大量的磁盘空间，对用户产生额外成本。 GaussDB(DWS)中的HStore表， 在使用列存储格式尽量降低磁盘占用的同时，支持高并发的更新操作入库以及高性能的查询效率。面向对于实时入库和实时查询有较强诉求的场景，同时拥有处理传统TP场景的事务能力。HStore 表的实现主要依靠一张新设计的Delta表以及内存并发控制机制，这里简单讲一下Delta表的实现以及简单的观察Delta表。 HStore 的 Delta 表主要用于存放入库产生的 Insert/Delete/Update 操作，小批量Insert 的数据会先进入Delta形成一条类型是I(Insert)的记录；删除会往Delta表插入一条类型是D(Delete)的记录；更新操作(Upsert与Update)会拆分成 Delete + Insert会插入一条类型 X(表示由更新产生的删除)的记录以及一条类型 I 的记录；（类型是U(Update)的记录由轻量化Update产生，不过当前轻量化更新默认关闭，所以不用管。） 可以看到，入库时的 Upsert/Update/Delete 都会转换成相应类型的记录插入的HStore 的 Delta 表中，再结合内存并发控制机制，就能保证同一个CU上更新于删除操作不会阻塞。同时，由于小批量的插入只会在Delta表上形成一条记录，相比与列存老Delta的直接存储数据，能减少IO占用，提高MERGE效率。

最基础的表类型，顾名思义，数据按行存储，在实际的物理块中，数据的所示的方式存储： 优势很明显，点查场景下，直接就能索引到行存某行元组的位置，点查性能好。数据库中的系统表就是行存表，对于用户的一些对点查性能要求高或者频繁更新的小表，都推荐用行存表。 AP场景下，常常需要对某列进行批量查询来做分析业务，这时候采用行存的话就会把所有列都读出来产生冗余IO, 同时AP场景下的表数据量往往很大，行存表压缩暂未商用，使用行存表也会导致占用空间过大。GaussDB(DWS)中的列存表就是针对这种场景实现的。列存表将每列的数据批量存储成一个 CU(Compress Unit), 能带来了很好的空间压缩与批量查询性能提升，对于一些涉及多表关联的分析类复杂查询、数据不经常更新的表，推荐使用列存表。对于列存表，如果业务是频繁的小批量插入，那么将产生大量的小CU(单个CU里只有几百条甚至几条数据), 每个列的CU都是有压缩代价的，小CU过多将严重影响列存表的查询性能。列存的 Delta 表就是针对这种场景实现的，让小批量插入的数据先存储到行存Delta 表，满6w后由后台autovacuum异步merge到主表CU。如图3所示，需要注意的是列存带Delta表只解决小批量入库产生的小CU问题，不解决同一个CU上的并发更新问题。

在对HStore进行点查时，会首先通过CU的min/max来进行粗过滤，我们希望通过min/max 过滤掉大部分数据，这就要求每个 CU 的数据尽可能的接近，而不能过于分散。目前GaussDB(DWS)已经实现了局部聚簇 (Partial Cluster Key, 简称PCK)，在数据批插过程中就会进行排序。但还是会有如下几种情况导致CU的聚簇性无法达到要求： 1) 写入数据时，如果不是批量导入，则不会把数据写入排序器，而是直接插入Delta表，当 Delta 表 MERGE 的时候，也不会先走排序逻辑，而是直接将数据写入CU； 2) 当CU中的数据被删除的足够多时，就变成了小CU，聚簇性本身就会变差，就算进行了小 CU 合并，也依然不会走排序逻辑，而是将数据直接写入 Delta 表，merge流程与1）一致； 3) 实际上就是增加数据+删除数据。通过将HStore中多个CU的数据根据partial cluster key进行排序，生成新的CU再重新写入，新CU的数据会有更高的聚集性，即CU的min,max会在一个较小的区间内。异步排序时的并发处理与小CU合并类似。经过测试，排序后的CU聚簇性极大提升，粗过滤效率的提升与原本的数据特征有关。但是排序过程中会对所有参与排序的CU加CU级锁，此过程会阻塞部分DML操作，因此不建议在业务高峰期使用此功能。 

小CU合并不是直接产生新的CU，而是将小CU数据重新插入到delta表后标记删除，然后依赖Delta表的自动MERGE攒够后再产生完整的新CU； 和正常的Delta merge不同在于，小CU被标记删除后新插入Delta表的记录会申请新的ctid，因为 ctid是变化的，所以该操作和DML操作存在冲突。当DML操作时遇到小CU合并，使用等待重试的方式处理；当compact操作时遇到DML冲突时直接跳过即可,原因就是删除和更新操作还是会将数据标记删除，因此可以直接放弃合并此条数据。小CU合并的事务可见性基于现有的csn机制，compaction inprogress或者回滚对外不可见，还是看到老记录，compaction提交老记录就不再可见看到新记录。 具体一个 CU 中剩余多少条数据才算是小 CU，应该是与业务强相关。因此，小CU阈值应该可以使用GUC参数调节。0CU的处理比小CU的处理简单的多，我们直接从CUDESC表中将0CU记录删除即可。这里指的删除天然支持MVCC，因此老的快照查询依然可以访问被删除的记录。 小CU合并的过程就是不断的尝试把小于一定阈值的CU标记删除，转移数据到Delta 中，直到这个CU全部被标记删除后变成0 CU，就可以当做0 CU彻底清理。成功解决小CU问题，并且在小CU合并期间对实时入库性能几乎没有影响（推荐小CU行数阈值下upsert性能劣化1%），但是因为小CU问题的解决，可以很好的解决查询性能劣化，空间膨胀等问题，并且小CU合并完成后，最终实时入库性能还是会有显著提升。

 问题诱因 1) 有些实时表入库量并不大，不定期会有入库，因为 MERGE 的判断标准有两个：行数或者时间，超过时间没有入库后也会强制MERGE，这种情况下MERGE产生的CU的行数不可控，可能产生小CU； 2) 对于缓慢变化维表来说，可能很长时间才改变一次，每次都可能产生一个小 CU，虽然不会有太多这种小CU，但长期运行后，这种维度表数量还很多的情况下，小CU的数量就会到达影响系统性能的级别； 3) 频繁upsert、update、delete等更新后，CU中大部分数据被标记删除，这样的CU虽然会被列存VACUUM通过填充NULL进行回收，但是依然会导致小IO和cudesc表的膨胀，进而影响性能。 问题影响 1) CUDesc 并不会因为 CU 变小而变小，因此当小 CU 过多会导致存储利用率过低。比如一个1000列的大宽表产生的CU只包含1行数据，但是因为每一列都会在CUDESC表中记录，CUDesc也会增加一千多行数据； 2) 只剩下几十行甚至几行的小CU会引发大量的小IO； 3) 粗过滤效率降低，因为CUDESC表中会存储CU的最值，当进行查询时可以先通过最值进行粗过滤，但是如果CU中数据太少导致数据范围小，则会降低粗过滤效率； 4) 降低压缩率。因为数据压缩是以CU为单位的，但是CU过小会导致压缩表现达不到预期。

列存储优势明显，但是劣势也比较明显，传统列存表基本无法支持并发更新入库。随着业务复杂程度的提升，出现了对于实时入库和实时查询有较强诉求的场景，这要求数据库同时拥有处理传统TP 场景的事务能力和强大的数据分析能力。这时就可以使用HStore 来处理这些场景了。 HStore 利用Delta表存储update/delete/insert等操作信息。之后依赖后台常驻autovacuum 来做MERGE操作将数据写入主表。 利用特有的 Delta 表，HStore 解决了传统列存表 CU 锁的问题，支持上游upsert/update 等操作实时并发入库。同时还能保证和普通列存表相近的数据分析与数据压缩能力。 HStore 表技术特点如下：完整的事务一致性：支持全面的事务能力，数据插入或者更新提交后即可见不存在时延，保证数据ACID一致性。 全面的功能支持：提供和当前列存一样全面的功能和语法支持。 查询性能好：适用多表关联等复杂AP查询场景，相对于传统行存表，拥有更完善的分布式查询计划与更先进的分布式执行器，性能优势明显。支持复杂的子查询和存储过程，支持主键等传统索引能力去重和加速点查，也支持分区、全局字典、局部排序等方式进一步加速AP查询。 入库快：彻底解决列存CU锁冲突问题，支持高并发的更新入库操作，典型场景下，并发更新性能是之前的百倍以上。 高压缩：数据在MERGE进入列存主表后，按列存储具有天然的压缩优势，能极大地节省磁盘空间与IO资源。 

行存储 传统OLTP（OnLine Transaction Processsing 联机事务处理）场景与功能、业务强相关，数据需要进行频繁的增删改查，这时比较适合使用行存储式。行存储的优势主要有两个方面：首先是点查性能好，在点查场景下可以直接索引到某行数据的元组位置；其次就是更新效率高，行存储在实时并发入库，并发更新方面依然有着比较大的优势。 列存储 传统行存储形式的数据库主要为业务服务，但是如果涉及到分析查询场景，特别是在数据量大且复杂的查询时，就会遇到性能瓶颈了，性能瓶颈是数据存储方式决定的。因此OLTP（OnLine Transaction Processsing 联机事务处理）场景一般会交给列存储引擎去做。列存储的优势主要有两方面：首先是批量查询性能好，当分析查询只涉及某列或者某几列，不需要访问无关列，特别是在表的宽度比较大时（如一千列），优势更加明显；其次就是列存储的压缩性能更高，原因就是因为数据按列存储，单列类型相同。 列存储引擎的最小存储单位是CU(Compression Unit, 压缩单元)：一个CU是由表中某一列的一部分数据组成的压缩数据块， 通过(cu_id,col_id)标识一个CU。另外，列存引擎通过Delta表，避免了小CU的产生，显著提升列存表单条导入的性能，同时解决由于小CU导致的数据膨胀问题。当单条或小批量数据导入到列存表时，需先存入Delta表，当Delta表中数据积攒到指定行数时再存入新产生的CU中。