This appendix describes the parameters to be set in the postgresql.conf file of Fujitsu Enterprise Postgres.
The postgresql.conf file is located in the data storage destination.
Information
The maximum value that can be expressed as a 4-byte signed integer changes according to the operating system. Follow the definition of the operating system in use.
This parameter specifies the directory where the corefile is to be output. If this parameter is omitted, the data storage destination is used by default. This parameter can only be set when specified on starting an instance. It cannot be changed dynamically, while an instance is active.
This parameter specifies the contents to be included in the corefile.
full: Outputs all contents of the server process memory to the corefile.
none: Does not output a corefile.
minimum: Outputs only non-shared memory server processes to the corefile. This reduces the size of the corefile. However, in some cases, this file may not contain sufficient information for examining the factor that caused the corefile to be output.
If this parameter is omitted, "minimum" is used by default. This parameter can only be set when specified on starting an instance. It cannot be changed dynamically, while an instance is active.
This parameter specifies the directory that stores the keystore file. Specify a different location from other database clusters. This parameter can only be set when specified on starting an instance. It cannot be changed dynamically, while an instance is active.
Cannot be specified with the tde_kms.kms_conninfo_file parameter.
When connecting to the key management system using a plug-in, specify the directory that stores the plug-in with an absolute path. Only database administrators should be able to store plugins in this directory. It can only be set by specifying a parameter when starting the instance.
tde_kms.enable_shared_dek (boolean)
Enables or disables sharing of data encryption keys between backend processes for each encrypted tablespace in transparent data encryption. Default is off. It can only be set by specifying a parameter when starting the instance.
tde_kms.max_shared_dek (numerical value)
Specify the maximum number of shared data encryption keys when sharing data encryption keys per tablespace in transparent data encryption. Default is 1000. It can only be set by specifying a parameter when starting the instance.
tde_kms.kms_conninfo_file (string)
When using the key management service as a key store, specifies the file that contains the connection information for the key management system. Cannot be specified with the keystore_location parameter.
Create a connection information file for the key management system in one of the following format:
kmip kms-name address port auth-method [auth-options]
custom kms-name plugin-name [plugin-options] [extra-args]
Specify one of the following methods to connect to the key management system.
kmip
Access this key management system using the KMIP protocol. Take out and use the encryption key in this key management system.
custom
Access the key management system using a plugin module. The encryption/decryption process is done inside the plugin or inside the key management system. Fujitsu Enterprise Postgres does not use cryptographic keys directly.
kms-name
The key management system name assigned to the key management system and specified when declaring the master encryption key or opening the keystore. The name of the key management system must be unique within this file. The key management system name must be a string of no more than 63 characters beginning with a-z, consisting of a-z, a number (0 -9), and an underscore. Upper and lower case letters are the same.
address
Specifies the host name or IP address of the key management service.
port
Specifies the port number on which the key management service listens for services.
auth-method
Specifies the authentication method for the key management service.
auth-options
The auth-method is followed by the authentication method options. You can specify multiple options in a name = value field.
A certificate is used to authenticate the KMIP server and the client, Fujitsu Enterprise Postgres, to each other. The auth-options can be.
sslcert
Specifies the file name of the client certificate. The corresponding format is PEM format.
sslkey
Specifies the file name of the private key used for the client certificate. The corresponding format is PEM format. If you choose to encrypt the file with a passphrase, use a passphrase that is no more than 1023 bytes long.
sslkeypassphrase-obf
Specifies the file that contains the obfuscated passphrase for the private key file specified by sslkey. This option allows the keystore to be opened automatically when the server starts. The pgx _ keystore command creates obfuscated files. It can be omitted.
sslrootcert
Specifies the file name of the SSL Certificate Authority certificate. The corresponding format is PEM format. Used to verify the server certificate of the connection destination.
Example
kmip mykmipsvr mykmipsvr.example.com 5696 cert sslcert=postgres.crt sslkey=postgres.key sslrootcert=root.crt
Note
cert authentication does not verify that the server you are connecting to is the same server you are trying to connect to. Any server using a server certificate that is signed with the certificate of the certificate authority specified in sslrootcert is considered the correct destination. To avoid problems with this behavior, consider using your own CA or self-signed certificate for the KMIP server.
kms-name
The key management system name given to the key management system, specified when declaring a master encryption key or opening a keystore. The name of the key management system must be unique within this file. The key management system name must be a string of up to 63 characters starting with a-z and consisting of a-z, digits (0-9) and underscores. Uppercase and lowercase letters are considered the same.
plugin-name
Specify the name of the plugin. Use the file with the same name as the plugin name in the directory specified by tde_kms.plugin_path as the plugin module. The file must have execution privilege for the OS user that starts the Fujitsu Enterprise Postgres server.
plugin-options
Specify other options for the plugin. Multiple options can be specified in name=value format fields. You can specify the following option names.
kms-secret-obf
Specify the file containing the obfuscated KMS secret when enabling automatic opening of the keystore using transparent data encryption. The obfuscated file is created with the pgx_keystore command. The obfuscated file contents are decrypted by Fujitsu Enterprise Postgres and passed to the plugin. Can be omitted if you are not using transparent data encryption to enable automatic opening of the keystore.
extra-args
Specify additional arguments to pass to the specified shell command in the form arg=value. If you specify multiple extra-args, the values are passed to the shell command in that order.
Example
If you pass the plugin an additional argument: --profile user1
custom mykms mykms arg=--profile arg=user1
tablespace_encryption_algorithm (string)
This parameter specifies the encryption algorithm for tablespaces that will be created. Valid values are "AES128", "AES256", and "none". If you specify "none", encryption is not performed. The default value is "none". To perform encryption, it is recommended that you specify "AES256". Only superusers can change this setting.
This parameter specifies the absolute path of the directory where pgx_dmpall will store the backup data. Specify a different location from other database clusters. This parameter can only be set when specified on starting an instance. It cannot be changed dynamically, while an instance is active.
Place this directory on a different disk from the data directory to be backed up and the tablespace directory. Ensure that users do not store arbitrary files in this directory, because the contents of this directory are managed by the database system.
When using the SUBSTR function compatible with Oracle databases, set "oracle" and "pg_catalog" in the search_path parameter. You must specify "oracle" before "pg_catalog".
Example
search_path = '"$user", public, oracle, pg_catalog'
Information
The search_path feature specifies the priority of the schema search path. The SUBSTR function in Oracle database is defined in the oracle schema.
Refer to "Statement Behavior" under "Server Administration" in the PostgreSQL Documentation for information on search_path.
This parameter enables collection of statistics for pgx_stat_lwlock and pgx_stat_latch.
on: Enables collection of statistics.
off: Disables collection of statistics.
If this parameter is omitted, "on" is assumed.
Only superusers can change this setting.
This parameter enables collection of statistics for pgx_stat_sql.
on: Enables collection of statistics.
off: Disables collection of statistics.
If this parameter is omitted, "on" is assumed.
Only superusers can change this setting.
reserve_buffer_ratio (numerical value)
This parameter specifies the proportion of shared memory to be used for a stable buffer table.
Minimum value: 0
Maximum value: 80
If this parameter is omitted, 0 will be used.
vci.cost_threshold (numerical value)
This parameter specifies the lowest cost that selects an execution plan that uses a VCI. If the cost of the best execution plan that does not use a VCI is lower than this value, that execution plan will be selected.
Minimum value: 0
Maximum value: Maximum value that can be expressed as a 4-byte signed integer
If this parameter is omitted or a value outside this range is specified, 18000 will be used.
vci.control_max_workers (numerical value)
This parameter specifies the number of background workers that manage VCI. The number of workers for the entire instance is limited by max_worker_processes, so add the value specified here to max_worker_processes.
Minimum value: 1
Maximum value: 8388607
If this parameter is omitted or a value outside this range is specified, 8 will be used.
This parameter enables or disables VCI.
on: Enables VCI.
off: Disables VCI.
If this parameter is omitted, "on" will be used.
This parameter enables or disables log output when VCI is not used due to insufficient memory specified by vci.max_local_ros.
on: Enables log output.
off: Disables log output.
If this parameter is omitted, "off" will be used.
vci.maintenance_work_mem (numerical value)
This parameter specifies the maximum memory size used for maintenance of VCI (when executing CREATE INDEX, for example).
Minimum value: 1 MB
Maximum value: Maximum value that can be expressed as a 4-byte signed integer
If this parameter is omitted or a value outside this range is specified, 256 MB will be used.
vci.max_local_ros (numerical value)
This parameter specifies the maximum memory size used for VCI scan.
Minimum value: 64 MB
Maximum value: Maximum value that can be expressed as a 4-byte signed integer
If this parameter is omitted or a value outside this range is specified, 64 MB will be used.
vci.max_parallel_degree (numerical value)
This parameter specifies the maximum number of background workers used for parallel scan. The number of workers for the entire instance is limited by max_worker_processes, so add the value specified here to max_worker_processes.
A value from -8388607 to 8388607 can be specified.
Integer (1 or greater): Parallel scan is performed using the specified degree of parallelism.
0: Stops the parallel scan process.
Negative number: The specified value minus the maximum number of CPUs obtained from the environment is used as the degree of parallelism and parallel scan is performed.
If this parameter is omitted or a value outside this range is specified, 0 will be used.
vci.shared_work_mem (numerical value)
This parameter specifies the maximum memory size used for VCI parallel scan.
Minimum value: 32 MB
Maximum value: Maximum value that can be expressed as a 4-byte signed integer
If this parameter is omitted or a value outside this range is specified, 1 GB will be used.
pgx_global_metacache (numerical value)
Specifies the memory size of the GMC area.
Specify a value calculated by the formula below.
A value lower than the calculated value will still work, but the meta cache may not be able to fit into the GMC area.
In this case, the system will discard the meta cache it thinks it is no longer needed, but if it is needed again, the meta cache will need to be expanded and will not perform well.
If the value is less than 10 MB and is set to a nonzero value that disables the feature, the database startup fails because the Global Meta Cache feature cannot operate.
A setting of 0 disables the Global Meta Cache feature. The default is 0.
Changing this setting requires restarting the database.
Size of GMC area
= Max(10MB,
(All user table x 0.4 KB
+ All user Indexes x 0.3 KB
+ All user columns x 0.8 KB) x 1.5 (*1) )
*1) Safety Factor (1.5)
This value takes into account the case where both GMC before and after the change temporarily exist at the same time in shared memory when the table definition is changed or the row of the system catalog is changed.This parameter enables collection of statistics for pgx_stat_gmc.
on: Enables collection of statistics.
off: Disables collection of statistics.
If this parameter is omitted, "on" is used.
Only superusers can change this setting.
pgx_catalog_cache_max_size(numerical value)
Specifies the maximum amount of memory that the backend process should use as the catalog cache.
You can enable catalog cache deletion by setting it to 8 KB or more.
A setting of 0 disables the catalog cache removal. The default is 0.
If no units are specified, they are treated as KB.
Minimum value: 8KB
Maximum value: Maximum value that can be expressed as a 4-byte signed integer
When calculating the parameter settings, the factors that determine the cache size are calculated as the number of tables, the number of indexes, and the number of columns. What is kept as a catalog cache or relation cache also includes objects such as databases, roles, or procedures, but these are small compared to the above factors and do not need to be factored into them. It also includes a calculation method for pgx_relacion_cache_max_size because the given memory is distributed between the catalog cache and the relation cache.
Note
The calculation method here assumes that all backends have similar access and that the transaction also has access to a similar number of resources. If you have a small number of singular backends or transactions, consider excluding them as errors.
Determine how much memory a backend process can use. Decide by subtracting the memory size required by the entire system such as the database cache from the installed memory and dividing the rest by the number of connections.
For best performance, use the following formula to calculate the total memory size of the catalog cache when the backend holds the catalog cache for all resources accessed during its lifetime.
The amount of memory varies depending on whether Global Meta Cache is enabled or disabled. Enabling Global Meta Cache reduces the amount of memory required because most of the cache is located on shared memory.
When Global Meta Cache is enabled:
(Number of tables to access + Number of indexes to access + Number of columns to access)
× 0.1KB × 1.5 (*1)
When Global Meta Cache is disabled:
{ Number of tables to access × 0.5KB(pg_class tuple size)
+ Number of indexes to access × 0.5KB(pg_index tuple size)
+ Number of columns to access × 1.0KB(pg_statistic tuple size)} × 1.5 (*1)
*1) Safety Factor (1.5)
The system catalog contains columns with variable-length types. For example, the tuple size in pg_class is a constant value multiplied by the number of tables, while relname in pg_class is variable length data.
It is not practical to calculate every definition in detail, so we added 50% to the above formula.In the same way as in 2., calculate the relation cache using the following formula.
(1.4KB × Number of tables to access + 2.4KB × Number of indexes to access) × 1.5 (*1) *1) Safety Factor (1.5) The relation cache is structured to facilitate the use of table and index definitions, holds pointers to various objects, and is sized to include them. It is variable length because the type of object allocated by the table definition and its size change. Since it is not realistic to calculate for all definitions, 50% is added.
If the value of 1. ≥ the value of 2. + the value of 3., the backend process can keep all caches to the extent allowed, so there is no need to limit the caches. If you want to cap for safety, set the value of 2. to pgx_catalog_cache_max_size and the value of 3. to pgx_relation_cache_max_size.
If the value of 1. < the value of 2. + the value of 3. then you need to limit the cache. However, this parameter does not limit the size of the cache used by a transaction. Therefore, take the following steps.
Calculate the catalog cache used by a transaction using the formula in 2.
Calculate the relation cache used by a transaction using the formula in 3.
If the value of 1. < the value of 6. + the value of 7., then the value of 1. needs to be increased. In other words, in some cases, it may be necessary to increase the installed memory or reduce the number of connections.
If the value of 1. ≥ the value of 6. + the value of 7., the condition of 1. can be satisfied by limiting the cache with this parameter. Divide the value of 1. by the ratio of 2. and 3. and set it as a parameter. Set the value distributed to 2. to pgx_catalog_cache_max_size and the value distributed to 3. to pgx_relation_cache_max_size.
The value calculated in 9. is a provisional value. If you cannot meet your target performance, first try to shift the focus of allocation to the relation cache. This is because when executing SQL, the relation cache generated based on the catalog cache is mainly referenced, so it is advantageous to leave a large amount of relation cache. If the performance is still not satisfied, adjust the parameters by referring to "15.1.4 Performance Impact and Parameter Tuning of the Local Meta Cache Limit Feature".
Note
Be careful when partitioning the table.
The cached definition changes depending on whether the parent table is specified in the SQL statement or the child table is specified. In particular, note that if you specify a parent table, the definitions of all child tables are cached. This is because when you specify a parent table in an SQL statement, you need to know the definitions of all the child tables in order to determine which child table will contain the desired data. Note that the column information of the parent table is not cached.
When specifying the parent table:
Number of tables to access = Number of parent tables to access + Number of defined child tables Number of columns = Number of defined columns x number of defined child tables
When specifying the child table directly:
Number of tables to access = Number of child tables actually accessed Number of columns ＝ Number of defined columns x number of child tables actually accessed
Example)
Suppose the parent table T (1 index, 3 columns) is split from child tables T1 to T5 (1 index, 3 columns, respectively). If the parent table T is specified in SQL, when the child tables that contain the data to be queried are limited to T1 and T2, and when accessing the data using the indexes defined by T1 and T2, calculate as follows.
Number of tables = 1(parent table) + 5(child table) = 6 Number of indexes = 2 (index to access) Number of columns = 3 (number of columns) x 5 (child table) = 15
If you specify child tables T1 and T2 in SQL and use the indexes defined on T1 and T2 when accessing data, the calculation is as follows.
Number of tables = 2(child table) Number of indexes = 2 (index to access) Number of columns = 3 (number of columns) x 2 (child table) = 6
pgx_relation_cache_max_size(numerical value)
Specifies the maximum amount of memory that the backend process should use as the relation cache.
You can enable catalog cache deletion by setting it to 8 KB or more.
A setting of 0 disables the relation cache removal. The default is 0.
If no units are specified, they are treated as KB.
Minimum value: 8KB
Maximum value: Maximum value that can be expressed as a 4-byte signed integer
For the calculation method for parameter setting, refer to the calculation method of pgx_catalog_cache_max_size.
pgx_cache_hit_log_interval(numerical value)
Specifies the time interval to output a message indicating the cache reference status for each backend process.
When the transaction ends, if the time set in this parameter has elapsed since the previous message was output, the message is output.
If set to 0, a message will be output each time the transaction ends.
Setting -1 disables the output. The default value is 10min.
If no units are specified, they are treated as ms.
Even if pgx_catalog_cache_max_size and pgx_relation_cache_max_size are disabled, the message output of the corresponding cache will be invalid.
Immediately after connecting to the server, a small transaction occurs before the request from the user application, such as for user authentication. Since it is meaningless to know the hit rate for these, a message will be output at the end of the transaction that started after the time set in this parameter has elapsed after connecting to the server.
For the same reason, setting a small value such as 0 may result in a message being printed at the end of such a small transaction.
You can check which transaction the message corresponds to from the information output at the beginning.
This information depends on the setting of the parameter log_line_prefix.
Minimum value: 0
Maximum value: 2147483647ms
See
Refer to "Server Configuration" under "Server Administration" in the PostgreSQL Documentation for information on other postgresql.conf parameters.
Specifies the name of the database to which the background worker process connects. This parameter must specify a connectable database name. If you omit this parameter, then it is assumed to be "postgres".
The pseudo database "replication" cannot be specified for streaming replication.
If you change this parameter, reload the configuration file.
Specifies the OpenSSL configuration file. Specifying a valid configuration file makes legacy algorithms available. Use the example below to prepare the configuration file in any directory.
If this parameter is not specified, the empty string is assumed.
This parameter can only be set by specifying the parameter at instance startup.
You cannot make dynamic changes during instance startup.
Example
openssl_conf = '/path/to/openssl.conf'
[OpenSSL Configuration File (openssl.conf) Example]
===================== openssl_conf = openssl_init [openssl_init] providers = provider_sect [provider_sect] default = default_sect legacy = legacy_sect [default_sect] activate = 1 [legacy_sect] activate = 1 =====================
Specifies the directory that contains additional OpenSSL modules.
Legacy algorithms are available by specifying 'server installation directory/lib/ossl-modules'.
If this parameter is not specified, the empty string is assumed.
This parameter can only be set by specifying the parameter at instance startup.
You cannot make dynamic changes during instance startup.
This parameter sets the OPENSSL_MODULES environment variable to be applied to the server process. Do not set the OPENSSL_MODULES environment variable in any way other than setting this parameter, as this may cause abnormal behavior.
Example
openssl_modules = '/opt/fsepv<x>server64/lib/ossl-modules'
Note that "<x>" indicates the product version.
Information
Legacy algorithms include the following encryption algorithms:
BF
CAST5
DES-ECB
DES-CBC
MD4
Whirlpool
pgx_stat_vacuum_freeze.track_freeze_info (boolean)
Collects and maintains information about aggressive freeze for tuples. The default is on.
on: Enables information collection.
off: Disables information collection.
pgx_stat_vacuum_freeze.monitoring_interval (numerical value)
Aggregate statistics for aggressive freeze for tuples in this interval, so that statistics for each interval can be obtained in the pgx_stat_freeze_results table. Note that an aggressive freeze for tuples across this interval will still account for the overall tuple freeze at completion time. The default is 1hour.
Set the aggressive freeze for tuples script to run at weekly intervals of days, daily intervals of hours, and hourly intervals of minutes.
Minimum value: 1min
Maximum value: 1d
If a value out of range is specified, 1h is set.
If this parameter is omitted, 1h is assumed.
pgx_stat_vacuum_freeze.log_retention_period (numerical value)
Specify how long statistics are kept. The default is 90 days.
Set the period according to the work cycle. For example, if a monthly batch is to be processed every 1 month, set it to 90 days, which is approximately 3 times that period. to capture changes in the aggressive freeze for tuples over the duration of the cycle.
Minimum value: 1d
Maximum value: 180d
If an out-of-range value is specified, 90d is set.
If this parameter is omitted, 90d is assumed.