Debezium Oracle connector high MilliSecondsBehindSource

[Thành Văn]

Hi everybody,

Can you help to optimize Oracle connector, currently I got high value of metrics MilliSecondsBehindSource. This value is variety, it can be < 1 minutes but can peak to hours or even day. Below is my current connector config. Thanks in advance!

"name": "MY_CDC_DEBEZIUM_SOURCE",

"tasks.max": "1",

"connector.class": "io.debezium.connector.oracle.OracleConnector",

"snapshot.locking.mode": "none",

"snapshot.mode": "when_needed",

"topic.creation.default.partitions": "2",

"topic.prefix": "MY_CDC",

"topic.creation.default.replication.factor": "3",

"topic.creation.default.compression.type": "lz4",

"topic.creation.default.cleanup.policy": "compact",

"schema.history.internal.store.only.captured.tables.ddl": "true",

"schema.history.internal.store.only.captured.databases.ddl": "true",

"schema.history.internal.kafka.topic": "MY_CDC_SCHEMA_HISTORY",

"schema.history.internal.kafka.bootstrap.servers": "a.b.c.31:9092,a.b.c.32:9092,a.b.c.33:9092,a.b.c.34:9092,a.b.c.35:9092",

"transforms": "route",

"transforms.route.type": "org.apache.kafka.connect.transforms.RegexRouter",

"transforms.route.regex": "MY_CDC\\.([^.]+)\\.([^.]+)",

"transforms.route.replacement": "MY_CDC_$1_$2",

"database.history.store.only.captured.tables.ddl": "true",

"database.user": "xxxxxxxx",

"database.server.id": "20257",

"database.url": "jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=ON)(LOAD_BALANCE=OFF)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=x.ys.z.84)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=x.ys.z.85)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=x.ys.z.86)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ebdab)))",

"database.port": "1521",

"database.driverClassName": "oracle.jdbc.OracleDriver",

"database.password": "xxxxxxxx",

"database.dbname": "MY_CDC",

"table.include.list": "MULTIPLE_TABLES"

"heartbeat.action.query": "UPDATE MY_TABLE.heartbeat_table SET now = CURRENT_TIMESTAMP WHERE ID=1",

"heartbeat.interval.ms": "30000",

"event.processing.failure.handling.mode": "warn",

"inconsistent.schema.handling.mode": "warn",

"lob.enabled": "true",

"max.request.size": "536870912",

"max.message.bytes": "5083491",

"producer.max.request.size": "536870912",

"producer.max.message.bytes": "5083491",

"max.batch.size": "20000",

"max.queue.size": "25000",

"log.mining.transaction.retention.ms": "14400000",

"log.mining.sleep.time.min.ms": "2000",

"log.mining.sleep.time.max.ms": "5000",

"log.mining.sleep.time.increment.ms": "1000",

"log.mining.batch.size.max": "250000",

"log.mining.batch.size.default": "20000",

"log.mining.session.max.ms": "21600000",

"archive.log.hours": "47",

"internal.log.mining.log.query.max.retries": "15",

"internal.log.mining.log.backoff.initial.delay.ms": "5000",

"internal.log.mining.log.backoff.max.delay.ms": "180000",

"poll.interval.ms": "6000"

[Chris Cranford]

Hi 

The MilliSecondsBehindSource is going to depend on a variety of factors. You have `lob.enabled`, which means for any transaction, we must always go back to the start of the transaction and re-read it from the beginning. If you have long running or large transactions that can take minutes or hours to complete, this will have a direct impact on this number. In addition, you are not setting `log.mining.query.filter.mode` with a value of `in`, which means every operation in a transaction is being sent back to the connector to filter rather than having the database perform a pre-filter pass. You might want to consider setting this as long as your `table.include.list` does not include any regular expressions.

When the value begins to grow, can you look at OldestScnAgeInMilliseconds and see if that value also continues to grow? If it does, then this is due to large/long-running transactions.

-cc

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/debezium/b2d35b77-8737-4cb6-bead-cd87af21a324n%40googlegroups.com.

[Thành Văn]

Dear Chris,

Thanks for reply my issue.

Currently I got more than 2 hours for both MilliSecondsBehindSource and OldestScnAgeInMilliseconds. As I understand that I have query to update hearbeat table, so SCN will be increased every 30s, I am wondering that why these metrics is too high?

[Chris Cranford]

Hi -

As I mentioned below, its due to a long running transaction. The OldestScnAgeInMilliseconds refers to the start position of the eldest in-progress transaction and how old it is relative to the current time. 

Thanks,
-cc

To view this discussion visit https://groups.google.com/d/msgid/debezium/e8ba83fc-92eb-42d3-a2f0-7ba2e0544026n%40googlegroups.com.

[Thành Văn]

Thanks Chris,

Do you have any suggestion for my current connector configuration beside of lob.enable and log.mining.query.filter.mode ?

Vào Th 2, 2 thg 2, 2026 lúc 22:39 Chris Cranford <cran...@gmail.com> đã viết:

To view this discussion visit https://groups.google.com/d/msgid/debezium/3aefc426-f17c-4f67-b7b7-f58014e2db12%40gmail.com.