Postgres Tuning for XNAT Installations

This content was taken from the XNAT 2016 Workshop practical exercise on setting up a development XNAT instance. Your settings may vary depending on your system requirements and database size.

Goal

Getting your server started is just the beginning. A working production server also needs tuning for the performance of supporting services. The most important of these factors is database performance. In this section, you can work with a few of the more common database performance tuning options.

The default PostgreSQL setup is for a very small system with almost no RAM. This makes it perform very poorly even for a small instance like our xnatdev VM. The defaults on Ubuntu assume more RAM than the standard Postgres defaults.

The adjustments in this practical were originally derived from the PostgreSQL wiki page: Tuning Your PostgreSQL Server

For specific numbers based on your available system resources, we recommend this calculator which is popular in the PostgreSQL community: https://pgtune.leopard.in.ua/.

Memory Available to PostgreSQL

Before tuning PostgreSQL we must decide on the maximum amount of RAM that PostgreSQL can use. In our instance PostgreSQL is sharing resources with Tomcat7 and ZFS on a 2GB virtual machine. We've already allocated 512MB to ZFS. The OS will typically take another 512MB. This leaves us with 1GB for Tomcat7 adn PostgreSQL. An even split makes sense for XNAT. For this example we'll allocate 512MB to PostgreSQL.

Edit postgresql.conf

BASH

nano /etc/postgresql/9.5/main/postgresql.conf

Tip for editing long files in the console

Most command line text editors will have a function that allows you to find a term. In nano, use CTRL-W to open a small search bar at the bottom of your screen. You can type a term like "shared_buffers" and hit enter to be taken to the first instance of that term in the file. Continuing to type CTRL-W will allow you to search for the next instance of that term, or start a new search for a new term.

shared_buffers

The Ubuntu default of 128MB is sufficient in our instance.

A good rule of thumb is 1/4 the maximum allocation. Which works out to 128MB in our case. No changes necessary.

work_mem and maintenance_work_mem

Letting these scale linearly is not always beneficial. I use the following logic to set these:

RUBY

if memavail >= 7000 then
    work_mem = 80
else
    work_mem = 50
end
if memavail >= 1024 then
    maintenance_work_mem = 256
else
    maintenance_work_mem = 128
end

Edit the lines:

BASH

work_mem = 50MB
maintenance_work_mem = 128MB

effective_cache_size

This is best set to 1/2 the maximum allocation:

Edit the line:

BASH

effective_cache_size = 256MB

Restart PostgreSQL

BASH

root@xnatdev:~# service postgresql restart
 * Restarting PostgreSQL 9.5 database server                                                                                             [ OK ]