Configuring the database

When running MariaDB in production it is recommended to adjust its configuration to suit your needs and to get the best possible performance out of ftrack.

Recommended configuration for MariaDB

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2048M

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log

Note

The most important configuration option is “innodb-buffer-pool-size” and should be adjusted as your database grows. It is recommended to set it to roughly the same size as your database or more. A quick way to approximate your database size is to check the size of the ibdata1 file in the mysql folder.

ls -lh /var/lib/mysql/ibdata1

Be careful when setting to a high value, if there is not enough RAM available MariaDB will not start.