Обсуждение: vacuum question

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

Kevin Kempter <kevink@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

Kevin Kempter <kevink@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

On Tuesday 18 August 2009 13:37:12 Tom Lane wrote:
> Kevin Kempter <kevink@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?

On Tue, Aug 18, 2009 at 2:41 PM, Kevin
Kempter<kevink@consistentstate.com> wrote:
> On Tuesday 18 August 2009 13:37:12 Tom Lane wrote:
>> Kevin Kempter <kevink@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?

Nope, just an open transaction of any kind.