Thread: [PERFORM] Memory question on win32 systems

config:
port = 5432                # (change requires restart)
max_connections = 50                    # (change requires restart)

shared_buffers = 320MB            # min 128kB or max_connections*16kB
temp_buffers = 80MB            # min 800kB
max_prepared_transactions = 10        # can be 0 or more
work_mem = 10MB                # min 64kB
maintenance_work_mem = 160MB        # min 1MB
Max_stack_depth = 20MB            # min 100kB
max_fsm_pages = 2048000            # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 10000        # min 100, ~70 bytes each
shared_preload_libraries = '$libdir/plugins/plugin

On Thu, May 29, 2008 at 4:26 AM, Sabbiolina <sabbiolina@gmail.com> wrote:
> On Thu, May 29, 2008 at 2:11 AM, Douglas McNaught <doug@mcnaught.org> wrote:
>> On Wed, May 28, 2008 at 7:05 PM, Sabbiolina <sabbiolina@gmail.com> wrote:
>> > Hello, in my particular case I need to configure Postgres to handle only
>> > a
>> > few concurrent connections, but I need it to be blazingly fast, so I
>> > need it
>> > to cache everything possible. I've changed the config file and

[this should really be on -perform]

This question gets asked a lot...by tweaking memory settings in
postgresql.conf you can only reserve memory for one thing at the
expense of the other.  Shared buffers, for example will reserve space
for the postgresql built in buffer cache instead of letting the
operating system use it's own cache.

Even if your shared buffers were set to the minimum setting, you would
still not be going to disk very much if your active working set of
files fit in RAM.

work_mem and other settings _can_ have a drastic impact on
performance, because they can force the server to use query plans
depending on how much memory it has for things like sorting.

You didn't give much detail...maybe if you gave examples of queries
you don't think are going fast enough (with explain analyze)?

merlin

Sabbiolina wrote:

On Thu, May 29, 2008 at 2:11 AM, Douglas McNaught <doug@mcnaught.org> wrote:

On Wed, May 28, 2008 at 7:05 PM, Sabbiolina <sabbiolina@gmail.com> wrote:
> Hello, in my particular case I need to configure Postgres to handle only a
> few concurrent connections, but I need it to be blazingly fast, so I need it
> to cache everything possible. I've changed the config file and multiplied
> all memory-related values by 10, still Postgres uses only less than 50 Mb of
> my RAM.

How are you measuring this?

Task Manager

> I have 4 Gigs of RAM, how do I force Postgres to use a higher part of such
> memory in order to cache more indexes, queries and so on?

Post the settings values you're using and people will be better able
to help you.

-Doug

config:
port = 5432                # (change requires restart)
max_connections = 50                    # (change requires restart)

shared_buffers = 320MB            # min 128kB or max_connections*16kB
temp_buffers = 80MB            # min 800kB
max_prepared_transactions = 10        # can be 0 or more
work_mem = 10MB                # min 64kB
maintenance_work_mem = 160MB        # min 1MB
Max_stack_depth = 20MB            # min 100kB
max_fsm_pages = 2048000            # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 10000        # min 100, ~70 bytes each
shared_preload_libraries = '$libdir/plugins/plugin

_debugger.dll'        # (change requires restart)
wal_buffers = 640kB            # min 32kB

log_destination = 'stderr'        # Valid values are combinations of
logging_collector = on        # Enable capturing of stderr and csvlog
log_line_prefix = '%t '            # special values:

datestyle = 'iso, dmy'
lc_messages = 'Italian_Italy.1252'            # locale for system error message
lc_monetary = 'Italian_Italy.1252'            # locale for monetary formatting
lc_numeric = 'Italian_Italy.1252'            # locale for number formatting
lc_time = 'Italian_Italy.1252'                # locale for time formatting
default_text_search_config = 'pg_catalog.italian'

Need to Use Performance monitor and Process Explorer to track down the caching on windows machines and performance.

Postgresql does not work like MS SQL where when it start ups grabs a large chuck memory that it uses to keep  last  X number of queries along with the indexes and tables in memory then its pushed out only when new queries that completely different need the memory.

To my 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.

On Thu, May 29, 2008 at 10:19:46AM -0400, Justin wrote:
> To my 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.

Incorrect, the shared buffer cache is shared between all running
postgresql processes and between connections. Also the OS cache is
shared between all programs on the system.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

On Thu, May 29, 2008 at 10:19 AM, Justin <justin@emproshunts.com> wrote:
> To my 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.

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

<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 /> 

On Thu, May 29, 2008 at 4:45 PM, Justin <justin@emproshunts.com> wrote:
>
> Then what is the purpose of shared buffers if nothing is being reused  is it
> only used to keep track locks, changes and what is to being spooled to the
> kernel???

It caches disk pages (and holds other data structures), not query results.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

On Thu, 29 May 2008, Justin wrote:

> I'm confussed trying to figure out how caches are being use and being 
> moving through postgresql backend.

The shared_buffers cache holds blocks from the database files.  That's it. 
If you want some more information about how that actually works head to 
http://www.westnet.com/~gsmith/content/postgresql/ and read "Inside the 
PostgreSQL Buffer Cache".

The work memory allocated for sorting is separate from that, and it 
doesn't cache anything.  It just provides working room for a query that's 
being executed right now.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Thu, May 29, 2008 at 4:52 PM, Dave Page <dpage@pgadmin.org> wrote:
> On Thu, May 29, 2008 at 4:45 PM, Justin <justin@emproshunts.com> wrote:
>>
>> Then what is the purpose of shared buffers if nothing is being reused  is it
>> only used to keep track locks, changes and what is to being spooled to the
>> kernel???
>
> It caches disk pages (and holds other data structures), not query results.

Oops, misread that. Shared *buffers* is disk pages. Shared memory
holds the buffers and other stuff as well.


-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

> On Thu, May 29, 2008 at 10:19 AM, Justin <justin@emproshunts.com> wrote:
> Quoting You "Also, postgresql doesn't as a rule cache 'results and queries'.
>
> Then what is the purpose of shared buffers if nothing is being reused  is it
> only used to keep track locks, changes and what is to being spooled to the
> kernel???

not much.  PostgreSQL provides an alternative file cache to the o/s
buffer cache (rather, it layers on top of it).  The performance
difference between these caches is an interesting topic.  In my
experience, it isn't very much, so I tend to keep shared buffers on
the low side.

> 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.

If your database is smaller than working memory, it will be completely
cached all the time, unless you have some big transient memory demand
(a big sort, or result set).  It's just a matter of which cache it
sits in...o/s, or postgres cache, or both.

This is actually the normal state of affairs in many databases.
memory .conf optimization is pretty marginal in effect until your
database is 'big' in terms of relative size to memory, does a lot of
sorting (work_mem becomes important), or receives a lot of writing
(vacuum, sync, checkpoints, and bgwriter become important).  There are
a few things you have to pay attention to, especially in older
versions.  For example effective cache size...but mainly because it
affects query plans chosen, not cache policy.

For smallish databases, your best investment is writing efficient
queries and good index strategy.

merlin