Skip site navigation (1) Skip section navigation (2)

Re: 2nd Level Buffer Cache

From: rsmogura <rsmogura(at)softperience(dot)eu>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: 2nd Level Buffer Cache
Date: 2011-03-18 14:57:48
Message-ID: ef7df947a33eaa145f94eabe430f7074@mail.softperience.eu (view raw or flat)
Thread:
Lists: pgsql-hackers
 On Thu, 17 Mar 2011 16:02:18 -0500, Kevin Grittner wrote:
> Rados*aw Smogura<rsmogura(at)softperience(dot)eu> wrote:
>
>> I have implemented initial concept of 2nd level cache. Idea is to
>> keep some segments of shared memory for special buffers (e.g.
>> indices) to prevent overwrite those by other operations. I added
>> those functionality to nbtree index scan.
>>
>> I tested this with doing index scan, seq read, drop system
>> buffers, do index scan and in few places I saw performance
>> improvements, but actually, I'm not sure if this was just "random"
>> or intended improvement.
>
> I've often wondered about this.  In a database I developed back in
> the '80s it was clearly a win to have a special cache for index
> entries and other special pages closer to the database than the
> general cache.  A couple things have changed since the '80s (I mean,
> besides my waistline and hair color), and PostgreSQL has many
> differences from that other database, so I haven't been sure it
> would help as much, but I have wondered.
>
> I can't really look at this for a couple weeks, but I'm definitely
> interested.  I suggest that you add this to the next CommitFest as a
> WIP patch, under the Performance category.
>
> https://commitfest.postgresql.org/action/commitfest_view/open
>
>> There is few places to optimize code as well, and patch need many
>> work, but may you see it and give opinions?
>
> For something like this it makes perfect sense to show "proof of
> concept" before trying to cover everything.
>
> -Kevin

 Yes, there is some change, and I looked at this more carefully, as my 
 performance results wasn't such as I expected. I found PG uses 
 BufferAccessStrategy to do sequence scans, so my test query took only 32 
 buffers from pool and didn't overwritten index pool too much. This BAS 
 is really surprising. In any case when I end polishing I will send good 
 patch, with proof.

 Actually idea of this patch was like this:
 Some operations requires many buffers, PG uses "clock sweep" to get 
 next free buffer, so it may overwrite index buffer. From point of view 
 of good database design We should use indices, so purging out index from 
 cache will affect performance.

 As the side effect I saw that this 2nd level keeps pg_* indices in 
 memory too, so I think to include 3rd level cache for some pg_* tables.

 Regards,
 Radek

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2011-03-18 15:07:23
Subject: Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.
Previous:From: Greg StarkDate: 2011-03-18 14:55:56
Subject: Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group