From our experience, MariaDb (MySQL-compatible database) have performed very well as the Persephone back end. In most of our tests MariaDb beats Oracle, so, if you have a choice of the database engine for Persephone, we recommend to go with MariaDb.

1. Install MariaDB Engine

Please visit the official site http://downloads.mariadb.org to find all necessary instructions for your operating system.

IMPORTANT:

you must install MariaDB version from the branch 10.1.x. Normally you should use the latest version from the 10.1.x branch. Other good candidates are 10.1.31(available at Amazon RDS) and 10.1.28 (we tested it a lot).

2. Update configuration file

Update the contents of MariaDB configuration file with settings specific to our application.

On Windows, the configuration file is typically located at

%PROGRAMFILES%\MariaDB 10.1\data\my.ini

On Unix-like systems, check out

$MYSQL_HOME/my.cnf

You should replace the contents of your configuration file with the settings below. Keep in mind that settings not mentioned here, were omitted intentionally – to make them equal to their default values. The highlighted values should be adjusted to your system, use the values from the original configuration file.

[mysqld]# Data directory (machine-specific)
datadir=D:/Data/MariaDB/data
# Server port (machine-specific)
port=3306
# Security
# ssl-ca=C:/Program Files/MariaDB 10.1/ssl/ca-cert.pem
# ssl-cert=C:/Program Files/MariaDB 10.1/ssl/server-cert.pem
# ssl-key=C:/Program Files/MariaDB 10.1/ssl/server-key.pem
# Strict engine settings conformant to SQL Standard.
# Additional note about NO_AUTO_VALUE_ON_ZERO - it allows you to insert zero into AUTO_INCREMENT field
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY
# Strict settings for InnoDB/XtraDB engine
innodb_strict_mode = 1
default_storage_engine = innodb
# This setting makes table names case-insensitive
lower_case_table_names = 1
# utf8 seems to be most efficient for client-server communications
character_set_server = utf8
# setting default time zone to zero removes unnecessary time conversions between server and client
default_time_zone = "+00:00"
# READ-COMMITTED for high performance
transaction_isolation = READ-COMMITTED
# Most important option, memory cnsumption is mainly controlled by this option
# Set it to at least 4GB for resonable performance
innodb_buffer_pool_size = 4G
# Very good default settings, applicable to most workloads
key_buffer_size = 32M
innodb_log_file_size = 512M    # Another good option is 256M to shorten recovery time (noticeable on Amazon RDS)
innodb_log_buffer_size = 16M
# Those settings are per-thread, so andjust them with care
tmp_table_size = 32M           # 16M - 64M, default: 16M
max_heap_table_size = 32M      # 16M - 64M, default: 16M
sort_buffer_size = 2M          # 2M - 8M, default: 2M
join_buffer_size = 2M          # 256K - 8M, default: 256K
# Table definition cache
table_definition_cache = 400   # default: 400
table_open_cache = 2000        # default: 2000
# Turn off query cache
query_cache_type = 0
query_cache_size = 0
# Limits
max_allowed_packet = 64M       # default: 16M
max_connections = 500
open_files_limit = 16384
# .Net Timeout
net_write_timeout = 3600
net_read_timeout = 3600

[client]
port=3306
plugin-dir=C:/Program Files/MariaDB 10.1/lib/plugin

This configuration allows MariaDB engine to use approx 5 GB of operating memory, which is considered a minimum for production. Memory usage is primarily controlled by innodb_buffer_pool_size option. If you are short on memory, you can safely decrease it to as little as 1GB, this should be enough for testing purposes. On the production machine, dedicated completely to MariaDB, this option should be set to 70% of available operating memory.

3. Restart MariaDB service after updating the configuration file

4. Create a new database

In MySQL/MariaDB, “database” is synonym of Oracle’s “schema”. You can create new empty schema using the following command:

CREATE DATABASE database_name COLLATE 'utf8_bin';

Alternatively, you can use tool like HeidiSQL, and create databases using its GUI.

5. Initialize the empty database using PersephoneShell's command init.

The rest of the procedure is common for local or Amazon-based MariaDb and is described here.