Bi-modal streaming replication throughput - Mailing list pgsql-performance

# The setup

We run large, SSD-backed, i3.16xl (64 cores visible to Linux, ~500GB of RAM, with 8GB of shared_buffers, fast NVMe drives) nodes running PG 9.3 in a vanilla streaming asynchronous replication setup: 1 primary node, 1 replica designated for failover (left alone) and 6 read replicas, taking queries.

Under normal circumstances this is working exactly as planned but when I dial up the number of INSERTs on the primary to ~10k rows per second, or roughly 50MB of data per second (not enough to saturate the network between nodes). The INSERTs themselves are fairly straightforward: a 20-bytea checksum is computed off-node and used as a unicity constraint at insert time. Each record is 4,500 bytes wide on average.

ere’s the table where inserts happen.

                                                  Table “T”
     Column     |            Type             |               Modifiers                | Storage  |
----------------+-----------------------------+----------------------------------------+----------+
 key            | bigint                      | not null default T.next_key()      | plain    |
 a              | integer                     | not null                               | plain    |
 b              | integer                     |                                        | plain    |
 c              | text                        |                                        | extended |
 d              | text                        |                                        | extended |
 e              | text[]                      |                                        | extended |
 f              | integer                     | not null                               | plain    |
 created        | timestamp without time zone | not null default now()                 | plain    |
 cksum          | bytea                       | not null                               | extended |
Indexes:
    “T_pkey" PRIMARY KEY, btree (key)
    “T_cksum” UNIQUE, btree (cksum)
    “T_created_idx" btree (created)
    “T_full_idx" btree (a, b, c, d, e)
    “T_a_idx" btree (a)


# The symptoms

Once the primary starts to process INSERTs to the tune of 10k/s (roughly 0MB/s or 150GB/h), replication throughput becomes bi-modal
1. We see read replicas fall behind and we can measure their replication throughput to be 1-2% of what the primary is sustaining, by measuring the replication delay (in second) every second. We quickly getto 0.98-0.99 (1 means that replication is completely stuck). CPU, memory, I/O or network as a whole resource are not maxed out
2. If we stop incoming queries from one of the replicas, we see it catch up at 2x insert throughput (roughly 80MB/s or 300GB/h) as it is cutting through the backlog. A perf sample shows a good chunk of time spent in `mdnblocks`. I/O wait remains a few %