XA Datasource: More efficient way to add?
Opened this issue · 2 comments
cristalp commented
As a newbie, I've started to define my datasources (I have 30 datasources).
This is a typical example:
<xa-datasource jndi-name="java:jboss/datasources/dsAuditTrail" pool-name="AuditTrailPool" enabled="true" spy="false" use-ccm="true" statistics-enabled="true">
<xa-datasource-property name="URL">
jdbc:sqlserver://foobar:1433;databaseName=audit_trail
</xa-datasource-property>
<xa-datasource-property name="User">
audit_trail_user01
</xa-datasource-property>
<xa-datasource-property name="Password">
change_the_password
</xa-datasource-property>
<xa-datasource-property name="SelectMethod">
cursor
</xa-datasource-property>
<xa-datasource-property name="ApplicationIntent">
ReadWrite
</xa-datasource-property>
<xa-datasource-property name="ApplicationName">
JBoss-AuditTrailPool
</xa-datasource-property>
<driver>sqlserver</driver>
<xa-pool>
<min-pool-size>5</min-pool-size>
<max-pool-size>64</max-pool-size>
<prefill>false</prefill>
<is-same-rm-override>false</is-same-rm-override>
<no-tx-separate-pools>true</no-tx-separate-pools>
<pad-xid>true</pad-xid>
</xa-pool>
<recovery>
<recover-credential>
<user-name>audit_trail_user01</user-name>
<password>change_the_password</password>
</recover-credential>
</recovery>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
<validate-on-match>false</validate-on-match>
<background-validation>true</background-validation>
<background-validation-millis>5000</background-validation-millis>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter"/>
</validation>
<statement>
<track-statements>true</track-statements>
<prepared-statement-cache-size>5000</prepared-statement-cache-size>
</statement>
</xa-datasource>
If I add it using JBoss CLI, I do the following:
xa-data-source add --name=AuditTrailPool --jndi-name=java:jboss/datasources/dsAuditTrail \
--spy=false --use-ccm=true --statistics-enabled=true --driver-name=sqlserver \
--xa-datasource-properties={ \
"URL" => "jdbc:sqlserver://foobar:1433;databaseName=audit_trail", \
"User" => "audit_trail_user01", \
"Password" => "change_the_password", \
"SelectMethod" => "cursor", \
"ApplicationIntent" => "ReadWrite", \
"ApplicationName" => "JBoss-AuditTrailPool" } \
--recovery-username=audit_trail_user01 --recovery-password=change_the_password \
--valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker \
--validate-on-match=false --background-validation=true \
--background-validation-millis=5000 \
--exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter \
--track-statements=true --prepared-statements-cache-size=5000 \
--min-pool-size=5 --max-pool-size=64 --pool-prefill=false \
--same-rm-override=false --pad-xid=true --no-tx-separate-pool=true \
--enabled=true
This happens instantaneously, and then I :reload .
Now, the same example with just the basic configuration looks like this for Jcliff (if I'm doing it right):
{ "xadatasource" => {
"AuditTrailPool" => {
"jndi-name" => "java:jboss/datasources/dsAuditTrail",
"pool-name" => "AuditTrailPool",
"spy" => "false",
"use-ccm" => "true",
"statistics-enabled" => "true",
"driver-name" => "sqlserver",
"xa-datasource-properties" => {
"URL" => { "value" => "jdbc:sqlserver://foobar:1433;databaseName=audit_trail" },
"User" => { "value" => "audit_trail_user01" },
"Password" => { "value" => "change_the_password" },
"SelectMethod" => { "value" => "cursor" },
"ApplicationIntent" => { "value" => "ReadWrite" },
"ApplicationName" => { "value" => "JBoss-AuditTrailPool" }
}
}
}
}
I measure the time this takes, and it takes 4 1/2 minutes:
time jcliff datasource.jcliff
Jcliff version 2.12.7
2021-02-17 13:56:44:0255: /subsystem=datasources:read-children-resources(child-type=xa-data-source,recursive=true)
2021-02-17 13:56:46:0241: xa-data-source add --name=AuditTrailPool --jndi-name="java:jboss/datasources/dsAuditTrail" --driver-name="sqlserver" --xa-datasource-properties=URL="jdbc:sqlserver://foobar:1433;databaseName=audit_trail"
if result.value==false of /subsystem=datasources/xa-datasource=AuditTrailPool:read-resource
xa-data-source enable --name=AuditTrailPool
:reload
2021-02-17 13:57:10:0094: /subsystem=datasources:read-children-resources(child-type=xa-data-source,recursive=true)
2021-02-17 13:57:11:0996: /subsystem=datasources/xa-data-source=AuditTrailPool:write-attribute(name=statistics-enabled,value="true")
xa-data-source disable --name=xadatasource
:reload
/subsystem=datasources/xa-data-source=AuditTrailPool/xa-datasource-properties=User:add(value="audit_trail_user01")
:reload
xa-data-source enable --name=xadatasource
xa-data-source disable --name=xadatasource
:reload
/subsystem=datasources/xa-data-source=AuditTrailPool/xa-datasource-properties=Password:add(value="change_the_password")
:reload
xa-data-source enable --name=xadatasource
xa-data-source disable --name=xadatasource
:reload
/subsystem=datasources/xa-data-source=AuditTrailPool/xa-datasource-properties=SelectMethod:add(value="cursor")
:reload
xa-data-source enable --name=xadatasource
xa-data-source disable --name=xadatasource
:reload
/subsystem=datasources/xa-data-source=AuditTrailPool/xa-datasource-properties=ApplicationIntent:add(value="ReadWrite")
:reload
xa-data-source enable --name=xadatasource
xa-data-source disable --name=xadatasource
:reload
/subsystem=datasources/xa-data-source=AuditTrailPool/xa-datasource-properties=ApplicationName:add(value="JBoss-AuditTrailPool")
:reload
xa-data-source enable --name=xadatasource
real 4m29.473s
user 1m49.782s
sys 0m6.752s
Of course, I understand that idempotency is Jcliff's forte. But waiting two hours just to add a couple of datasources is too long for my use case.
So, I have two questions:
- Am I doing this right in Jcliff?
- Is there a way where the idempotency could only consist of removing the datasource and adding it in the way shown above (my CLI example)?
bserdar commented
@cristalp The default wait for "reload" is 20secs. Give a different value using --reconnect-delay flag. It is in msecs.
I will look at grouping reloads together.
cristalp commented
Thanks for the configuration hint!