Database

PhixFlow stores configuration, analysis and task data in any of Oracle, SQL Server or MariaDB (a dialect of MySQL). For version compatability, please refer to System Requirements and Compatibility.

There are many issues to consider when installing a database, several of which will be concerned with your company’s own internal procedures, backup policies etc. The notes below describe just the core requirements which will enable the installation of PhixFlow to be completed. However, the DBA who carries out the installation should ensure that the database is configured to be performant for the volumes of data that PhixFlow is expected to process.

In normal operation, the database will require the usual ongoing management e.g. monitoring database files to ensure that the database does not run out of space. Over time, the data held in the PhixFlow database will grow naturally as more Controls are configured. However, model designers should ensure that archive policies are configured within PhixFlow to free up space when data is no longer needed.

Oracle

When using Oracle, the following initial database configuration is recommended. However, some of the options below may need to change over time as the work carried out by PhixFlow increases.

General

Storage / Tablespaces

1. The storage area sizes and I/O Rates are for a typical small server using conventional hard disk drives, and should be revised as part of the sizing process. Rates are quoted in Megabytes
2. The I/O Rates are for sustained data transfers spanning many tens of minutes. Peak transfer rates, assuming transfers to/from fast memory buffers, may be much higher, but are not significant unless solid-state disks are used.

Datapump

To support maintenance backups, the following datapump directory is required configured as:

CREATE DIRECTORY dpump_phixflow AS ‘/u02/phixflow/dbexport’;

Database User

The following Oracle user should be set up:

Microsoft SQL Server

When using SQL Server, the following initial database configuration is recommended. However, some of the options below may need to change over time as the work carried out by PhixFlow increases.

Server

Logins

Create a new login as follows:

Database

Create a new database as follows:

Filegroups

Database Files

MariaDB

MariaDB is a dialect of MySQL.

When using MariaDB, the following initial database configuration is recommended. However, some of the options below may need to change over time as the work carried out by PhixFlow increases.

Sizing and tuning

Full guidance is given here: ​​. Typically, MariaDB installations used for PhixFlow use InnoDB as the storage engine.

You can aslo get an insight into the memory requirements of your database by using database management tools like mysqltuner (​​ ). Note that mysqltuner can be installed from the standard repositories on many linux distributions.

However, as a very broad rule of thumb, we recommend that you allow at least 20% head room on top of the assigned buffer pool size, taking into account any other software that needs to run on the sever and an allowance for the operating system itself.

- the key setting is innodb_buffer_pool_size.

Roughly speaking, after tomcat, leave about 1GB memory space and allocate the rest to this setting. E.g. on an 8GB machine, if you assign 5GB to tomcat, then assign 2GB to MariaDB (although you can probably get away with leaving 0.5 GB spare).

Restart the MySQL service:

Installation

Example

If you add all parameters above to my.cnf, including the optional parameters, then the [mysqld] section of your my.cnf file should look something like this:

[mysqld]
...
datadir = /var/lib/mysql
...
wait_timeout = 28800
...
binlog_format = mixed
log_bin_trust_function_creators = 1

Database

The following settings are required for the PhixFlow database.

You will need a database, and a login with all privileges on that database. The database should be created with the following options:

Running non-deterministic functions

PhixFlow's migration scripts sometimes require the use of non-deterministic functions. To run, these require either a setting (log_bin_trust_function_creators) to be updated, or to be run with sufficient privileges.

Permanent setting change to allow non-deterministic functions

You can permanently allow non-deterministic functions to run in migration scripts by updating the setting log_bin_trust_function_creators in the my.cnf file. This is convenient, because you don't have to remember to make any changes to your session when running PhixFlow migration scripts. If you don't want to make this permanent change, see the following section.

Instructions for setting log_bin_trust_function_creators in the my.cnf file are given in MariaDB installation.

Session change to allow non-deterministic functions

If you do not update the log_bin_trust_function_creators setting in my.cnf, you must make sure that your session either has this setting updated, or that you run migration scripts with sufficient privileges. Full details for doing this are given in Upgrading PhixFlow.

Publishing space

Notes

Windows / Antivirus

MariaDB is not compatible with on-access anti-virus software so this must be disabled for the MariaDB data folder(s).