GaussDB(DWS)运维管理系列基础篇二:集群GUC参数简单介绍及配置说明
前言
GaussDB(DWS)通过GUC(grand unified configuration)参数来控制数据库的部署形态和运行行为。参数种类繁多,从功能上大致可分为集群配置类参数,性能/资源控制类参数,功能开关参数和HA相关参数。通过了解这些参数的含义、使用场景及配置方法,可以快速理解和使用数据库。这里进行GUC参数的简单介绍和配置说明。
参数介绍
GUC参数根据参数类型可以分为6类:
参数类型 | 说明 | 设置方式 |
INTERNAL | 固定参数,在创建数据库的时候确定,用户无法修改,只能通过show语法或者pg_settings视图进行查看。 | 无 |
POSTMASTER | 数据库服务端参数,在数据库启动时确定,可以通过配置文件指定。 | 方式一 |
SIGHUP | 数据库全局参数,可在数据库启动时设置或者在数据库启动后,发送指令重新加载。 | 方式一、方式二。 |
BACKEND | 会话连接参数。在创建会话连接时指定,连接建立后无法修改。连接断掉后参数失效。内部使用参数,不推荐用户设置。 | 方式一、方式二。 |
SUSET | 数据库管理员参数。可在数据库启动时、数据库启动后或者数据库管理员通过SQL进行设置。 | 方式一、方式二或由数据库管理员通过方式三设置。 |
USERSET | 普通用户参数。可被任何用户在任何时刻设置。 | 方式一、方式二或方式三。 |
有三种设置方式:
方式一 | 1. 使用如下命令修改参数。 gs_guc set -Z nodetype -D datadir -c ""paraname=value"" 使用以下命令在CN和DN上同时设置某个参数。 gs_guc set -Z coordinator -Z datanode -N all -I all -c ""paraname=value"" 2. 重启数据库使参数生效。 重启集群操作会导致用户执行操作中断,请在操作之前规划好合适的执行窗口。 gs_om -t stop && gs_om -t start |
方式二 | gs_guc reload -Z nodetype -D datadir -c ""paraname=value"" 使用以下命令在CN和DN上同时设置某个参数。 gs_guc reload -Z coordinator -Z datanode -N all -I all -c ""paraname=value"" |
方式三 | 修改指定数据库,用户,会话级别的参数。 设置数据库级别的参数 postgres=# ALTER DATABASE dbname SET paraname TO value; 在下次会话中生效。 设置用户级别的参数 postgres=# ALTER USER username SET paraname TO value; 在下次会话中生效。 设置会话级别的参数 postgres=# SET paraname TO value; 修改本次会话中的取值。退出会话后,设置将失效。 |
GUC参数根据使用场景可以分为四类,集群配置类参数,性能/资源控制类参数,功能开关参数和HA相关参数
集群配置类参数:控制集群部署形态和拓扑结构,在集群初始化时自动设置,不建议用户更改。如:
路径类参数:audit_directory,data_directory,log_directory,stats_temp_directory,unix_socket_directory
IP类参数:listen_addresses,local_bind_address,gtm_host
端口类参数:port,comm_control_port,comm_sctp_port,pooler_port,gtm_port
性能/资源控制类参数:控制数据库运行过程中对系统资源的使用情况。在集群初始化时根据硬件规格自动设置。用户也可以根据业务场景自行调优。如:
内存相关参数:shared_buffers,cstore_buffers,max_process_memory,work_mem,cn_send_buffer_size,data_replicate_buffer_size
功能开关参数:控制数据库特性或者功能的开关,一般以enable或者disable开头的参数。用户可根据业务场景进行设置。如:
enable_alarm,audit_enabled,enable_resource_track,enable_hashjoin,enable_index_nestloop,disable_memory_protect
HA相关参数:DN高可用相关参数和超时类参数。不建议用户更改。如:
hot_standby,wal_level,replconninfo1,checkpoint_timeout,gtm_connect_timeout
参数配置
集群配置类参数和HA相关参数在集群初始化时自动根据集群拓扑配置,也不建议更改,此处不做说明。集群初始化时还额外设置如下参数:
<cn> <PARAM KEY='checkpoint_segments' VALUE='64' /> <PARAM KEY='comm_tcp_mode' VALUE='on' /> <PARAM KEY='comm_quota_size' VALUE='1024kB' /> <PARAM KEY='max_connections' VALUE='800' /> <PARAM KEY='max_active_statements' VALUE='60' /> <PARAM KEY='max_pool_size' VALUE='800' /> <PARAM KEY='max_prepared_transactions' VALUE='800' /> <PARAM KEY='work_mem' VALUE='64MB' /> <PARAM KEY='comm_max_datanode' VALUE='MAX_MASTER_DATANUM_ALLNODES' /> <PARAM KEY='max_process_memory' VALUE='PHYSIC_MEMORY*0.665/(N+MAX_MASTER_DATANUM_IN_ONENODE)' /> <PARAM KEY='shared_buffers' VALUE='512MB' /> <PARAM KEY='max_wal_senders' VALUE='100' /> <PARAM KEY='behavior_compat_options' VALUE=''check_function_conflicts'' /> <PARAM KEY='wal_compression' VALUE='on' /> </cn> <dn> <PARAM KEY='checkpoint_segments' VALUE='64' /> <PARAM KEY='comm_tcp_mode' VALUE='on' /> <PARAM KEY='comm_quota_size' VALUE='1024kB' /> <PARAM KEY='max_connections' VALUE='5000' /> <PARAM KEY='max_active_statements' VALUE='60' /> <PARAM KEY='max_pool_size' VALUE='800' /> <PARAM KEY='max_prepared_transactions' VALUE='800' /> <PARAM KEY='work_mem' VALUE='64MB' /> <PARAM KEY='comm_max_datanode' VALUE='MAX_MASTER_DATANUM_ALLNODES' /> <!-- Do not set max_process_memory in dummy datanodes --> <PARAM KEY='max_process_memory' VALUE='PHYSIC_MEMORY*0.665/(N+MAX_MASTER_DATANUM_IN_ONENODE)' /> <PARAM KEY='shared_buffers' VALUE='1GB' /> <PARAM KEY='behavior_compat_options' VALUE=''check_function_conflicts'' /> <PARAM KEY='wal_compression' VALUE='on' /> </dn>
DWS/HCS集群初始化时根据硬件规格和使用场景还会额外设置如下GUC参数,这里只列举几种常见规格的参数,全量规格详见附件。
CN | DN | |||
d2.12xlarge.8 | "autovacuum" | "off" | "autovacuum" | "off" |
"bgwriter_delay" | "10000ms" | "bgwriter_delay" | "10000ms" | |
"checkpoint_timeout" | "15min" | "checkpoint_timeout" | "15min" | |
"checkpoint_segments" | "128" | "checkpoint_segments" | "128" | |
"checkpoint_warning" | "5min" | "checkpoint_warning" | "5min" | |
"data_replicate_buffer_size" | "16MB" | "cstore_buffers" | "4GB" | |
"maintenance_work_mem" | "256MB" | "data_replicate_buffer_size" | "256MB" | |
"shared_buffers" | "512MB" | "maintenance_work_mem" | "256MB" | |
"wal_buffers" | "256MB" | "shared_buffers" | "2GB" | |
"wal_keep_segments" | "128" | "wal_buffers" | "256MB" | |
"wal_receiver_buffer_size" | "64MB" | "wal_keep_segments" | "128" | |
"work_mem" | "4GB" | "wal_receiver_buffer_size" | "64MB" | |
"comm_max_datanode" | "1024" | "work_mem" | "4GB" | |
"max_process_memory" | "27095MB" | "comm_max_datanode" | "1024" | |
"comm_usable_memory" | "4000MB" | "max_process_memory" | "27095MB" | |
"enable_nestloop" | "off" | "comm_usable_memory" | "4000MB" | |
"enable_mergejoin" | "off" | "enable_nestloop" | "off" | |
"standby_shared_buffers_fraction" | "0.3" | "enable_mergejoin" | "off" | |
"log_line_prefix" | "'%m %c %d %p %a %x %n %e '" | "standby_shared_buffers_fraction" | "0.3" | |
"explain_perf_mode" | "pretty" | "log_line_prefix" | "'%m %c %d %p %a %x %n %e '" | |
"comm_quota_size" | "2MB" | "explain_perf_mode" | "pretty" | |
"max_connections" | "2048" | "comm_quota_size" | "2MB" | |
"max_pool_size" | "2048" | "max_connections" | "2048" | |
"max_prepared_transactions" | "2048" | "max_pool_size" | "2048" | |
"comm_max_stream" | "2048" | "max_prepared_transactions" | "2048" | |
"max_coordinators" | "10" | "comm_max_stream" | "2048" | |
"max_active_statements" | "100" | "max_coordinators" | "10" | |
"enable_data_replicate" | "on" | "max_active_statements" | "100" | |
"comm_tcp_mode" | "on" | "enable_data_replicate" | "on" | |
"enable_dynamic_workload" | "on" | "comm_tcp_mode" | "on" | |
"max_locks_per_transaction" | "512" | "enable_dynamic_workload" | "on" | |
"max_locks_per_transaction" | "512" | |||
dws.ds.12xlarge | "autovacuum" | "off" | "autovacuum" | "off" |
"bgwriter_delay" | "10000ms" | "bgwriter_delay" | "10000ms" | |
"checkpoint_timeout" | "15min" | "checkpoint_timeout" | "15min" | |
"checkpoint_segments" | "128" | "checkpoint_segments" | "128" | |
"checkpoint_warning" | "5min" | "checkpoint_warning" | "5min" | |
"data_replicate_buffer_size" | "16MB" | "cstore_buffers" | "4GB" | |
"maintenance_work_mem" | "256MB" | "data_replicate_buffer_size" | "256MB" | |
"shared_buffers" | "512MB" | "maintenance_work_mem" | "256MB" | |
"wal_buffers" | "256MB" | "shared_buffers" | "2GB" | |
"wal_keep_segments" | "128" | "wal_buffers" | "256MB" | |
"wal_receiver_buffer_size" | "64MB" | "wal_keep_segments" | "128" | |
"work_mem" | "4GB" | "wal_receiver_buffer_size" | "64MB" | |
"query_dop" | "0" | "work_mem" | "4GB" | |
"comm_max_datanode" | "1024" | "query_dop" | "0" | |
"max_process_memory" | "51GB" | "comm_max_datanode" | "1024" | |
"comm_usable_memory" | "4000MB" | "max_process_memory" | "51GB" | |
"enable_nestloop" | "off" | "comm_usable_memory" | "4000MB" | |
"enable_mergejoin" | "off" | "enable_nestloop" | "off" | |
"standby_shared_buffers_fraction" | "0.3" | "enable_mergejoin" | "off" | |
"log_line_prefix" | "'%m %c %d %p %a %x %n %e '" | "standby_shared_buffers_fraction" | "0.3" | |
"explain_perf_mode" | "pretty" | "log_line_prefix" | "'%m %c %d %p %a %x %n %e '" | |
"comm_quota_size" | "2MB" | "explain_perf_mode" | "pretty" | |
"max_connections" | "2048" | "comm_quota_size" | "2MB" | |
"max_pool_size" | "2048" | "max_connections" | "2048" | |
"max_prepared_transactions" | "2048" | "max_pool_size" | "2048" | |
"comm_max_stream" | "2048" | "max_prepared_transactions" | "2048" | |
"max_coordinators" | "10" | "comm_max_stream" | "2048" | |
"max_active_statements" | "100" | "max_coordinators" | "10" | |
"enable_data_replicate" | "on" | "max_active_statements" | "100" | |
"comm_tcp_mode" | "on" | "enable_data_replicate" | "on" | |
"enable_dynamic_workload" | "on" | "comm_tcp_mode" | "on" | |
"max_locks_per_transaction" | "512" | "enable_dynamic_workload" | "on" | |
"max_locks_per_transaction" | "512" |
总结
GUC参数是DWS数据库的重要属性,控制了数据库的拓扑结构,功能开关,资源使用情况。可以通过合理配置GUC参数来提升数据库的性能,可用性,可靠性。这里简单介绍了GUC参数的分类和配置说明。用户可以通过产品文档来详细了解SUSET和USERSET类参数的含义和使用场景,根据业务场景和硬件情况,合理调配GUC参数也是一种常见有效的调优手段。
- 点赞
- 收藏
- 关注作者
评论(0)