vacuum question

--Boundary-00=_46viKalyYoXbVEM
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi all;

we have a large table that gets a lot of churn throughout the day.

performance has dropped off a cliff. A vacuum verbose on the table showed us
this:

INFO: "action_rollup_notifier": found 0 removable, 34391214 nonremovable row
versions in 152175 pages
DETAIL: 22424476 dead row versions cannot be removed yet.
There were 0 unused item pointers.
2 pages contain useful free space.
0 pages are entirely empty.
CPU 0.03s/2.58u sec elapsed 4.89 sec.
VACUUM

were running at isolation level 1 (autocommit), using serialized transactions
and a persistent db connection.

Anyone have any suggestions per why these rows cannot be removed yet? As far
as we can see these rows should be 'frozen' at this point. Can we force the
issue somehow?

Thanks in advance

--Boundary-00=_46viKalyYoXbVEM
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN" "http://www.w3.org/TR/REC-html40/strict.dtd"><html><head><meta name="qrichtext" content="1" /><style type="text/css">p, li { white-space: pre-wrap; }</style></head><body style=" font-family:'Sans Serif'; font-size:8pt; font-weight:400; font-style:normal;">Hi all; 
 we have a large table that gets a lot of churn throughout the day. 
 performance has dropped off a cliff. A vacuum verbose on the table showed us this: 
 
 INFO: "action_rollup_notifier": found 0 removable, 34391214 nonremovable row versions in 152175 pages 
DETAIL: 22424476 dead row versions cannot be removed yet. 
There were 0 unused item pointers. 
2 pages contain useful free space. 
0 pages are entirely empty. 
CPU 0.03s/2.58u sec elapsed 4.89 sec. 
VACUUM 
 were running at isolation level 1 (autocommit), using serialized transactions and a persistent db connection. 
 Anyone have any suggestions per why these rows cannot be removed yet? As far as we can see these rows should be 'frozen' at this point. Can we force the issue somehow? 
 Thanks in advance 
 </body></html>
--Boundary-00=_46viKalyYoXbVEM--

Kevin Kempter <kevink [at] consistentstate.com> writes:
> INFO: "action_rollup_notifier": found 0 removable, 34391214 nonremovable row
> versions in 152175 pages
> DETAIL: 22424476 dead row versions cannot be removed yet.

> Anyone have any suggestions per why these rows cannot be removed yet?

You've got an open transaction that's older than the one that deleted
those rows (or at least started before the latter committed).
pg_stat_activity might be helpful in fingering the culprit.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Kevin Kempter <kevink [at] consistentstate.com> wrote:
> INFO: "action_rollup_notifier": found 0 removable, 34391214
> nonremovable row versions in 152175 pages
> DETAIL: 22424476 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 2 pages contain useful free space.
> 0 pages are entirely empty.

> Anyone have any suggestions per why these rows cannot be removed
> yet?

The only time I've seen numbers like that is when there was a
long-running transaction which was preventing VACUUM from cleaning
anything up. (In our case it was a programmer using an unapproved
tool against a production server; the tool issued a BEGIN TRANSACTION
and just sat there.)

Look at the pg_stat_activity table. If you have any values in
xact_start more than a few seconds old, you've identified your
culprit. I'm betting it will be "idle in transaction".

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Tuesday 18 August 2009 13:37:12 Tom Lane wrote:
> Kevin Kempter <kevink [at] consistentstate.com> writes:
> > INFO: "action_rollup_notifier": found 0 removable, 34391214 nonremovable
> > row versions in 152175 pages
> > DETAIL: 22424476 dead row versions cannot be removed yet.
> >
> > Anyone have any suggestions per why these rows cannot be removed yet?
>
> You've got an open transaction that's older than the one that deleted
> those rows (or at least started before the latter committed).
> pg_stat_activity might be helpful in fingering the culprit.
>
> regards, tom lane

Does the use of serialized transactions affect this in any way?

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Tue, Aug 18, 2009 at 2:41 PM, Kevin
Kempter<kevink [at] consistentstate.com> wrote:
> On Tuesday 18 August 2009 13:37:12 Tom Lane wrote:
>> Kevin Kempter <kevink [at] consistentstate.com> writes:
>> > INFO: "action_rollup_notifier": found 0 removable, 34391214 nonremovab=
le
>> > row versions in 152175 pages
>> > DETAIL: 22424476 dead row versions cannot be removed yet.
>> >
>> > Anyone have any suggestions per why these rows cannot be removed yet?
>>
>> You've got an open transaction that's older than the one that deleted
>> those rows (or at least started before the latter committed).
>> pg_stat_activity might be helpful in fingering the culprit.
>>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 regards, tom lane
>
> Does the use of serialized transactions affect this in any way?

Nope, just an open transaction of any kind.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

vacuum question

Re: vacuum question

Re: vacuum question

Re: vacuum question

Re: vacuum question