Re: Need to run CLUSTER to keep performance

Scott Marlowe wrote:
> On Nov 12, 2007 10:11 AM, Rafael Martinez <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)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