From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Rafael Martinez <r.m.guerrero@usit.uio.no>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need to run CLUSTER to keep performance
Date: Mon, 12 Nov 2007 17:01:19 +0000
Message-ID: <473886DF.1050902@enterprisedb.com> (raw)
In-Reply-To: <dcc563d10711120835q55fa3829s6d8932058ae6182e@mail.gmail.com>
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>
<dcc563d10711120835q55fa3829s6d8932058ae6182e@mail.gmail.com>
Scott Marlowe wrote:
> On Nov 12, 2007 10:11 AM, Rafael Martinez <r.m.guerrero@usit.uio.no> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, scott.marlowe@gmail.com, r.m.guerrero@usit.uio.no, tgl@sss.pgh.pa.us
Subject: Re: Need to run CLUSTER to keep performance
In-Reply-To: <473886DF.1050902@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox