Обсуждение: logical replication initial copy and WAL generation
Hello Forum,
I know it sounds weird but we have faced it a couple of times already and have no idea.
We're trying to establish logical replication from RDS(10.9) to RDS(12.5).
We're adding tables to the publication one by one.
During the initial copy for some tables we experience huge WAL generation on the source db.
From 0.5mb/s it jumps to 15MB/s.
This happens when a copy just started or in the middle or at the end.
WAL generation causes service statements to wait on WALWriteLock causing high response time.
Any idea how the initial copy can cause WAL generation ?!
Thank you very much,
Rado
On Wed, 2021-05-19 at 14:16 +0300, Radoslav Nedyalkov wrote: > Hello Forum, > I know it sounds weird but we have faced it a couple of times already and have no idea. > We're trying to establish logical replication from RDS(10.9) to RDS(12.5). > We're adding tables to the publication one by one. > During the initial copy for some tables we experience huge WAL generation on the source db. > From 0.5mb/s it jumps to 15MB/s. > This happens when a copy just started or in the middle or at the end. > WAL generation causes service statements to wait on WALWriteLock causing high response time. > > Any idea how the initial copy can cause WAL generation ?! I don't know how much RDS is like PostgreSQL, but on PostgreSQL this could be the setting of hint bits for new rows that have never been read before. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
We have wal_log_hints = off.
Then I thought hint bits are never WAL logged.
Or am I missing the part - they are, upon first read ?
Thanks again
Rado
On Wed, May 19, 2021 at 3:56 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2021-05-19 at 14:16 +0300, Radoslav Nedyalkov wrote:
> Hello Forum,
> I know it sounds weird but we have faced it a couple of times already and have no idea.
> We're trying to establish logical replication from RDS(10.9) to RDS(12.5).
> We're adding tables to the publication one by one.
> During the initial copy for some tables we experience huge WAL generation on the source db.
> From 0.5mb/s it jumps to 15MB/s.
> This happens when a copy just started or in the middle or at the end.
> WAL generation causes service statements to wait on WALWriteLock causing high response time.
>
> Any idea how the initial copy can cause WAL generation ?!
I don't know how much RDS is like PostgreSQL, but on PostgreSQL this could be
the setting of hint bits for new rows that have never been read before.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com