From: Scott Marlowe <scott.marlowe@gmail.com>
To: Rafael Martinez <r.m.guerrero@usit.uio.no>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need to run CLUSTER to keep performance
Date: Mon, 12 Nov 2007 10:35:52 -0600
Message-ID: <dcc563d10711120835q55fa3829s6d8932058ae6182e@mail.gmail.com> (raw)
In-Reply-To: <47387B4F.7000608@usit.uio.no>
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>
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.
> 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.
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: scott.marlowe@gmail.com, r.m.guerrero@usit.uio.no, tgl@sss.pgh.pa.us, heikki@enterprisedb.com
Subject: Re: Need to run CLUSTER to keep performance
In-Reply-To: <dcc563d10711120835q55fa3829s6d8932058ae6182e@mail.gmail.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