Re: Query caching

From: KuroiNeko <evpopkov(at)carrier(dot)kiev(dot)ua>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Query caching
Date: 2000-11-01 04:41:05
Message-ID: 39FF9EE1.nailCAD1EV2K7@ed.ed
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

> I've looked for references as to
> Postgresql's ability to do something like this, but I've never been
> certain if it's possible. Can postgresql do this, please? And, if not,
> does it have to hit the disk for every SQL instruction (I would assume
> so)?

Doing so, as you might guess is quite dangerous. Eg, RAM failures are
extremely rare, with probability very close to 0, but there's nothing
absolutely reliable.
From my, quite limited, experience, I can tell that PGSQL relies more on
file caching (or whatever is the term), provided by the OS, rather than on
slurping relations into RAM. See the recent discussion of [f]sync(), maybe
it sheds more light.

> I would imagine that the actual query cache would be slightly orthogonal
> to this in-RAM database cache

Actually, there are several ways to keep the data in memory, each having
its advantages drawbacks and reasons. To name just a few: caching pages and
files, mapping files, storing `internal' structures (like the tuples in
your example) in shared memory areas.
Apologets and enemies of each method come in all shapes, but the real life
is even worse. Often these methods interfere with each other, and
inaccurate combination (you cache the pages, but overlooked file caching,
performed by the OS) may easily become a bottleneck.

> I'd appreciate any pointers to more information on specific performance
> tuning in this area (IMHO, it would probably be a boon to the postgresql
> database and its community, if there existed some reference like
> O'Reilly's _Oracle Performance Tuning_ that was focused on Postgresql.)

As I see it, performance tuning with PGSQL should be concentrated around
quality design of your DB and queries. I may be wrong, but there's not much
to play with where PGSQL server touches the system.
Maybe it's bad, but I like it. General suggestions about fs performance
apply to PGSQL and you don't have to re-invent the wheel. There are just
files. Play with sync, install a RAID of SCSI drives, keep your swap on
separate controller. Nothing really special that would impact or, what's
more important, interfere with other services running on the same box.
Change must come from inside :) Here, inside is DB design.

Ed

--

contaminated fish and microchips
huge supertankers on Arabian trips
oily propaganda from the leaders' lips
all about the future
there's people over here, people over there
everybody's looking for a little more air
crossing all the borders just to take their share
planning for the future

Rainbow, Difficult to Cure

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Igor Roboul 2000-11-01 04:58:04 Re: SQL question - problem with INTERSECT
Previous Message Daniel Freedman 2000-11-01 04:18:05 Re: Query caching

Browse pgsql-hackers by date

  From Date Subject
Next Message Denis Perchine 2000-11-01 09:23:08 Re: Query caching
Previous Message Daniel Freedman 2000-11-01 04:18:05 Re: Query caching