advertisement

The Essential postgresql.conf

50 %
50 %
advertisement
Information about The Essential postgresql.conf
Technology

Published on November 8, 2008

Author: xzilla

Source: slideshare.net

Description

Discusses the the most important parameters inside the postgresql.conf. Given at the Beijing Perl Workshop, China, 2008
advertisement

The Essential postgresql.conf Robert Treat Beijing Perl Workshop, China, 2008

Introduction Robert Treat DBA, Postgres Major Contributor http://www.xzilla.net/writings.html OmniTI Internet Scalability Experts http://omniti.com/is/hiring

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

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

Understanding the postgresql.conf Types of settings When settings take effect postgresql.conf gotchas Viewing your current settings

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 params ● Time : d,m,s (30s) ● Strings : single quotes ('pg_log') ● Enum : single quotes ('WARNING' or 'ERROR') ● Limited set of acceptable strings ● Lists : comma separated ('”$user”,public,tsearch2')

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, effects only single session See pg_settings.context to determine setting type

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, effects only single session See pg_settings.context to determine setting type

postgresql.conf Gotchas! ● $PGDATA/postgresql.conf ● Watch out for symbolic links ● Some distros put it other places (/etc) ● Lines with # ● Line is a comment, has no effect ● On fresh install, uses default ● On <8.3, comment a line does not restore the default ● Settings listed multiple times ● Last one wins

View Your Current Settings ● Look in postgresql.conf ● Works if you follow best practice ● Not definitive! ● Show all, show <setting> ● Shows current value ● Watch out for session specific changes ● Select * from pg_settings ● Shows current value ● “source” column shows if session specific

Essential postgresql.conf parameters Getting connected Logging Sizing Memory WAL Management Checkpoint Management Vacuuming Leftovers

Getting 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 pool

Logging ● log_destination, log_directory, log_filename ● Find out where you are logging ● Might be good to log to different disk than data ● log_min_error_statement ● Make sure set to at least 'ERROR' ● log_line_prefix ● Generic recommendation: '%t:%r:%u@%d:[%p]: ' ● Timestamp, connecting host, username, database, pid

Sizing Memory ● shared_buffers ● Requires Restart! ● Most important setting for good performance ● Allocation of memory for Postgres caching ● ~ 20% of Total RAM (up to 2GB) ● 32GB of RAM, 1920MB probably ok ● Windows users probably want less ● May require adjusting kernel params (shmmax) ● http://www.postgresql.org/docs/current/interactive/kernel-resources.html

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 applications ● PGRAM = RAM needed for Postgres (shared_buffers, etc...) ● Guide for available memory, not an allocation

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 ● Values up to 128MB have been found useful

WAL Management ● wal_buffers ● Requires restart! ● needed for write-heavy databases ● 1024kB is good starting point ● wal_sync_method ● WAL is sync'd after every transaction ● fsync is ok default, but tuning can be helpful sometimes ● http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm ● synchronous_commit ● Can increase tps by large margin ● Allows data loss without corruption ● BAD: fsync=off ● GOOD: synchronous_commit=off ● Set per database, user, or transaction

Checkpoint Management ● checkpoint_segments ● Controls amount of data required per checkpoint ● Recommend default 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

Vacuum Management ● autovacuum ● Just do it! (Default on 8.3+) ● default_statistics_target ● Defines amount of data collected on tables for planner ● Important for good query planning ● 100 is a better default (might need further tuning)

Vacuum Management ● max_fsm_relations, max_fsm_pages ● Both Require Restart! ● Track dead tuples in database cluster ● fsm_relations – number of tables in all databases ● fsm_pages – number of pages with dead tuples in all databases ● “vacuum verbose” in postgres database INFO: free space map contains 5293 pages in 214 relations DETAIL: A total of 8528 page slots are in use (including overhead). 8528 page slots are required to track all free space. Current limits are: 204800 page slots, 1000 relations, using 1265 kB ● check_postgres script has a check for both ● http://bucardo.org/check_postgres/ ● Requires pg_freespacemap from contrib ● These go away in Postgres 8.4 (yay!)

Leftovers ● random_page_cost ● For server grade hardware, most set to 3 ● max_prepared_transactions ● Can be set to zero if not using prepared transactions ● constraint_exclusion ● Needed for table partitioning ● Good for setting at database or query level

The End ● Additional Resources ● http://www.postgresql.org/docs/8.3/interactive/runtime-config.html ● http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ● Slides ● http://www.xzilla.net/writings.html ● Xìe xìe

Add a comment

Related presentations

Presentación que realice en el Evento Nacional de Gobierno Abierto, realizado los ...

In this presentation we will describe our experience developing with a highly dyna...

Presentation to the LITA Forum 7th November 2014 Albuquerque, NM

Un recorrido por los cambios que nos generará el wearabletech en el futuro

Um paralelo entre as novidades & mercado em Wearable Computing e Tecnologias Assis...

Microsoft finally joins the smartwatch and fitness tracker game by introducing the...

Related pages

PostgreSQL - Community Help Wiki

pgAdmin III is a handy GUI for PostgreSQL, it is essential to beginners. ... Some settings changes in postgresql.conf require a full restart, ...
Read more

Essential PostgreSQL - DZone - Refcardz

Essential PostgreSQL Become a PostgreSQL Power User. Written by. ... postgresql.conf : Controls the listening port, IP, and default query planner settings, ...
Read more

Tuning Your PostgreSQL Server - PostgreSQL wiki

Tuning Your PostgreSQL Server. From ... auto.conf settings override postgresql.conf ... for an essential introduction to ...
Read more

Re: postgresql.conf (Proposed settings)

And I have read Stinson's PostgreSQL Essential Reference the same way. ... postgresql.conf (Proposed settings) at 2001-11-22 16:28:55 from Peter Eisentraut;
Read more

PostgreSQL/Configuration - Wikibooks, open books for an ...

PostgreSQL Configuration: MVCC: Contents. ... The main configuration file is postgresql.conf. ... Especially the 'archive_command' is essential.
Read more

annotated postgresql.conf - Varlena, LLC

Annotated postgresql.conf and Global User Configuration (GUC) Guide. Conf Setting. ... Essential for databases in a secure network with a DMZ, ...
Read more

Tuning Your PostgreSQL Server/pt BR - PostgreSQL wiki

Tuning Your PostgreSQL Server/pt BR. ... changes to the postgresql.conf do not take effect without ... See WAL reliability for an essential introduction to ...
Read more

PgUS Purchase Page | United States PostgreSQL Association

Steps for registering for PostgreSQL Conference East ... Understanding PostgreSQL memory usage, the postgresql.conf ... learn the essential details of ...
Read more