>> > I think some file systems are more optimised for 8K blocks. I may be
>> > thinking on the original reason for the 8k limit in the first
>> > place, but I remember there was discussions about this when the block
>> > size was altered.
>>
>> Yes, most UFS file systems use 8k blocks/2k fragments. It allows write
>> of block in one i/o operation.
But modern Unixes have read/write ahead i/o if it seems a sequential
access, don't they. I did some testing on my LinuxPPC box.
0. create table t2(i int,c char(4000));
1. time psql -c "copy t2 from '/tmp/aaa'" test (aaa has 5120 records and this will create 20MB table)
2. time psql -c "select count(*) from t2" test
3. total time of the regression test
o result of testing 1
8K: 0.02user 0.04system 3:26.20elapsed
32K: 0.03user 0.06system 0:48.25elapsed
32K is 4 times faster than 8k!
o result of testing 2
8K: 0.02user 0.04system 6:00.31elapsed
32K: 0.04user 0.02system 1:02.13elapsed
32K is neary 6 times faster than 8k!
o result of testing 3
8K: 11.46user 9.51system 6:08.24
32K: 11.34user 9.54system 7:35.35
32K is a little bit slower than 8K?
My thought:
In my test case the tuple size is relatively large, so by using
ordinary size tuple, we may get different results. And of course
different OS may behave differently...
Another point is the access method. I only tested for seq scan. I
don't know for index scan.
Additional testings are welcome...
>The max is 32k because of the aforementioned 15 bits available, but I'd
>be a bit cautious of trying it. When I put this in, the highest I could
>get to work on AIX was 16k. Pushing it up to 32k caused major breakage
>in the system internals. Had to reboot the machine and fsck the file
>system. Some files were linked incorrectly, other files disappeared, etc,
>a real mess.
>
>Not sure exactly what it corrupted, but I'd try the 32k limit on a non-
>production system first...
I did above on 6.4.2. What kind of version are you using? Or maybe
platform dependent problem?
BTW, the biggest problem is there are some hard coded query length
limits in somewhere(for example MAX_MESSAGE_LEN in libpq-int.h). Until
these get fixed, 32K option is only useful for (possible) performance
boosting.
---
Tatsuo Ishii