Message-ID: <4E37B882.8080005@gmail.com>
Date: Tue, 02 Aug 2011 11:42:42 +0300
From: Vitalii Tymchyshyn <tivv00@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: Robert Ayrapetyan <robert.ayrapetyan@comodo.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Performance die when COPYing to table with bigint PK
References: 
 <CAAboi9sk0EnbS23-tr86X9i7LdHTfdbTP4aa7r2q7NpWKaHWPA@mail.gmail.com>
	<4E366C9C.4040904@gmail.com>
	<CAAboi9uU+hMg8deuJ6Q2LxncE2HO2jykPW89fPVxHLsU8_tFrw@mail.gmail.com>
	<CAAboi9s9MCsmqYPR+KqtXstPUodiE5MzvEpk1-e+C0n6FwkkyQ@mail.gmail.com>
In-Reply-To: 
 <CAAboi9s9MCsmqYPR+KqtXstPUodiE5MzvEpk1-e+C0n6FwkkyQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

02.08.11 11:26, Robert Ayrapetyan написав(ла):
> Seems this assumption is not right. Just created simple index on
> bigint column - situation with huge performance
> degradation repeated. Dropping this index solved COPY issues on the fly.
> So I'm still convinced - this bug relates to FreeBSD 64-bit + UFS +
> bigint column index
> (some of these may be superfluous, but I have no resources to check on
> different platforms with different filesystems).
Interrrresting. We also have FreeBSDx64 on UFS and are using bigint 
(bigserial) keys. It seems I will need to perform more tests here 
because I do see similar problems. I for sure can do a copy of data with 
int4 keys and test the performance.
BTW: The thing we are going to try on next upgrade is to change UFS 
block size from 16K to 8K. What problem I saw is that with default 
setting, UFS needs to read additional 8K when postgresql writes it's 
page (and for index random writes can be vital). Unfortunately, such a 
changes requires partition reformat and I can't afford it for now.

Best regards, Vitalii Tymchyshyn