Question about config setting(defaultRowPrefetch, defaultBatchValue...)

[ddaeyongee]

@cer @dartartem @eventuateio

Hi. I'm taeyong In LG CNS Software Application Architect Unit. Spring has come. Please take care of your health in the changing season.
I have one question about config.

I want to increase the performance of linked oracles in the process of mass publishing a specific topic. When I searched related oracle connection properties, these properties exist.

defaultRowPrefetch - the default number of rows to prefetch from the server (default value is "10")
defaultBatchValue - the default batch value that triggers an execution request (default value is "10")

So, I applied it to application.properties,

application.properties

eventuate.cdc.reader.reader.readersample.datasourceUrl=jdbc:oracle:thin:@111.11.11.111:3525
eventuate.cdc.reader.reader.readersample.datasourceUsername=test
eventuate.cdc.reader.reader.readersample.datasourcePassword=test01
eventuate.cdc.reader.reader.readersample.datasourceDriverClassName=oracle.jdbc.OracleDriver
eventuate.cdc.reader.reader.readersample.leadershipLockPath=/eventuate/cdc-test/leader/sample
eventuate.cdc.reader.reader.readersample.pollingIntervalInMilliseconds=1
eventuate.cdc.reader.reader.readersample.maxEventsPerPolling=1000
eventuate.cdc.reader.reader.readersample.maxAttemptsForPolling=100
eventuate.cdc.reader.reader.readersample.pollingRetryIntervalInMilliseconds=100

eventuate.cdc.pipeline.pipelinesample.type=eventuate-tram
eventuate.cdc.pipeline.pipelinesample.eventuateDatabaseSchema=sample
eventuate.cdc.pipeline.pipelinesample.sourceTableName=message
eventuate.cdc.pipeline.pipelinesample.reader=readersample

eventuate.local.kafka.producer.perperties.buffer.memory=35544320
eventuate.local.kafka.producer.perperties.batch.size=1024000
...
spring.datasource.connection-properties:
defaultRowPrefetch=10;defaultBatchValue=10;useFetchSizeWithLongColumn=true

At first, I thought the connectionProperties value was being set but as I fine-tuned the query in SQL Developer (cost went from 3670 to 285 and plan explain went from :10 to :03), the time in the application never fluctuated from the original 15 seconds. Removing the connectionProperties setting had no effect.

So, what I did was this:

DAO class

private List getAllActivitiesJustJDBC() {
String query = "select * " + "from activity a, work_order w "
+ "where a.ac_customer = 'CSC' "
+ "and w.wo_customer = a.ac_customer "
+ "and a.ac_workorder = w.wo_workorder ";
long startTime = System.currentTimeMillis();
List activities = new ArrayList();
try {
Connection conn = jdbcTemplate.getDataSource().getConnection();
PreparedStatement st = conn.prepareStatement(query);
st.setFetchSize(1000);
ResultSet rs = st.executeQuery();
ActivityMapper mapper = new ActivityMapper();
while (rs.next()) {
Activity activity = mapper.mapRow(rs, 1);
activities.add(activity);
}
} catch (Exception ex) {
ex.printStackTrace();
}
System.out.println("Time it took...."
+ (System.currentTimeMillis() - startTime));
System.out.println("Number of activities = " + activities.size());
return activities;
}

This time, the time it took to fetch 11,115 rows took on average 2 seconds. The key statement is the setFetchSize(1000).

do I need to close the connection or is Spring handling this for me?
How can oracle connection-properties be involved in eventuate-cdc behavior? If you have any advice, please reply.

[cer]

I hope all is well with you.

This is how it currently works.
A DataSource is created dynamically - not by Spring Boot - for each polling reader:

• The DataSource is created in DataSourceFactory
• createDataSource() is called by PollingCdcPipelineReaderFactory
• ConnectionPoolConfigurationProperties is an @ConnectionPoolConfigurationProperties class

This is what needs to happen:

• defaultRowPrefetch=10;defaultBatchValue=10;useFetchSizeWithLongColumn=true need to be passed to HikariConfig.setDataSourceProperties()/addDataSourceProperty()

One solution would be to add a Map property to ConnectionPoolConfigurationProperties for these DataSourceProperties.

I noticed however that Hikari can read a configuration file. I wonder whether a workaround would be to specified these nested "dataSourceProperties" in the configuration file.