On 3/10/21 2:10 AM, Radoslav Nedyalkov 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.
We are doing full-load and full-load-and-cdc. At this point it’s just cdc since the full load was done month ago.
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.
We are copying a lot of tables. 100+
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.
My guess at this point is that the CPU load increases due to the increase of AccessShareLocks, is that a fair assumption ? Have you seen a similar behavior in Oracle ?
Physical replication is a different thing. It happens at
another level.
Regards
Rado
--
Angular momentum makes the world go 'round.
|
