EDB PPAS (PostgreSQL plus advanced server) 10 参数模板 - 珍藏级
背景
系统参数模板
《PostgreSQL on Linux 最佳部署手册 - 珍藏级》
PostgreSQL 10 参数模板
PPAS 10 参数模板
除以下参数模板以外，其他参数参考 《PostgreSQL 10 参数模板》
1、通用部分参数
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Kernel Resource Usage -
shared_preload_libraries = 'auto_explain,index_advisor,pg_stat_statements,$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq'
# (change requires restart)
# - InfiniteCache
# 如果没有ICACHE的话，可以不配
# 将数据缓存在k-v(memcache)中，通常解决本地SHARED BUFFER不足的问题
#edb_enable_icache = off
#edb_icache_servers = '' #'host1:port1,host2,ip3:port3,ip4'
#edb_icache_compression_level = 6
# - EDB Resource Manager -
# [《PostgreSQL 商用版本EPAS(阿里云ppas(Oracle 兼容版)) HTAP功能之资源隔离管理 - CPU与刷脏资源组管理》](201801/20180113_01.md)
edb_max_resource_groups = 16 # 0-65536 (change requires restart)
#edb_resource_group = ''
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
# 是否允许plan hint
#enable_hints = on # enable optimizer hints in SQL statements.
# [《PostgreSQL 商用版本EPAS(阿里云ppas(Oracle 兼容版)) - 分区表性能优化 (堪比pg_pathman)》](201801/20180122_03.md)
#edb_enable_pruning = on # fast pruning for EDB-partitioned tables
# - Other Planner Options -
[《PostgreSQL 商用版本EPAS(阿里云ppas(Oracle 兼容版)) 自定义custom plan次数》](201801/20180118_04.md)
#edb_custom_plan_tries = 5 # 0 disable custom plan evaluation
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Process Title -
#utl_http.debug = off # trace network conversations
#---------------------------------------------------------------------------
# EDB AUDIT
#---------------------------------------------------------------------------
#edb_audit = 'none' # none, csv or xml
# These are only used if edb_audit is not none:
#edb_audit_directory = 'edb_audit' # Directory where log files are written
# Can be absolute or relative to PGDATA
#edb_audit_filename = 'audit-%Y-%m-%d_%H%M%S' # Audit file name pattern.
# Can include strftime() escapes
#edb_audit_rotation_day = 'every' # Automatic rotation of logfiles based
# on day of week. none, every, sun,
# mon, tue, wed, thu, fri, sat
#edb_audit_rotation_size = 0 # Automatic rotation of logfiles will
# happen after this many megabytes (MB)
# of log output. 0 to disable.
#edb_audit_rotation_seconds = 0 # Automatic log file rotation will
# happen after this many seconds.
#edb_audit_connect = 'failed' # none, failed, all
#edb_audit_disconnect = 'none' # none, all
#edb_audit_statement = 'ddl, error' # Statement type to be audited:
# none, dml, insert, update, delete, truncate,
# select, error, rollback, ddl, create, drop,
# alter, grant, revoke, all
#edb_audit_tag = '' # Audit log session tracking tag.
#edb_log_every_bulk_value = off # Writes every set of bulk operation
# parameter values during logging.
# This GUC applies to both EDB AUDIT and PG LOGGING.
#edb_audit_destination = 'file' # file or syslog
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Locale and Formatting -
#intervalstyle = 'postgres'
# HINT: Available values: postgres, postgres_verbose, sql_standard, iso_8601.
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
#dbms_pipe.total_message_buffer = 30kB # default: 30KB, max: 256MB, min: 30KB
#dbms_alert.max_alerts = 100 # default 100, max: 500, min: 0
#---------------------------------------------------------------------------
# DYNA-TUNE
#---------------------------------------------------------------------------
edb_dynatune = 100 # percentage of server resources
# dedicated to database server,
# defaults to 66
edb_dynatune_profile = mixed # workload profile for tuning.
# 'oltp', 'reporting' or 'mixed',
#---------------------------------------------------------------------------
# QREPLACE
#---------------------------------------------------------------------------
#qreplace_function = '' # function used by Query Replace.
#---------------------------------------------------------------------------
# RUNTIME INSTRUMENTATION AND TRACING
#---------------------------------------------------------------------------
timed_statistics = on # record wait timings, defaults to on
# Add settings for extensions here
index_advisor.enabled=on
2、Oracle兼容模式部分参数
# ----------------------------------------------
# - Oracle compatibility -
edb_redwood_date = on # translate DATE to TIMESTAMP(0)
edb_redwood_greatest_least = on # GREATEST/LEAST are strict
edb_redwood_strings = on # treat NULL as an empty string in
# string concatenation
edb_redwood_raw_names = on # don't uppercase/quote names in sys views
# edb_stmt_level_tx = off # 即使是ORACLE也建议关闭
edb_stmt_level_tx = on # allow continuing on errors instead
# rolling back
db_dialect = 'redwood' # Sets the precedence of built-in
# namespaces.
# 'redwood' means sys, dbo, pg_catalog
# 'postgres' means pg_catalog, sys, dbo
optimizer_mode = choose # Oracle-style optimizer hints.
# choose, all_rows, first_rows,
# first_rows_10, first_rows_100,
# first_rows_1000
edb_early_lock_release = on # release locks for prepared statements
# when the portal is closed
# 如果需要使用oracle dblink，必须安装oracle的lib
# [《PostgreSQL 商用版本EPAS(阿里云ppas) - 测试环境部署(EPAS 安装、配置、管理、Oracle DBLINK、外表)》](201801/20180119_01.md)
#oracle_home ='' # path to the Oracle home directory;
# only used by OCI Dblink; defaults
# to ORACLE_HOME environment variable.
datestyle = 'redwood,show_time'
default_with_oids = on
default_with_rowids = on
3、PostgreSQL兼容模式部分参数
# ----------------------------------------------
# - PostgreSQL compatibility -
edb_redwood_date = off # translate DATE to TIMESTAMP(0)
edb_redwood_greatest_least = off # GREATEST/LEAST are strict
edb_redwood_strings = off # treat NULL as an empty string in
# string concatenation
edb_redwood_raw_names = off # don't uppercase/quote names in sys views
edb_stmt_level_tx = off
db_dialect = 'postgres' # Sets the precedence of built-in
# namespaces.
# 'redwood' means sys, dbo, pg_catalog
# 'postgres' means pg_catalog, sys, dbo
optimizer_mode = choose # Oracle-style optimizer hints.
# choose, all_rows, first_rows,
# first_rows_10, first_rows_100,
# first_rows_1000
edb_early_lock_release = off # release locks for prepared statements
# when the portal is closed
datestyle = 'iso, ymd' # PostgreSQL default for your locale
default_with_oids = off
default_with_rowids = off
ssd机器建议另外再配置一下
max_wal_size=2*shared_buffer
min_wal_size=shared_buffer/2
checkpoint_completion_target=0.1
log_duration=off
log_statement='ddl'
log_min_duration_statement='5s'
random_page_cost=1.1
4、其他，使用默认值
3.1.3.12.2 dbms_alert.max_alerts
Parameter Type: Integer
Default Value: 100
Range: 0 to 500
Minimum Scope of Effect: Cluster
When Value Changes Take Effect: Restart
Required Authorization to Activate: EPAS service account
Specifies the maximum number of concurrent alerts allowed on a system using the DBMS_ALERTS package.
3.1.3.12.3 dbms_pipe.total_message_buffer
Parameter Type: Integer
Default Value: 30 Kb
Range: 30 Kb to 256 Kb
Minimum Scope of Effect: Postmaster
When Value Changes Take Effect: Restart
Required Authorization to Activate: EPAS service account
Specifies the total size of the buffer used for the DBMS_PIPE package.
3.1.3.12.4 index_advisor.enabled
Parameter Type: Boolean
Default Value: true
Range: {true | false}
Minimum Scope of Effect: Per session
When Value Changes Take Effect: Immediate
Required Authorization to Activate: Session user
3.1.3.12.5 edb_sql_protect.enabled
Parameter Type: Boolean
Default Value: false
Range: {true | false}
Minimum Scope of Effect: Cluster
When Value Changes Take Effect: Reload
Required Authorization to Activate: EPAS service account
Controls whether or not SQL/Protect is actively monitoring protected roles by analyzing SQL statements issued by those roles and reacting according to the setting of edb_sql_protect.level. When you are ready to begin monitoring with SQL/Protect set this parameter to on.
3.1.3.12.6 edb_sql_protect.level
Parameter Type: Enum
Default Value: passive
Range: {learn | passive | active}
Minimum Scope of Effect: Cluster
When Value Changes Take Effect: Reload
Required Authorization to Activate: EPAS service account
3.1.3.12.7 edb_sql_protect.max_protected_relations
Parameter Type: Integer
Default Value: 1024
Range: 1 to 2147483647
Minimum Scope of Effect: Cluster
When Value Changes Take Effect: Restart
Required Authorization to Activate: EPAS service account
3.1.3.12.8 edb_sql_protect.max_protected_roles
Parameter Type: Integer
Default Value: 64
Range: 1 to 2147483647
Minimum Scope of Effect: Cluster
When Value Changes Take Effect: Restart
Required Authorization to Activate: EPAS service account
3.1.3.12.9 edb_sql_protect.max_queries_to_save
Parameter Type: Integer
Default Value: 5000
Range: 100 to 2147483647
Minimum Scope of Effect: Cluster
When Value Changes Take Effect: Restart
Required Authorization to Activate: EPAS service account
3.1.3.12.10 edbldr.empty_csv_field
Parameter Type: Enum
Default Value: NULL
Range: {NULL | empty_string | pgsql}
Minimum Scope of Effect: Per session
When Value Changes Take Effect: Immediate
Required Authorization to Activate: Session user
3.1.3.12.11 utl_encode.uudecode_redwood
Parameter Type: Boolean
Default Value: false
Range: {true | false}
Minimum Scope of Effect: Per session
When Value Changes Take Effect: Immediate
Required Authorization to Activate: Session user
3.1.3.12.12 utl_file.umask
Parameter Type: String
Default Value: 0077
Range: Octal digits for umask settings
Minimum Scope of Effect: Per session
When Value Changes Take Effect: Immediate
Required Authorization to Activate: Session user
3.1.3.13 Ungrouped
Configuration parameters in this section apply to Advanced Server only and are for a specific, limited purpose.
3.1.3.13.1 nls_length_semantics
Parameter Type: Enum
Default Value: byte
Range: {byte | char}
Minimum Scope of Effect: Per session
When Value Changes Take Effect: Immediate
Required Authorization to Activate: Superuser
3.1.3.13.2 query_rewrite_enabled
Parameter Type: Enum
Default Value: false
Range: {true | false | force}
Minimum Scope of Effect: Per session
When Value Changes Take Effect: Immediate
Required Authorization to Activate: Session user
This parameter has no effect in Advanced Server.
3.1.3.13.3 query_rewrite_integrity
Parameter Type: Enum
Default Value: enforced
Range: {enforced | trusted | stale_tolerated}
Minimum Scope of Effect: Per session
When Value Changes Take Effect: Immediate
Required Authorization to Activate: Superuser
This parameter has no effect in Advanced Server.
Required Authorization to Activate: Session user
Controls the collection of timing data for the Dynamic Runtime Instrumentation Tools Architecture (DRITA) feature. When set to on, timing data is collected.
Note: When Advanced Server is installed, the postgresql.conf file contains an explicit entry setting timed_statistics to off. If this entry is commented out letting timed_statistics to default, and the configuration file is reloaded, timed statistics collection would be turned on.
参考
《EDB PPAS(Oracle 兼容版) Oracle与PostgreSQL 兼容模式的参数配置切换》