Re: Logical Replication, CPU load and Locking contention

On Wed, Mar 10, 2021 at 3:56 AM Martín Fernández <fmartin91(at)gmail(dot)com>
wrote:

> Hello,
>
> I’m troubleshooting a problem at my company with a pg 12 cluster that we
> run.
>
> We are using Amazon DMS to replicate data from our database into S3
> buckets. DMS replicates data by using logical replication slots.
>
> After introducing DMS in our environment, we have seen an increase in CPU
> load of 20 points at business hours (from ~60% to ~80%).
>
> The other thing that we have identified is that AccessShareLocks increase
> considerably when DMS running.
>
> Based on this information, I’m trying to understand if this is something
> expected when running logical replication or not. We’ve been running
> physical replication for several years and we haven’t seen nothing like
> this. It could be the case that the issue is not related at all with
> logical replication and is purely a DMS artifact.
>
> Thanks before hand!
>
> Best,
> Martín
>
>
Hi,

I would check in pg_stat_activity what those logical replication slots do.
I guess COPY.
Are you doing one shot copy ? every day ? Then copying all the tables will
lead to load increase.
How many tables at a time DMS copies? It should be configurable.
AccessShareLock is absolutely normal. You have a transaction doing SELECT
(COPY) over a table.

Physical replication is a different thing. It happens at another level.

Regards
Rado