Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 9CC6C37C69D for ; Mon, 12 Nov 2007 13:01:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 83378-01 for ; Mon, 12 Nov 2007 13:01:24 -0400 (AST) Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.184]) by postgresql.org (Postfix) with ESMTP id F148837C678 for ; Mon, 12 Nov 2007 13:01:29 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id f5so923851nfh for ; Mon, 12 Nov 2007 09:01:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; bh=byu9MOX4u+uKOHUVgVCFqmUqGW3GTgLOp7miwYJYEco=; b=bem8atGUg7obJTRBZrxJQS92s5tB6WsyKgGUnNhbQpu3NAbliH+NWokAHYwFHv7yR6hhpHjcGsR/UCIMC+h+0tvceNAc6vTpJvTvwAeeIEpFBk9klH/dK/97pMcml0eVDY4vuqX/on8egul84SQO5HdjLZYSBdsUwBt8IFx3Z38= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=dvR/ctFoXA63kcIhjRGr1nirl6iSta1R3LDfkuKvPgo7zuZ49ijxFJD//SsZuR8MKYaqyMEe14r8q8vuWQHKBi8uj215oJGDhLIho0ffhveP0xx+NMeojsmtrK0HtEQjYBa0/LnwiOf4YEOs+4I/Xocs00iPOIy8h12Nq0fSCBw= Received: by 10.78.147.6 with SMTP id u6mr5620495hud.1194886887875; Mon, 12 Nov 2007 09:01:27 -0800 (PST) Received: from ?192.168.1.33? ( [82.25.187.136]) by mx.google.com with ESMTPS id c5sm438859nfi.2007.11.12.09.01.22 (version=SSLv3 cipher=RC4-MD5); Mon, 12 Nov 2007 09:01:26 -0800 (PST) Message-ID: <473886DF.1050902@enterprisedb.com> Date: Mon, 12 Nov 2007 17:01:19 +0000 From: Heikki Linnakangas User-Agent: Icedove 1.5.0.12 (X11/20070730) MIME-Version: 1.0 To: Scott Marlowe CC: Rafael Martinez , Tom Lane , pgsql-performance@postgresql.org Subject: Re: Need to run CLUSTER to keep performance References: <4732E6B4.1030907@usit.uio.no> <4732EF07.3060807@enterprisedb.com> <47332200.9050705@usit.uio.no> <24775.1194535980@sss.pgh.pa.us> <4738735B.6030408@usit.uio.no> <47387B4F.7000608@usit.uio.no> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/185 X-Sequence-Number: 27600 Scott Marlowe wrote: > On Nov 12, 2007 10:11 AM, Rafael Martinez wrote: > >> Sending this just in case it can help .... >> >> Checking all the log files from these vacuum jobs we have been running, >> we found one that looks difference from the rest, specially on the >> amount of removed pages. >> >> We are sending also the output before and after the one we are talking >> about: >> >> ############################################### >> 2007-11-11_0245.log >> ############################################### >> COMMAND: /local/opt/pgsql-8.1/bin/psql -h /tmp/pg_sockets/dbpg-meridien >> -p 5432 scanorama -c 'VACUUM VERBOSE ANALYZE hosts' >> CODE: 0 >> >> OUTPUT: >> INFO: vacuuming "public.hosts" >> INFO: index "hosts_pkey" now contains 110886 row versions in 554 pages >> DETAIL: 0 index pages have been deleted, 0 are currently reusable. >> CPU 0.02s/0.00u sec elapsed 0.87 sec. >> INFO: "hosts": found 0 removable, 110886 nonremovable row versions in >> 3848 pages >> DETAIL: 94563 dead row versions cannot be removed yet. >> There were 0 unused item pointers. > > You see that right there? You've got 94k dead rows that cannot be removed. > > Then, later on, they can: > >> CPU 0.04s/0.09u sec elapsed 590.48 sec. >> INFO: "hosts": removed 94551 row versions in 3835 pages >> DETAIL: CPU 0.00s/0.03u sec elapsed 0.10 sec. >> INFO: "hosts": found 94551 removable, 16695 nonremovable row versions >> in 3865 pages > > So, between the first and second vacuum you had a long running > transaction that finally ended and let you clean up the dead rows. No, before 8.3, CLUSTER throws away non-removable dead tuples. So the long running transaction might still be there. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com