Greg,
Let's look at this from an application developer perspective. Suppose my
application has a few hundreds of tables. I know _very_well_ how the
tables are used. I'd like to tune PostgreSQL so that it would respect
how the application works in order to get best possible performance.
My database is 500GB. One set of tables, configuration and operational
data, takes around 20GB. Historical data takes 480GB.
Configuration: 20GB, lots of selects and updates. Latency is important.
History: 480GB, frequent inserts, selects. Latency is important as well.
Since PostgreSQL has only one buffer cache, operations with one set of
tables affects other set of tables. Bunch of selects from the historical
tables could easily cause cache pollution.
Pinning buffers is a nice feature, but imho it would be hard to tune and
control. However I think that pinning relations (tables) would help a
lot. In this case all configuration tables could be pinned, therefore
selects and updates will be very fast.
Alternatively if PostgreSQL supported multiple buffer cache, I would
assign relatively small configuration tables to one buffer and
historical tables to another, so they won't affect each other. It may
also work on per database level, so a database can be assigned to one of
available buffers.
Does it make sense?
Kind regards,
Alexei
Greg Stark wrote:
I doubt pinning buffers ever improve system on any halfway modern
system. It will often *look* like it has improved performance because
it improves the performance of the queries you're looking at -- but at
the expense of slowing down everything else.
There is a use case it would be useful for though. When you have some
queries that are latency critical. Then you might want to pin the
buffers those queries use to avoid having larger less urgent queries
purge those buffers.
If we had a way to mark latency critical queries that might be a more
flexible interface but ewe would need some way to control just how
critical they are. we wouldn't want to keep those buffets pinned forever.
greg
On 7 Feb 2010 07:36, "Tom Lane" <tgl@xxxxxxxxxxxxx
<mailto:tgl@xxxxxxxxxxxxx>> wrote:
Greg Smith <greg@xxxxxxxxxxxxxxx <mailto:greg@xxxxxxxxxxxxxxx>> writes:
> ... Part of the reason this feature
> hasn't been been perceived as more valuable is because just letting
the
> two cache levels involv...
Or to put it even more clearly: the only way that pinning particular
tables into RAM could beat letting the caching algorithm take care of
it is if the caching algorithm is seriously suboptimal. Therefore,
it would make more sense to put development effort into improving
the cache algorithm than to put it into giving the DBA a rather blunt
instrument for overriding the cache algorithm. We've already made
several rounds of improvements of that kind, and are quite happy to
consider more.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx
<mailto:pgsql-general@xxxxxxxxxxxxxx>)
To make changes to your subs...
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
