Re: Feature proposal

On 26/08/2010 1:06 AM, Steve Clark wrote:
> On 08/25/2010 12:30 PM, Joshua D. Drake wrote:
>> On Wed, 2010-08-25 at 12:20 -0400, Eric Comeau wrote:
>>>
>>>> Without even changing any line of data or code in sql !
>>>>
>>>> Incredible, isn't it ?
>>>>
>>>
>>> Curious- what postgresql.conf settings did you change to improve it?
>>
>> The most obvious would be to turn fsync off, sychronous_commit off,
>> increase work_mem, increase checkpoint_timeout, increase wal_segments.
>>
>> JD
>>
>>>
>>>
>>>
>>
> can these be changed on the fly via set commands or does the config file
> have to be changed and postgres stopped and restarted.

First: Many options can be changed by editing the config file then
telling the postmaster to reload its configuration, rather that
restarting the postmaster. See pg_ctl.

As for the specific options:

Checkpoint and WAL tuning is necessary and important in any real
postgresql instance under load, and it's quite safe to adjust the
checkpoint timeouts and wal segment counts to suit your needs. You'll
need a restart to change the number of wal segments; I'm not so sure
about the checkpoint timeout.

You can't change fsync without a config file edit and a restart. You
should **NEVER** be using fsync=off with data you cannot afford to lose,
so it's a good thing in a way. You might use it to help initially load a
database with bulk data, but fsync should be turned back on and the
database restarted before you start actually using it. fsync=off is
**NOT SAFE**.

synchronous_commit also has effects on data safety. It permits the loss
of transactions committed within the commit delay interval if the server
crashes. If you turn it on, you need to decide how much recent work you
can afford to lose if the database crashes. Not sure if it can be
applied with a reload or whether it requires a full server restart.

So: if you don't know exactly what you're doing, leave fsync alone.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/