pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVersion 7 question
12+ messages / 7 participants
[nested] [flat]
* Version 7 question
@ 2003-07-01 12:10 Hilary Forbes <hforbes@dmr.co.uk>
2003-07-01 12:17 ` Re: Version 7 question Michael Mattox <michael.mattox@verideon.com>
2003-07-01 12:52 ` Re: Version 7 question Robert Treat <xzilla@users.sourceforge.net>
2003-07-02 01:37 ` Re: Version 7 question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 3 replies; 12+ messages in thread
From: Hilary Forbes @ 2003-07-01 12:10 UTC (permalink / raw)
To: pgsql-performance
I'm just trying to improve performance on version 7 before doing some tests and hopefully upgrading to 7.3.
At the moment we have
B=64 (no of shared buffers)
N=32 (no of connections)
in postmaster.opt which I take it is the equivalent of the new postgresql.conf file.
From all that is being written about later versions I suspect that this is far too low. Would I be fairly safe in making the no of shared buffers larger? Also is there an equivalent of effective_cache_size that I can set for version 7?
Many thanks in advance
Hilary
Hilary Forbes
-------------
DMR Computer Limited: http://www.dmr.co.uk/
Direct line: 01689 889950
Switchboard: (44) 1689 860000 Fax: (44) 1689 860330
E-mail: hforbes@dmr.co.uk
**********************************************************
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Version 7 question
2003-07-01 12:10 Version 7 question Hilary Forbes <hforbes@dmr.co.uk>
@ 2003-07-01 12:17 ` Michael Mattox <michael.mattox@verideon.com>
2003-07-01 12:55 ` Re: Version 7 question scott.marlowe <scott.marlowe@ihs.com>
2 siblings, 1 reply; 12+ messages in thread
From: Michael Mattox @ 2003-07-01 12:17 UTC (permalink / raw)
To: Hilary Forbes <hforbes@dmr.co.uk>; pgsql-performance
I have my shared buffers at 8192 and my effective cache at 64000 (which is
500 megs). Depends a lot on how much RAM you have. I have 1.5 gigs and
I've been asking my boss for another 512megs for over a month now. I have
no idea if my buffers are too high/low.
Michael
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Hilary
> Forbes
> Sent: Tuesday, July 01, 2003 2:10 PM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Version 7 question
>
>
> I'm just trying to improve performance on version 7 before doing
> some tests and hopefully upgrading to 7.3.
>
> At the moment we have
> B=64 (no of shared buffers)
> N=32 (no of connections)
> in postmaster.opt which I take it is the equivalent of the new
> postgresql.conf file.
>
> From all that is being written about later versions I suspect
> that this is far too low. Would I be fairly safe in making the
> no of shared buffers larger? Also is there an equivalent of
> effective_cache_size that I can set for version 7?
>
> Many thanks in advance
> Hilary
>
>
>
>
> Hilary Forbes
> -------------
> DMR Computer Limited: http://www.dmr.co.uk/
> Direct line: 01689 889950
> Switchboard: (44) 1689 860000 Fax: (44) 1689 860330
> E-mail: hforbes@dmr.co.uk
>
> **********************************************************
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Version 7 question
2003-07-01 12:10 Version 7 question Hilary Forbes <hforbes@dmr.co.uk>
2003-07-01 12:17 ` Re: Version 7 question Michael Mattox <michael.mattox@verideon.com>
@ 2003-07-01 12:55 ` scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 0 replies; 12+ messages in thread
From: scott.marlowe @ 2003-07-01 12:55 UTC (permalink / raw)
To: Michael Mattox <michael.mattox@verideon.com>; +Cc: Hilary Forbes <hforbes@dmr.co.uk>; pgsql-performance
8192 is only 64 megs of RAM, not much, but a good number. Keep in mind
that the kernel tends to be better at buffering huge amounts of disk,
while postgresql is better left to use buffers that are large enough for
the current working set (i.e. not your whole database, just the largest
amount of data you're slinging about on a regular basis in one query.)
On a machine with 1.5 gig of RAM, I've found settings as high as 32768
(256 megs of ram) to run well, but anything over that doesn't help. Of
course, we don't toss around more than a hundred meg or so at a time. If
our result sets were in the gigabyte range, I'd A: want more memory and B:
Give more of it to postgresql.
The original poster was, I believe running 7.0.x, which is way old, so no,
I don't think there was an equivalent of effective_cache_size in that
version. Upgrading would be far easier than performance tuning 7.0. since
the query planner was much simpler (i.e. more prone to make bad decisions)
in 7.0.
On Tue, 1 Jul 2003, Michael Mattox wrote:
> I have my shared buffers at 8192 and my effective cache at 64000 (which is
> 500 megs). Depends a lot on how much RAM you have. I have 1.5 gigs and
> I've been asking my boss for another 512megs for over a month now. I have
> no idea if my buffers are too high/low.
>
> Michael
>
> > -----Original Message-----
> > From: pgsql-performance-owner@postgresql.org
> > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Hilary
> > Forbes
> > Sent: Tuesday, July 01, 2003 2:10 PM
> > To: pgsql-performance@postgresql.org
> > Subject: [PERFORM] Version 7 question
> >
> >
> > I'm just trying to improve performance on version 7 before doing
> > some tests and hopefully upgrading to 7.3.
> >
> > At the moment we have
> > B=64 (no of shared buffers)
> > N=32 (no of connections)
> > in postmaster.opt which I take it is the equivalent of the new
> > postgresql.conf file.
> >
> > From all that is being written about later versions I suspect
> > that this is far too low. Would I be fairly safe in making the
> > no of shared buffers larger? Also is there an equivalent of
> > effective_cache_size that I can set for version 7?
> >
> > Many thanks in advance
> > Hilary
> >
> >
> >
> >
> > Hilary Forbes
> > -------------
> > DMR Computer Limited: http://www.dmr.co.uk/
> > Direct line: 01689 889950
> > Switchboard: (44) 1689 860000 Fax: (44) 1689 860330
> > E-mail: hforbes@dmr.co.uk
> >
> > **********************************************************
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Version 7 question
2003-07-01 12:10 Version 7 question Hilary Forbes <hforbes@dmr.co.uk>
@ 2003-07-01 12:52 ` Robert Treat <xzilla@users.sourceforge.net>
2003-07-01 13:02 ` Re: Version 7 question Michael Mattox <michael.mattox@verideon.com>
2 siblings, 1 reply; 12+ messages in thread
From: Robert Treat @ 2003-07-01 12:52 UTC (permalink / raw)
To: Hilary Forbes <hforbes@dmr.co.uk>; +Cc: pgsql-performance
On Tue, 2003-07-01 at 08:10, Hilary Forbes wrote:
> I'm just trying to improve performance on version 7 before doing some
tests and hopefully upgrading to 7.3.
>
> At the moment we have
> B=64 (no of shared buffers)
> N=32 (no of connections)
> in postmaster.opt which I take it is the equivalent of the new
postgresql.conf file.
>
> From all that is being written about later versions I suspect that
>this is far too low. Would I be fairly safe in making the no of shared
>buffers larger?
yes, I'd say start with about 25% of RAM, then adjust from there. If 25%
takes you over your SHMMAX then start at your SHMMAX.
>Also is there an equivalent of effective_cache_size that I can set for
>version 7?
>
If by 7 your mean 7.0.x then I don't believe so, been awhile though, I
could be wrong. IMHO no amount of tuning you can do in 7.0 would be as
effective as an upgrade, after setting your shared buffers up, I'd put
your efforts into upgrading. (Note Beta test for 7.4 starts in 2 weeks)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Version 7 question
2003-07-01 12:10 Version 7 question Hilary Forbes <hforbes@dmr.co.uk>
2003-07-01 12:52 ` Re: Version 7 question Robert Treat <xzilla@users.sourceforge.net>
@ 2003-07-01 13:02 ` Michael Mattox <michael.mattox@verideon.com>
2003-07-01 18:01 ` Re: Version 7 question Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 1 reply; 12+ messages in thread
From: Michael Mattox @ 2003-07-01 13:02 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-performance
> yes, I'd say start with about 25% of RAM, then adjust from there. If 25%
> takes you over your SHMMAX then start at your SHMMAX.
You're the first person I've seen to suggest that many buffers. I've read
that too many can slow down performance. I have 1.5 gigs of RAM on my
server but I'm also running a few other java programs that take up probably
500 megs total of memory, leaving me 1gig for Postgres. Should I set my
shared buffers to be 25% of 1gig? That would be 32768. Then what should my
effective cache be? Right now I have it set to 64000 which would be
512megs. Between the buffers and cache that'd be a total of 768megs,
leaving approximately 768 for my other java apps & the OS.
Sounds reasonable to me.
Michael
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Version 7 question
2003-07-01 12:10 Version 7 question Hilary Forbes <hforbes@dmr.co.uk>
2003-07-01 12:52 ` Re: Version 7 question Robert Treat <xzilla@users.sourceforge.net>
2003-07-01 13:02 ` Re: Version 7 question Michael Mattox <michael.mattox@verideon.com>
@ 2003-07-01 18:01 ` Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 0 replies; 12+ messages in thread
From: Manfred Koizar @ 2003-07-01 18:01 UTC (permalink / raw)
To: michael.mattox@verideon.com; +Cc: Robert Treat <xzilla@users.sourceforge.net>; pgsql-performance
On Tue, 1 Jul 2003 15:02:21 +0200, "Michael Mattox"
<michael.mattox@verideon.com> wrote:
>I have 1.5 gigs of RAM on my
>server but I'm also running a few other java programs that take up probably
>500 megs total of memory, leaving me 1gig for Postgres. Should I set my
>shared buffers to be 25% of 1gig? That would be 32768. Then what should my
>effective cache be? Right now I have it set to 64000 which would be
>512megs. Between the buffers and cache that'd be a total of 768megs,
>leaving approximately 768 for my other java apps & the OS.
Michael, by setting effective_cache_size you do not allocate anything.
This configuration variable is just a *hint* to the planner how much
RAM is used for caching on your system (as shown by top or free).
Servus
Manfred
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Version 7 question
2003-07-01 12:10 Version 7 question Hilary Forbes <hforbes@dmr.co.uk>
@ 2003-07-02 01:37 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2 siblings, 0 replies; 12+ messages in thread
From: Christopher Kings-Lynne @ 2003-07-02 01:37 UTC (permalink / raw)
To: pgsql-performance; Hilary Forbes <hforbes@dmr.co.uk>
Hi Hillary,
I'd suggest around 1000 to 2000 shared buffers and bump your max connections
to at least 64.
Make sure you're kernel allowed enough shared memory for the above (2000 *
8k = 16MB)
Chris
----- Original Message -----
From: "Hilary Forbes" <hforbes@dmr.co.uk>
To: <pgsql-performance@postgresql.org>
Sent: Tuesday, July 01, 2003 8:10 PM
Subject: [PERFORM] Version 7 question
> I'm just trying to improve performance on version 7 before doing some
tests and hopefully upgrading to 7.3.
>
> At the moment we have
> B=64 (no of shared buffers)
> N=32 (no of connections)
> in postmaster.opt which I take it is the equivalent of the new
postgresql.conf file.
>
> From all that is being written about later versions I suspect that this
is far too low. Would I be fairly safe in making the no of shared buffers
larger? Also is there an equivalent of effective_cache_size that I can set
for version 7?
>
> Many thanks in advance
> Hilary
>
>
>
>
> Hilary Forbes
> -------------
> DMR Computer Limited: http://www.dmr.co.uk/
> Direct line: 01689 889950
> Switchboard: (44) 1689 860000 Fax: (44) 1689 860330
> E-mail: hforbes@dmr.co.uk
>
> **********************************************************
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* FW: Version 7 question
@ 2003-07-01 13:06 Howard Oblowitz <HowardO@LEWIS-STORES.com>
2003-07-01 13:19 ` Re: FW: Version 7 question scott.marlowe <scott.marlowe@ihs.com>
2003-07-01 13:55 ` Re: Version 7 question Michael Mattox <michael.mattox@verideon.com>
0 siblings, 2 replies; 12+ messages in thread
From: Howard Oblowitz @ 2003-07-01 13:06 UTC (permalink / raw)
To: pgsql-performance
What would be the best value range for effective_cache_size
on Postgres 7.3.2, assuming say 1.5 GB of RAM and
shared_buffers set to 8192, and shmmax set to 750mb?
And what are the most important factors one should take
into account in determining the value?
> -----Original Message-----
> From: scott.marlowe [SMTP:scott.marlowe@ihs.com]
> Sent: 01 July 2003 02:56
> To: Michael Mattox
> Cc: Hilary Forbes; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Version 7 question
>
> 8192 is only 64 megs of RAM, not much, but a good number. Keep in mind
> that the kernel tends to be better at buffering huge amounts of disk,
> while postgresql is better left to use buffers that are large enough for
> the current working set (i.e. not your whole database, just the largest
> amount of data you're slinging about on a regular basis in one query.)
>
> On a machine with 1.5 gig of RAM, I've found settings as high as 32768
> (256 megs of ram) to run well, but anything over that doesn't help. Of
> course, we don't toss around more than a hundred meg or so at a time. If
>
> our result sets were in the gigabyte range, I'd A: want more memory and B:
>
> Give more of it to postgresql.
>
> The original poster was, I believe running 7.0.x, which is way old, so no,
>
> I don't think there was an equivalent of effective_cache_size in that
> version. Upgrading would be far easier than performance tuning 7.0. since
>
> the query planner was much simpler (i.e. more prone to make bad decisions)
>
> in 7.0.
>
> On Tue, 1 Jul 2003, Michael Mattox wrote:
>
> > I have my shared buffers at 8192 and my effective cache at 64000 (which
> is
> > 500 megs). Depends a lot on how much RAM you have. I have 1.5 gigs and
> > I've been asking my boss for another 512megs for over a month now. I
> have
> > no idea if my buffers are too high/low.
> >
> > Michael
> >
> > > -----Original Message-----
> > > From: pgsql-performance-owner@postgresql.org
> > > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Hilary
> > > Forbes
> > > Sent: Tuesday, July 01, 2003 2:10 PM
> > > To: pgsql-performance@postgresql.org
> > > Subject: [PERFORM] Version 7 question
> > >
> > >
> > > I'm just trying to improve performance on version 7 before doing
> > > some tests and hopefully upgrading to 7.3.
> > >
> > > At the moment we have
> > > B=64 (no of shared buffers)
> > > N=32 (no of connections)
> > > in postmaster.opt which I take it is the equivalent of the new
> > > postgresql.conf file.
> > >
> > > From all that is being written about later versions I suspect
> > > that this is far too low. Would I be fairly safe in making the
> > > no of shared buffers larger? Also is there an equivalent of
> > > effective_cache_size that I can set for version 7?
> > >
> > > Many thanks in advance
> > > Hilary
> > >
> > >
> > >
> > >
> > > Hilary Forbes
> > > -------------
> > > DMR Computer Limited: http://www.dmr.co.uk/
> > > Direct line: 01689 889950
> > > Switchboard: (44) 1689 860000 Fax: (44) 1689 860330
> > > E-mail: hforbes@dmr.co.uk
> > >
> > > **********************************************************
> > >
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 5: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/docs/faqs/FAQ.html
> > >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: FW: Version 7 question
2003-07-01 13:06 FW: Version 7 question Howard Oblowitz <HowardO@LEWIS-STORES.com>
@ 2003-07-01 13:19 ` scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 0 replies; 12+ messages in thread
From: scott.marlowe @ 2003-07-01 13:19 UTC (permalink / raw)
To: Howard Oblowitz <HowardO@LEWIS-STORES.com>; +Cc: pgsql-performance
The best way to set it is to let the machine run under normal load for a
while, then look at the cache / buffer usage using top (or some other
program, top works fine for this).
My server with 1.5 gig ram shows 862328K cache right now. So, just divide
by page size (usually 8192) and I get ~ 100,000 blocks.
On Tue, 1 Jul 2003, Howard Oblowitz wrote:
> What would be the best value range for effective_cache_size
> on Postgres 7.3.2, assuming say 1.5 GB of RAM and
> shared_buffers set to 8192, and shmmax set to 750mb?
>
> And what are the most important factors one should take
> into account in determining the value?
>
>
>
> > -----Original Message-----
> > From: scott.marlowe [SMTP:scott.marlowe@ihs.com]
> > Sent: 01 July 2003 02:56
> > To: Michael Mattox
> > Cc: Hilary Forbes; pgsql-performance@postgresql.org
> > Subject: Re: [PERFORM] Version 7 question
> >
> > 8192 is only 64 megs of RAM, not much, but a good number. Keep in mind
> > that the kernel tends to be better at buffering huge amounts of disk,
> > while postgresql is better left to use buffers that are large enough for
> > the current working set (i.e. not your whole database, just the largest
> > amount of data you're slinging about on a regular basis in one query.)
> >
> > On a machine with 1.5 gig of RAM, I've found settings as high as 32768
> > (256 megs of ram) to run well, but anything over that doesn't help. Of
> > course, we don't toss around more than a hundred meg or so at a time. If
> >
> > our result sets were in the gigabyte range, I'd A: want more memory and B:
> >
> > Give more of it to postgresql.
> >
> > The original poster was, I believe running 7.0.x, which is way old, so no,
> >
> > I don't think there was an equivalent of effective_cache_size in that
> > version. Upgrading would be far easier than performance tuning 7.0. since
> >
> > the query planner was much simpler (i.e. more prone to make bad decisions)
> >
> > in 7.0.
> >
> > On Tue, 1 Jul 2003, Michael Mattox wrote:
> >
> > > I have my shared buffers at 8192 and my effective cache at 64000 (which
> > is
> > > 500 megs). Depends a lot on how much RAM you have. I have 1.5 gigs and
> > > I've been asking my boss for another 512megs for over a month now. I
> > have
> > > no idea if my buffers are too high/low.
> > >
> > > Michael
> > >
> > > > -----Original Message-----
> > > > From: pgsql-performance-owner@postgresql.org
> > > > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Hilary
> > > > Forbes
> > > > Sent: Tuesday, July 01, 2003 2:10 PM
> > > > To: pgsql-performance@postgresql.org
> > > > Subject: [PERFORM] Version 7 question
> > > >
> > > >
> > > > I'm just trying to improve performance on version 7 before doing
> > > > some tests and hopefully upgrading to 7.3.
> > > >
> > > > At the moment we have
> > > > B=64 (no of shared buffers)
> > > > N=32 (no of connections)
> > > > in postmaster.opt which I take it is the equivalent of the new
> > > > postgresql.conf file.
> > > >
> > > > From all that is being written about later versions I suspect
> > > > that this is far too low. Would I be fairly safe in making the
> > > > no of shared buffers larger? Also is there an equivalent of
> > > > effective_cache_size that I can set for version 7?
> > > >
> > > > Many thanks in advance
> > > > Hilary
> > > >
> > > >
> > > >
> > > >
> > > > Hilary Forbes
> > > > -------------
> > > > DMR Computer Limited: http://www.dmr.co.uk/
> > > > Direct line: 01689 889950
> > > > Switchboard: (44) 1689 860000 Fax: (44) 1689 860330
> > > > E-mail: hforbes@dmr.co.uk
> > > >
> > > > **********************************************************
> > > >
> > > >
> > > > ---------------------------(end of
> > broadcast)---------------------------
> > > > TIP 5: Have you checked our extensive FAQ?
> > > >
> > > > http://www.postgresql.org/docs/faqs/FAQ.html
> > > >
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 5: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/docs/faqs/FAQ.html
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Version 7 question
2003-07-01 13:06 FW: Version 7 question Howard Oblowitz <HowardO@LEWIS-STORES.com>
@ 2003-07-01 13:55 ` Michael Mattox <michael.mattox@verideon.com>
2003-07-01 14:19 ` Re: Version 7 question scott.marlowe <scott.marlowe@ihs.com>
2003-07-01 14:22 ` Re: Version 7 question scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 2 replies; 12+ messages in thread
From: Michael Mattox @ 2003-07-01 13:55 UTC (permalink / raw)
To: Howard Oblowitz <HowardO@LEWIS-STORES.com>; pgsql-performance
My understanding is to use as much effect cache as possible, so figure out
how much ram you need for your other applications & OS and then give the
rest to postgres as effective cache.
What I learned to day is the shared_buffers 25% of RAM guideline.
Michael
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Howard
> Oblowitz
> Sent: Tuesday, July 01, 2003 3:06 PM
> To: pgsql-performance@postgresql.org
> Subject: FW: [PERFORM] Version 7 question
>
>
> What would be the best value range for effective_cache_size
> on Postgres 7.3.2, assuming say 1.5 GB of RAM and
> shared_buffers set to 8192, and shmmax set to 750mb?
>
> And what are the most important factors one should take
> into account in determining the value?
>
>
>
> > -----Original Message-----
> > From: scott.marlowe [SMTP:scott.marlowe@ihs.com]
> > Sent: 01 July 2003 02:56
> > To: Michael Mattox
> > Cc: Hilary Forbes; pgsql-performance@postgresql.org
> > Subject: Re: [PERFORM] Version 7 question
> >
> > 8192 is only 64 megs of RAM, not much, but a good number. Keep in mind
> > that the kernel tends to be better at buffering huge amounts of disk,
> > while postgresql is better left to use buffers that are large
> enough for
> > the current working set (i.e. not your whole database, just the largest
> > amount of data you're slinging about on a regular basis in one query.)
> >
> > On a machine with 1.5 gig of RAM, I've found settings as high as 32768
> > (256 megs of ram) to run well, but anything over that doesn't help. Of
> > course, we don't toss around more than a hundred meg or so at a
> time. If
> >
> > our result sets were in the gigabyte range, I'd A: want more
> memory and B:
> >
> > Give more of it to postgresql.
> >
> > The original poster was, I believe running 7.0.x, which is way
> old, so no,
> >
> > I don't think there was an equivalent of effective_cache_size in that
> > version. Upgrading would be far easier than performance tuning
> 7.0. since
> >
> > the query planner was much simpler (i.e. more prone to make bad
> decisions)
> >
> > in 7.0.
> >
> > On Tue, 1 Jul 2003, Michael Mattox wrote:
> >
> > > I have my shared buffers at 8192 and my effective cache at
> 64000 (which
> > is
> > > 500 megs). Depends a lot on how much RAM you have. I have
> 1.5 gigs and
> > > I've been asking my boss for another 512megs for over a month now. I
> > have
> > > no idea if my buffers are too high/low.
> > >
> > > Michael
> > >
> > > > -----Original Message-----
> > > > From: pgsql-performance-owner@postgresql.org
> > > > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Hilary
> > > > Forbes
> > > > Sent: Tuesday, July 01, 2003 2:10 PM
> > > > To: pgsql-performance@postgresql.org
> > > > Subject: [PERFORM] Version 7 question
> > > >
> > > >
> > > > I'm just trying to improve performance on version 7 before doing
> > > > some tests and hopefully upgrading to 7.3.
> > > >
> > > > At the moment we have
> > > > B=64 (no of shared buffers)
> > > > N=32 (no of connections)
> > > > in postmaster.opt which I take it is the equivalent of the new
> > > > postgresql.conf file.
> > > >
> > > > From all that is being written about later versions I suspect
> > > > that this is far too low. Would I be fairly safe in making the
> > > > no of shared buffers larger? Also is there an equivalent of
> > > > effective_cache_size that I can set for version 7?
> > > >
> > > > Many thanks in advance
> > > > Hilary
> > > >
> > > >
> > > >
> > > >
> > > > Hilary Forbes
> > > > -------------
> > > > DMR Computer Limited: http://www.dmr.co.uk/
> > > > Direct line: 01689 889950
> > > > Switchboard: (44) 1689 860000 Fax: (44) 1689 860330
> > > > E-mail: hforbes@dmr.co.uk
> > > >
> > > > **********************************************************
> > > >
> > > >
> > > > ---------------------------(end of
> > broadcast)---------------------------
> > > > TIP 5: Have you checked our extensive FAQ?
> > > >
> > > > http://www.postgresql.org/docs/faqs/FAQ.html
> > > >
> > >
> > >
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 5: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/docs/faqs/FAQ.html
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Version 7 question
2003-07-01 13:06 FW: Version 7 question Howard Oblowitz <HowardO@LEWIS-STORES.com>
2003-07-01 13:55 ` Re: Version 7 question Michael Mattox <michael.mattox@verideon.com>
@ 2003-07-01 14:19 ` scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 0 replies; 12+ messages in thread
From: scott.marlowe @ 2003-07-01 14:19 UTC (permalink / raw)
To: Michael Mattox <michael.mattox@verideon.com>; +Cc: Howard Oblowitz <HowardO@LEWIS-STORES.com>; pgsql-performance
I think you're confusing effect_cache_size with shared_buffers.
effective_cache_size tells the planner about how much disk cache the OS is
using for postgresql behind its back, so to speak.
On Tue, 1 Jul 2003, Michael Mattox wrote:
> My understanding is to use as much effect cache as possible, so figure out
> how much ram you need for your other applications & OS and then give the
> rest to postgres as effective cache.
>
> What I learned to day is the shared_buffers 25% of RAM guideline.
>
> Michael
>
> > -----Original Message-----
> > From: pgsql-performance-owner@postgresql.org
> > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Howard
> > Oblowitz
> > Sent: Tuesday, July 01, 2003 3:06 PM
> > To: pgsql-performance@postgresql.org
> > Subject: FW: [PERFORM] Version 7 question
> >
> >
> > What would be the best value range for effective_cache_size
> > on Postgres 7.3.2, assuming say 1.5 GB of RAM and
> > shared_buffers set to 8192, and shmmax set to 750mb?
> >
> > And what are the most important factors one should take
> > into account in determining the value?
> >
> >
> >
> > > -----Original Message-----
> > > From: scott.marlowe [SMTP:scott.marlowe@ihs.com]
> > > Sent: 01 July 2003 02:56
> > > To: Michael Mattox
> > > Cc: Hilary Forbes; pgsql-performance@postgresql.org
> > > Subject: Re: [PERFORM] Version 7 question
> > >
> > > 8192 is only 64 megs of RAM, not much, but a good number. Keep in mind
> > > that the kernel tends to be better at buffering huge amounts of disk,
> > > while postgresql is better left to use buffers that are large
> > enough for
> > > the current working set (i.e. not your whole database, just the largest
> > > amount of data you're slinging about on a regular basis in one query.)
> > >
> > > On a machine with 1.5 gig of RAM, I've found settings as high as 32768
> > > (256 megs of ram) to run well, but anything over that doesn't help. Of
> > > course, we don't toss around more than a hundred meg or so at a
> > time. If
> > >
> > > our result sets were in the gigabyte range, I'd A: want more
> > memory and B:
> > >
> > > Give more of it to postgresql.
> > >
> > > The original poster was, I believe running 7.0.x, which is way
> > old, so no,
> > >
> > > I don't think there was an equivalent of effective_cache_size in that
> > > version. Upgrading would be far easier than performance tuning
> > 7.0. since
> > >
> > > the query planner was much simpler (i.e. more prone to make bad
> > decisions)
> > >
> > > in 7.0.
> > >
> > > On Tue, 1 Jul 2003, Michael Mattox wrote:
> > >
> > > > I have my shared buffers at 8192 and my effective cache at
> > 64000 (which
> > > is
> > > > 500 megs). Depends a lot on how much RAM you have. I have
> > 1.5 gigs and
> > > > I've been asking my boss for another 512megs for over a month now. I
> > > have
> > > > no idea if my buffers are too high/low.
> > > >
> > > > Michael
> > > >
> > > > > -----Original Message-----
> > > > > From: pgsql-performance-owner@postgresql.org
> > > > > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Hilary
> > > > > Forbes
> > > > > Sent: Tuesday, July 01, 2003 2:10 PM
> > > > > To: pgsql-performance@postgresql.org
> > > > > Subject: [PERFORM] Version 7 question
> > > > >
> > > > >
> > > > > I'm just trying to improve performance on version 7 before doing
> > > > > some tests and hopefully upgrading to 7.3.
> > > > >
> > > > > At the moment we have
> > > > > B=64 (no of shared buffers)
> > > > > N=32 (no of connections)
> > > > > in postmaster.opt which I take it is the equivalent of the new
> > > > > postgresql.conf file.
> > > > >
> > > > > From all that is being written about later versions I suspect
> > > > > that this is far too low. Would I be fairly safe in making the
> > > > > no of shared buffers larger? Also is there an equivalent of
> > > > > effective_cache_size that I can set for version 7?
> > > > >
> > > > > Many thanks in advance
> > > > > Hilary
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > Hilary Forbes
> > > > > -------------
> > > > > DMR Computer Limited: http://www.dmr.co.uk/
> > > > > Direct line: 01689 889950
> > > > > Switchboard: (44) 1689 860000 Fax: (44) 1689 860330
> > > > > E-mail: hforbes@dmr.co.uk
> > > > >
> > > > > **********************************************************
> > > > >
> > > > >
> > > > > ---------------------------(end of
> > > broadcast)---------------------------
> > > > > TIP 5: Have you checked our extensive FAQ?
> > > > >
> > > > > http://www.postgresql.org/docs/faqs/FAQ.html
> > > > >
> > > >
> > > >
> > > >
> > > > ---------------------------(end of
> > broadcast)---------------------------
> > > > TIP 5: Have you checked our extensive FAQ?
> > > >
> > > > http://www.postgresql.org/docs/faqs/FAQ.html
> > > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Version 7 question
2003-07-01 13:06 FW: Version 7 question Howard Oblowitz <HowardO@LEWIS-STORES.com>
2003-07-01 13:55 ` Re: Version 7 question Michael Mattox <michael.mattox@verideon.com>
@ 2003-07-01 14:22 ` scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 0 replies; 12+ messages in thread
From: scott.marlowe @ 2003-07-01 14:22 UTC (permalink / raw)
To: Michael Mattox <michael.mattox@verideon.com>; +Cc: Howard Oblowitz <HowardO@LEWIS-STORES.com>; pgsql-performance
On Tue, 1 Jul 2003, Michael Mattox wrote:
> My understanding is to use as much effect cache as possible, so figure out
> how much ram you need for your other applications & OS and then give the
> rest to postgres as effective cache.
>
> What I learned to day is the shared_buffers 25% of RAM guideline.
Note that the best guideline is the one that your testing shows you makes
the most sense. If you never access more than a few megs at a time, then
there's no need to have 25% of a machine with 1 gig given over to the
database's shared buffers, it's better to let the machine cache that for
you. If you access hundreds of megs at a time, then 25% of RAM is a good
idea. Usually 25% of RAM is about the max that gives good results, but in
some corner cases, using more still makes sense. Usually at that point,
you've also increased sort_mem up a bit too, but be careful, sort_mem is
PER SORT, not per backend or per database cluster, so it can add up very
quickly and make the machine run out of RAM.
Setting these settings is a lot like playing Jenga (the game with the
wooden blocks stacked up where you pull one out and put them on top one at
a time.) Everything seems just fine, the machine's getting faster and
faster, everybody's loving life, then you crank one up a little too high,
cause a swap storm, and the whole thing slows to a crawl.
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2003-07-02 01:37 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-01 12:10 Version 7 question Hilary Forbes <hforbes@dmr.co.uk>
2003-07-01 12:17 ` Michael Mattox <michael.mattox@verideon.com>
2003-07-01 12:55 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-01 12:52 ` Robert Treat <xzilla@users.sourceforge.net>
2003-07-01 13:02 ` Michael Mattox <michael.mattox@verideon.com>
2003-07-01 18:01 ` Manfred Koizar <mkoi-pg@aon.at>
2003-07-02 01:37 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-07-01 13:06 FW: Version 7 question Howard Oblowitz <HowardO@LEWIS-STORES.com>
2003-07-01 13:19 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-01 13:55 ` Michael Mattox <michael.mattox@verideon.com>
2003-07-01 14:19 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-01 14:22 ` scott.marlowe <scott.marlowe@ihs.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