Re: FreeBSD page size - Mailing list pgsql-performance

Ok... simple tests have completed.  Here are some numbers.

FreeBSD 4.8
PG 7.4b2
4GB Ram
Dual Xeon 2.4GHz processors
14 U320 SCSI disks attached to Dell PERC3/DC RAID controller in RAID 5
 config with 32k stripe size

Dump file:
-rw-r--r--  1 vivek  wheel  1646633745 Aug 28 11:01 19-Aug-2003.dump

When restored (after deleting one index that took up ~1Gb -- turned
out it was redundant to another multi-column index):

% df -k /u/d02
Filesystem    1K-blocks     Used     Avail Capacity  Mounted on
/dev/amrd1s1e 226408360 18067260 190228432     9%    /u/d02



postgresql.conf alterations from standard:
shared_buffers = 60000
sort_mem = 8192
vacuum_mem=131702
max_fsm_pages=1000000
effective_cache_size=25600
random_page-cost = 2


restore time: 14777 seconds
vacuum analyze time: 30 minutes
select count(*) from user_list where owner_id=315;   50388.64 ms


the restore complained often about checkpoints occurring every few
seconds:

Sep  2 11:57:14 d02 postgres[49721]: [5-1] LOG:  checkpoints are occurring too frequently (15 seconds apart)
Sep  2 11:57:14 d02 postgres[49721]: [5-2] HINT:  Consider increasing CHECKPOINT_SEGMENTS.

The HINT threw me off since I had to set checkpoint_segments in
postgresql.conf, where as CHECKPOINT_SEGMENTS implied to me a
compile-time constant.

Anyhow, so I deleted the PG data directory, and made these two
changes:

checkpoint_segments=50
sort_mem = 131702

This *really* improved the time for the restore:

restore time: 11594 seconds

then I reset the checkpoint_segments and sort_mem back to old
values...

vacuum analyze time is still 30 minutes
select count(*) from user_list where owner_id=315;   51363.98 ms

so the select appears a bit slower but it is hard to say why.  the
system is otherwise idle as it is not in production yet.


Then I took the suggestion to update PG's page size to 16k and did the
same increase on sort_mem and checkpoint_segments as above.  I also
halved the shared_buffers and max_fsm_pages  (probably should have
halved the effective_cache_size too...)

restore time: 11322 seconds
vacuum analyze time: 27 minutes
select count(*) from user_list where owner_id=315;   48267.66 ms


Granted, given this simple test it is hard to say whether the 16k
blocks will make an improvement under live load, but I'm gonna give it
a shot.  The 16k block size shows me roughly 2-6% improvement on these
tests.

So throw in my vote for 16k blocks on FreeBSD (and annotate the docs
to tell which parameters need to be halved to account for it).


--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/