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.
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.
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
This is best set to 1/2 the maximum allocation:
Edit the line:
effective_cache_size = 256MB
root@xnatdev:~# service postgresql restart * Restarting PostgreSQL 9.5 database server [ OK ]
If you've made it this far, you now have a working XNAT installation! When you're ready, you can...