X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 0DD62D1B8C6 for ; Wed, 3 Sep 2003 20:33:20 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 42821-06 for ; Wed, 3 Sep 2003 17:32:49 -0300 (ADT) Received: from ganymede.hub.org (u173n10.eastlink.ca [24.224.173.10]) by svr1.postgresql.org (Postfix) with ESMTP id 9A78FD1B8C5 for ; Wed, 3 Sep 2003 17:32:47 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id 60BB8344B6; Wed, 3 Sep 2003 17:32:37 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 572BF34067; Wed, 3 Sep 2003 17:32:37 -0300 (ADT) Date: Wed, 3 Sep 2003 17:32:37 -0300 (ADT) From: "Marc G. Fournier" To: Vivek Khera Cc: pgsql-performance@postgresql.org Subject: Re: FreeBSD page size In-Reply-To: Message-ID: <20030903173214.I803@ganymede.hub.org> References: <3F4C0DC4.8070105@potentialtech.com> <001201c36d24$3c572460$0500a8c0@MOBILE01> <20030828161233.GB83759@perrin.nxad.com> <3F4E54FB.3020408@potentialtech.com> <20030828193123.GI83317@perrin.nxad.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/31 X-Sequence-Number: 3397 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) >