Please wait...

MySQL / MariaDB Optimisations

This article describes various MySql/MariaDB configuration adjustments that will speed up MailWizz big time.
As always, MailWizz tries to access the database as optimal as possible, but still, there are cases when the only way to speed up access is to adjust your database server configuration.
Below changes are to be applied to the database server config file, that is usually /etc/my.cnf

1. InnoDB Buffer Pool Size
innodb_buffer_pool_size = XXX
Where XXX is usually at least 70% of the available RAM on the server, so if you have 4GB RAM on your server, this value will be set to 2867M, so we have:
innodb_buffer_pool_size = 2867M
A good read related to this setting can be found here.

2. InnoDB Flush Log At Transaction Commit
innodb_flush_log_at_trx_commit = X
The default value of 1 is required for full ACID compliance. With this value, the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk.
Depending on your requirements, you can loosen this up and set the value to 2 which means the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions in an operating system crash or a power outage.
innodb_flush_log_at_trx_commit = 2
A good read related to this setting can be found here.

3. Allowed connections count
max_connections = X
The default value of this setting is usually too small for the type of work a mailer is doing. Best is to keep it at a higher value:
max_connections = 500
A good read related to this setting can be found at here

Please do not forget to restart mysql after you apply each of the above settings.

Tools that you can use to optimise your database settings:
1. Percona Tools
2. Mysql Tuner