Re: Postgres 7.3.1 poor insert/update/search performance

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.pdf
http://www.postgresql.org/idocs/index.php?runtime-config.html
http://www.postgresql.org/idocs/index.php?kernel-resources.html
http://www.postgresql.org/idocs/index.php?performance-tips.html
http://www.ca.postgresql.org/docs/momjian/hw_performance/node6.html
http://www.ca.postgresql.org/docs/momjian/hw_performance/node5.html
http://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(at)sysd(dot)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(at)postgresql(dot)org)