BDR, near xid wraparound, a lot of files in pg_subtrans directory

Started by milist ujangover 8 years ago6 messagesgeneral

Jump to latest

milist ujang

ujang.milist@gmail.com

over 8 years ago

HI list,

I have a database with bdr environment which keep alerting these messages
in log file:

HINT: Close open transactions soon to avoid wraparound problems.
WARNING: oldest xmin is far in the past

Querying pg_stat_activity where state='active';

datname | template1
query | autovacuum: VACUUM pg_catalog.pg_depend (to prevent
wraparound)

datname | template1
query | autovacuum: VACUUM pg_toast.pg_toast_1255 (to prevent
wraparound)

datname | template1
query | autovacuum: VACUUM pg_catalog.pg_ts_parser (to prevent
wraparound)

SELECT
pg_namespace.nspname
,c.relname AS relname
,greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
,c.relfrozenxid
,t.relfrozenxid
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
LEFT JOIN
pg_namespace
ON pg_namespace.oid = c.relnamespace
WHERE c.relkind = 'r' and c.relname='pg_depend';

Trying to vacuum manual, but no luck:

postgres=# vacuum pg_catalog.pg_depend;
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
VACUUM
postgres=# vacuum freeze pg_catalog.pg_depend;
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
VACUUM
postgres=# vacuum full pg_catalog.pg_depend;
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
VACUUM

Check fozenxid again after vacuum, not moving:

Searching the similar situation, found this url:
https://postgrespro.com/list/thread-id/1556972

but the above url was in streaming replication, not bdr; but symptom is
likely same:
there are a lot of files inside pg_subtrans directory, many files older
than latest cluster restarted.

query pg_prepared_xacts, pg_stat_activity, pg_locks with no indication
about long running queries or even open transactions.

--
regards

ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

Craig Ringer

craig@2ndquadrant.com

over 8 years ago

In reply to: milist ujang (#1)

Re: BDR, near xid wraparound, a lot of files in pg_subtrans directory

On 14 September 2017 at 13:35, milist ujang <ujang.milist@gmail.com> wrote:

HI list,

I have a database with bdr environment which keep alerting these messages
in log file:

HINT: Close open transactions soon to avoid wraparound problems.
WARNING: oldest xmin is far in the past

Do you have any idle/old replication slots, perhaps from failed node joins
or abandoned nodes not properly parted?

SELECT *
FROM pg_replication_slots;

Also check

SELECT oid,*
FROM pg_database;

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

milist ujang

ujang.milist@gmail.com

over 8 years ago

In reply to: Craig Ringer (#2)

Re: BDR, near xid wraparound, a lot of files in pg_subtrans directory

Hi Craig,

Thanks again for pointing to inactive replication slot.
After inactive replication slot been dropped, the relfrozenxid now moving.

I wonder if replication identifier will have some issue if left
un-chained? since at other side there are inactive replication identifier.

On Fri, Sep 15, 2017 at 9:36 AM, Craig Ringer <craig@2ndquadrant.com> wrote:

Do you have any idle/old replication slots, perhaps from failed node joins
or abandoned nodes not properly parted?

SELECT *
FROM pg_replication_slots;

Also check

SELECT oid,*
FROM pg_database;

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
regards

ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

Craig Ringer

craig@2ndquadrant.com

over 8 years ago

In reply to: milist ujang (#3)

Re: BDR, near xid wraparound, a lot of files in pg_subtrans directory

On 15 September 2017 at 11:46, milist ujang <ujang.milist@gmail.com> wrote:

Hi Craig,

Thanks again for pointing to inactive replication slot.
After inactive replication slot been dropped, the relfrozenxid now moving.

I wonder if replication identifier will have some issue if left
un-chained? since at other side there are inactive replication identifier.

No, that's harmless.

However, if there's still an "other side" at all, you presumably have
broken replication.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

milist ujang

ujang.milist@gmail.com

over 8 years ago

In reply to: Craig Ringer (#4)

Re: BDR, near xid wraparound, a lot of files in pg_subtrans directory

Hi Craig,

So, is it safe to drop those list from this query output?

select riname from pg_replication_identifier where riname not in
(select external_id from pg_replication_identifier_progress);

I cannot read pg_get_replication_identifier_progress function, is it likely
c function?

On Fri, Sep 15, 2017 at 11:14 AM, Craig Ringer <craig@2ndquadrant.com>
wrote:

On 15 September 2017 at 11:46, milist ujang <ujang.milist@gmail.com>
wrote:

Hi Craig,

Thanks again for pointing to inactive replication slot.
After inactive replication slot been dropped, the relfrozenxid now moving.

I wonder if replication identifier will have some issue if left
un-chained? since at other side there are inactive replication identifier.

No, that's harmless.

However, if there's still an "other side" at all, you presumably have
broken replication.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
regards

ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

milist ujang

ujang.milist@gmail.com

over 8 years ago

In reply to: milist ujang (#5)

Re: [GENERAL] BDR, near xid wraparound, a lot of files in pg_subtrans directory

Hi craig,

I have a bunch of orphaned replication identifier.
How to cleanup those orpaned identifier without affecting currently
configured slots?

I mean what is relation the replication identifier with slot_name in
replication slots?

On Mon, Sep 18, 2017 at 3:00 PM, milist ujang <ujang.milist@gmail.com>
wrote:

Hi Craig,

So, is it safe to drop those list from this query output?

select riname from pg_replication_identifier where riname not in
(select external_id from pg_replication_identifier_progress);

I cannot read pg_get_replication_identifier_progress function, is it
likely c function?

On Fri, Sep 15, 2017 at 11:14 AM, Craig Ringer <craig@2ndquadrant.com>
wrote:

On 15 September 2017 at 11:46, milist ujang <ujang.milist@gmail.com>
wrote:

Hi Craig,

Thanks again for pointing to inactive replication slot.
After inactive replication slot been dropped, the relfrozenxid now
moving.

I wonder if replication identifier will have some issue if left
un-chained? since at other side there are inactive replication identifier.

No, that's harmless.

However, if there's still an "other side" at all, you presumably have
broken replication.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
regards

ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

--
regards

ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab