Database installation

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

Option	Setting
Oracle Edition	PhixFlow runs on all Oracle Database editions. PhixFlow partitioned streams (optional) require the Enterprise Edition Partitioning option.
Version	See System Requirements and Compatibility. Patches should be applied as advised by Oracle.
NLS_CHARACTERSET	AL32UTF8
NLS_LANG	american_america. AL32UTF8 (Not used by PhixFlow. For support purposes only)
NLS_SORT	BINARY
NLS_COMP	BINARY
NLS_NCHAR_CHARACTERSET	NCHARS are not used by PhixFlow however set to AL32UTF8
sga_max_size	At least 2 GB For larger systems, this should be provided by the sizing process.
SID	phixflow
Connection Type	Dedicated
Max Number of Connections	Variable, depending on system size and workload. Typically >100. For larger systems, this should be provided by the sizing process.
Storage Type	Any, subject to throughput requirements.

Storage / Tablespaces

Tablespace Name	Size	I/O Rate (Mb/s sustained)
(Redo Logs)	3 x 0.5Gb	10Mb/s
CONFIG_DATA_01	2 GB	0.5Mb/s
ANALYSIS_DATA_01	100 GB	10Mb/s
UNDOTBS1 (UNDO)	10GB	10Mb/s

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
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:

User

SQL Script

phixflow

CREATE USER phixflow PROFILE DEFAULT

IDENTIFIED BY <password>

DEFAULT TABLESPACE CONFIG_DATA_01

QUOTA UNLIMITED ON CONFIG_DATA_01

QUOTA UNLIMITED ON ANALYSIS_DATA_01

ACCOUNT UNLOCK;

GRANT CREATE SESSION TO phixflow;

GRANT CREATE TABLE TO phixflow;

GRANT CREATE VIEW TO phixflow;

GRANT READ,WRITE ON DIRECTORY phixflow_dpump TO phixflow;

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

Option	Setting
SQL Server Edition	Standard or Enterprise
Version	See System Requirements and Compatibility.
Service Name	(Please provide to PhixFlow installation consultant)
Collation Character set	Latin1_General_CI_AS
Max Server Memory	At least 2 GB
Server Authentication	SQL Server and Windows Authentication Mode

Logins

Create a new login as follows:

Parameter	Setting
Login Name	phixflow
Authentication	SQL Server
Password	Please provide to the PhixFlow installation consultant
Enforce password policy	No
Default database	Set to phixflow once the phixflow database has been created (below)
Default language	As appropriate

Database

Create a new database as follows:

Parameter	Setting
Name	phixflow
Owner	phixflow
Collation	Latin1_General_CI_AS
READ_COMMITTED_SNAPSHOT	ON

Filegroups

Name	Default
PRIMARY	Yes
ANALYSIS_DATA

Database Files

Logical Name	Setting
phixflow	Type: Rows Data Filegroup: PRIMARY Initial Size: 2000 MB Autogrowth: 10% Unrestricted (or as per management policy) Path: (As per management policy)
phixflow_data_01	Type: Rows Data Filegroup: ANALYSIS_DATA Initial Size: 100,000 MB Autogrowth: 10% Unrestricted (or as per management policy) Path: (As per management policy)
phixflow_log	Type: Log Initial Size: 1000 MB Autogrowth: 10% Unrestricted (or as per management policy) Path: (As per management policy)

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).

innodb_buffer_pool_size = 2G

Restart the MySQL service:

sudo systemctl stop mysql
sudo systemctl start mysql

Installation

Option	Setting
Version	See System Requirements and Compatibility.
Max Server Memory	At least 2 GB
The following configuration parameters must be set in my.cnf
datadir	Ensure that the data dir being used has sufficient space for the initial period of PhixFlow operation. This is in the [mysqld] section of the my.cnf file.
binlog_format	mixed This must be added to the [mysqld] section of the my.cnf file.
wait_timeout	28800 (this is the default, but some installation processes set this to a much lower value) This must be in the [mysqld] section of the my.cnf file.
The following configuration parameters can optionally be set in my.cnf
log_bin_trust_function_creators	1 If set in my.cnf, this must be added to the [mysqld] section of the file. For details on why this setting is recommended - and the alternatives if you do not want to set in this my.cnf - see Running non-deterministic functions

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.

All of these parameters are reflected in the example MariaDB database creation command in Configure the Database.

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

Option	Setting
character_set_server	utf8
collation_server	utf8_bin

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

To ensure that PhixFlow can publish data changes, its database must have enough space to hold a copy of the largest table. For the different databases, the space needs to be in:

Oracle: temporary table space
SQL Server: temporary file group
Maria DB: the file system.

Notes

Windows / Antivirus

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