Publishing Space

Tuning

Tuning MySQL 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

MySQL installations used for PhixFlow use InnoDB as the storage engine.

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

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 Tomcat Planning 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 Install MySQL to make sure that any anti-virus software does not interfere with MySQL

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 MySQL, set this in /etc/mysql/my.cnf:

Restart the MySQL service:

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 MySQL installation, in particular, what you need in addition to that specified by innodb_buffer_pool_size), as well as other tuning suggestions.