Postgres Pro
Downloads
Home   >   mailing lists

Thread: logical replication initial copy and WAL generation

logical replication initial copy and WAL generation

From
Radoslav Nedyalkov
Date:
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

Re: logical replication initial copy and WAL generation

From
Laurenz Albe
Date:
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

Re: logical replication initial copy and WAL generation

From
Radoslav Nedyalkov
Date:
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