Hi James Hinkey,
As we have talked in the Core meetings, tuning the database specifically for the upgrade process is really important. In the page 32 of this presentation you have the reasons why we should proceed in this way:
So we have to recommend users to take some measures to improve the upgrade experience such as:
- Using SSH disk for the database
- Reduce the connection latency between the server where the DB upgrade tool is and the database.
- Tune the database specically for this process based on the fact that the majority of SQL statements are writings.
Some good examples about how to tune the database are the following ones:
- Asynchronous writings to disk (see page 33 and 34 in the previous presentation to check the results):
- Mysql: innodb_flush_log_at_trx_commit set to 0
- Postgres: synchronous_commit set to OFF with wal_writer_delay set to 1000ms
- Or deactivate query cache:
- Mysql: query_cache_size set to 0
Similar configurations and others specific for this case can be applied to all supported databases so customers/users should check the more convinient ones with their DBAs.
IMPORTANT: Please, warn users of this configration can be applied under certain conditions such as having a backup and having totally controlled environment as it occurs during an upgrade process. However they have to be sure that they deactivate the database configuration which can be harmful for the regular use of the portal such as the asynchronous writings to disk.