Re: BLCKSZ - Mailing list pgsql-performance

At 04:32 PM 12/5/2005, Olleg wrote:
>Tom Lane wrote:
>>Olleg Samoylov <olleg_s@mail.ru> writes:
>>
>>>I try to test this. Linux, memory page 4kb, disk page 4kb. I set
>>>BLCKSZ to 4kb. I get some performance improve, but not big, may be
>>>because I have 4Gb on test server (amd64).
>>It's highly unlikely that reducing BLCKSZ is a good idea.  There
>>are bad side-effects on the maximum index entry size, maximum
>>number of tuple fields, etc.
>
>Yes, when I set BLCKSZ=512, database dont' work. With BLCKSZ=1024
>database very slow. (This was surprise me. I expect increase
>performance in 8 times with 1024 BLCKSZ. :) )

No wonder pg did not work or was very slow BLCKSZ= 512 or 1024 means
512 or 1024 *Bytes* respectively.  That's 1/16 and 1/8 the default 8KB BLCKSZ.


>  As I already see in this maillist, increase of  BLCKSZ reduce
> performace too.

Where?  BLCKSZ as large as 64KB has been shown to improve
performance.  If running a RAID, BLCKSZ of ~1/2 the RAID stripe size
seems to be a good value.


>May be exist optimum value? Theoretically BLCKSZ equal memory/disk
>page/block size may reduce defragmentation drawback of memory and disk.
Of course there's an optimal value... ...and of course it is
dependent on your HW, OS, and DB application.

In general, and in a very fuzzy sense, "bigger is better".  pg files
are laid down in 1GB chunks, so there's probably one limitation.
Given the HW you have mentioned, I'd try BLCKSZ= 65536 (you may have
to recompile your kernel) and a RAID stripe of 128KB or 256KB as a first guess.


>>In any case, when you didn't say *what* you tested, it's
>>impossible to judge the usefulness of the change.
>>                         regards, tom lane
>
>I test performace on database test server. This is copy of working
>billing system to test new features and experiments. Test task was
>one day traffic log. Average time of a one test was 260 minutes.

How large is a record in your billing system?  You want it to be an
integer divisor of BLCKSZ (so for instance odd sizes in Bytes are BAD),
Beyond that, you application domain matters.  OLTP like systems need
low latency access for frequent small transactions.  Data mining like
systems need to do IO in as big a chunk as the HW and OS will
allow.  Probably a good idea for BLCKSZ to be _at least_ max(8KB, 2x
record size)


>  Postgresql 7.4.8. Server dual Opteron 240, 4Gb RAM.

_Especially_ with that HW, upgrade to at least 8.0.x ASAP.  It's a
good idea to not be running pg 7.x anymore anyway, but it's
particularly so if you are running 64b SMP boxes.

Ron