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

Re: how much mem to give postgres?

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: "Josh Close" <narshe(at)gmail(dot)com>,"POSTGRES-PERFORMANCE" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: how much mem to give postgres?
Date: 2004-10-20 00:33:16
Message-ID: 01f301c4b63c$643340b0$6400a8c0@Nightingale (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
>Josh Close
> I'm trying to figure out what I need to do to get my postgres server
> moving faster. It's just crawling right now. It's on a p4 HT with 2
> gigs of mem.

....and using what version of PostgreSQL are you using? 8.0beta, I hope?

> I was thinking I need to increase the amount of shared buffers, but
> I've been told "the sweet spot for shared_buffers is usually on the
> order of 10000 buffers". I already have it set at 21,078. If you have,
> say 100 gigs of ram, are you supposed to still only give postgres
> 10,000?

Thats under test currently. My answer would be, "clearly not", others
differ, for varying reasons.

> Also, do I need to up the shmmax at all? I've used the formula "250 kB
> + 8.2 kB * shared_buffers + 14.2 kB * max_connections up to infinity"
> at
> but it's never quite high enough, so I just make sure it's above the
> amount that the postgres log says it needs.

shmmax isn't a tuning parameter for PostgreSQL, its just a limit. If you get
no error messages, then its high enough.

> Are there any other settings I should be concerned with? I've heard
> about the effective_cache_size setting, but I haven't seen anything on
> what the size should be.

wal_buffers if the databases are heavily updated.

> Any help would be great. This server is very very slow at the moment.

Try *very fast disks*, especially for the logs.

Best regards, Simon Riggs

In response to


pgsql-performance by date

Next:From: Brock HenryDate: 2004-10-20 01:53:37
Subject: Insert performance, what should I expect?
Previous:From: Pierre-Frédéric CaillaudDate: 2004-10-20 00:08:16
Subject: Re: Speeding up this function

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