华为云用户手册

  • 审视和修改表定义概述 在分布式框架下,数据分布在各个DN上。一个或者几个DN的数据存在一块物理存储设备上,好的表定义至少需要达到以下几个目标: 表数据均匀分布在各个DN上,以防止单个DN对应的存储设备空间不足造成集群有效容量下降。选择合适的分布列,避免数据分布倾斜可以实现该点。 表Scan压力均匀分散在各个DN上,以避免单DN的Scan压力过大,形成Scan的单节点瓶颈。分布列不选择基表上等值filter中的列可以实现该点。 减少扫描数据量。通过分区的剪枝机制可以实现该点。 减少随机I/O。通过聚簇可以实现该点。 避免数据shuffle,减小网络压力。通过选择join-condition或者group by列为分布列可以最大程度实现这点。 从上述描述来看表定义中最重要的一点是分布列的选择。创建表定义一般遵循图1所示流程。表定义在数据库设计阶段创建,在SQL调优过程中进行审视和修改。 图1 表定义流程 父主题: 审视和修改表定义
  • 示例 删除函数可省略参数列表 --创建函数。 gaussdb=# CREATE FUNCTION func_test(varchar) RETURN VARCHAR AS BEGIN RETURN $1||_'test'; END; / --删除函数。 gaussdb=# DROP FUNCTION func_test; 删除存在同名的函数 如果存在同名函数,删除时需加上参数列表,否则报错。 --创建函数。 gaussdb=# CREATE FUNCTION func_add(int) RETURNS int AS $$ BEGIN RETURN $1+10; END; $$ LANGUAGE PLPGSQL; --重载函数func_add。 gaussdb=# CREATE FUNCTION func_add(int,int) RETURNS int AS $$ BEGIN RETURN $1+$2; END; $$ LANGUAGE PLPGSQL; --删除函数。 gaussdb=# DROP FUNCTION func_add(int); gaussdb=# DROP FUNCTION func_add(int,int);
  • 参数说明 IF EXISTS IF EXISTS表示如果函数存在则执行删除操作,函数不存在也不会报错,只是发出一个notice。 function_name 要删除的函数名称。 取值范围:已存在的函数名。 argmode 函数参数的模式。 argname 函数参数的名称。 argtype 函数参数的类型 CASCADE | RESTRICT CASCADE:级联删除依赖于函数的对象 。 RESTRICT:如果有任何依赖对象存在,则拒绝删除该函数(缺省行为)。
  • commit_delay 参数说明:表示一个已经提交的数据在WAL缓冲区中存放的时间。 该参数属于USERSET类型参数,请参考表1中对应设置方法进行设置。 设置为非 0 值时事务执行commit后不会立即写入WAL中,而仍存放在WAL缓冲区中,等待WalWriter线程周期性写入磁盘。 如果系统负载很高,在延迟时间内,其他事务可能已经准备好提交。但如果没有事务准备提交,这个延迟就是在浪费时间。 取值范围:整型, 0~100000(微秒),其中0表示无延迟。 默认值:0
  • wal_file_preinit_bounds 参数说明:设置业务运行时,WAL writer辅助线程每秒可预扩的WAL日志段文件的数量限制(WAL日志段文件的大小为16MB)。当该参数值为0时,表示不进行限制。 参数类型:整型 参数单位:无 取值范围:0~1024 默认值:0 设置方式:该参数属于SIGHUP类型参数,请参考表2中对应设置方法进行设置。 设置建议:设置为默认值,不做调整。当把该参数设置为有效值时,需基于磁盘IO能力进行设置,可参考参数max_io_capacity的数值进行设置: 若max_io_capacity为500MB,可设置为25; 若max_io_capacity为1GB,可设置为50。
  • wal_flush_timeout 参数说明:遍历WalInsertStatusEntryTbl的超时时间。xLog刷盘自适应控制的刷盘IO遍历WalInsertStatusEntryTbl等待的最大时间。 该参数属于SIGHUP类型参数,请参考表2中对应设置方法进行设置。 如果时间过长可能造成xLog刷盘频率降低,降低xLog处理性能。 取值范围:整型, 0 ~ 90000000(微秒) 默认值:2us
  • fsync 参数说明:设置 GaussDB 服务器是否使用fsync()系统函数(请参见wal_sync_method)确保数据的更新及时写入物理磁盘中。 该参数属于SIGHUP类型参数,请参考表1中对应设置方法进行设置。 使用fsync()系统函数可以保证在操作系统或者硬件崩溃的情况下将数据恢复到一个已知的状态。 如果将此参数关闭,可能会在系统崩溃时无法恢复原来的数据,导致数据库不可用。 取值范围:布尔型 on表示使用fsync()系统函数。 off表示不使用fsync()系统函数。 默认值:on
  • wal_level 参数说明:设置写入WAL信息量的级别,不能为空或被注释掉。 如果需要启用WAL日志归档和主备机的数据流复制,必须将此参数设置为archive、hot_standby或者logical。 如果该参数设置为archive或minimal,则hot_standby参数必须设置为off。在分布式环境中,因为会对双机其他一些功能产生影响,hot_standby参数不能设置成off。因此不建议该参数设置为archive或minimal,否则数据库将无法启动。 参数类型:枚举类型 取值范围: minimal 优点:一些重要操作(包括创建表、创建索引、簇操作和表的复制)都能安全的跳过,这样就可以使操作变得更快。 缺点:WAL仅提供从数据库服务器崩溃或者紧急关闭状态恢复时所需要的基本信息,无法用WAL归档日志恢复数据。 archive 这个参数增加了WAL归档需要的日志信息,从而可以支持数据库的归档恢复。 hot_standby 这个参数进一步增加了在备机上运行的SQL查询的信息,这个参数只能在数据库服务重新启动后生效。 为了在备机上开启只读查询,wal_level必须在主机上设置成hot_standby,并且备机必须打开hot_standby参数。hot_standby和archive级别之间的性能只有微小的差异,如果它们的设置对产品的性能影响有明显差异,欢迎反馈。 logical 设置为logical后才可以进行逻辑日志解析,设置后xLog日志中会额外记录主键信息。 默认值:hot_standby 设置方式:该参数属于POSTMASTER类型参数,请参考表1中对应设置方法进行设置。 设置建议:开启逻辑复制功能时设置为logical,其他场景设置为hot_standby。
  • wal_sync_method 参数说明:设置向磁盘强制更新WAL数据的方法。 该参数属于SIGHUP类型参数,请参考表1中对应设置方法进行设置。 如果将fsync关闭,这个参数的设置就没有意义,因为所有数据更新都不会强制写入磁盘。 取值范围:枚举类型 open_datasync表示用带O_DSYNC选项的open()打开“WAL”文件。 fdatasync表示每次提交的时候都调用fdatasync()(支持suse10和suse11)。 fsync_writethrough表示每次提交的时候调用fsync()强制把缓冲区任何数据写入磁盘。 由于历史原因,Windows平台支持将wal_sync_method设置为fsync_writethrough。在windows平台上fsync_writethrough和fsync等效。 fsync表示每次提交的时候调用fsync()(支持suse10和suse11)。 open_sync表示用带O_SYNC选项的open()写“WAL”文件(支持suse10和suse11)。 不是所有的平台都支持以上参数。 默认值:fdatasync
  • full_page_writes 参数说明:设置GaussDB服务器在检查点之后对页面的第一次修改时,是否将每个磁盘页面的全部内容写到WAL日志中。 该参数属于SIGHUP类型参数,请参考表1中对应设置方法进行设置。 设置这个参数是因为在操作系统崩溃过程中可能磁盘页面只写入了一部分内容,从而导致在同一个页面中包含新旧数据的混合。在崩溃后的恢复期间,由于在WAL日志中存储的行变化信息不够完整,因此无法完全恢复该页。把完整的页面影像保存下来就可以保证页面被正确还原,代价是增加了写入WAL日志的数据量。 关闭此参数,在系统崩溃的时候,可能无法恢复原来的数据。如果服务器硬件的特质(比如电池供电的磁盘控制器)可以减小部分页面的写入风险,或者文件系统特性支持(比如ReiserFS 4),并且清楚知道写入风险在一个可以接受的范畴,可以关闭这个参数。 取值范围:布尔型 on表示启用此特性。 off表示关闭此特性。 默认值:on
  • log_rotation_age 参数说明:logging_collector设置为on时,log_rotation_age决定创建一个新日志文件的时间间隔。当现在的时间减去上次创建一个服务器日志的时间超过了log_rotation_age的值时,将生成一个新的日志文件。 该参数属于SIGHUP类型参数,请参考表1中对应设置方法进行设置。 取值范围:整型,0 ~ 35791394,单位为min。其中0表示关闭基于时间的新日志文件的创建。 默认值:1d(即1440min)
  • log_truncate_on_rotation 参数说明:logging_collector设置为on时,log_truncate_on_rotation设置日志消息的写入方式。 该参数属于SIGHUP类型参数,请参考表1中对应设置方法进行设置。 示例如下: 假设日志需要保留7天,每天生成一个日志文件,日志文件名设置为server_log.Mon、server_log.Tue等。第二周的周二生成的日志消息会覆盖写入到server_log.Tue。设置方法:将log_filename设置为server_log.%a ,log_truncate_on_rotation设置为on,log_rotation_age设置为1440,即日志有效时间为1天。 取值范围: 布尔型 on表示GaussDB以覆盖写入的方式写服务器日志消息。 off表示GaussDB将日志消息附加到同名的现有日志文件上。 默认值:off
  • log_rotation_size 参数说明:logging_collector设置为on时,log_rotation_size决定服务器日志文件的最大容量。当日志消息的总量超过日志文件容量时,服务器将生成一个新的日志文件。 该参数属于SIGHUP类型参数,请参考表1中对应设置方法进行设置。 取值范围:整型,0 ~ 2097151,单位为KB。 0表示关闭基于容量的新日志文件的创建。 默认值:20MB
  • log_filename 参数说明:logging_collector设置为on时,log_filename决定服务器运行日志文件的名称。通常日志文件名是按照strftime模式生成,因此可以用系统时间定义日志文件名,用%转义字符实现,仅sysadmin用户可以访问。 该参数属于SIGHUP类型参数,请参考表1中对应设置方法进行设置。 建议使用%转义字符定义日志文件名称,否则难以对日志文件进行有效的管理。 当log_destination设为csvlog时,系统会生成附加了时间戳的日志文件名,文件格式为csv格式,例如“server_log.1093827753.csv”。 取值范围:字符串 默认值:gaussdb-%Y-%m-%d_%H%M%S.log
  • log_file_mode 参数说明:logging_collector设置为on时,log_file_mode设置服务器日志文件的权限。在Windows系统下,此选项无效。通常log_file_mode的取值是能够被chmod和umask系统调用接受的数字。 该参数属于SIGHUP类型参数,请参考表1中对应设置方法进行设置。 使用此选项前请设置log_directory,将日志存储到数据目录之外的地方。 因日志文件可能含有敏感数据,故不能将其设为对外可读。 取值范围:整型,0000~0777 (8进制计数,转化为十进制 0 ~ 511)。 0600表示只允许服务器管理员读写日志文件。 0640表示允许管理员所在用户组成员只能读日志文件。 默认值:0600
  • logging_collector 参数说明:控制开启后端日志收集线程logger进行日志收集。该线程捕获发送到stderr或csvlog的日志消息并写入日志文件。 这种记录日志的方法比将日志记录到syslog更加有效,因为某些类型的消息在syslog的输出中无法显示。例如动态链接库加载失败消息和脚本产生的错误消息。 该参数属于POSTMASTER类型参数,请参考表1中对应设置方法进行设置。 将服务器日志发送到stderr时可以不使用logging_collector参数,此时日志消息会被发送到服务器的stderr指向的空间。这种方法的缺点是日志回滚困难,只适用于较小的日志容量。 取值范围:布尔型 on表示开启日志收集功能。 off表示关闭日志收集功能。 默认值:on
  • log_directory 参数说明:logging_collector设置为on时,log_directory决定存放服务器日志文件的目录。它可以是绝对路径,或者是相对路径(相对于数据目录的路径)。log_directory支持动态修改,可以通过gs_guc reload实现,仅sysadmin用户可以访问。 该参数属于SIGHUP类型参数,请参考表1中对应设置方法进行设置。 当配置文件中log_directory的值为非法路径时,会导致集群无法重新启动。 通过gs_guc reload动态修改log_directory时,当指定路径为合法路径时,日志输出到新的路径下。当指定路径为非法路径时,日志输出到上一次合法的日志输出路径下而不影响数据库正常运行。此时即使指定的log_directory的值非法,也会写入到配置文件中。 在沙箱环境,路径中不可以包含/var/chroot,例如log的绝对路径是/var/chroot/var/lib/log/Ruby/gs_log/cn_log,则只需要设置为/var/lib/log/Ruby/gs_log/cn_log。 合法路径:用户对此路径有读写权限。 非法路径:用户对此路径无读写权限。 取值范围:字符串 默认值:安装时指定
  • 示例 --设置时区为意大利。 gaussdb=# SET timezone TO 'Europe/Rome'; --查看当前时区。 gaussdb=# SHOW timezone; TimeZone ------------- Europe/Rome (1 row) --将时区设置为缺省值。 gaussdb=# RESET timezone; --查看当前时区。 gaussdb=# SHOW timezone; TimeZone ---------- PRC (1 row) --如上SQL等效于如下两条SQL,将时区设置为缺省值。 gaussdb=# SET timezone TO DEFAULT; gaussdb=# ALTER SESSION SET timezone to DEFAULT;
  • 检查隐式转换的性能问题 在某些场景下,数据类型的隐式转换可能会导致潜在的性能问题。请看如下场景: SET enable_fast_query_shipping = off; CREATE TABLE t1(c1 VARCHAR, c2 VARCHAR); CREATE INDEX on t1(c1); EXPLAIN verbose SELECT * FROM t1 WHERE c1 = 10; 上述查询的执行计划如下: c1的数据类型是varchar,当查询的过滤条件为c1 = 10时,优化器默认将c1隐式转换为bigint类型,导致两个后果: 不能进行DN裁剪,计划下发到所有DN上执行。 计划中不能使用Index Scan方式扫描数据。 这会引起潜在的性能问题。 当知道了问题原因后,可以做针对性的SQL改写。对于上述场景,只要将过滤条件中的常量显式转换为varchar类型,结果如下: EXPLAIN verbose SELECT * FROM t1 WHERE c1 = 10::varchar; 为了提前识别隐式类型转换可能带来的性能影响,GaussDB提供了一个guc option:check_implicit_conversions。打开该参数后,对于查询中出现的隐式类型转换的索引列,在路径生成阶段进行检查,如果发现索引列没有生成候选的索引扫描路径,则会通过报错的形式提示给用户。举例如下: SET check_implicit_conversions = on; SELECT * FROM t1 WHERE c1 = 10; ERROR: There is no optional index path for index column: "t1"."c1". 参数check_implicit_conversions只用于检查隐式类型转换引起的潜在性能问题,在正式生产环境中请关闭该参数(该参数默认关闭)。 在将check_implicit_conversions打开时,必须同时关闭enable_fast_query_shipping参数,否则由于后一个参数的作用,无法查看对隐式类型转换修复的结果。 一个表的候选路径可能包括seq scan和index scan等多个可能的数据扫描方式,最终执行计划使用的表扫描方式是由执行计划的代价来决定的,因此即使生成了索引扫描的候选路径,也可能生成的最终执行计划中使用其它扫描方式。 父主题: SQL调优指南
  • 参数说明 cursor_name 将要创建的游标名。 取值范围:遵循数据库对象命名规范。 BINARY 指明游标以二进制而不是文本格式返回数据。 NO SCROLL 声明游标检索数据行的方式。 NO SCROLL:声明该游标不能用于以倒序的方式检索数据行。 未声明:根据执行计划的不同,自动判断该游标是否可以用于以倒序的方式检索数据行。 WITH HOLD | WITHOUT HOLD 声明当创建游标的事务结束后,游标是否能继续使用。 WITH HOLD:声明该游标在创建它的事务结束后仍可继续使用。 WITHOUT HOLD:声明该游标在创建它的事务之外不能再继续使用,此游标将在事务结束时被自动关闭。 如果不指定WITH HOLD或WITHOUT HOLD,默认行为是WITHOUT HOLD。 跨节点事务不支持WITH HOLD(例如在多Coordinator部署集群中所创建的含有DDL的事务属于跨节点事务)。 query 使用SELECT或VALUES子句指定游标返回的行。 取值范围:SELECT或VALUES子句。
  • 示例 示例1: --建表并插入数据。 gaussdb=# CREATE TABLE tbl_test(c1 int); gaussdb=# INSERT INTO tbl_test VALUES (generate_series(1,20)); --建立一个名为cursor1的游标。 gaussdb=# BEGIN; gaussdb=# CURSOR cursor1 FOR SELECT * FROM tbl_test ORDER BY 1; --使用FETCH命令检索3行数据。 gaussdb=# FETCH FORWARD 3 FROM cursor1; c1 ---- 1 2 3 (3 rows) --使用MOVE命令使游标向后移动2行,不返回结果。 gaussdb=# MOVE FORWARD 2 FROM cursor1; MOVE 2 --使用FETCH命令检索两行数据。 gaussdb=# FETCH FORWARD 2 FROM cursor1; c1 ---- 6 7 (2 rows) --关闭游标并结束事务。 gaussdb=# CLOSE cursor1; gaussdb=# END; 示例2:带WITH HOLD属性的游标: --建立一个名为cursor2的WITH HOLD游标。 gaussdb=# BEGIN; gaussdb=# CURSOR cursor2 WITH HOLD FOR SELECT * FROM tbl_test ORDER BY 1; --使用FETCH命令检索3行数据。 gaussdb=# FETCH FORWARD 3 FROM cursor2; c1 ---- 1 2 3 (3 rows) --和普通的游标不一样,结束事务后,带WITH HOLD属性的游标依然可以使用。 gaussdb=# END; gaussdb=# FETCH FORWARD 2 FROM cursor2; c1 ---- 4 5 (2 rows) --关闭游标。 gaussdb=# CLOSE cursor2; --删除。 gaussdb=# DROP TABLE tbl_test;
  • 注意事项 游标命令只能在事务块里使用。 通常游标和SELECT一样返回文本格式。因为数据在系统内部是用二进制格式存储的,系统必须对数据做一定转换以生成文本格式。一旦数据是以文本形式返回,客户端应用需要把它们转换成二进制进行操作。使用FETCH语句,游标可以返回文本或二进制格式。 应该小心使用二进制游标。文本格式一般都比对应的二进制格式占用的存储空间大。二进制游标返回内部二进制形态的数据,可能更易于操作。如果想以文本方式显示数据,则以文本方式检索会为用户节约很多客户端的工作。比如,如果查询从某个整数列返回1,在缺省的游标里将获得一个字符串1,但在二进制游标里将得到一个4字节的包含该数值内部形式的数值(大端顺序)。 静态游标支持定义参数,参数支持默认值。 游标的定义会产生查询对象,存储过程暂不支持建立对查询对象的依赖关系。
  • 参数说明 IF EXISTS 如果指定的模式不存在,发出一个notice而不是抛出一个错误。 schema_name 模式的名称。 取值范围:已存在模式名。 CASCADE | RESTRICT CASCADE:自动删除包含在模式中的对象。 RESTRICT:如果模式包含任何对象,则删除失败(缺省行为)。 不要随意删除pg_temp或pg_toast_temp开头的模式,这些模式是系统内部使用的,如果删除,可能导致无法预知的结果。 无法删除当前模式。如果要删除当前模式,须切换到其他模式下。
  • backtrace_min_messages 参数说明:控制当产生该设置参数级别相等或更高级别的信息时,会打印函数的堆栈信息到服务器日志文件中。 该参数属于SUSET类型参数,请参考表1中对应设置方法进行设置。 该参数作为客户现场问题定位手段使用,且由于频繁的打印函数栈会对系统的开销及稳定性有一定的影响,因此如果需要进行问题定位时,建议避免将backtrace_min_messages的值设置为fatal及panic以外的级别。 取值范围:枚举类型 有效值有debug、debug5、debug4、debug3、debug2、debug1、info、log、notice、warning、error、fatal、panic。参数的详细信息请参见表1。 默认值:panic
  • log_min_duration_statement 参数说明:该参数用于控制记录每条完成语句的持续时间。当某条语句的持续时间大于或者等于特定的毫秒数时,这条语句将会被记录。 设置log_min_duration_statement可以很方便地跟踪需要优化的查询语句。对于使用扩展查询协议的客户端,语法分析、绑定、执行每一步所花的时间将被独立记录。 参数类型:整型 参数单位:毫秒(ms) 当此选项与log_statement同时使用时,已经被log_statement记录的语句文本不会被重复记录。在没有使用syslog情况下,推荐使用16.3.9.3-log_line_prefix记录PID或会话ID,方便将当前语句消息连接到最后的持续时间消息。 取值范围:-1~2147483647‬ 设置为250,所有运行时间不短于250ms的SQL语句都会被记录。 设置为0,输出所有语句的持续时间。 设置为-1,关闭此功能。 默认值:3s(即3000ms) 设置方式:该参数属于SUSET类型参数,请参见表1中对应设置方法进行设置。 设置建议:设置为默认值。
  • log_min_error_statement 参数说明:控制在服务器日志中记录错误的SQL语句。 该参数属于SUSET类型参数,请参考表1中对应设置方法进行设置。 取值范围:枚举类型,有效值有debug、debug5、debug4、debug3、debug2、debug1、info、log、notice、warning、error、fatal、panic。参数的详细信息请参见表1。 设置为error ,表示导致错误、日志消息、致命错误、panic的语句都将被记录。 设置为panic,表示关闭此特性。 默认值:error
  • log_min_messages 参数说明:控制写到服务器日志文件夹下CN、DN日志文件中的消息级别。每个级别都包含排在它后面的所有级别中的信息。级别越低,服务器运行日志中记录的消息就越少。 当client_min_messages和log_min_messages取相同值log时所代表的消息级别不同。 参数类型:枚举类型 参数单位:无 取值范围:有效值有debug、debug5、debug4、debug3、debug2、debug1、info、log、notice、warning、error、fatal、panic,其中debug和debug2等效。参数的详细信息请参见表1。 默认值:warning 设置方式:该参数属于SUSET类型参数,请参考表1中对应设置方法进行设置。
  • 优化说明 测试发现由于两表结果集过大,导致nestloop耗时过长,超过一小时未返回结果,因此性能优化的关键是消除nestloop,让join使用更高效的hashjoin来连接。从语义等价的角度消除any-clause,SQL改写如下: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 select ls_pid_cusr1,COALESCE(max(round(ym/365)),0) from ( ( SELECT ls_pid_cusr1,(current_date-bthdate) as ym FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2 WHERE t1.ls_pid_cusr1 = t2.id and t1.ls_pid_cusr1 != t2.id15 ) union all ( SELECT ls_pid_cusr1,(current_date-bthdate) as ym FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2 WHERE t1.ls_pid_cusr1 = id15 ) ) GROUP BY ls_pid_cusr1; 优化后的SQL查询由两个等值join的子查询构成,而每个子查询都可以使用更适合此场景的hashjoin。优化后的执行计划如下: 优化后,从超过1个小时未返回结果优化到7s返回结果。
  • enable_ai_watchdog_forcible_oom_detection 参数说明:强制开启或关闭AI Watchdog的OOM探测功能,若关闭该参数,则会自动根据当前数据库的规格判断是否需要启动OOM探测功能。自动判断模式下,对于max_process_memory设置为64GB及以上的场景,才会启动OOM探测功能。由于OOM探测功能依赖内存管理模块获取的信息,因此,如果内存管理模块未开启或失效,则OOM探测功能也不会启用。 参数类型:布尔型 参数单位:无 取值范围: on:表示开启。 off:表示关闭。 默认值:off 设置方式:该参数属于SIGHUP类型参数,请参考表2中对应设置方法进行设置。 设置建议:建议设置为默认值。如开启可能会对系统性能产生影响。
  • enable_operator_prefer 参数说明:该参数用于指定是否开启算子倾向性规则,在估计代价相近的情况下,倾向于选择参数化路径执行表连接。 该参数属于USERSET类型参数,请参考表2中对应设置方法进行设置。 取值范围:布尔型 on表示开启参数化路径优先。 off表示不开启参数化路径优先。 默认值:off 此参数生效有两个必要的前置条件: 参数化路径被生成。 参数化路估计的代价和其他索引扫描算子类似。
共100000条