From: Vivek Khera <khera@kcilink.com>
To: pgsql-performance@postgresql.org
Subject: Re: FreeBSD page size
Date: Wed, 03 Sep 2003 15:16:30 -0400
Message-ID: <x7n0dl8yy9.fsf@yertle.int.kciLink.com> (raw)
References: <3F4C0DC4.8070105@potentialtech.com>
<001201c36d24$3c572460$0500a8c0@MOBILE01>
<m3u182gg08.fsf@chvatal.cbbrowne.com>
<20030828161233.GB83759@perrin.nxad.com>
<3F4E54FB.3020408@potentialtech.com>
<20030828193123.GI83317@perrin.nxad.com>
<x7u17t98l5.fsf_-_@yertle.int.kciLink.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: khera@kcilink.com
Subject: Re: FreeBSD page size
In-Reply-To: <x7n0dl8yy9.fsf@yertle.int.kciLink.com>
References: <3F4C0DC4.8070105@potentialtech.com> <001201c36d24$3c572460$0500a8c0@MOBILE01> <m3u182gg08.fsf@chvatal.cbbrowne.com> <20030828161233.GB83759@perrin.nxad.com> <3F4E54FB.3020408@potentialtech.com> <20030828193123.GI83317@perrin.nxad.com> <x7u17t98l5.fsf_-_@yertle.int.kciLink.com> <x7n0dl8yy9.fsf@yertle.int.kciLink.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox