Re: Logical Replication, CPU load and Locking contention

On 3/10/21 2:10 AM, Radoslav Nedyalkov wrote:
>
> On Wed, Mar 10, 2021 at 3:56 AM Martín Fernández <fmartin91(at)gmail(dot)com
> <mailto: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.

If it's a full-load or full-load-and-cdc, then it's almost certainly a 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.

It definitely is, with the MaxFullLoadSubTasks parameter.  The default is 8.

> AccessShareLock is absolutely normal. You have a transaction doing SELECT
> (COPY) over a table.

If DMS with Postgresql as the source is anything like when Oracle is the
source (we're testing Oracle -> RDS Postgresql) then it starts a
SERIALIZABLE transaction.

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

--
Angular momentum makes the world go 'round.