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

Re: Tuning

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: "John Parnefjord" <John(dot)Parnefjord(at)kib(dot)ki(dot)se>
Subject: Re: Tuning
Date: 2007-01-28 23:24:24
Message-ID: 200701281524.24317.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
John,

> -work_mem

Depends on the number of concurrent queries you expect to run and what size 
sorts you expect them to do.

> -maintenance_work_mem - 50% of the largest table?

Actually, in current code I've found that anything over 256mb doesn't actually 
get used.

> -shared_buffers - max value 50000

Actually, I need to update that.   On newer faster multi-core machines you may 
want to allocate up to 1GB of shared buffers.

> -effective_cache_size - max 2/3 of available ram, ie 24GB on the
> hardware described above

Yes.

> -shmmax - how large dare I set this value on dedicated postgres servers?

Set it to 2GB and you'll be covered.

> -checkpoint_segments - this is crucial as one of the server is
> transaction heavy

Well, it only helps you to raise this if you have a dedicated disk resource 
for the xlog.   Otherwise having more segments doesn't help you much.

> -vacuum_cost_delay

Try 200ms to start.

Also, set wal_buffers to 128.

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco

In response to

  • Tuning at 2007-01-26 11:28:19 from John Parnefjord

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2007-01-28 23:38:18
Subject: Re: Tuning
Previous:From: Bill MoranDate: 2007-01-28 22:11:18
Subject: Re: work_mem

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