Skip to content

Latest commit

 

History

History
176 lines (132 loc) · 12.1 KB

howto-changing-config.md

File metadata and controls

176 lines (132 loc) · 12.1 KB
copyright lastupdated keywords subcollection
years
2019, 2021
2021-03-02
postgresql, databases, config
databases-for-postgresql

{:new_window: target="_blank"} {:shortdesc: .shortdesc} {:screen: .screen} {:codeblock: .codeblock} {:pre: .pre} {:tip: .tip}

Changing the PostgreSQL Configuration

{: #changing-configuration}

{{site.data.keyword.databases-for-postgresql_full}} allows you to change some of the PosgreSQL configuration settings so you can tune your PostgreSQL databases to your use-case. To make permanent changes to the database configuration, use the {{site.data.keyword.databases-for}} cli-plugin or API to write the changes to the configuration file for your deployment.

The configuration is defined in a schema. To make a change, you send a JSON object with the settings and their new values to the API or the CLI. For example, to set the max_connections setting to 150, you would supply

{"configuration":{"max_connections":150}}

{: .codeblock} to either the CLI or to the API.

For more information on checking the current value of max_connections, see the Managing PostreSQL Connections documentation.

Using the CLI

You can check the current configuration of your deployment with

ibmcloud cdb deployment-configuration-schema <deployment name or CRN>

{: pre}

To change your configuration through the {{site.data.keyword.databases-for}} cli-plugin, use deployment-configuration command.

ibmcloud cdb deployment-configuration <deployment name or CRN> [@JSON_FILE | JSON_STRING]

{: pre}

The command reads the changes that you would like to make from the JSON object or a file. For more information, see the reference page.

Using the API

There are two deployment-configuration endpoints, one for viewing the configuration schema and one for changing the configuration. To view the configuration schema, send a GET request to /deployments/{id}/configuration/schema.

To change the configuration, send the settings that you would like to change as a JSON object in the request body of a PATCH request to /deployments/{id}/configuration.

For more information, see the API Reference.

Available Configuration settings

Memory Settings

shared_buffers

  • Default - 32000 (number of 8 KiB buffers, or about 262 MB)
  • Recommended max value: 25% of available RAM
  • Restarts database? - Yes

Note: The recommended memory allocation for shared_buffers is 25% of the deployment's RAM. Warning: Setting shared_buffers any higher can result in memory issues that cause the database to crash, and might decrease the performance of your Database as data is most likely buffered by the OS already. Setting shared_buffers equal, close to equal, or higher than the amount of allocated memory prevents the database from starting. The setting specifies the number of 8 KiB shared memory buffers.

For example, 1 GB of shared_buffers space is 1048576 KiB, and (1048576 KiB / 8 KiB) is 131072 buffers. Your deployment can use extra RAM for caching and performance, even without allocating it to shared_buffers. You do not have to configure the database to use all of the allocated RAM in order for your deployment to use it.

For existing workloads, or when scaling RAM, increasing memory to shared buffers might not be the best course of action. Instead, track your table and index cache hit ratios. If the cache hit ratios are in the high nineties, you should let the OS use the memory in other areas instead of increasing shared_buffers.

You can use these queries as the admin user, or any user with the pg_monitor role to track the cache hit ratios:

Tables

SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  (sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as table_hit_ratio
FROM 
  pg_statio_user_tables;

{: .pre}

Indexes

SELECT 
  sum(idx_blks_read) as idx_read,
  sum(idx_blks_hit)  as idx_hit,
  (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as index_hit_ratio
FROM 
pg_statio_user_indexes;

{: .pre}

General Settings

max_connections

max_prepared_transactions

  • Default - 0
  • Restarts database? - YES
  • Notes - The default value of 0 disables use of prepared transactions and is strongly recommended unless you need to use them.

synchronous_commit

  • Default - local
  • Restarts database? - No
  • Options - local or off
  • Notes - Setting synchronous_commit to off increases transaction commit rate at the expense of a loss of committed transactions if an unclean shutdown occurs.

effective_io_concurrency

  • Default - 12
  • Restarts database - No
  • Notes - It is recommended to leave this setting at the default. Only increase it if you profiled SQL queries and have observed inefficient bitmap heap scans. As IOPS are tied to disk size, increasing this setting on default or smaller sized disks is also not recommended.

deadlock_timeout

  • Default - 10000
  • Restarts database - No
  • Options - Minimum value of 100
  • Notes - The number of milliseconds to wait before checking for deadlock and the duration where lock waits are logged. Logs available through the logging integration. Setting this number too low negatively impacts performance.

log_connections

  • Default - off
  • Restarts database - No
  • Options - Values of on or off
  • Notes - Setting this value to on will make the logs very verbose. It also shows the connections of the monitoring tool as it extracts metrics every 60 seconds. When this is set to on, it is recommended to set the application_name in the connection URI to keep an overview in the logs, as the IP addresses shown are the Kubernetes internal IPs. Details about adjusting the connection URI are found in the PostgreSQL documentation. When set to off, there is no change in behavior to the default setting and no connections are logged. Logs are available through the logging integration. If on is set, the logs show lines similar to this example, where the application name is set as test-app: 
    2021-03-01 10:27:56 UTC [[unknown]] [00000] [708]: [2-1] user=admin,db=ibmclouddb,client=127.0.0.1 LOG:  connection authorized: user=admin database=ibmclouddb application_name=test-app SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256, compression=off)

log_disconnections

  • Default - off
  • Restarts database - No
  • Options - Values of on or off
  • Notes - Setting this value to on will make the logs very verbose. It will also show the disconnections of the monitoring tooling as it extracts metrics every 60 seconds. When this is set to on, it is recommended to set the application_name in the connection URI to keep an overview in the logs, as the IP addresses shown are the Kubernetes internal IPs. Details about adjusting the connection URI are found in the PostgreSQL documentation. When set to off, there is no change in behavior to the default setting and no disconnections are logged. Logs are available through the logging integration. If on is set, the logs show lines similar to this example where the application name is set as test-app: 
    2021-03-01 10:27:56 UTC [test-app] [00000] [708]: [3-1] user=admin,db=ibmclouddb,client=127.0.0.1 LOG:  disconnection: session time: 0:00:00.793 user=admin database=ibmclouddb host=127.0.0.1 port=50638

log_min_duration_statement

  • Default - 100
  • Restarts database - No
  • Options - Minimum value of 100
  • Notes - Statements that take longer than the specified number of milliseconds are logged.

WAL Settings

archive_timeout

  • Default - 1800
  • Restarts database - No
  • Options - Minimum value of 300
  • Notes - The number of seconds to wait before forcing a switch to the next WAL file. If the number of seconds has passed and if there has been database activity, the server switches to a new segment. Effectively limits the amount of time data can remain unarchived.

The next three settings wal_level, max_replication_slots and max_wal_senders enable use of the wal2json logical decoding plug-in. Anyone not using this plug-in should leave these settings at the default.

wal_level

  • Default - hot_standby
  • Restarts database - YES
  • Notes - Controls WAL level. Allowed values are hot_standby or logical. Set to logical to use logical decoding. If you are not using logical decoding, using logical increases the WAL size, which has several disadvantages and no real advantage. If you check your configuration using SHOW wal_level; in psql, note that as of version 9.6 the value hot_standby is mapped to replica.

max_replication_slots

  • Default - 10
  • Restarts database - YES
  • Notes - The maximum number of simultaneously defined replication slots. The default and minimum number of slots is 10. Twenty slots are reserved for internal use by your deployment for High-Availability (HA) purposes. To use slots, you need to set the value above 20 and have 1 slot per consumer. It is recommended to add one additional slot over the minimum per expected consumer. Using wal2json and not increasing max_replication_slots can impact HA and read-only replicas. If you are not using wal2json, you should leave this setting at the default.

max_wal_senders

  • Default - 12
  • Restarts database - YES
  • Notes - The maximum number of simultaneously running WAL sender processes. The default and minimum is 12. One wal_sender per consumer is required. Twenty slots are reserved for internal use by your deployment for High-Availability (HA) purposes. You need to set the value above 20 and it is recommended to add one additional wal_sender over the minimum per expected consumer. Using wal2json and not increasing max_wal_senders can impact HA and read-only replicas. If you are not using wal2json, you should leave this setting at the default.