Hi all,
I’m trying to do an upgrade from 4.2.0 to the last version.
My context: SpringBoot 2.3.3 and Postgresql.
I faced 2 main issues, I tried to identified which version was responsible of each.
- The first one is slowness
Upgrading until 4.3.5 is transparent. From an empty DB it’s take 80s to create the schema and insert our referential Data.
When moving to 4.4.0 the initialisation double. The duration increase from 80s to 160s.
I don’t find a specific changeset that is responsible of the slowness, but it should be related to ‘loadData’ and prepareStatement.
Moving to 4.4.3 increase the slowness. Going to a duration of with a factor of 2, 4.5.0 is the same.
I do some test with a simple changelog file: a table creation and the loading of my biggest file (40’000 lines).
Using: usePreparedStatements: true takes 95s
Using: usePreparedStatements: false takes 19s
5 times slower with preparestatements (taht is the default value), seems to be strange.
- The second one is: my existing script can’t be executed by the new version without changing my history.
My ‘loadData’ changeset doesn’t have the usePreparedStatements parameter.
Starting from the 4.5.0, the loadData can’t be run when one of the column is a numeric value.
I have the following error:
Caused by: org.postgresql.util.PSQLException: ERROR: column "minimum_allocation" is of type double precision but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 215
I found 2 workaround:
a) add usePreparedStatements: false on all my loadData
b) add column definition in the loadData for all the numeric values:
- loadData:
encoding: UTF-8
file: db/changelog/data/mydata.csv
separator: ","
tableName: MY_TABLE
columns:
- column:
header: MINIMUM
type: NUMERIC
- column:
header: MAXIMUM
type: NUMERIC
The solution b) can’t be use all the times as loadData may have to use prepare statement (CLOB, longtext, etc.).
But the very good news is: the solution b) solve my perf issue, boosting the original perf.
At he end, with usePreparedStatements: false everytime it’s possible and the last (4.6.2)decrease the duration from 35s down to 80s.
But why :
- PrepareStatement need to redefined the numeric type in th loadData ?
- The PrepareStatement default value very slow ?
I don’t find any post about performance issue or configuration adaption in the release notes.
Has someone faced to such behaviour ?
Regards,
Philippe