Re: Priority table or Cache table

On Thu, Feb 20, 2014 at 10:23 AM, Haribabu Kommi
<kommi(dot)haribabu(at)gmail(dot)com>wrote:

> On Thu, Feb 20, 2014 at 2:26 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>wrote:
>
>> On Thu, Feb 20, 2014 at 6:24 AM, Haribabu Kommi
>> <kommi(dot)haribabu(at)gmail(dot)com> wrote:
>> > On Thu, Feb 20, 2014 at 11:38 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> >> > I want to propose a new feature called "priority table" or "cache
>> >> > table".
>> >> > This is same as regular table except the pages of these tables are
>> >> > having
>> >> > high priority than normal tables. These tables are very useful,
>> where a
>> >> > faster query processing on some particular tables is expected.
>> >>
>> >> Why exactly does the existing LRU behavior of shared buffers not do
>> >> what you need?
>> >
>> >
>> > Lets assume a database having 3 tables, which are accessed regularly.
>> The
>> > user is expecting a faster query results on one table.
>> > Because of LRU behavior which is not happening some times.
>>
>> I think this will not be a problem for regularly accessed tables(pages),
>> as per current algorithm they will get more priority before getting
>> flushed out of shared buffer cache.
>> Have you come across any such case where regularly accessed pages
>> get lower priority than non-regularly accessed pages?
>>
>
> Because of other regularly accessed tables, some times the table which
> expects faster results is getting delayed.
>

The solution involving buffer pools partitions the buffer cache in separate
pools explicitly. The way PostgreSQL buffer manager works, for a regular
pattern table accesses the buffer cache automatically reaches a stable
point where the number of buffers containing pages belonging to a
particular table starts to stabilize. Thus at an equilibrium point for
given access pattern, the buffer cache automatically gets partitioned by
the tables, each using its share of buffers. So, solution using buffer
pools seems useless.

PFA some scripts, which I used to verify the behaviour. The scripts create
two tables, one large and other half it's size (buffer_usage_objects.sql).
The other script contains few queries which will simulate a simple table
access pattern by running select count(*) on these tables N times. The same
script contains query of pg_buffercache view provided by pg_buffercache
extension. This query counts the number of buffers uses by either of these
tables. So, if you run three session in parallel, two querying either of
the tables and the third taking snapshot of buffer usage per table, you
would be able to see this partitioning.

>
>
>> However it might be required for cases where user wants to control
>> such behaviour and pass such hints through table level option or some
>> other way to indicate that he wants more priority for certain tables
>> irrespective
>> of their usage w.r.t other tables.
>>
>> Now I think here important thing to find out is how much helpful it is for
>> users or why do they want to control such behaviour even when Database
>> already takes care of such thing based on access pattern.
>>
>
> Yes it is useful in cases where the application always expects the faster
> results whether the table is used regularly or not.
>

In such case, it might be valuable to see if we should play with the
maximum usage parameter, which is set to 5 currently.
54 #define BM_MAX_USAGE_COUNT 5

> Regards,
> Hari Babu
> Fujitsu Australia
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company