阿里云RDS for PostgreSQL用户如何定制数据库参数

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 背景 为了满足大多数用户的需求,阿里云提供的RDS PG,数据库参数是根据通用性场景设置的。 如果用户不是通用的场景,或者用户有自定义参数的需求怎么办呢? 首先数据库的参数是分级的,有些参数允许在高层设置,例如运行时的参数。 PostgreSQL参数分级 环境变量 配置文件(

背景

为了满足大多数用户的需求,阿里云提供的RDS PG,数据库参数是根据通用性场景设置的。

如果用户不是通用的场景,或者用户有自定义参数的需求怎么办呢?

由于数据库的参数是分级的,层级越高优先级越高,用户可以在高层级设置参数的值,以此来覆盖RDS PG设置的一些参数,达到修改参数值的目的。

PostgreSQL参数分级

  1. 环境变量
  2. 配置文件(postgresql.conf)
  3. postgres 命令行启动参数
  4. 用户、数据库级别参数(alter role set, alter database set 设置的参数)
  5. 会话级参数,影响当前会话
  6. 事务级参数,影响当前事务
  7. 函数级参数 (function option) (SET configuration_parameter { TO value | = value | FROM CURRENT })
  8. 函数内设置的参数 (函数内调用 set 设置的参数)

以上数字越大,优先级越高。

RDS PG用户如何定制参数

前面讲解了参数的分级,接下来教大家如何修改RDS PG的参数值。

为了解决用户自定义参数的问题,RDS SUPERUSER开放了修改用户与数据库级别参数的权限,从而来调整自己定制的参数。

例1
用户可以设置角色级别的 random_page_cost 参数,调整索引 page页的成本。

以rds_superuser角色登陆RDS PG数据库 , 然后执行  

alter role all set random_page_cost=1.3;  

如果反馈需要超级用户才能修改,可能是你的版本没有开放这个参数。 
那么就只能改用户自己的,如果有多个用户需要修改多次。  

connect to role1 
aler role role1 set random_page_cost=1.3;   

connect to role2 
aler role role2 set random_page_cost=1.3;   

这样设置后,新发起的用户会话,会使用这个新的参数值。

重新连接,即可享受 random_page_cost=1.3

例2
调整表级垃圾回收和计算统计信息的阈值(假设表被频繁的更新,插入,想快速的生成统计信息,可以把阈值调小).

以表的owner连接数据库  

alter table test set (autovacuum_analyze_scale_factor = 0.02);  
alter table test set (autovacuum_vacuum_scale_factor = 0.01);  

https://www.postgresql.org/docs/9.5/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS

关于垃圾回收,除了设置以上两个阈值,还有3个非常关键的参数

postgres=# show autovacuum_max_workers ;
 autovacuum_max_workers 
------------------------
 5
(1 row)
同时可以起多少个垃圾回收的进程
  
postgres=# show autovacuum_naptime ;
 autovacuum_naptime 
--------------------
 1min
(1 row)
autovacuum守护进程要做一件事情,轮询每个数据库,并查找出需要被autovacuum垃圾回收的表。  
这个参数决定了轮询完所有的数据库需要多久,也就是说,值越小,轮询越快。  
  
postgres=# show autovacuum_work_mem ;
 autovacuum_work_mem 
---------------------
 -1
(1 row)
每个垃圾回收的进程可以使用多少内存

这三个参数应该如何设置

postgres=# show autovacuum_max_workers ;
如果你的业务会涉及频繁的更新或删除非常多的表,在有足够的CPU核与IOPS能力的情况下这个值越大越好。  
  
postgres=# show autovacuum_naptime ;
如果你的业务,在非常多的数据库中,涉及到非常多的表的频繁更新或删除。  
在有足够的CPU核与IOPS能力的情况下这个值越大越好,这个值越小越好。  
  
postgres=# show autovacuum_work_mem ;
系统必须预留给autovacuum worker足够的内存,大小为 autovacuum_work_mem * autovacuum_work_mem 

如果垃圾回收进程影响了数据库业务的SQL怎么办?
PostgreSQL有简单的autovacuum调度,当垃圾回收消耗了一定的成本后,会SLEEP一段时间,给业务留出系统资源,涉及几个参数。

1. autovacuum_vacuum_cost_limit
当所有autovacuum worker进程的cost和超出这个COST,worker开始休息,COST计算方法由以下参数决定  
vacuum_cost_page_hit (integer)
The estimated cost for vacuuming a buffer found in the shared buffer cache. It represents the cost to lock the buffer pool, lookup the shared hash table and scan the content of the page. The default value is one.

vacuum_cost_page_miss (integer)
The estimated cost for vacuuming a buffer that has to be read from disk. This represents the effort to lock the buffer pool, lookup the shared hash table, read the desired block in from the disk and scan its content. The default value is 10.

vacuum_cost_page_dirty (integer)
The estimated cost charged when vacuum modifies a block that was previously clean. It represents the extra I/O required to flush the dirty block out to disk again. The default value is 20.

2. autovacuum_vacuum_cost_delay
休息多久

如果用户的SQL已经出现了倾斜,或者用户想绑定执行计划怎么办?
可以参考这篇帖子,使用plan hint插件来绑定执行计划
https://yq.aliyun.com/articles/17212

注意,RDS PG并不是开放所有的参数给用户设置,同时因受到PG内核的限制,有些参数只能启动时设置,例如共享内存段的大小,不过大多数和性能或者使用环境相关的参数,都是可以设置的,如果您有新的需求,也欢迎回复或提工单告知。

通过这个源码文件,可以了解参数的一些其他细节
src/include/utils/guc.h

参数允许在什么时候被设置

/*
 * Certain options can only be set at certain times. The rules are
 * like this:
 *
 * INTERNAL options cannot be set by the user at all, but only through
 * internal processes ("server_version" is an example).  These are GUC
 * variables only so they can be shown by SHOW, etc.
 *
 * POSTMASTER options can only be set when the postmaster starts,
 * either from the configuration file or the command line.
 *
 * SIGHUP options can only be set at postmaster startup or by changing
 * the configuration file and sending the HUP signal to the postmaster
 * or a backend process. (Notice that the signal receipt will not be
 * evaluated immediately. The postmaster and the backend check it at a
 * certain point in their main loop. It's safer to wait than to read a
 * file asynchronously.)
 *
 * BACKEND and SU_BACKEND options can only be set at postmaster startup,
 * from the configuration file, or by client request in the connection
 * startup packet (e.g., from libpq's PGOPTIONS variable).  SU_BACKEND
 * options can be set from the startup packet only when the user is a
 * superuser.  Furthermore, an already-started backend will ignore changes
 * to such an option in the configuration file.  The idea is that these
 * options are fixed for a given backend once it's started, but they can
 * vary across backends.
 *
 * SUSET options can be set at postmaster startup, with the SIGHUP
 * mechanism, or from the startup packet or SQL if you're a superuser.
 *
 * USERSET options can be set by anyone any time.
 */
typedef enum
{
        PGC_INTERNAL,  // 只能看的参数
        PGC_POSTMASTER,  // 只能在数据库启动是设置
        PGC_SIGHUP,  //  可以通过修改配置文件,然后给postmaster发SIGHUP信号更新参数值
        PGC_SU_BACKEND,  // 只能在数据库启动是设置,或者在客户端连接数据库时设置,通过libpq提供的PGOPTIONS接口。(只允许超级用户连接时设置)
        PGC_BACKEND,  //  同上,但是允许普通用户设置
        PGC_SUSET,   //  同上,同时允许在会话中设置,必须是超级用户
        PGC_USERSET  // 同上,同时允许在会话中设置,允许普通用户设置
} GucContext;

参数或变量的作用域

/*
 * The following type records the source of the current setting.  A
 * new setting can only take effect if the previous setting had the
 * same or lower level.  (E.g, changing the config file doesn't
 * override the postmaster command line.)  Tracking the source allows us
 * to process sources in any convenient order without affecting results.
 * Sources <= PGC_S_OVERRIDE will set the default used by RESET, as well
 * as the current value.  Note that source == PGC_S_OVERRIDE should be
 * used when setting a PGC_INTERNAL option.
 *
 * PGC_S_INTERACTIVE isn't actually a source value, but is the
 * dividing line between "interactive" and "non-interactive" sources for
 * error reporting purposes.
 *
 * PGC_S_TEST is used when testing values to be used later ("doit" will always
 * be false, so this never gets stored as the actual source of any value).
 * For example, ALTER DATABASE/ROLE tests proposed per-database or per-user
 * defaults this way, and CREATE FUNCTION tests proposed function SET clauses
 * this way.  This is an interactive case, but it needs its own source value
 * because some assign hooks need to make different validity checks in this
 * case.  In particular, references to nonexistent database objects generally
 * shouldn't throw hard errors in this case, at most NOTICEs, since the
 * objects might exist by the time the setting is used for real.
 *
 * NB: see GucSource_Names in guc.c if you change this.
 */
typedef enum
{
        PGC_S_DEFAULT,                          /* hard-wired default ("boot_val") */
        PGC_S_DYNAMIC_DEFAULT,          /* default computed during initialization */
        PGC_S_ENV_VAR,                          /* postmaster environment variable */
        PGC_S_FILE,                                     /* postgresql.conf */
        PGC_S_ARGV,                                     /* postmaster command line */
        PGC_S_GLOBAL,                           /* global in-database setting */
        PGC_S_DATABASE,                         /* per-database setting */
        PGC_S_USER,                                     /* per-user setting */
        PGC_S_DATABASE_USER,            /* per-user-and-database setting */
        PGC_S_CLIENT,                           /* from client connection request */
        PGC_S_OVERRIDE,                         /* special case to forcibly set default */
        PGC_S_INTERACTIVE,                      /* dividing line for error reporting */
        PGC_S_TEST,                                     /* test per-database or per-user setting */
        PGC_S_SESSION                           /* SET command */
} GucSource;

祝大家玩得开心,欢迎随时来 阿里云促膝长谈 业务需求 ,恭候光临。

阿里云的小伙伴们加油,努力做 最贴地气的云数据库

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
16小时前
|
SQL 关系型数据库 MySQL
使用 C++ 结合 MySQL 数据库实现留言板
使用 C++ 结合 MySQL 数据库实现留言板
11 0
|
1天前
|
关系型数据库 MySQL API
实时计算 Flink版操作报错合集之同步MySQL数据到另一个MySQL数据库,第一次同步后源表数据发生变化时目标表没有相应更新,且Web UI中看不到运行的任务,该怎么解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
1天前
|
存储 弹性计算 关系型数据库
云服务器 ECS产品使用问题之安装MySQL数据库间断性无法连接,提示“数据库链接被拒绝”或“数据库链接丢失”的问题,该怎么解决
云服务器ECS(Elastic Compute Service)是各大云服务商阿里云提供的一种基础云计算服务,它允许用户租用云端计算资源来部署和运行各种应用程序。以下是一个关于如何使用ECS产品的综合指南。
|
2天前
|
SQL 存储 关系型数据库
关系型数据库中的PostgreSQL
【6月更文挑战第11天】
33 3
|
2天前
|
存储 关系型数据库 MySQL
关系型数据库中的MySQL
【6月更文挑战第11天】
55 4
|
3天前
|
SQL 存储 关系型数据库
深入理解MySQL:数据库管理与性能优化
第一章:MySQL基础 MySQL概述:简要介绍MySQL的历史、特点和应用领域
|
3天前
|
SQL 关系型数据库 MySQL
精通MySQL:从数据库管理到性能优化
第一章:MySQL入门 MySQL简介:了解MySQL的起源、发展历程以及在Web开发中的重要性
|
3天前
|
监控 安全 关系型数据库
精通MySQL:数据库核心技术与应用实践
h3> 一、引言 MySQL作为开源关系型数据库管理系统的佼佼者,凭借其出色的性能、灵活性和稳定性,成为许多企业和开发者的首选
|
3天前
|
关系型数据库 MySQL 数据库
精通MySQL:数据库管理、性能优化与最佳实践
h3> 一、引言 MySQL是一个功能强大的开源关系型数据库管理系统,广泛应用于各种Web应用、企业级应用和数据分析等领域
|
4天前
|
存储 关系型数据库 MySQL
MySQL周内训参照1、ER实体关系图与数据库模型图绘制
MySQL周内训参照1、ER实体关系图与数据库模型图绘制
18 1

相关产品

  • 云数据库 RDS MySQL 版
  • 云原生数据库 PolarDB