X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 68DE6D1B267 for ; Thu, 8 Jul 2004 15:03:46 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05136-05 for ; Thu, 8 Jul 2004 18:03:45 +0000 (GMT) Received: from web13121.mail.yahoo.com (web13121.mail.yahoo.com [216.136.174.83]) by svr1.postgresql.org (Postfix) with SMTP id 0E2D7D1B260 for ; Thu, 8 Jul 2004 15:03:43 -0300 (ADT) Message-ID: <20040708180343.88032.qmail@web13121.mail.yahoo.com> Received: from [63.78.248.48] by web13121.mail.yahoo.com via HTTP; Thu, 08 Jul 2004 11:03:43 PDT Date: Thu, 8 Jul 2004 11:03:43 -0700 (PDT) From: Litao Wu Subject: vacuum_mem To: "pgsql-performance@postgresql.org" Cc: "pgsql-performance@postgresql.org" In-Reply-To: <1089305420.5999.208.camel@jester> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=HOT_NASTY, TO_ADDRESS_EQ_REAL X-Spam-Level: X-Archive-Number: 200407/38 X-Sequence-Number: 7429 Hi, I tested vacuum_mem setting under a 4CPU and 4G RAM machine. I am the only person on that machine. The table: tablename | size_kb | reltuples ---------------------------+------------------------- big_t | 2048392 | 7.51515e+06 Case 1: 1. vacuum full big_t; 2. begin; update big_t set email = lpad('a', 255, 'b'); rollback; 3. set vacuum_mem=655360; -- 640M 4. vacuum big_t; It takes 1415,375 ms Also from top, the max SIZE is 615M while SHARE is always 566M PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 5914 postgres 16 0 615M 615M 566M D 7.5 15.8 21:21 postgres: postgres mydb xxx.xxx.xxx.xxx:34361 VACUUM Case 2: 1. vacuum full big_t; 2. begin; update big_t set email = lpad('a', 255, 'b'); rollback; 3. set vacuum_mem=65536; -- 64M 4. vacuum big_t; It takes 1297,798 ms Also from top, the max SIZE is 615M while SHARE is always 566M PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 3613 postgres 15 0 615M 615M 566M D 17.1 15.8 9:04 postgres: postgres mydb xxx.xxx.xxx.xxx:34365 VACUUM It seems vacuum_mem does not have performance effect at all. In reality, we vaccum nightly and I want to find out which vacuum_mem value is the best to short vacuum time. Any thoughts? Thanks, __________________________________ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail