Connections on the segments waiting on "MPPEXEC INSERT waiting for XXX/XXXXXXXX replication”

Products

• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite

Issue/Introduction

Due to high workloads, connections on the segments might be waiting on the following: "MPPEXEC INSERT waiting for XXX/XXXXXXXX replication”

Environment

Greenplum Database

Resolution

The connection state "waiting for replication" usually occurs when the primary to mirror sync is slow and It indicates the backend process on the primary is waiting for synchronizing WALs to mirror. This usually happens during the transaction commit stage, the WALs were flushed to local disk on the primary, but need to be transmitted and flushed to mirror disk.

Showing too many this messages may be caused by various reasons:

1. Network I/O status between primary and mirror
2. Workloads or disk I/O on remote (or mirror) machine
3. Mirror is in catchup state, "select * from gp_stat_replication;" on the current primary helps to understand this.
4. Large amount of concurrent write requests.

One of the action items is to gather the following on the primary and mirror host:

1. iostat -dx 1 15
2. netstat -i [Run this a minimum 3-4 times to gather consistent outputs]
3. Check if the cluster is using an architecture similar to GBB [Primary on NVME drives, mirror on slower SSDs]
4. The issue can also occur if the current primary has got marked down and has left orphaned sessions that are waiting on "waiting for XXX/XXXXXXXX replication”. In this case, try connecting to the primary in utility mode and issue a pg_terminate_backend on the orphaned connections.

In the above iostat and netstat outputs, watch for:

1. Higher awaits or w_awaits on the mirror mountpoints. 
2. Increasing RX-ERR, RX-DRP in the netstat -i outputs. [Try collecting 4-5 runs of netstat for checking increase in the packet numbers. 
3. Gather /var/log/messages on the mirror host to check for disk or controller issues.