My default postgresql.conf file, step by step Ilya Kosmodemiansky - - PowerPoint PPT Presentation

▶
my default postgresql conf file step by step
SMART_READER_LITE
LIVE PREVIEW

My default postgresql.conf file, step by step Ilya Kosmodemiansky - - PowerPoint PPT Presentation

Jul 31, 2023 118 likes •672 views

My default postgresql.conf file, step by step Ilya Kosmodemiansky ik@dataegret.com Before we start 269 settings in version 10 314 so far in version 12 Settings in postgresql.conf are to be change manually

slide-1
SLIDE 1

My ”default” postgresql.conf file, step by step

Ilya Kosmodemiansky

ik@dataegret.com

slide-2
SLIDE 2

Before we start

  • 269 settings in version 10
  • 314 so far in version 12
  • Settings in postgresql.conf are to be change manually
  • postgresql.auto.conf can be changed only through ALTER

SYSTEM

  • pg settings view combines everything together

dataegret.com

slide-3
SLIDE 3

pg settings

postgres=# \x Expanded display is on. postgres=# select * from pg_settings where name ~ ’checkpoint_timeout’;

  • [ RECORD 1 ]---+---------------------------------------------------------

name | checkpoint_timeout setting | 3600 unit | s category | Write-Ahead Log / Checkpoints short_desc | Sets the maximum time between automatic WAL checkpoints. extra_desc | context | sighup vartype | integer source | configuration file min_val | 30 max_val | 86400 enumvals | boot_val | 300 reset_val | 3600 sourcefile | /etc/postgresql/10/main/postgresql.conf sourceline | 208 pending_restart | f

dataegret.com

slide-4
SLIDE 4

pg settings: context

postgres=# select distinct(context) from pg_settings ; context

  • postmaster

superuser-backend user internal backend sighup superuser (7 rows)

dataegret.com

slide-5
SLIDE 5

postgresql.conf

  • Plase do not change the order of the settings when you edit

them manually

dataegret.com

slide-6
SLIDE 6

postgresql.conf

  • Plase do not change the order of the settings when you edit

them manually

  • postgresql.conf supports includes

dataegret.com

slide-7
SLIDE 7

postgresql.conf

  • Plase do not change the order of the settings when you edit

them manually

  • postgresql.conf supports includes
  • Always check pg settings if you doubt...

dataegret.com

slide-8
SLIDE 8

postgresql.conf

  • Plase do not change the order of the settings when you edit

them manually

  • postgresql.conf supports includes
  • Always check pg settings if you doubt...
  • And off we go

dataegret.com

slide-9
SLIDE 9

listen addresses

  • * or 127.0.0.1

dataegret.com

slide-10
SLIDE 10

listen addresses

  • * or 127.0.0.1
  • 127.0.0.1 is OK, when pgbouncer is used

dataegret.com

slide-11
SLIDE 11

listen addresses

  • * or 127.0.0.1
  • 127.0.0.1 is OK, when pgbouncer is used
  • Your database must be firewall protected

dataegret.com

slide-12
SLIDE 12

max connections

  • Client connection cause Postgres to spawn a ”heavy”

Unix-Process

dataegret.com

slide-13
SLIDE 13

max connections

  • Client connection cause Postgres to spawn a ”heavy”

Unix-Process

  • Thats why things like max connections = 1000 will never work

dataegret.com

slide-14
SLIDE 14

max connections

  • Client connection cause Postgres to spawn a ”heavy”

Unix-Process

  • Thats why things like max connections = 1000 will never work
  • A much better idea: max connections = 100 or 200 and really

small pool sizes in pgbouncer or another connection pooler

dataegret.com

slide-15
SLIDE 15

superuser reserved connections

  • When all of max connections are utilized, DBA needs to

connect to a database server in order to troubleshoot such situation

dataegret.com

slide-16
SLIDE 16

superuser reserved connections

  • When all of max connections are utilized, DBA needs to

connect to a database server in order to troubleshoot such situation

  • Should be at least 5, better 10

dataegret.com

slide-17
SLIDE 17

tcp keepalives idle

  • If network is unstable, 5 seconds can really help

dataegret.com

slide-18
SLIDE 18

tcp keepalives idle

  • If network is unstable, 5 seconds can really help
  • tcp keepalives interval = 1
  • tcp keepalives count = 5

dataegret.com

slide-19
SLIDE 19

shared buffers

  • Rule of Thumb: 25% of RAM

dataegret.com

slide-20
SLIDE 20

shared buffers

  • Rule of Thumb: 25% of RAM
  • But to use 16/32/64Gb of shared buffers efficiently, fast

discs are required

dataegret.com

slide-21
SLIDE 21

shared buffers

  • Rule of Thumb: 25% of RAM
  • But to use 16/32/64Gb of shared buffers efficiently, fast

discs are required

  • If the database is definitely smaller than RAM, 75% of RAM

for shared buffers can also work

dataegret.com

slide-22
SLIDE 22

huge pages

  • Rule of thumb: when there are 8-16Gb shared buffers, using of

Huge Pages is recommended

  • huge pages = on

dataegret.com

slide-23
SLIDE 23

huge pages

  • Rule of thumb: when there are 8-16Gb shared buffers, using of

Huge Pages is recommended

  • huge pages = on
  • Huge Pages should be first enabled in kernel

dataegret.com

slide-24
SLIDE 24

huge pages

  • Rule of thumb: when there are 8-16Gb shared buffers, using of

Huge Pages is recommended

  • huge pages = on
  • Huge Pages should be first enabled in kernel
  • vm.nr overcommit hugepages and vm.nr hugepages

dataegret.com

slide-25
SLIDE 25

Linux Memory allocation CPU L1 MMU TLB Memory L2 L3

page table Virtual addressing Translation Physical addressing

dataegret.com

slide-26
SLIDE 26

work mem

  • RAM per process, Postgres workers use this RAM for sorting,

hash joins etc.

dataegret.com

slide-27
SLIDE 27

work mem

  • RAM per process, Postgres workers use this RAM for sorting,

hash joins etc.

  • 128Mb is a good starting point

dataegret.com

slide-28
SLIDE 28

work mem

  • RAM per process, Postgres workers use this RAM for sorting,

hash joins etc.

  • 128Mb is a good starting point
  • To high setting could cause OOM

dataegret.com

slide-29
SLIDE 29

work mem

  • RAM per process, Postgres workers use this RAM for sorting,

hash joins etc.

  • 128Mb is a good starting point
  • To high setting could cause OOM
  • Could be individually configured for each session

dataegret.com

slide-30
SLIDE 30

maintenance work mem

  • Same as work mem but for superuser connections

dataegret.com

slide-31
SLIDE 31

maintenance work mem

  • Same as work mem but for superuser connections
  • 256-512Mb, if there is enough RAM

dataegret.com

slide-32
SLIDE 32

maintenance work mem

  • Same as work mem but for superuser connections
  • 256-512Mb, if there is enough RAM
  • Could be quite helpful for CREATE INDEX

CONCURRENTLY

dataegret.com

slide-33
SLIDE 33

maintenance work mem

  • Same as work mem but for superuser connections
  • 256-512Mb, if there is enough RAM
  • Could be quite helpful for CREATE INDEX

CONCURRENTLY

  • autovacuum work mem is a part of maintenance work mem,

can be smaller

dataegret.com

slide-34
SLIDE 34

Settings fro manual vacuum

  • vacuum cost delay = 0
  • vacuum cost page hit = 0
  • vacuum cost page miss = 10
  • vacuum cost page dirty = 10
  • vacuum cost limit = 100

dataegret.com

slide-35
SLIDE 35

Write Ahead Log

  • wal level = replica

dataegret.com

slide-36
SLIDE 36

Write Ahead Log

  • wal level = replica
  • checkpoint timeout = 60min, if it is by given recovery target

acceptable, could gain performance improvement

  • max wal size = 16GB

dataegret.com

slide-37
SLIDE 37

Write Ahead Log

  • wal level = replica
  • checkpoint timeout = 60min, if it is by given recovery target

acceptable, could gain performance improvement

  • max wal size = 16GB
  • checkpoint completion target = 0.9

dataegret.com

slide-38
SLIDE 38

bgwriter

  • Background Writer helps Checkpointer to send unused dirty

pages to disk

dataegret.com

slide-39
SLIDE 39

bgwriter

  • Background Writer helps Checkpointer to send unused dirty

pages to disk

  • Regret to say, it is not the best part of PostgreSQL codebase

dataegret.com

slide-40
SLIDE 40

bgwriter

  • Background Writer helps Checkpointer to send unused dirty

pages to disk

  • Regret to say, it is not the best part of PostgreSQL codebase
  • All settings to maximum:

◮ bgwriter delay = 10ms ◮ bgwriter lru maxpages = 1000 ◮ bgwriter lru multiplier = 10.0

dataegret.com

slide-41
SLIDE 41

effective io concurrency

  • 1 by default, which enables prefetch

dataegret.com

slide-42
SLIDE 42

effective io concurrency

  • 1 by default, which enables prefetch
  • Documentation recommends higher values for storages with

high parallelism capabilities

dataegret.com

slide-43
SLIDE 43

effective io concurrency

  • 1 by default, which enables prefetch
  • Documentation recommends higher values for storages with

high parallelism capabilities

  • But there are evidences, that sometimes disabling

effective io concurrency leads to better results (https://www.postgresql.org/message-id/flat/6c7a45df-f6ab- f2ce-6f84-9555864f6c86

dataegret.com

slide-44
SLIDE 44

Must have optimizer settings

  • effective cache size = 2 * shared buffers or less
  • default statistics target = 100

dataegret.com

slide-45
SLIDE 45

Autovacuum

  • autovacuum vacuum threshold = 50
  • autovacuum vacuum scale factor = 0.05

dataegret.com

slide-46
SLIDE 46

Autovacuum

  • autovacuum vacuum threshold = 50
  • autovacuum vacuum scale factor = 0.05
  • autovacuum naptime = 1s

dataegret.com

slide-47
SLIDE 47

Autovacuum

  • autovacuum vacuum threshold = 50
  • autovacuum vacuum scale factor = 0.05
  • autovacuum naptime = 1s
  • autovacuum max workers = 10

dataegret.com

slide-48
SLIDE 48

Autovacuum

  • autovacuum vacuum threshold = 50
  • autovacuum vacuum scale factor = 0.05
  • autovacuum naptime = 1s
  • autovacuum max workers = 10
  • autovacuum analyze threshold = 50
  • autovacuum analyze scale factor = 0.05

dataegret.com

slide-49
SLIDE 49

Autovacuum

  • autovacuum vacuum threshold = 50
  • autovacuum vacuum scale factor = 0.05
  • autovacuum naptime = 1s
  • autovacuum max workers = 10
  • autovacuum analyze threshold = 50
  • autovacuum analyze scale factor = 0.05

dataegret.com

slide-50
SLIDE 50

Autovacuum

  • autovacuum vacuum threshold = 50
  • autovacuum vacuum scale factor = 0.05
  • autovacuum naptime = 1s
  • autovacuum max workers = 10
  • autovacuum analyze threshold = 50
  • autovacuum analyze scale factor = 0.05

dataegret.com

slide-51
SLIDE 51

Autovacuum

  • autovacuum vacuum threshold = 50
  • autovacuum vacuum scale factor = 0.05
  • autovacuum naptime = 1s
  • autovacuum max workers = 10
  • autovacuum analyze threshold = 50
  • autovacuum analyze scale factor = 0.05
  • autovacuum freeze min age = 20000000 # 9.6 and older -

default is most likely enough, older versions often require up to 1B

  • autovacuum freeze table age = 15000000

dataegret.com

slide-52
SLIDE 52

Logging

log_directory = ’’/var/log/postgresql’’ log_filename = ’’postgresql-%Y-%m-%d.log’’ log_rotation_age = 1d log_rotation_size = 0 log_min_error_statement = error log_min_duration_statement = 1000 log_checkpoints = on log_line_prefix = ’’%m %p %u@%d from %h [vxid:%v txid:%x] [%i] ’’ log_lock_waits = on log_statement = ’’none’’ log_replication_commands = on log_temp_files = 0 log_timezone = ’’Europe/Berlin’’

dataegret.com

slide-53
SLIDE 53

Don’t forget about one very useful extension

  • shared preload libraries = ’pg stat statements’
  • pg stat statements.max = 10000
  • pg stat statements.track = top

dataegret.com

slide-54
SLIDE 54

Questions?

ik@dataegret.com

dataegret.com