Re: Need to run CLUSTER to keep performance

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.

> After this last job the amount of dead rows just continued growing until
> today.

I think you've got a long running transaction that's preventing you
from recovering dead rows.