pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.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@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)




view thread (16+ messages)  latest in thread

Message-ID: <1CE2D1AE-2DAB-11D7-BE6C-000393D9FD00@mobygames.com>
Permalink:  ../1CE2D1AE-2DAB-11D7-BE6C-000393D9FD00@mobygames.com/
Also on:    postgresql.org/message-id/1CE2D1AE-2DAB-11D7-BE6C-000393D9FD00@mobygames.com

 · 

reply

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