...
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 | |
Service Name | (Please provide to PhixFlow installation consultant) |
Collation Character set | Latin1_General_CI_AS |
Max Server Memory | See MS SQL Server planning |
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 is a dialect of MySQL.
InnoDB
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 = 2GRestart the MySQL service:
sudo systemctl stop mysql sudo systemctl start mysqlInstallation
Option | Setting |
Version | |
| 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:
| Code Block |
|---|
[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.
| Note |
|---|
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:
...
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).