Message-ID: <4E7008C7.4060904@ringerc.id.au>
Date: Wed, 14 Sep 2011 09:52:07 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:5.0) Gecko/20110707 Thunderbird/5.0
MIME-Version: 1.0
To: Carlo Stonebanks <stonec.register@sympatico.ca>
CC: kevin.grittner@wicourts.gov,
	Performance support Postgresql <pgsql-performance@postgresql.org>
Subject: Re: Migrated from 8.3 to 9.0 - need to update config (re-post)
References: <BLU153-W201587527064DA1B99CA1396010@phx.gbl>,
	<4E6A11AC0200002500041055@gw.wicourts.gov>
	<BLU153-W38BEC392CDDE8C835F000296050@phx.gbl>
In-Reply-To: <BLU153-W38BEC392CDDE8C835F000296050@phx.gbl>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 09/14/2011 02:56 AM, Carlo Stonebanks wrote:

> Even for 300 stateful applications that can remain connected for up to a
> week, continuously distilling data (imports)?

If they're all doing active work all that time you can still benefit 
from a pooler.

Say your server can service 50 connections at optimum speed, and any 
more result in reduced overall throughput. You have 300 apps with 
statements they want to run. Your pooler will basically queue them, so 
at any one time 50 are doing work and 250 are waiting for database 
access. This should _improve_ database throughput by reducing contention 
if 50 worker connections is your sweet spot. However, it will also 
increase latency for service for those workers because they may have to 
wait a while before their transaction runs, even though their 
transaction will complete much faster.

You'd probably want to pool at the transaction level, so once a client 
gets a connection it keeps it for the lifetime of that transaction and 
the connection is handed back to the pool when the transaction commits 
or rolls back.

>>  you want the controller configured for write-back (with automatic
>>  switch to write-through on low or failed battery, if possible).
>
> For performance or safety reasons? Since the sys admin thinks there's no
> performance benefit from this, I would like to be clear on why we should
> do this.

fsync!

If your workload is read-only, it won't help you much. If your workload 
is write-heavy or fairly balanced it'll make a HUGE difference, because 
fsync() on commit won't have to wait for disk I/O, only I/O to the RAID 
card's cache controller.

You can also play with commit_delay and synchronous_commit to trade 
guarantees of data persistence off against performance. Don't mind 
losing up to 5 mins of commits if you lose power? These options are for you.

Whatever you do, do NOT set fsync=off. It should be called "Eat my data 
if anything goes even slightly wrong=on"; it does have legitimate uses, 
but they're not yours.

>> > Can our particular setup benefit from changing the bgwriter values?
>>  Probably not. If you find that your interactive users have periods
>>  where queries seem to "freeze" for a few minutes at a time and then
>>  return to normal levels of performance, you might need to make this
>>  more aggressive.
>
> We actually experience this. Once again, remember the overwhelming use
> of the system is long-running import threads with continuous
> connections. Every now and then the imports behave as if they are
> suddenly taking a deep breath, slowing down. Sometimes, so much we
> cancel the import and restart (the imports pick up where they left off).

This could definitely be checkpointing issues. Enable checkpoint logging.

> What would the bg_writer settings be in this case?

You need to tune it for your workload I'm afraid. See the manual and 
mailing list discussions.

--
Craig Ringer