From: Brian Hirt <bhirt@mobygames.com>
To: pgsql-performance@postgresql.org
Cc: bhirt@mobygames.com
Subject: Re: Postgres 7.3.1 poor insert/update/search performance
Date: Tue, 21 Jan 2003 18:44:57 -0700
Message-ID: <1CE2D1AE-2DAB-11D7-BE6C-000393D9FD00@mobygames.com> (raw)
In-Reply-To: <13165.1043188295@sss.pgh.pa.us>
Tom and others:
There has been a lot of talk about shared memory size recently, along
with many conflicting statements from various people. Earlier threads
said that setting the shared buffer to a high values (like 512MB on a
2GB dedicated DB server) is not a good idea. A couple of reasons were
mentioned. a) potential inefficiencies with the kernel and VM system
b) modern kernels aggressive caching with all free memory and c) the
shared memory stealing from memory the kernel would use to cache, etc.
So my question is: if the kernel is caching all this data, what's the
benefit of setting this to 1000 or higher? Why wouldn't i just set it
to 0 if I believe my kernel is doing a good job.
From all the discussion on this topic, it's still not clear to me how
to calculate what value this should be set at and why. I've read these
documents and others and have yet to find explanations and
recommendations that i can use.
http://www.postgresql.org/docs/momjian/hw_performance.pdfhttp://www.postgresql.org/idocs/index.php?runtime-config.htmlhttp://www.postgresql.org/idocs/index.php?kernel-resources.htmlhttp://www.postgresql.org/idocs/index.php?performance-tips.htmlhttp://www.ca.postgresql.org/docs/momjian/hw_performance/node6.htmlhttp://www.ca.postgresql.org/docs/momjian/hw_performance/node5.htmlhttp://www.ca.postgresql.org/docs/faq-english.html#3.6
This is such a common topic, it would be nice to see a more definitive
and comprehensive section in the docs for tuning. Google searches for
"shared_buffers site:www.postgresql.org" and "tuning
site:www.postgresql.org" come up with little info.
FYI: I've been running our database which is mostly read only with 1500
buffers. On a whole, we see very little IO. postgresql performs
many many million queries a day, many simple, many complex. Though the
database is relatively small, around 3GB.
--brian
On Tuesday, January 21, 2003, at 03:31 PM, Tom Lane wrote:
> Seth Robertson <pgsql-performance@sysd.com> writes:
>> I'll try that and report back later, but I was under the (false?)
>> impression that it was primarily important when you had multiple
>> database connections using the same table.
>
> Definitely false. shared_buffers needs to be 1000 or so for
> production-grade performance. There are varying schools of thought
> about whether it's useful to raise it even higher, but in any case
> 64 is just a toy-installation setting.
>
>> seth=> explain analyze select accum from test where val = 5;
>> QUERY PLAN
>> ----------------------------------------------------------------------
>> -------------------------
>> Seq Scan on test (cost=0.00..323.89 rows=1 width=4) (actual
>> time=0.13..14.20 rows=1 loops=1)
>> Filter: (val = 5)
>> Total runtime: 14.26 msec
>> (3 rows)
>
>> seth=> explain analyze update test set accum = accum + 53 where val =
>> '5';
>> QUERY PLAN
>> ----------------------------------------------------------------------
>> -----------------------------------------
>> Index Scan using test_pkey on test (cost=0.00..5.99 rows=1
>> width=18) (actual time=0.24..0.24 rows=1 loops=1)
>> Index Cond: (val = 5::bigint)
>> Total runtime: 0.39 msec
>> (3 rows)
>
> The quotes are important when you are dealing with BIGINT indexes.
> You won't get an indexscan if the constant looks like int4 rather than
> int8.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: bhirt@mobygames.com
Subject: Re: Postgres 7.3.1 poor insert/update/search performance
In-Reply-To: <1CE2D1AE-2DAB-11D7-BE6C-000393D9FD00@mobygames.com>
References: <13165.1043188295@sss.pgh.pa.us> <1CE2D1AE-2DAB-11D7-BE6C-000393D9FD00@mobygames.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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