Re: Priority table or Cache table - Mailing list pgsql-hackers

On Fri, Feb 21, 2014 at 12:02 PM, Haribabu Kommi
<kommi.haribabu@gmail.com> wrote:
> On Thu, Feb 20, 2014 at 10:06 PM, Ashutosh Bapat
> <ashutosh.bapat@enterprisedb.com> wrote:
>>
>> On Thu, Feb 20, 2014 at 10:23 AM, Haribabu Kommi
>> <kommi.haribabu@gmail.com> wrote:
>>>
>>> On Thu, Feb 20, 2014 at 2:26 PM, Amit Kapila <amit.kapila16@gmail.com>
>>> wrote:
>>>>
>>>> On Thu, Feb 20, 2014 at 6:24 AM, Haribabu Kommi
>>>> <kommi.haribabu@gmail.com> wrote:
>>>> > On Thu, Feb 20, 2014 at 11:38 AM, Tom Lane <tgl@sss.pgh.pa.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 Implemented a proof of concept patch to see whether the buffer pool
split can improve the performance or not.

Summary of the changes:
1. The priority buffers are allocated as continuous to the shared buffers.
2. Added new reloption parameter called "buffer_pool" to specify the
buffer_pool user wants the table to use.
3. Two free lists are created to store the information for two buffer pools.
4. While allocating the buffer based on the table type, the
corresponding buffer is allocated.


The Performance test is carried as follows:
1. Create all the pgbench tables and indexes on the new buffer pool.
2. Initialize the pgbench test with a scale factor of 75 equals to a
size of 1GB.
3. Create an another load test table with a size of 1GB with default
buffer pool.
4. In-parallel with performance test the select and update operations
are carried out on the load test table (singe thread).

Configuration changes:
shared_buffers - 1536MB (Head)                Patched Shared_buffers
-512MB, Priority_buffers - 1024MB.
synchronous_commit - off, wal_buffers-16MB, checkpoint_segments - 255,
checkpoint_timeout - 15min.

Threads         Head     Patched     Diff
    1                  25          25              0%
    2                  35          59              68%
    4                  52          79              51%
    8                  79          150            89%

In my testing it shows very good improvement in performance.

The POC patch and the test script is attached in the mail used for
testing the performance.
The modified pgbench.c code is also attached to use the newly created
buffer pool instead of default for the test purpose.
Copy the test script to the installation folder and execute as
./rub_bg.sh ./run_reading.sh 1 1

please let me know your suggestions.

Regards,
Hari Babu
Fujitsu Australia