Message-ID: <4F9892AB.1010100@2ndQuadrant.com>
Date: Wed, 25 Apr 2012 20:11:23 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.24) Gecko/20111120 Icedove/3.1.16
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Configuration Recommendations
References: 
 <CANxH4hGW+RYXsB8BdAY9hoGzje26EapX1H6H-jrNZAMQKZ+hMQ@mail.gmail.com>
In-Reply-To: 
 <CANxH4hGW+RYXsB8BdAY9hoGzje26EapX1H6H-jrNZAMQKZ+hMQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 04/23/2012 10:56 PM, Jan Nielsen wrote:
> We are planning to rebuild our production 50GB PG 9.0 database serving
> our application platform on the new hardware below. The web-applications
> are 80/20 read/write and the data gateways are even mix 50/50
> read/write; one of the gateways nightly exports & imports ~20% of our
> data.

With enough RAM to hold the database, but that much churn in the nightly 
processing, you're most likely to run into VACUUM issues here.  The 
trigger point for autovacuum to kick off is at just around 20%, so you 
might see problems come and go based on the size of the changed set. 
You might consider making your own benchmark test out of a change like 
the gateway introduces.  Consider doing your own manual VACUUM or maybe 
even VACUUM FREEZE cleanup in sync with the nightly processing if you 
want that to be predictable.

> If there are "obviously correct" choices in PG configuration, this would
> be tremendously helpful information to me. I'm planning on using pgbench
> to test the configuration options.

The info at 
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server is as 
useful a checklist for getting started as any.  Note that pgbench is a 
very insensitive tool for testing configuration changes usefully. 
Results there will bounce around if you change shared_buffers and 
checkpoint_segments, but not much else.  And even the changes that test 
positive with it don't necessarily translate into better real-world 
performance.  For example, you might set shared_buffers to 8GB based on 
pgbench TPS numbers going up as it increases, only to find that allows 
way too much memory to get dirty between a checkpoint in 
production--resulting in slow periods on the server.

And many of the more interesting and tricky parameters to try and tweak 
in production, such as work_mem, don't even matter to what pgbench does. 
  It's easy to get lost trying pgbench tests without making clear 
forward progress for weeks.  Once you've validated the hardware seems to 
be delivering reasonable performance, consider running your own more 
application-like benchmarks instead.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com