Step 8 of 8: Postgres Tuning

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 pratical were orginally derived from the PostgreSQL wiki page: Tuning Your PostgreSQL Server

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

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:

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:

work_mem = 50MB
maintenance_work_mem = 128MB

effective_cache_size

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

Edit the line:

effective_cache_size = 256MB

Restart PostgreSQL

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

Completed!

If you've made it this far, you now have a working XNAT installation! When you're ready, you can...

Continue to Part 2: XNAT Administration

@a user