pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
how much mem to give postgres?
11+ messages / 6 participants
[nested] [flat]

* how much mem to give postgres?
@ 2004-10-19 22:31  Josh Close <narshe@gmail.com>
  0 siblings, 3 replies; 11+ messages in thread

From: Josh Close @ 2004-10-19 22:31 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: how much mem to give postgres?
@ 2004-10-20 00:33  Simon Riggs <simon@2ndquadrant.com>
  parent: Josh Close <narshe@gmail.com>
  2 siblings, 1 reply; 11+ messages in thread

From: Simon Riggs @ 2004-10-20 00:33 UTC (permalink / raw)
  To: Josh Close <narshe@gmail.com>; 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
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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: how much mem to give postgres?
@ 2004-10-20 04:02  Josh Close <narshe@gmail.com>
  parent: Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Josh Close @ 2004-10-20 04:02 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: how much mem to give postgres?
@ 2004-10-20 04:35  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Josh Close <narshe@gmail.com>
  2 siblings, 1 reply; 11+ messages in thread

From: Tom Lane @ 2004-10-20 04:35 UTC (permalink / raw)
  To: Josh Close <narshe@gmail.com>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: how much mem to give postgres?
@ 2004-10-20 05:23  Josh Berkus <josh@agliodbs.com>
  parent: Josh Close <narshe@gmail.com>
  2 siblings, 1 reply; 11+ messages in thread

From: Josh Berkus @ 2004-10-20 05:23 UTC (permalink / raw)
  To: pgsql-performance@postgresql.org, Josh Close <narshe@gmail.com>

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: how much mem to give postgres?
@ 2004-10-20 13:36  Josh Close <narshe@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 11+ messages in thread

From: Josh Close @ 2004-10-20 13:36 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: how much mem to give postgres?
@ 2004-10-20 13:39  Josh Close <narshe@gmail.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Josh Close @ 2004-10-20 13:39 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: how much mem to give postgres?
@ 2004-10-20 14:07  Matt Clark <matt@ymogen.net>
  parent: Josh Close <narshe@gmail.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Matt Clark @ 2004-10-20 14:07 UTC (permalink / raw)
  To: Josh Close <narshe@gmail.com>; +Cc: pgsql-performance


>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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: how much mem to give postgres?
@ 2004-10-20 17:50  Steve Atkins <steve@blighty.com>
  parent: Matt Clark <matt@ymogen.net>
  0 siblings, 1 reply; 11+ messages in thread

From: Steve Atkins @ 2004-10-20 17:50 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: how much mem to give postgres?
@ 2004-10-20 18:16  Matt Clark <matt@ymogen.net>
  parent: Steve Atkins <steve@blighty.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Matt Clark @ 2004-10-20 18:16 UTC (permalink / raw)
  To: Steve Atkins <steve@blighty.com>; +Cc: pgsql-performance


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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: how much mem to give postgres?
@ 2004-10-20 18:39  Steve Atkins <steve@blighty.com>
  parent: Matt Clark <matt@ymogen.net>
  0 siblings, 0 replies; 11+ messages in thread

From: Steve Atkins @ 2004-10-20 18:39 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2004-10-20 18:39 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-19 22:31 how much mem to give postgres? Josh Close <narshe@gmail.com>
2004-10-20 00:33 ` Simon Riggs <simon@2ndquadrant.com>
2004-10-20 04:02   ` Josh Close <narshe@gmail.com>
2004-10-20 04:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-20 13:36   ` Josh Close <narshe@gmail.com>
2004-10-20 05:23 ` Josh Berkus <josh@agliodbs.com>
2004-10-20 13:39   ` Josh Close <narshe@gmail.com>
2004-10-20 14:07     ` Matt Clark <matt@ymogen.net>
2004-10-20 17:50       ` Steve Atkins <steve@blighty.com>
2004-10-20 18:16         ` Matt Clark <matt@ymogen.net>
2004-10-20 18:39           ` Steve Atkins <steve@blighty.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox