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 B643ED1B8B0 for ; Wed, 3 Sep 2003 19:17:16 +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 33723-06 for ; Wed, 3 Sep 2003 16:16:47 -0300 (ADT) Received: from lorax.kciLink.com (lorax.kciLink.com [206.112.95.1]) by svr1.postgresql.org (Postfix) with ESMTP id 79AD4D1B8BB for ; Wed, 3 Sep 2003 16:16:45 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by lorax.kciLink.com (Postfix) with ESMTP id 95E503E1F for ; Wed, 3 Sep 2003 15:16:41 -0400 (EDT) Received: from lorax.kciLink.com ([127.0.0.1]) by localhost (lorax.kciLink.com [127.0.0.1]) (amavisd-new, port 10024) with SMTP id 04094-09 for ; Wed, 3 Sep 2003 15:16:31 -0400 (EDT) Received: from lorax.kciLink.com (localhost [127.0.0.1]) by lorax.kciLink.com (Postfix) with ESMTP id E43B13E0F for ; Wed, 3 Sep 2003 15:16:30 -0400 (EDT) Received: (from news@localhost) by lorax.kciLink.com (8.12.8p1/8.12.8/Submit) id h83JGUwo043830 for pgsql-performance@postgresql.org; Wed, 3 Sep 2003 15:16:30 -0400 (EDT) To: pgsql-performance@postgresql.org Path: not-for-mail From: Vivek Khera Newsgroups: ml.postgres.performance Subject: Re: FreeBSD page size Date: Wed, 03 Sep 2003 15:16:30 -0400 Organization: Khera Communications, Inc., Rockville, MD Lines: 89 Message-ID: References: <3F4C0DC4.8070105@potentialtech.com> <001201c36d24$3c572460$0500a8c0@MOBILE01> <20030828161233.GB83759@perrin.nxad.com> <3F4E54FB.3020408@potentialtech.com> <20030828193123.GI83317@perrin.nxad.com> NNTP-Posting-Host: yertle.kcilink.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Trace: lorax.kciLink.com 1062616590 42707 216.194.193.105 (3 Sep 2003 19:16:30 GMT) X-Complaints-To: daemon@kciLink.com NNTP-Posting-Date: Wed, 3 Sep 2003 19:16:30 +0000 (UTC) User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Portable Code, berkeley-unix) Cancel-Lock: sha1:G6d1P/Zod/EcLIYLHTj6s013ors= X-Virus-Scanned: by amavisd-new at kciLink.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/24 X-Sequence-Number: 3390 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/