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 D49F6D1B99C for ; Thu, 4 Sep 2003 16:10:22 +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 51223-05 for ; Thu, 4 Sep 2003 13:09:52 -0300 (ADT) Received: from lorax.kciLink.com (lorax.kciLink.com [206.112.95.1]) by svr1.postgresql.org (Postfix) with ESMTP id F2951D1B574 for ; Thu, 4 Sep 2003 13:09:51 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by lorax.kciLink.com (Postfix) with ESMTP id A896F3E33 for ; Thu, 4 Sep 2003 12:09:51 -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 LMTP id 16807-01 for ; Thu, 4 Sep 2003 12:09:41 -0400 (EDT) Received: from lorax.kciLink.com (localhost [127.0.0.1]) by lorax.kciLink.com (Postfix) with ESMTP id F1D523E24 for ; Thu, 4 Sep 2003 12:09:40 -0400 (EDT) Received: (from news@localhost) by lorax.kciLink.com (8.12.8p1/8.12.8/Submit) id h84G9esH023187 for pgsql-performance@postgresql.org; Thu, 4 Sep 2003 12:09:40 -0400 (EDT) To: pgsql-performance@postgresql.org Path: not-for-mail From: Vivek Khera Newsgroups: ml.postgres.performance Subject: Re: FreeBSD page size Date: Thu, 04 Sep 2003 12:09:40 -0400 Organization: Khera Communications, Inc., Rockville, MD Lines: 45 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> <20030903173214.I803@ganymede.hub.org> NNTP-Posting-Host: yertle.kcilink.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Trace: lorax.kciLink.com 1062691780 65673 216.194.193.105 (4 Sep 2003 16:09:40 GMT) X-Complaints-To: daemon@kciLink.com NNTP-Posting-Date: Thu, 4 Sep 2003 16:09:40 +0000 (UTC) User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Portable Code, berkeley-unix) Cancel-Lock: sha1:Cr0ew9tZP8ilJHNbIEHAiUaleds= X-Virus-Scanned: by amavisd-new at kciLink.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/63 X-Sequence-Number: 3429 >>>>> "MGF" == Marc G Fournier writes: MGF> Just curious, but Bruce(?) mentioned that apparently a 32k block size was MGF> found to show a 15% improvement ... care to run one more test? :) Well, it is hard to tell based on my quick and dirty test: 16k page size: restore time: 11322 seconds vacuum analyze time: 1663 seconds (27 minutes) select count(*) from user_list where owner_id=315; 56666.64 ms 32k page size: restore time: 11430 seconds vacuum analyze time: 1346 seconds select count(*) from user_list where owner_id=315; 63275.73 ms one anomaly I note is that if I re-run the select count(*) query above, the large the page size, the longer the query takes. In the standard 8k page size, it was on the order of 306ms, with 16k page size it was over 1400, and with 32k page size nearly 3000ms. Another anomaly I note is that for the larger indexes, the relpages doesn't scale as expected. ie, I'd expect roughly half the relpages per index for 32k page size as for 16k page size, but this is not always the case... some are about the same size and some are about 2/3 and some are about 1/2. The smaller indexes are often the same number of pages (when under 20 pages). I think I'm going to write a synthetic load generator that does a bunch of inserts to some linked tables with several indexes, then goes thru and pounds on it (update/select) from multiple children with occasional vacuum's thrown in. That's the only way to get 'real' numbers, it seems. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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/