Обсуждение: [GENERAL] BDR, near xid wraparound, a lot of files in pg_subtrans directory

Поиск
Список
Период
Сортировка

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

От
milist ujang
Дата:
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';

-[ RECORD 1 ]+-----------
nspname      | pg_catalog
relname      | pg_depend
age          | 1165907267
relfrozenxid | 102246720
relfrozenxid |

 
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:

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';
-[ RECORD 1 ]+-----------
nspname      | pg_catalog
relname      | pg_depend
age          | 1165908742
relfrozenxid | 102246720
relfrozenxid |


Searching the similar situation, found this url:

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

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

От
Craig Ringer
Дата:
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

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

От
milist ujang
Дата:
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

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

От
Craig Ringer
Дата:
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

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

От
milist ujang
Дата:
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

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

От
milist ujang
Дата:
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 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 > wrote: > >> On 15 September 2017 at 11:46, milist ujang >> 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