Thanks.
Some theoretical questions.
The documentation says that Effective Cache Size sets the optimizer's
assumption
about the effective size of the disk cache ( that is, the portion of the
kernel's disk
cache that will be used for PostgreSQL data files ).
What then will be the effect of setting this too high?
And too low?
How does it impact on other applications eg Java ?
-Original Message-
From: scott.marlowe [SMTP:[EMAIL PROTECTED]
Sent: 01 July 2003 03:20
To: Howard Oblowitz
Cc: [EMAIL PROTECTED]
Subject: Re: FW: [PERFORM] Version 7 question
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:[EMAIL PROTECTED]
Sent: 01 July 2003 02:56
To: Michael Mattox
Cc: Hilary Forbes; [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Hilary
Forbes
Sent: Tuesday, July 01, 2003 2:10 PM
To: [EMAIL PROTECTED]
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 86 Fax: (44) 1689 860330
E-mail: [EMAIL PROTECTED]
**
---(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 [EMAIL PROTECTED])
---(end of broadcast)---
TIP 8: explain analyze is