X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 784C53ABE85 for ; Wed, 22 Dec 2004 16:38:50 +0000 (GMT) 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 50871-04 for ; Wed, 22 Dec 2004 16:38:47 +0000 (GMT) Received: from web12707.mail.yahoo.com (web12707.mail.yahoo.com [216.136.173.244]) by svr1.postgresql.org (Postfix) with SMTP id 1E3EC3ABEB5 for ; Wed, 22 Dec 2004 16:38:46 +0000 (GMT) Received: (qmail 93594 invoked by uid 60001); 22 Dec 2004 16:38:44 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=cjBUNNOq114c0NLNNm4776xDjinuP15VRg9GvQ2zht/LWE/IBMuQCdx6HCaD1NyAELhE/v6ocGWRLbMbZ6XDHwZM2z+/bhD2HCkSWVhzblZyvQIndI61euBPuFQLGAhdgMCoKGj/DRdChkwy0i03nB8O7edq1A1Q7Pyk4vVpg10= ; Message-ID: <20041222163844.93592.qmail@web12707.mail.yahoo.com> Received: from [216.194.17.194] by web12707.mail.yahoo.com via HTTP; Wed, 22 Dec 2004 08:38:43 PST Date: Wed, 22 Dec 2004 08:38:43 -0800 (PST) From: Reply-To: ogjunk-pgjedan@yahoo.com Subject: Re: VACUUMing for 30 minutes To: pgsql-admin@postgresql.org In-Reply-To: <4284.1103732076@sss.pgh.pa.us> 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.459 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200412/224 X-Sequence-Number: 15810 Hello, --- Tom Lane wrote: > writes: > > VACUUMing this DB takes about 30 minutes, and during that time the > DB > > is pretty unresponsive, although the PG process is not using a lot > of > > CPU (load ~ 1) nor memory (~20MB for the VACUUM process). > > How big is the DB physically ("du $PGDATA" results)? 4.2 GB: # du -h ~postgres/data 3.6M /var/lib/pgsql/data/base/1 3.6M /var/lib/pgsql/data/base/16975 4.0K /var/lib/pgsql/data/base/16976/pgsql_tmp 4.1G /var/lib/pgsql/data/base/16976 4.1G /var/lib/pgsql/data/base 152K /var/lib/pgsql/data/global 129M /var/lib/pgsql/data/pg_xlog 1.1M /var/lib/pgsql/data/pg_clog 4.2G /var/lib/pgsql/data > If you've been lax > about vacuuming or not had your FSM parameters set high enough, there > could be a whole lot of dead space for VACUUM to scan through. I've been vacuuming every night, like a good DBwife. > If so, > VACUUM FULL or possibly CLUSTER would be the best way to re-compact > the > tables. (VACUUM VERBOSE on your larger tables would be another way > to > investigate this.) I will try VACUUM VERBOSE on the biggest (and most active) table tonight and report the findings. > The other possibility is that you have a seriously slow disk drive > :-( It looks like I have an ATA-6 drive with only 2MB cache, and the following throughput: # /sbin/hdparm -tT /dev/hda /dev/hda: Timing buffer-cache reads: 128 MB in 0.56 seconds =228.57 MB/sec Timing buffered disk reads: 64 MB in 1.18 seconds = 54.24 MB/sec Not SCSI, not RAID, but not the slowest HDD on the continent. Thanks, Otis