教你识别一些sequence的相关问题
摘要:通过一些自定义的视图或者查询语句,批量识别集群的sequence相关问题本文分享自华为云社区《GaussDB(DWS)运维 -- sequence常见运维操作》,作者: 譡里个檔。
【DWS的序列概述】 【教你识别一些sequence的相关问题】GaussDB(DWS)是一个share nothing架构的产品,seqeunce编号需要通过GTM统一分配。集群规模越大、节点数越多、sequence调用越频繁,sequnce对GTM的压力就越大。对于seqeunce的使用一般有如下建议:
- 建议业务中不要大量使用sequence,建议不要超过50个
- 如果业务允许,建议使用uuid替换sequence
- 使用sequence的时候,需要定义cache值不小于100
CREATE OR REPLACE FUNCTION public.dfm_get_seqence_info ( OUT namespace text,-- 序列的schema OUT sequencename text, -- 序列名称 OUT min_value bigint,-- 序列最小值 OUT max_value bigint,-- 序列最大值 OUT start_value bigint,-- 起始值 OUT increment_by bigint,-- 增加步长 OUT cache_value bigint,-- cache值 OUT is_cycled boolean -- 是否可以循环使用 ) RETURNS SETOF record LANGUAGE plpgsql NOT FENCED NOT SHIPPABLE AS $function$ DECLARE rowObject record; rowDetail record; queryObjectStr text; queryDetailStr text; BEGIN --Get all the node names queryObjectStr := 'SELECT c.oid, relname, n.nspname FROM pg_class c INNER JOIN pg_namespace n on c.relnamespace = n.oid WHERE c.relkind = ''S'''; FOR rowObject IN EXECUTE(queryObjectStr) LOOP queryDetailStr := 'SELECT start_value, increment_by, max_value, min_value, cache_value, is_cycled FROM ' || quote_ident(rowObject.nspname) || '.' || quote_ident(rowObject.relname); FOR rowDetail IN EXECUTE(queryDetailStr) LOOP namespace = rowObject.nspname; sequencename = rowObject.relname; start_value = https://www.it610.com/article/rowDetail.start_value; increment_by = rowDetail.increment_by; max_value = rowDetail.max_value; min_value = rowDetail.min_value; cache_value = rowDetail.cache_value; is_cycled = rowDetail.is_cycled; RETURN next; END LOOP; END LOOP; RETURN; END; $function$ ; CREATE VIEW public.dfm_get_seqence_info AS SELECT * FROM public.dfm_get_seqence_info();
示例
postgres=# CREATE SEQUENCE public.test_seq; CREATE SEQUENCE Time: 13.917 ms postgres=# CREATE TABLE public.test_serial(a bigserial, b int) DISTRIBUTE BY ROUNDROBIN; NOTICE:CREATE TABLE will create implicit sequence "test_serial_a_seq" for serial column "test_serial.a" SQLSTATE: 00000 LOCATION:CreateSeqOwnedByTable, parse_utilcmd.cpp:144 CREATE TABLE Time: 16.572 ms postgres=# CREATE SEQUENCE public.test_seq_1; CREATE SEQUENCE Time: 13.412 ms postgres=# CREATE TABLE public.test_serial_default(a bigint default nextval('public.test_seq_1'::regclass), b int) DISTRIBUTE BY ROUNDROBIN; CREATE TABLE Time: 12.006 ms postgres=# SELECT * FROM public.dfm_get_seqence_info; namespace |sequencename| min_value |max_value| start_value | increment_by | cache_value | is_cycled -----------+-------------------+-----------+---------------------+-------------+--------------+-------------+----------- public| test_seq|1 | 9223372036854775807 |1 |1 |1 | f public| test_serial_a_seq |1 | 9223372036854775807 |1 |1 |1 | f public| test_seq_1|1 | 9223372036854775807 |1 |1 |1 | f (3 rows)
【序列和表的bind关系查询】 查询sequence和表的bind关系,主要场景有两个
- 表的字段类型为bigserial或者serial类型,内置生成一个序列
- 表的字段的默认值为序列的序列号
postgres=# SELECT postgres-#pg_get_userbyid(c1.relowner) AS seqowner,-- sequence的用户 postgres-#n1.nspname AS seqschema,-- sequence的schema postgres-#c1.relname AS seqname, -- -- sequence的名称 postgres-#(pg_sequence_parameters(c1.oid)).minimum_value AS minvalue, -- sequence的最小值 postgres-#(pg_sequence_parameters(c1.oid)).maximum_value AS maxvalue, -- sequence的最大值 postgres-#(pg_sequence_parameters(c1.oid)).increment AS incrementby, -- sequence的步长 postgres-#pg_get_userbyid(c.relowner) AS tableowner, -- 关联的表的owner postgres-#n.nspname AS tableschema,-- 关联的表的schema postgres-#c.relname AS tablename,-- 关联的表的名称 postgres-#a.attname, -- -- 关联的表的列的名称 postgres-#d.adsrc as defaultexpression -- 关联的sequence的表达式 postgres-# FROM pg_catalog.pg_class c postgres-# INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace postgres-# INNER JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid postgres-# INNER JOIN pg_catalog.pg_attrdef d ON d.adrelid = c.oid AND d.adnum = a.attnum postgres-# INNER JOIN pg_catalog.pg_depend p ON p.objid = d.oid postgres-# INNER JOIN pg_catalog.pg_class c1 ON c1.oid = p.refobjid postgres-# INNER JOIN pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace postgres-# WHERE c.relkind = 'r'::"char" AND c.oid > 16384 postgres-# AND n.nspname not in ('pg_toast', 'cstore', 'pg_catalog') postgres-# AND a.attnum > 0 postgres-# AND p.classid='pg_catalog.pg_attrdef'::regclass postgres-# AND refclassid = 'pg_catalog.pg_class'::regclass postgres-# AND p.refobjsubid = 0 postgres-# AND c1.relkind = 'S'::"char" postgres-# ; seqowner| seqschema |seqname| minvalue |maxvalue| incrementby | tableowner | tableschema |tablename| attname |defaultexpression -----------+-----------+-------------------+----------+---------------------+-------------+------------+-------------+---------------------+---------+---------------------------------------- j00565968 | public| test_serial_a_seq |1 | 9223372036854775807 |1 | j00565968| public| test_serial| a| nextval('test_serial_a_seq'::regclass) j00565968 | public| test_seq_1|1 | 9223372036854775807 |1 | j00565968| public| test_serial_default | a| nextval('test_seq_1'::regclass) (2 rows)
【替换方案】 实际业务中使用sequence一般有两个场景
- 保证表数据均匀分布。这种场景可以把sequence字段替换为uuid
postgres=# DROP TABLE public.test_serial; DROP TABLE Time: 19.871 ms postgres=# CREATE TABLE public.test_serial(a text DEFAULT sys_guid(), b int) DISTRIBUTE BY HASH(a); CREATE TABLE Time: 19.245 ms
或者使用roundrobin分布替换,然后删除sequence相关列
postgres=# ALTER TABLE public.test_serialDISTRIBUTE BY ROUNDROBIN; ALTER TABLE Time: 218.796 ms postgres=# ALTER TABLE public.test_serial DROP COLUMN a; ALTER TABLE Time: 9.509 ms
- 保证字段数据的唯一性。这种场景可以使用uuid()+主键的方式实现
postgres=# DROP TABLE public.test_serial; DROP TABLE Time: 18.786 ms postgres=# CREATE TABLE public.test_serial(a text DEFAULT sys_guid(), b int, primary key(a)) DISTRIBUTE BY HASH(a); NOTICE:CREATE TABLE / PRIMARY KEY will create implicit index "test_serial_pkey" for table "test_serial" CREATE TABLE
注:理论上一个集群内的uuid不会重复,但还是建议表上增加主键约束
点击关注,第一时间了解华为云新鲜技术~
推荐阅读
- Python|详细解读文字识别工具———Tesseract-OCR
- java|分享Java代码的一些小建议,脱离小白——学会优化代码50个方案
- diss验证码系列|《Diss验证码》——Python验证码破解(图像字符验证码识别(1-入门))
- 教你几招保存推特视频
- C/C++气象数据中心实战,手把手教你做工业级项目吾爱fen享
- 深度学习|Keras卷积神经网络识别CIFAR-10图像(1)
- 一文教你如何使用原生的Feign
- 前端|手把手教你撸一个网页聊天室
- NodeJS|教你撸一个网页聊天室
- 教你如何使用|教你如何使用 Python 向手机发送通知!