2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Greg's PlanetPostgreSQL3 / Reducing the postgresql.conf, parameter at a time
2ndQuadrant Press

Reducing the postgresql.conf, parameter at a time

January 27, 2011/0 Comments/in Greg's PlanetPostgreSQL, PostgreSQL, United States News /by 2ndQuadrant Press



One of the more useful bits of
PostgreSQL documentation I ever worked on is Tuning Your PostgreSQL
Server
.  When that was written in the summer of 2008, a few months after the
release of PostgreSQL 8.3, it was hard to find any similar guide that
was both (relatively) concise and current. Since then, myself and
many other PostgreSQL contributors have helped keep that document up
to date as changes to PostgreSQL were made.

The interesting and helpful trend
during that period is that parameters keep disappearing from the set
of ones you need to worry about. In PostgreSQL 8.2, there was a long
list of parameters you likely needed to adjust for good performance
on a PostgreSQL server: shared_buffers, effective_cache_size,
checkpoint_segments, autovacuum, max_fsm_pages,
default_statistics_target, work_mem, wal_buffers, and (if using
partitioning) constraint_exclusion.

8.3 made autovacuum default to being
turned on with reasonable behavior, along with removing a few
background writer parameters that caused nothing but trouble (they
never made it onto the list). 8.4 removed the need for the two
max_fsm_* parameters, increased default_statistics_target to a much
better starting value, and made setting constraint_exclusion
unnecessary in the most common cases. That’s six less parameters
that you are likely to need to adjust.

Unfortunately version 9.0 only made
server configuration more complicated. And newer Linux kernels even
pushed default behavior backwards. Starting with Linux kernel
2.6.33, the default value picked for wal_sync_method changed to
open_datasync. This turns out to have terrible performance
implications for PostgreSQL, particularly when combined with the low
default setting for wal_buffers in the server.

But the march toward better default
behavior has recently resumed for what is eventually planned to be
PostgreSQL 9.1. During the last CommitFest, a patch originated Marti
Raudsepp to fix the wal_sync_method problem was committed
after some heavy arguments over what form that change should take.
Discovering that this behavior change broke PostgreSQL altogether
when running on ext4 with “data=journal” option helped
settle the right thing to do here by default.

Two parameters I don’t recommend
touching in most cases are commit_siblings and commit_delay,
artifacts of an older attempt to improve performance on systems with
slow commit times (which includes most systems that don’t have a
battery-backed write cache for accelerating that area). Nowadays
turning off the synchronous_commit parameter introduced in 8.3 is
much more likely to help here. While these are unlikely to improve
performance, people who do try setting them have suffered more than
necessary from the downsides of that decision. The worst-case
behavior here was improved considerably in a patch I wrote to optimize how the logic those parameters control executes.

And this week the latest parameter to
be effectively eliminated in most cases is wal_buffers. A change I
suggested was commited to set this automatically as a percentage of the size (about 3%)
allocated to the normally much larger shared_buffers parameters.
This sets the value of wal_buffers to the normal upper limit of its
effective range, 16MB, once you’re allocated at least 512MB to
shared_buffers. And if you’ve increased shared_buffers from its tiny
default at all, you’ll get a corresponding improvement in this
important commit performance parameter. You’ll have to go out of
your way to break the setting of this parameter to hit the bad
situations possible in earlier versions.

Having the amount of configuration you
need to do to the server by default get less complicated is always
worthwhile, and seeing parameters disappear form the critical list is
a welcome change. What’s next? The core issues with allocating
shared memory on UNIX-derived operating systems, particularly Linux,
make it very difficult to eliminate shared_buffers. And concerns
over the server taking over the system altogether limit the ability
to automatically set parameters like work_mem to the right range.
Some proposals for better managing the working memory pool have been
suggested, so that one might see some improvement.

The next parameter I have my eye on is
checkpoint_segments. After adding just extra logging in this area in
the last CommitFest, there are some improvements in this area nearing
commit now to actually improve checkpoint behavior. I hope to
eventually switch over checkpoint tuning to be strictly controlled
via time-oriented parameters, rather than requiring users to
understand the mechanics of how the write-ahead log works to tune the
system. There’s still too many ugly situations possible here to do
that in time for 9.1, but setting the segments count automatically is
feasible to target for 9.2.

Tags: PostgreSQL
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
How not to build PostgreSQL 9.0 extensions on RPM platforms Tuning Linux for low PostgreSQL latency
Scroll to top
×