Skip site navigation (1) Skip section navigation (2)

Re: How to configure a read-only database server?

From: Greg Smith <greg(at)2ndQuadrant(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: How to configure a read-only database server?
Date: 2011-04-19 14:30:48
Message-ID: (view raw or whole thread)
Lists: pgsql-performance
On 04/18/2011 06:08 PM, Stefan Keller wrote:
> * What about wal_level and archive_mode?

Presumably you don't care about either of these.  wal_level=minimal, 

The other non-obvious thing you should do in this situation is do all 
the database maintenance in one big run after the data is loaded, 
something like:


Otherwise you will still have some trickle of write-activity going on, 
not always efficiently, despite being in read-only mode.  It's because 
of what's referred to as Hint Bits:

VACUUMing everything will clean those us, and freezing everything makes 
sure there's no old transactions to concerned about that might kick off 
anti-wraparound autovacuum.

The only other thing you probably want to do is set checkpoint_segments 
to a big number.  Shouldn't matter normally, but when doing this freeze 
operation it will help that execute quickly.  You want a lower 
maintenance_work_mem on a read-only system than the master too, possibly 
a higher shared_buffers as well.  It's all pretty subtle beyond the big 
parameters you already identified.

Greg Smith   2ndQuadrant US    greg(at)2ndQuadrant(dot)com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support
"PostgreSQL 9.0 High Performance":

In response to

pgsql-performance by date

Next:From: Josh BerkusDate: 2011-04-20 01:50:40
Subject: Re: Shouldn't we have a way to avoid "risky" plans?
Previous:From: Robert HaasDate: 2011-04-19 14:29:13
Subject: Re: Shouldn't we have a way to avoid "risky" plans?

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group