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
...
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 MariaDB, this space is in the file system.
Tuning
Tuning MariaDB for optimum performance is a potentially complex task. However, in general, because PhixFlow uses simple statements to perform its operations, you can rely in most cases on a few settings to make use of available resources on the database server.
Memory
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.
...
Rule of thumb settings
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 (innodb_buffer_pool_size, taking into ) for running the database. To work out how much to assign to the database, take into account 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).
| Code Block |
|---|
innodb_buffer_pool_size = 2G |
Restart the MySQL service:
| Code Block |
|---|
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:
...
| 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.
...
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
Notes
Windows / Antivirus
MariaDB is not compatible with on-access anti-virus software so this must be disabled for the MariaDB data folder(s)Again, as a very rough rule of thumb, allow at least 2 GB for the operating system. In practice, the kernel probably requires a lot less than this, but there are are utilities and other essential programs required for a running server that consume memory. In practice, if you have a large server, you may find that you need more than this for the server to run efficiently. Remember that this is only a very rough rule of thumb.
Other processes will include anything you have installed on the server, including:
Tomcat, if you are running Tomcat and the database on the same server (see https://phixflow.atlassian.net/wiki/pages/resumedraft.action?draftId=8643674113 for Tomcat’s memory requirements)
A reverse proxy, if you are also running this on the same server; although in general these will have a minor footprint across all resources (CPU, memory, storage) for most PhixFlow instances
Anti-virus, and other security and monitoring software; see note on https://phixflow.atlassian.net/wiki/pages/createpage.action?spaceKey=INTRANET&title=MariaDB%20Linux%20install to make sure that any anti-virus software does not interfere with MariaDB
You can estimate a suitable memory setting with the calcluation:
innodb_buffer_pool_size * 1.2 + [memory for other processes] + 2 GB < [total server memory]
Once you have decided on the amount you can assign to MariaDB, set this in /etc/mysql/my.cnf:
| Code Block |
|---|
innodb_buffer_pool_size = 2G |
Restart the MySQL service:
| Code Block |
|---|
sudo systemctl stop mysql
sudo systemctl start mysql |
Using mysqltuner
You can get an insight into the overall memory requirements of your database by using database management tools like mysqltuner (https://github.com/major/MySQLTuner-perl). Note that mysqltuner can be installed from the standard repositories on many linux distributions.
This will give an estimate of the overall memory requirements of your MariaDB installation, in particular, what you need in addition to that specified by innodb_buffer_pool_size), as well as other tuning suggestions.