Database Tuning for Upgrades
Performing an upgrade impacts the database differently from daily running in production. Because of this, you should tune your database for the upgrade process before you run it, and then re-apply your production settings after the upgrade completes.
The tips given in this article worked well in test runs on specific versions of each database. Optimal tuning depends on your own data, infrastructure conditions, and database vendor. Analyze your data, tune for upgrade, and time your test upgrades to determine the best database and Java process configuration for your Liferay DXP data upgrade.
Many more update statements are executed during data upgrade than in production. As such, here are some ways to tune your database for database upgrades:
-
Deactivate data integrity measures that impact performance. Restore to a backup if failures occur.
-
Disable or minimize transaction logging, because it is insignificant for data upgrades.
-
Make commit-related transaction I/O operations asynchronous.
-
Increase the interval to flush commits to disk.
-
Use an SSH connection for the database.
-
Reduce the connection latency between the server hosting the database upgrade tool and the database.
Some database properties and configurations are global and affect schemas in the same database.
The sections below link to vendor-specific information on tuning each database in the ways mentioned above.
IBM DB2
Please consult IBM’s official DB2 documentation.
MariaDB
Turn off InnoDB double-write and set the InnoDB flush log at transaction commit to 0.
Microsoft SQL Server
Set transaction durability to FORCED.
MySQL
Turn off InnoDB double-write and set the InnoDB flush log at transaction commit to 0. Deactivate query cache by setting the query cache size to 0.
Oracle Database
The default configuration works well. It configures asynchronous I/O to disk automatically.
PostgreSQL
Turn off synchronous commits and set the write ahead log writer delay to 1000 milliseconds.
Conclusion
When you’re done upgrading, make sure to revert your database configurations back to their production settings.