If you are evaluating whether the shared database is a good solution for you, consider Geneious Cloud as an alternative that requires no setup or ongoing administration of a server.
Supported database vendors include Microsoft SQL Server, PostgreSQL, Oracle and MySQL. It is possible to use other database systems if you provide the database driver.
The following SQL database versions (with the default configurations) are currently tested for Shared Databases in Geneious:
- Microsoft SQL Server 2019 and 2022
- PostgreSQL 15
- Oracle Database 19c
- MySQL 8.0
To avoid connection failures when many users access the Shared Database, the default connection pool should be adjusted to accommodate 6 connections for each user.
In most cases, the default configuration for whatever SQL database system you are using is sufficient.
However, there are a few exceptions where we recommend changes to the default configuration, listed below:
Character Set Encoding
The character set encoding for MS SQL should be set to default, all others should use UTF-8 to avoid any potential problems indexing documents containing unusual characters. Note that MySQL 5.6 and 5.7 use latin1 as the default encoding, this should be changed to CHARACTER SET utf8 and COLLATE utf8_unicode_520_ci.
MYSQL
Some changes you may need to make to the MySQL configuration file (my.cnf) to improve performance are as follows:
- innodb buffer pool size=[On a dedicated database server you may set this parameter up to 80% of the machine physical memory size]
- innodb flush log at trx commit=2
- query cache limit=2M
- query cache size=128M
- max allowed packet= 1073741824 (Note this is the maximum size for max allowed packet and can prevent errors when handling large files)
Further optional improvements:
- innodb log file size=256M
- innodb log buffer size=16M
Note: If you change the innodb log file size then you will need to delete the current log files before the server will start up again.
Microsoft SQL Server
Default settings for MS SQL server are generally sufficient. However, we strongly recommend using Snapshot Isolation to avoid deadlocks. These can occur when multiple users use the shared database at once and may lead to failure of some actions. This setting becomes necessary if there will be more than a handful of users using the database concurrently. Read more about Snapshot Isolation here.