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
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

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 

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

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

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

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: [EMAIL PROTECTED]       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to