OpenClinica User Manual/OptimisingPostgreSQL
PostgreSQL database configuration[edit | edit source]
The performance of OpenClinica is highly dependent on the performance of PostgreSQL. PostgreSQL is initially configured to use a small amount of memory, altering options to increase the memory used can improve the performance significantly, especially on large CRFs.
Open PostgreSQL admin. Select Tools, Server configuration, postgresql.conf
The options below are for a Windows server primarily running OpenClinica and optionally Tomcat with 2GB+ memory and plenty of disk space. This can also be used for developer machines with similar specs.
Alter the following options (remembering to tick the boxes to enable them), the instructions includes comments after the hash characters:
max_connections = 200 shared_buffers = 512MB #1/4 your RAM, with a max of 512MB effective_cache_size = 1GB #1/2 your RAM checkpoint_segments= 32 checkpoint_completion_target = 0.9 autovacuum = on work_mem = 16MB #1/32nd of shared_buffers above maintenance_work_mem= 256MB wal_sync_method = fsync_writethrough # previously fsync (commented) wal_buffers = 16MB random_page_cost = 3.0
On a Linux machine with 2GB+ memory and plenty of disk space, the configuration is the same, except for two differences:
shared_buffers = 32MB # the Linux kernel frequently doesn't support values over this wal_sync_method = fdatasync # leave as default
In Windows, typing ‘services’ into the search box above the Windows Orb allows you access to the services running on your machine to restart PostgreSQL (otherwise it will restart when you next restart your computer).
Reference: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server