Обсуждение: Vacuum Issues

On Mar 26, 2020, at 2:10 PM, Darron Harrison <darron@realtyserver.com> wrote:

Anyone have any thoughts on what we should be checking next?

Check the backend_xmin in pg_stat_activity; is the holdup in one of replicas or on the master? 

select pid

 , usename

 , client_addr

 , state

 , backend_type

 , backend_xid

 , backend_xmin

from pg_stat_activity

where backend_xmin is not null

order by age(backend_xmin) desc

limit 10

;

Two-phase transactions:

select * from pg_prepared_xacts;

Replication slots: 

select * from pg_replication_slots;

On Mar 26, 2020, at 3:01 PM, Darron Harrison <darron@realtyserver.com> wrote:

Unable to run the first query and third query. I believe it's a version issue, as we are running 9.2.4.

Looks like backend_xmin was added in 9.4; shoot

Replication slots was added in 9.4, too; no slots to worry about.

Since you don’t have access to backend_xmin; you have check the master and all the replicas as you have feedback enabled.  

I would look at all non “idle” sessions — i.e. “idle in tran” could be holding on to an old xmin. 

select pid

  , usename

  , client_addr

  , client_port

  , state

  , age(now(), state_change) as state_time

  , age(now(), xact_start) as xact_time

from pg_stat_activity

where state != 'idle'

order by xact_time desc

;

On Mar 26, 2020, at 4:18 PM, Darron Harrison <darron@realtyserver.com> wrote:

I am not seeing anything suspicious on the master or slaves.

I would ask what replication timeout values are but those where added in 9.3 (wal_reciver_timeout and wal_sender_timeout).

Are all the replicas receiving updates and current or are they lagging? Does replication traverse any firewalls?

On Mar 26, 2020, at 5:40 PM, Darron Harrison <darron@realtyserver.com> wrote:

There is no current lag on the replicas. Replication does traverse a firewall, but we have made no changes recently.

I will say that one of the hot standbys was only recently attached, and it seems like the issues started when we began sending some longer running queries it's way. We have since placed those queries back on the master, but the vacuum issues remain.

One side effect of whatever is happening, is that nightly backups are taking twice as long as normal.

It could sill be a bad/sale replication session.  If the firewall drops the replication stream and does not send a reset packets (bad pratice) then the replication session might still be lingering on the Postgres server and holding on to a very old xmin.  Do you know if you have TCP/IP Keepalive enabled? I don’t think in 9.2 replication sessions are listed in pg_stat_activity; thus, you’ll have to look for TCP/IP connections to the replics that should not exist. 

Check all the upstream servers for stale TCP/IP replication connections; using netstat.  I would also look at the system’s process list for walsender processes to see if there more more than there should be; i.e. two of them for single replica.

If you do find one; the best way to terminate it is to drop the TCP/IP connection.  i.e. In FreeBSD it would be the command “tcpdrop”; for Linux there are few utilities that do same -- I just don’t recall the name of them.