Thread: 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 makingthe 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 **********************************************************
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 >
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
> 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
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 >
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
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 >