Thread: how much mem to give postgres?

how much mem to give postgres?

From
Josh Close
Date:
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.

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?

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 http://www.postgresql.org/docs/7.4/interactive/kernel-resources.html#SYSVIPC
but it's never quite high enough, so I just make sure it's above the
amount that the postgres log says it needs.

What else can I do to speed this server up? I'm running vacuum analyze
on the heavily updated/inserted/deleted db's once an hour, and doing a
full vacuum once a night. Should I change the vacuum mem setting at
all?

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.

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

Also, I'm using a 2.6.8.1 kernel with high mem enabled, so all the ram
is recognized.

Thanks.

-Josh

Re: how much mem to give postgres?

From
"Simon Riggs"
Date:
>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
http://www.postgresql.org/docs/7.4/interactive/kernel-resources.html#SYSVIPC
> 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


Re: how much mem to give postgres?

From
Josh Close
Date:
On Wed, 20 Oct 2004 01:33:16 +0100, Simon Riggs <simon@2ndquadrant.com> wrote:
> ....and using what version of PostgreSQL are you using? 8.0beta, I hope?

I'm using version 7.4.5.

> > 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.

Should I stick the rule of around 15% of mem then? I haven't found any
information on why you should use certain settings at all. I read
somewhere on the postgres site about using as much memory as possible,
but leave a little room for other processes. Whould that be an ok
theory? I'd kinda like to know why I should or shouldn't do something
like this.

-Josh

Re: how much mem to give postgres?

From
Tom Lane
Date:
Josh Close <narshe@gmail.com> writes:
> I'm trying to figure out what I need to do to get my postgres server
> moving faster. It's just crawling right now.

I suspect that fooling with shared_buffers is entirely the wrong tree
for you to be barking up.  My suggestion is to be looking at individual
queries that are slow, and seeing how to speed those up.  This might
involve adding indexes, or tweaking the query source, or adjusting
planner parameters, or several other things.  EXPLAIN ANALYZE is your
friend ...

            regards, tom lane

Re: how much mem to give postgres?

From
Josh Berkus
Date:
JJosh,

> 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.

There have been issues with Postgres+HT, especially on Linux 2.4.   Try
turning HT off if other tuning doesn't solve things.

Otherwise, see:
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: how much mem to give postgres?

From
Josh Close
Date:
On Wed, 20 Oct 2004 00:35:31 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I suspect that fooling with shared_buffers is entirely the wrong tree
> for you to be barking up.  My suggestion is to be looking at individual
> queries that are slow, and seeing how to speed those up.  This might
> involve adding indexes, or tweaking the query source, or adjusting
> planner parameters, or several other things.  EXPLAIN ANALYZE is your
> friend ...
>
>                         regards, tom lane

Only problem is, a "select count(1)" is taking a long time. Indexes
shouldn't matter with this since it's counting every row, right? The
tables are fairly well indexed also, I could probably add a few more.

If shared_buffers isn't the way to go ( you said 10k is the sweetspot
), then what about the effective_cache_size? I was suggested on the
general list about possibly setting that to 75% of ram.

Thanks.

-Josh

Re: how much mem to give postgres?

From
Josh Close
Date:
On Tue, 19 Oct 2004 22:23:24 -0700, Josh Berkus <josh@agliodbs.com> wrote:
> There have been issues with Postgres+HT, especially on Linux 2.4.   Try
> turning HT off if other tuning doesn't solve things.
>
> Otherwise, see:
> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

How would I turn that off? In the kernel config? Not too familiar with
that. I have a 2 proc xeon with 4 gigs of mem on the way for postgres,
so I hope HT isn't a problem. If HT is turned off, does it just not
use the other "half" of the processor? Or does the processor just work
as one unit?

Also, I'm taking a look at that site right now :)

-Josh

Re: how much mem to give postgres?

From
Matt Clark
Date:
>How would I turn that off? In the kernel config? Not too familiar with
>that. I have a 2 proc xeon with 4 gigs of mem on the way for postgres,
>so I hope HT isn't a problem. If HT is turned off, does it just not
>use the other "half" of the processor? Or does the processor just work
>as one unit?
>
>
You turn it off in the BIOS.  There is no 'other half', the processor is
just pretending to have two cores by shuffling registers around, which
gives maybe a 5-10% performance gain in certain multithreaded
situations.  <opinion>A hack to overcome marchitactural limitations due
to the overly long pipeline in the Prescott core.</opinion>.  Really of
most use for desktop interactivity rather than actual throughput.

M

Re: how much mem to give postgres?

From
Steve Atkins
Date:
On Wed, Oct 20, 2004 at 03:07:00PM +0100, Matt Clark wrote:

> You turn it off in the BIOS.  There is no 'other half', the processor is
> just pretending to have two cores by shuffling registers around, which
> gives maybe a 5-10% performance gain in certain multithreaded
> situations.


> <opinion>A hack to overcome marchitactural limitations due
> to the overly long pipeline in the Prescott core.</opinion>.  Really of
> most use for desktop interactivity rather than actual throughput.

<OT>
Hyperthreading is actually an excellent architectural feature that
can give significant performance gains when implemented well and used
for an appropriate workload under a decently HT aware OS.

IMO, typical RDBMS streams are not an obviously appropriate workload,
Intel didn't implement it particularly well and I don't think there
are any OSes that support it particularly well.
</OT>

But don't write off using it in the future, when it's been improved
at both the OS and the silicon levels.

Cheers,
  Steve

Re: how much mem to give postgres?

From
Matt Clark
Date:
><OT>
>Hyperthreading is actually an excellent architectural feature that
>can give significant performance gains when implemented well and used
>for an appropriate workload under a decently HT aware OS.
>
>IMO, typical RDBMS streams are not an obviously appropriate workload,
>Intel didn't implement it particularly well and I don't think there
>are any OSes that support it particularly well.
></OT>
>
>But don't write off using it in the future, when it's been improved
>at both the OS and the silicon levels.
>
>
>
You are quite right of course  - unfortunately the current Intel
implementation meets nearly none of these criteria!  As Rod Taylor
pointed out off-list, IBM's SMT implementation on the Power5 is vastly
superior.  Though he's also just told me that Sun is beating IBM on
price/performance for his workload, so who knows how reliable a chap he
is... ;-)

M

Re: how much mem to give postgres?

From
Steve Atkins
Date:
On Wed, Oct 20, 2004 at 07:16:18PM +0100, Matt Clark wrote:
> ><OT>
> >Hyperthreading is actually an excellent architectural feature that
> >can give significant performance gains when implemented well and used
> >for an appropriate workload under a decently HT aware OS.
> >
> >IMO, typical RDBMS streams are not an obviously appropriate workload,
> >Intel didn't implement it particularly well and I don't think there
> >are any OSes that support it particularly well.
> ></OT>
> >
> >But don't write off using it in the future, when it's been improved
> >at both the OS and the silicon levels.
> >
> >
> >
> You are quite right of course  - unfortunately the current Intel
> implementation meets nearly none of these criteria!

Indeed. And when I said "no OSes support it particularly well" I meant
the x86 SMT implementation, rather than SMT in general.

As Rod pointed out, AIX seems to have decent support and Power has a
very nice implementation, and the same is probably true for at least
one other OS/architecture implementation.

> As Rod Taylor pointed out off-list, IBM's SMT implementation on the
> Power5 is vastly superior.  Though he's also just told me that Sun
> is beating IBM on price/performance for his workload, so who knows
> how reliable a chap he is... ;-)

:)

Cheers,
  Steve