Re: Multiple buffer cache? - Mailing list pgsql-general

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@sss.pgh.pa.us
>> <mailto:tgl@sss.pgh.pa.us>> wrote:
>>
>> Greg Smith <greg@2ndquadrant.com <mailto:greg@2ndquadrant.com>> 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@postgresql.org
>> <mailto:pgsql-general@postgresql.org>)
>> To make changes to your subs...
>>