The Essential postgresql.conf

0

No comments posted yet

Comments

Slide 1

The Essential PostgreSQL.conf Robert Treat, PGDay Denver, 2011 9

Slide 2

Introduction Robert Treat OmniTI Database Management and Consulting Large and/or High Traffic Architectures We’re Hiring! Postgres Web, Advocacy, Conferences, PhpPgAdmin Major Contributor xzilla.net @robtreat2

Slide 3

The Grand Scheme Understanding the postgresql.conf Essential postgresql.conf parameters

Slide 4

The Grand Scheme Understanding the postgresql.conf Essential postgresql.conf parameters

Slide 5

Understanding the postgresql.conf Types of Settings When Settings Take Effect PostgreSQL.conf “Gotchas” Viewing Your Current Settings

Slide 6

Types of Settings Boolean: true, false, on, off Integer: whole numbers (2112) Float: decimal values (21.12) Memory/Disk Units: 128MB, 2112GB Avoid using integers to size memory parameters Time: d,m,s (30s) Strings: single quotes (‘pg_log’) Enum: single quotes (‘WARNING’ or ‘ERROR’) Limited set of acceptable strings Lists: comma seperated (‘“$user”,public,tsearch2’)

Slide 7

When Settings Take Effect Internal: set at compile time Postmaster: requires database restart Backend: must be set before session start Sighup: change by reloading conf file pg_reload_conf(), pg_ctl reload, kill -HUP Superuser: set at runtime by superuser User: set at runtime, affects only a single session See pg_settings.context to determine setting type

Slide 8

When Settings Take Effect Internal: set at compile time Postmaster: requires database restart Backend: must be set before session start Sighup: change by reloading conf file pg_reload_conf(), pg_ctl reload, kill -HUP Superuser: set at runtime by superuser User: set at runtime, affects only a single session See pg_settings.context to determine setting type

Slide 9

postgresql.conf Gotchas! Where is my postgresql.conf ? $PGDATA/postgresql.conf watch out for symbolic links some distros put it in other places (/etc) SHOW data_directory; SHOW config_file;

Slide 10

postgresql.conf Gotchas! Lines with # This line is a comment, and has no effect On a fresh install, uses defaults Commenting a line is the same as deleting a line, it will restore the “default” for that instance

Slide 11

Viewing Your Current Settings Look in postgresql.conf Works if you follow best practices Not definitive!

Slide 12

Viewing Your Current Settings Look in postgresql.conf Works if you follow best practices Not definitive! SHOW ALL, SHOW <setting>, current_setting(setting) Shows the current value Watch out for session specific changes

Slide 13

Viewing Your Current Settings Look in postgresql.conf Works if you follow best practices Not definitive! SHOW ALL, SHOW <setting>, current_setting(setting) Shows the current value Watch out for session specific changes SELECT * FROM pg_settings Shows the current value “source” column

Slide 14

Viewing Your Current Settings pagila=# select current_setting(name), * from pg_settings where name = 'shared_buffers';-[ RECORD 1 ]------+----------------------------------------------current_setting | 1600kBname | shared_bufferssetting | 200unit | 8kBcategory | Resource Usage / Memoryshort_desc | Sets the number of shared memory buffers used by the server.extra_desc | context | postmastervartype | integersource | configuration filemin_val | 16max_val | 1073741823enumvals | boot_val | 1024reset_val | 200sourcefile | /opt/local/var/db/postgresql91/data/postgresql.confsourceline | 109

Slide 15

The Essential Parameters Logging Getting Connected Sizing Memory Checkpoint Management Vacuum Managment WAL management

Slide 16

Logging log_destination, log_directory, log_filename find out where you are logging might be good to log to a different disk than your data

Slide 17

Logging log_destination, log_directory, log_filename find out where you are logging might be good to log to a different disk than your data log_line_prefix generic recommendation: ‘%t: %r: %u@%d: [%p]: ‘ timestamp, connecting host, username, database, pid

Slide 18

Get Connected listen_addresses requires restart! default is localhost only use * to enable tcp/ip

Slide 19

Get Connected listen_addresses requires restart! default is localhost only use * to enable tcp/ip max_connections requires restart! 100 default (apache children default is higher) affects other settings (work_mem) for 1000+ connections, use connection pooler

Slide 20

Sizing Memory shared_buffers requires restart! most important setting for good performance allocation of memory for Postgres caching ~20% of Total Available RAM (up to 8GB) 32GB RAM, 1920 MB is probably ok 256GB RAM, 50 GB is probably not optimal Windows users probably want less may require adjusting kernel params (shmmax) http://www.postgresql.org/docs/current/interactive/kernel-resources.html

Slide 21

Sizing Memory effective_cache_size most important setting for good performance based on memory available for filesystem cache SET = TRAM - (DRAM - PGRAM) TRAM = Total RAM DRAM = RAM needed by OS and other apps PGRAM = RAM needed for Postgres (shared_buffers, etc...) Guide for available memory, not an allocation

Slide 22

Sizing Memory work_mem used by queries for sorting higher values for more complex queries limit is per sort 5 sorts in 1 query = 5*work_mem maintenance_work_mem used by vacuum, indexing, and similar operations semi-allocation of memory when needed don’t be scared to make this big (1GB)

Slide 23

Checkpoint Management checkpoint_segments controls the amount of data required per checkpoint default 3, recommended minimum of 10 set higher for heavy write activity 30 - 300 is not uncommon increases space needed in pg_xlog increases recovery time after crash checkpoint_completion_target with checkpoint_segments >= 10, set this to .9 http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm

Slide 24

Vacuum Management autovacuum max_fsm_pages max_fsm_relations default_statistics_target autovacuum_freeze_max_age set to 1,000,000,000 to reduce vacuums for non-activity requires restart

Slide 25

WAL Management / Replication WAL for Performance wal_buffers requires restart! needed for write heavy loads 16MB, “set it and forget it!” synchronous_commit can increase tps by a large margin allows data loss without corruption BAD: fsync = off GOOD: synchronous_commit = off can be set per database, user, or transaction

Slide 26

WAL Management / Replication WAL for Replication-ish archive_mode requires restart! archive_command command to manage WAL shipping not needed for streaming replication, but helps see my later talk on OmniPITR :-)

Slide 27

WAL Management / Replication WAL for Replication wal_level requires restart! “hot_standby” needed for... hot standbys max_wal_senders requires restart! controls the number of connections for streaming replication

Slide 28

WAL Management / Replication Key Replication Configs synchronous_standby_names used for setting up synchronous slaves hot_standby requires restart! controls if a server in recovery mode (like a slave) will allow queries against it

Slide 29

The End Thanks! Additional Resources http://www.postgresql.org/docs/current/interactive/runtime-config.html http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server Slides http://www.xzilla.net/writings.html

Slide 30

No Really, The End

Summary: Discusses the most important settings to be aware of when configuring your postgresql.conf

Tags: postgres postgresql tuning pgday denver omniti

URL:
More by this User
Most Viewed
Previous Page Next Page
Advanced WAL File Management With OmniPITR
Advanced WAL...
 
 
 
Previous Page Next Page