From: <gnuoytr@rcn.com>
Subject: Re: How does PG know if data is
 in memory?
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
In-Reply-To: <4CB42BFC0200002500036805@gw.wicourts.gov>
References: <AANLkTin5G0jXUbeNdC+1s9LQpk1ZpvDuX-6n90oonen3@mail.gmail.com>
	<4CA9488C.2080502@2ndquadrant.com> <4CAA5974.2050805@wizmail.org>
	<AANLkTi=gBcvy2_Ey2q_gaiyUOjrGU4LoYfdbO9uxEZDX@mail.gmail.com>
	<20101011231134.ANB76937@ms14.lnh.mail.rcn.net>
	<4CB42BFC0200002500036805@gw.wicourts.gov>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Message-Id: <20101012104944.AND07357@ms14.lnh.mail.rcn.net>
Date: Tue, 12 Oct 2010 10:49:44 -0400 (EDT)

Couldn't have said it better myself; covered all the bases.  If PG wants to become an industrial strength database, worthy of replacing DB2/etc., then these are the sorts of knobs and switches it will need. 


-- None of that is anything for amateurs to play with.

Not jam a stick in anybody's eye, but shouldn't database pros not be amateurs?  Or are most PG-ers coders who don't really want to design and tune a database?

Robert

---- Original message ----
>Date: Tue, 12 Oct 2010 09:35:56 -0500
>From: pgsql-performance-owner@postgresql.org (on behalf of "Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
>Subject: Re: [PERFORM] How does PG know if data is in memory?  
>To: <pgsql-performance@postgresql.org>,<gnuoytr@rcn.com>
>
><gnuoytr@rcn.com> wrote:
> 
>> An approach that works can be found in DB2, and likely elsewhere.
>> 
>> The key is that tablespaces/tables/indexes/buffers are all
>> attached through the bufferpool (the DB2 term).  A tablespace/
>> bufferpool match is defined.  Then tables and indexes are assigned
>> to the tablespace (and implicitly, the bufferpool).  As a result,
>> one can effectively pin data in memory. This is very useful, but
>> not low hanging fruit to implement.
> 
>This sounds similar to Sybase named caches.  You can segment off
>portions of the memory for specific caches, break that up into space
>reserved for different I/O buffer sizes, and bind specific database
>objects (tables and indexes) to specific caches.  On the few
>occasions where someone had failed to configure the named caches
>when setting up a machine, it was caught almost immediately after
>deployment because of end-user complaints about poor performance. 
>This was so critical to performance for us when we were using
>Sybase, that one of my first reactions on finding it missing in
>PostgreSQL was distress over the inability to tune as I had.
> 
>When I posted to the list about it, the response was that LRU
>eviction was superior to any tuning any human would do.  I didn't
>and don't believe that, but have found it's close enough in the
>PostgreSQL environment to be *way* down my list of performance
>issues.  In fact, when looking at the marginal benefits it would
>generate in PostgreSQL when done right, versus the number of people
>who would shoot themselves in the foot with it, even I have come
>around to feeling it's probably not a good idea.
> 
>FWIW, the four main reasons for using it were:
> 
>(1) Heavily used data could be kept fully cached in RAM and not
>driven out by transient activity.
> 
>(2) You could flag a cache used for (1) above as using "relaxed LRU
>accounting" -- it saved a lot of time tracking repeated references,
>leaving more CPU for other purposes.
> 
>(3) Each named cache had its own separate set of locks, reducing
>contention.
> 
>(4) Large tables for which the heap was often were scanned in its
>entirety or for a range on the clustered index could be put in a
>relatively small cache with large I/O buffers.  This avoided blowing
>out the default cache space for situations which almost always
>required disk I/O anyway.
> 
>None of that is anything for amateurs to play with.  You need to set
>up caches like that based on evidence from monitoring and do careful
>benchmarking of the results to actually achieve improvements over
>LRU logic.
> 
>> The introduction of rudimentary tablespaces is a first step.  I
>> assumed that the point was to get to a DB2-like structure at some
>> point.  Yes?
> 
>As far as I can tell, there is nobody with that intent.
> 
>-Kevin
>
>-- 
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance