Debezium 1.9 Oracle XStream performance issue
Marcell Rózsás
Hi,
We are using Debezium with Oracle XStream and I would like to ask for help to optimize or scale Debezium.
Debezium replicates 6 tables from a database. One of this table generates a lot of change. Every night the application updates all the records in this table. It’s around 20 million changes per night. (This application generates a lot of small transactions, it doesn’t update all the data in one large bulk. )
Unfortunately, our configuration can handle only 600-700 event per second, it means that Debezium processing time is around 8-10 hours. Our goal would be around 5000-7000 per second. So, we are very far from required performance.
This is an image which shows CPU and memory usage while we are processing the data. It uses only one core at 100%.
My questions are:
· How can we optimize our debezium or xtream? I tried to set poll.interval.ms, max.batch.size, and max.queue.size higher but there wasn’t any effect on performance
· How can we scale Debezium? Is it a good idea to create more outbound servers for different tables at Oracle side? As I’ve read about it, it isn’t a good solution, because in this case, more than one capture process will read the same file.
(Please consider, that Debezium processing was only 4.5 hours. The reason why this happened, is because capture process stopped, due to RMAN deleted the archive log file. It isn’t a problem. We use this configuration only in dev environment because we don’t have enough space for archive log files, and we don’t want to stop our database. So it’s intentional, not a bug or misconfiguration.)
Thanks, Marcel
This is our configuration:
{
"name": "xstream-connector-2",
"config": {
"connector.class": "io.debezium.connector.oracle.OracleConnector",
"transforms.TABLE4.replacement": "spli-debezium_table4",
"transforms.TABLE1.type": "org.apache.kafka.connect.transforms.RegexRouter",
"transforms.TABLE5.type": "org.apache.kafka.connect.transforms.RegexRouter",
"transforms.TABLE5.replacement": "spli-debezium_table5",
"tasks.max": "1",
"database.history.kafka.topic": "spli-debezium_schema-changes",
"transforms": "TABLE1,TABLE2,TABLE3,TABLE4,TABLE5",
"database.tablename.case.insensitive": "true",
"transforms.TABLE2.regex": ".*SCHEMA.TABLE2",
"transforms.TABLE3.replacement": "spli-debezium_table3",
"transforms.TABLE4.type": "org.apache.kafka.connect.transforms.RegexRouter",
"include.schema.changes": "false",
"decimal.handling.mode": "string",
"internal.database.oracle.version": "11",
"database.history.skip.unparseable.ddl": "true",
"database.out.server.name": "OUTBOUND_SERVER",
"database.oracle.version": "11",
"database.user": "DEBEZIUM_PROXY",
"database.dbname": "DBNAME",
"database.connection.adapter": "xstream",
"database.history.kafka.bootstrap.servers": "KAFKA",
"transforms.TABLE1.regex": ".*SYMBOLS.TABLE1",
"database.server.name": "DBNAME",
"database.port": "PORT",
"key.converter.schemas.enable": "false",
"transforms.TABLE1.replacement": "spli-debezium_table1",
"transforms.TABLE4.regex": ".*SCHEMA.TABLE4",
"database.hostname": "DBHOST",
"transforms.TABLE2.type": "org.apache.kafka.connect.transforms.RegexRouter",
"database.password": "PW",
"value.converter.schemas.enable": "false",
"transforms.TABLE3.type": "org.apache.kafka.connect.transforms.RegexRouter",
"name": "xstream-connector-2",
"table.include.list": ".*SCHEMA.TABLE1,.*SCHEMA.TABLE2,.*SCHEMA.TABLE3,.*SCHEMA.TABLE4',.*SCHEMA.TABLE5,.*SCHEMA.TABLE6",
"transforms.TABLE3.regex": ".*SCHEMA.TABLE3",
"transforms.TABLE5.regex": ".*SCHEMA.TABLE5",
"transforms.TABLE2.replacement": "spli-debezium_table2",
"key.converter": "org.apache.kafka.connect.json.JsonConverter",
"value.converter": "org.apache.kafka.connect.json.JsonConverter",
"poll.interval.ms":"5000",
"max.batch.size":"8192",
"max.queue.size":"327680"
}
}
Chris Cranford
So from the Debezium side, we simply invoke a callback into the Oracle XStream/GoldenGate API and wait for Oracle to return. If there is no data available for us, Oracle explicitly pauses for 1 second. This loop on the connector side will then simply recall the callback and wait for Oracle to return again. If Oracle returns us an LCR record, we process it and immediately call the callback again and await a response from Oracle. There is really not any tuning knob or setting to speed up this process unfortunately, the connector is from this regard at the mercy of Oracle.
That said, not all is lost. It is possible (you said this is a dev env) that the environment itself may be scaled back from production, meaning that GoldenGate processes may not perform nearly as efficiently as they may in production. You may want to check with your DBA team and see what Oracle tuning knobs for GoldenGate could be different in the dev environment that could lead to slower performance. As you noted, there is a capture process on the Oracle side that runs to read the logs, there is a reader & coordinator process that is responsible for batching LCR events together all before they're made available in the queue that Xstream reads to make them available to the client. It is highly possible that for the volume of changes, that environment may not be configured ideally for that.
As to number of outbound servers, as you mentioned, multiple servers means multiple capture processes which means more database processes that consume valuable resources. We haven't the effects of multiple outbound servers, but there shouldn't be any issue from Debezium's perspective as you would have a connector deployed for each outbound server you've configured. You would just want to make sure you don't share the database history topic across multiple connectors in this type of deployment.
And finally, regarding the image; one core makes sense. As I eluded to in the above, the interaction with Oracle XStream/GoldenGate is single-threaded, and we are restricted to using that JDBC connection with Xstream in a single-threaded way. The only way you could look into parallelizing that would be to have multiple outbound servers that handle each table separately with its own corresponding Debezium connector reading that given outbound server.
Another option you could consider would be to see if using the LogMiner adapter over XStream is any faster.
Thanks,
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 on the web visit https://groups.google.com/d/msgid/debezium/e52b125f-354b-471e-a266-906c3b653837n%40googlegroups.com.