From: gnuoytr@rcn.com
To: pgsql-performance@postgresql.org
Subject: Re: How does PG know if data is in memory?
Date: Tue, 12 Oct 2010 10:49:44 -0400 (EDT)
Message-ID: <20101012104944.AND07357@ms14.lnh.mail.rcn.net> (raw)
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>
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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gnuoytr@rcn.com
Subject: Re: How does PG know if data is in memory?
In-Reply-To: <20101012104944.AND07357@ms14.lnh.mail.rcn.net>
* 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