Re: [PERFORM] Memory question on win32 systems - Mailing list pgsql-hackers

<br /><br /> Merlin Moncure wrote: <blockquote cite="mid:b42b73150805290733y2d10b74ena234b3f276d19752@mail.gmail.com"
type="cite"><prewrap="">On Thu, May 29, 2008 at 10:19 AM, Justin <a class="moz-txt-link-rfc2396E"
href="mailto:justin@emproshunts.com"><justin@emproshunts.com></a>wrote: </pre><blockquote type="cite"><pre
wrap="">Tomy understanding Postgresql only caches queries and results in memory for
 
that specific connection.  So when that connection is closed those cached
results are cleared out.    So cached indexs and queries are for that
connection only.   I hope my understanding is correct.   </pre></blockquote><pre wrap="">
completely wrong.

They are called 'shared' buffers for a reason.  Also you are missing
the point of the o/s file cache which lies under that.  If you have a
computer with 4gb ram that is addressable, all its memory is used for
caching at all times, period, less what operating system needs or what
is used by running programs or temporary demands (sorting, etc).

Also, postgresql doesn't as a rule cache 'results and queries'.

shared buffers setting reserves memory for postgresql's internal cache
(plus some housekeeping things like locks)...which can be faster than
the o/s cache because it is more tightly integrated with the backend.
However a page fault to disk is much more interesting in performance
terms than the performance differences between shared buffers and o/s
cache.

merlin
 </pre></blockquote> This is not meant to be argumentative i'm trying to clearify what is going because documentation
fordifferent sources seem to be in conflict  or confusing.  <br />
---------------------------------------------------<br/> From Postgresql Second Edition:<br />     Shared_Buffers: This
cacheis shared by all clients connected to a single cluster. DISK I?) (and cache I/O) is performed in 8KB chunks.  The
shared_buffersparameter determines how many 8KB will be created in the shared cache. <br /><br /> from here explanation
isdifferent <br /><a class="moz-txt-link-freetext"
href="http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html">http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html</a> 
itreferrs to queries which means to me the SQL command and the result sets (but that is wrong)<br /><br /> We have
sort_memaka work_mem<br /> ---------------------------------------------------<br /> From Postgresql Second Edition:<br
/>     where postgresql processes query it transforms the query from string form into an execution plan. An execution
planis a sequence of operations that must be performed in order satisfy the query.    <br /><br /> (This specific to
theclient  connection and when the client is closed out this is cleared my thinking.)<br />
----------------------------------------------------<br/> From postgresql Documentation<br /><dl><dt><tt
class="VARNAME">work_mem</tt>(<tt class="TYPE">integer</tt>)<dd><p>Specifies the amount of memory to be used by
internalsort operations and hash tables before switching to temporary disk files. The value is defaults to one megabyte
(<ttclass="LITERAL">1MB</tt>). Note that for a complex query, several sort or hash operations might be running in
parallel;each one will be allowed to use as much memory as this value specifies before it starts to put data into
temporaryfiles. Also, several running sessions could be doing such operations concurrently. So the total memory used
couldbe many times the value of <tt class="VARNAME">work_mem</tt>; it is necessary to keep this fact in mind when
choosingthe value. Sort operations are used for <tt class="LITERAL">ORDER BY</tt>, <tt class="LITERAL">DISTINCT</tt>,
andmerge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of <tt
class="LITERAL">IN</tt>subqueries<dt>---------------------------------------------------------<br /></dl> Quoting You
"Also,postgresql doesn't as a rule cache 'results and queries'.<br /><br /> Then what is the purpose of shared buffers
ifnothing is being reused  is it only used to keep track locks, changes and what is to being spooled to the
kernel???<br/><br /> I'm confussed trying to figure out how caches are being use and being moving through postgresql
backend.  As many have stated that small database can fit completely in the caches How does that really work. <br /><br
/><br/><br /><br /><br /><br /><br /><br />