I'm migrating a database from Derby to MySQL and I have realized that
writeSchemaToDatabase and writeDataToDatabase processes takes near 7
minutes when creating and inserting at MySQL database.
I think there is some problem with the commits that make a very big delay
with the migration.
If I change MySQL configuration parameter "innodb_flush_log_at_trx_commit"
from 1(default value) to 0, the time this process takes is reduced to half
MySQL documentations says this about this parameter:
# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
I have autocommit set to 0 in both configurations, so it is supposed that
having "innodb_flush_log_at_trx_commit=1" should not make any write except
if it is explicitly done by ddlutils.
Is DdlUtils performing one commit after each INSERT? Is this the correct
behaviour? It is possible to modify it?