Message-ID: <4E1EABD9.5000308@enterprisedb.com>
Date: Thu, 14 Jul 2011 11:42:01 +0300
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; fi-FI;
	rv:1.9.2.18) Gecko/20110626 Icedove/3.1.11
MIME-Version: 1.0
To: Alexander Korotkov <aekorotkov@gmail.com>
CC: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: WIP: Fast GiST index build
References: <BANLkTikgOQ1+r=A9b08szNTeEWsk-cNMMg@mail.gmail.com>
	<BANLkTimr___BFy+98t36tkbUvvavUzjV9A@mail.gmail.com>
	<BANLkTi=x0Ck1z_NbkqeqvuKXs9kHOR+5WQ@mail.gmail.com>
	<BANLkTikP6RAYmUriquQEdKjEBviTH2vWRQ@mail.gmail.com>
	<4DFA4AE5.1010806@enterprisedb.com>
	<BANLkTinN6C8q61Fb8ASZVOPbYq7VoqoHOw@mail.gmail.com>
	<BANLkTin=gyX5PJW-JaN59uqRBSgdDHVNNA@mail.gmail.com>
	<BANLkTikSbaun6Sf8hJSkO0D4nu7VC2O35A@mail.gmail.com>
	<4E044D68.5070605@enterprisedb.com>
	<BANLkTi=umLx8LBCSmALKpoGTFguqQxQ61w@mail.gmail.com>
	<BANLkTinK-ZL-yQCNtKZLfGS=8VGmO13b8g@mail.gmail.com>
	<4E0894FF.8080202@enterprisedb.com>
	<BANLkTi=1TL+pH2sxiR7nf3oMFdWAHHgSyA@mail.gmail.com>
	<CAPpHfdt6ER8LKEEmnVnbYuaODKpDb4S5UuGRa5prio--Kp=LFw@mail.gmail.com>
	<26338.1310134685@sss.pgh.pa.us>
	<CAPpHfduE1LM7aCk7MZ_u_n2z58Jxps=TBD0eX4WX4gwBrkk=Yw@mail.gmail.com>
	<CAPpHfdvcAo2L2RVtnr+BtPE18Q4mJLaLf-R7SrKMTUousv8ChA@mail.gmail.com>
	<4E1DA4CB.9020209@enterprisedb.com>
	<CAPpHfdvSC3p7zR_BwbNhm+3fvg8b-rwe+p=HJ9Gii8_rethbuw@mail.gmail.
	com>
In-Reply-To: 
 <CAPpHfdvSC3p7zR_BwbNhm+3fvg8b-rwe+p=HJ9Gii8_rethbuw@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 14.07.2011 11:33, Alexander Korotkov wrote:
> On Wed, Jul 13, 2011 at 5:59 PM, Heikki Linnakangas<
> heikki.linnakangas@enterprisedb.com>  wrote:
>
>> One thing that caught my eye is that when you empty a buffer, you load the
>> entire subtree below that buffer, down to the next buffered or leaf level,
>> into memory. Every page in that subtree is kept pinned. That is a problem;
>> in the general case, the buffer manager can only hold a modest number of
>> pages pinned at a time. Consider that the minimum value for shared_buffers
>> is just 16. That's unrealistically low for any real system, but the default
>> is only 32MB, which equals to just 4096 buffers. A subtree could easily be
>> larger than that.
>>
> With level step = 1 we need only 2 levels in subtree. With mininun index
> tuple size (12 bytes) each page can have at maximum 675. Thus I think
> default shared_buffers is enough for level step = 1.

Hundreds of buffer pins is still a lot. And with_level_step=2, the 
number of pins required explodes to 675^2 = 455625.

Pinning a buffer that's already in the shared buffer cache is cheap, I 
doubt you're gaining much by keeping the private hash table in front of 
the buffer cache. Also, it's possible that not all of the subtree is 
actually required during the emptying, so in the worst case pre-loading 
them is counter-productive.

> I believe it's enough
> to add check we have sufficient shared_buffers, isn't it?

Well, what do you do if you deem that shared_buffers is too small? Fall 
back to the old method? Also, shared_buffers is shared by all backends, 
so you can't assume that you get to use all of it for the index build. 
You'd need a wide safety margin.

>> I don't think you're benefiting at all from the buffering that BufFile does
>> for you, since you're reading/writing a full block at a time anyway. You
>> might as well use the file API in fd.c directly, ie.
>> OpenTemporaryFile/FileRead/**FileWrite.
>
> BufFile is distributing temporary data through several files. AFAICS
> postgres avoids working with files larger than 1GB. Size of tree buffers can
> easily be greater. Without BufFile I need to maintain set of files manually.

Ah, I see. Makes sense.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com