Thread: Optimizing postgresql.conf for dedicated windows server 2003 x64 standard edition

I installed PostgreSql 9.0 Win 64 standard distro.

Stack builder offered in end of installation does not contain
postgresql.conf optimization wizard.

How to optimize postgresql.conf  for 64 bit dedicated postgresql windows
2003 server or is default .conf file best for this?

Andrus.

2010/9/29 Andrus <kobruleht2@hot.ee>:
> I installed PostgreSql 9.0 Win 64 standard distro.
>
> Stack builder offered in end of installation does not contain
> postgresql.conf optimization wizard.
>
> How to optimize postgresql.conf  for 64 bit dedicated postgresql windows
> 2003 server or is default .conf file best for this?

It depends on how write-heavy the database will be, how much memory
you have to spare, and what sort of data you keep in it.  I'm
absolutely certain you will need to alter the config from the
defaults.  It's set up to be very conservative by default.

But take a look at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

2010/9/29 Andrus <kobruleht2@hot.ee>:
> I installed PostgreSql 9.0 Win 64 standard distro.
>
> Stack builder offered in end of installation does not contain
> postgresql.conf optimization wizard.
>

We updated it for Win64, but it looks like the stackbuilder catalog
wasn't updated to allow it to be downloaded on that platform. I've
fixed that - it should be available within an hour or so.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

> We updated it for Win64, but it looks like the stackbuilder catalog
> wasn't updated to allow it to be downloaded on that platform. I've
> fixed that - it should be available within an hour or so.

I just installed 9.0 x64

Is it sufficient to run Program files / PostgreSql 9.0 / Application Stack
Builder  from this installation to get it ?
Will it set optimal configuration so that manual editing of postgresql.conf
is not required ?

Andrus.

On Wed, Sep 29, 2010 at 11:37 AM, Andrus <kobruleht2@hot.ee> wrote:
>> We updated it for Win64, but it looks like the stackbuilder catalog
>> wasn't updated to allow it to be downloaded on that platform. I've
>> fixed that - it should be available within an hour or so.
>
> I just installed 9.0 x64
>
> Is it sufficient to run Program files / PostgreSql 9.0 / Application Stack
> Builder  from this installation to get it ?

Yes, in an hour or so.

> Will it set optimal configuration so that manual editing of postgresql.conf
> is not required ?

It will generate a starter configuration, that will be based on your
selected workload type and machine resources. That may be fine for
your needs, or you may need to tune it to more precisely meet the
needs of your actual workload. It doesn't claim to eliminate the need
for any future manual tuning.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

>> Will it set optimal configuration so that manual editing of
>> postgresql.conf
>> is not required ?

>It will generate a starter configuration, that will be based on your
>selected workload type and machine resources. That may be fine for
>your needs, or you may need to tune it to more precisely meet the
>needs of your actual workload. It doesn't claim to eliminate the need
>for any future manual tuning.

How to configure automatically Linux and Mac servers ?
How to configure remote servers where only port 5432 is open ?
Why pgAdmin postgresql.conf editor does not contain automatic configurator ?

Andrus.

On Fri, Oct 1, 2010 at 2:24 PM, Andrus <kobruleht2@hot.ee> wrote:
>>> Will it set optimal configuration so that manual editing of
>>> postgresql.conf
>>> is not required ?
>
>> It will generate a starter configuration, that will be based on your
>> selected workload type and machine resources. That may be fine for
>> your needs, or you may need to tune it to more precisely meet the
>> needs of your actual workload. It doesn't claim to eliminate the need
>> for any future manual tuning.
>
> How to configure automatically Linux and Mac servers ?

Run the tuning wizard on them?

> How to configure remote servers where only port 5432 is open ?

You can't.

> Why pgAdmin postgresql.conf editor does not contain automatic configurator ?

Because it can't examine the server spec remotely through libpq, and
because noone wrote it yet. And I'm not sure we'd want it in pgAdmin
anyway as such functionality is somewhat outside the realm of what
pgAdmin does.


--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

On 1/10/2010 9:24 PM, Andrus wrote:

> How to configure remote servers where only port 5432 is open ?

You can't. You need to be able to modify postgresql.conf .

> Why pgAdmin postgresql.conf editor does not contain automatic
> configurator ?

Because nobody has written one for PgAdmin. The EnterpriseDB
configuration assistant is a commercial product that EnterpriseDB are
letting people use for free. Nobody's stepped up to do the incredible
amount of testing and benchmarking required to create a configuraton
assistant for inclusion in PgAdmin.

Such tools are a guide at best; they won't just magically configure your
system perfectly. All databases need a degree of tuning to achieve
optimal performance, and PostgreSQL is no exception.

--
Craig Ringer

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

>> How to configure automatically Linux and Mac servers ?
>
> Run the tuning wizard on them?

Where to find installation packages for Debian and Ubuntu ?

Andrus.

On Fri, Oct 1, 2010 at 3:04 PM, Andrus <kobruleht2@hot.ee> wrote:
>>> How to configure automatically Linux and Mac servers ?
>>
>> Run the tuning wizard on them?
>
> Where to find installation packages for Debian and Ubuntu ?

Installers can be downloaded in StackBuilder.


--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

>> Where to find installation packages for Debian and Ubuntu ?
>
> Installers can be downloaded in StackBuilder.

How to run StackBuilder from Debian bash command line ?

Andrus.

On Fri, Oct 1, 2010 at 3:13 PM, Andrus <kobruleht2@hot.ee> wrote:
>>> Where to find installation packages for Debian and Ubuntu ?
>>
>> Installers can be downloaded in StackBuilder.
>
> How to run StackBuilder from Debian bash command line ?

On RHEL/CentOS, I'd use the following from a root command prompt:

/opt/PostgreSQL/9.0/stackbuilder/bin/stackbuilder &

I assume the same would work on Debian, though you might need to use
sudo as with Ubuntu. The installers aren't supported on Debian though,
so I don't know if it'll work.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

On Friday 01 October 2010 7:13:09 am Andrus wrote:
> >> Where to find installation packages for Debian and Ubuntu ?
> >
> > Installers can be downloaded in StackBuilder.
>
> How to run StackBuilder from Debian bash command line ?
>
> Andrus.

http://www.enterprisedb.com/learning/pginst_guide.do

--
Adrian Klaver
adrian.klaver@gmail.com

1. Tuning wizard adds autovacuum = true    line even if
autovacuum is turned on:

#autovacuum = on   # Enable autovacuum subprocess?  'on'
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2010/10/18 11:19:56
autovacuum = true   # Enable autovacuum subprocess?  'on'

2. Wizards adds values without units. It is difficult to figure out how much
memory changed settings really take.

How to force tuning wizard to add changes in real units if server supports
them, e.q. with  KB or MB suffixes like in normal postgresql.conf file?

Andrus.