pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marc G. Fournier <scrappy@hub.org>
To: Vivek Khera <khera@kcilink.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: FreeBSD page size
Date: Wed, 3 Sep 2003 17:32:37 -0300 (ADT)
Message-ID: <20030903173214.I803@ganymede.hub.org> (raw)
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>



Just curious, but Bruce(?) mentioned that apparently a 32k block size was
found to show a 15% improvement ... care to run one more test? :)

On Wed, 3 Sep 2003, Vivek Khera wrote:

> 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/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>



view thread (13+ messages)  latest in thread

Message-ID: <20030903173214.I803@ganymede.hub.org>
Permalink:  ../20030903173214.I803@ganymede.hub.org/
Also on:    postgresql.org/message-id/20030903173214.I803@ganymede.hub.org

 · 

reply

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: scrappy@hub.org, khera@kcilink.com
  Subject: Re: FreeBSD page size
  In-Reply-To: <20030903173214.I803@ganymede.hub.org>
  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> <20030903173214.I803@ganymede.hub.org>

* 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