Database Settings

This page describes some recommended settings for the database server used by OpenCms. Currently, we only have recommended settings for MySQL/MariaDB.

MySQL / MariaDB

Blob Size Setting

OpenCms stores binary data such as gallery images in its database tables. MySQL's and MariaDB's default max_allowed_packet setting is chosen too conservatively for OpenCms. You MUST increase this value so that images and other binary files can be saved.

max_allowed_packet: Set it as large as the largest BLOB in the database. A suitable value for OpenCms is 256M.

Query Cache Settings

MySQL's / MariaDB's query cache can significantly improve the page load times of an OpenCms installation. For older versions of MySQL / MariaDB, the query cache was switched on by default, in newer versions it is switched off. Thus, it is worth checking the query cache settings for your OpenCms database.

Relevant Query Cache Settings

query_cache_type: Set to 1 to enable the query cache.
query_cache_limit: Maximum size in bytes to one query result stored in the query cache. 131072 is a suitable value for an OpenCms installation.
query_cache_size: Size in bytes available to the query cache. 67108864 is a suitable value for an OpenCms installation.

Please refer to the MySQL / MariaDB documentation for a detailed description of all available query cache settings.

InnoDB Settings

The following settings are only relevant when using MySQL/MariaDB with InnoDB as the storage engine for the OpenCms tables. Prior to OpenCms 12, the tables would be automatically created with MyISAM as the storage engine, but from OpenCms 12 onward, no storage engine is specified, so the default storage engine is used, which is usually InnoDB.

For more detailed information about these settings, refer to the MySQL or MariaDB documentation.

innodb_flush_log_at_trx_commit: This controls when the InnoDB redo logs are flushed. By default, this is set to 1, which flushes them after every transaction for safety and can cause bad write performance. Set it to 0 or 2 instead.
innodb_buffer_pool_size: Controls the size of the InnoDB buffer pool, which is used for caching data. The MySQL documentation recommends a size of 80% of available memory for a dedicated database server. This benefits both read and write operations.
innodb_log_file_size: Controls the size of the InnoDB redo log. If this is too small, it will slow the system down if there are a lot of write operations (e.g. during imports), so it should be adjusted upward.