Preamble
I assume that you already made your best with help of your favorite programming language
and recommended postgresql performance tips: http://www.postgresql.org/docs/8.4/static/performance-tips.html
Postgresql resources
No doubt that standard postgresql configuration is far from modern production environments.
Therefore you need to spend enough time with following sources.
1. resource consumption documentation:
http://www.postgresql.org/docs/8.4/static/runtime-config-resource.html
The most important parameters are:
work_mem (integer)
shared_buffers (integer)
2. Query Planning documentation:
http://www.postgresql.org/docs/current/static/runtime-config-query.html
The most important parameters are:
effective_cache_size (integer)
random_page_cost (floating point)
OpenBSD resources
The default sizes in the GENERIC kernel are insignificant also and waiting for your tuning as well.
Posgtresql doesn’t start without enough memory size so always know when you need to increase kern.shminfo.shmmax.
Setting that we can change in /etc/sysctl.conf
the maximum number of System V IPC system-wide semaphore sets (and identifiers) which can exist at any given time:
kern.seminfo.semmni
the maximum total individual System V IPC semaphores which can be assigned by applications:
kern.seminfo.semmns
the amount of shared memory available in the system (bytes):
kern.shminfo.shmmax
the maximum number of shared memory segments:
sysctl kern.shminfo.shmseg
Full list of setting you can see with:
# man sysctl
OpenBSD kernel parameters
So, there are set of parameters that can be tuned only with kernel rebuild.
You should tune them only if system works unstable with default values and you have:
kernel warnings: “uvm_mapent_alloc: out of static map entries”
or panics like: “panic: malloc: out of space in kmem_map”
NKMEMPAGES
This option defines number of pages in kernel kmem_map structure.
MAX_KMAPENT
It defines number of static entries in kernel kmem_map (kernel virtual memory).
They can be changed in:
/usr/src/sys/arch/conf/GENERIC
As start you need to recheck ‘Building the System from Source’ part of OpenBSD documentation:
http://openbsd.org/faq/faq5.htm
Usually administrators select these parameters using set of tests on dedicated testing box where
they emulate load of production servers.
Example
our test server: 1x Intel Quad core CPU, 2GB RAM
software: Ruby on Rails application, postgresql DB, memcached.
load: about 15.000 users/day, peak load: 10 users/sec.
postgresql_dir/data/postgresql.conf
1 2 3 4 5 6 7 8 9 | # RESOURCE USAGE shared_buffers = 738MB max_prepared_transactions = 30 work_mem = 16MB max_fsm_pages = 2000000 # QUERY TUNING effective_cache_size = 512MB random_page_cost = 1.7 |
/etc/sysctl.conf
1 2 3 | kern.seminfo.semmni = 256 kern.seminfo.semmns = 2048 kern.shminfo.shmmax = 805306368 # Shared memory segment size is 768M |
/usr/src/sys/arch/conf/GENERIC
1 2 3 | ## custom settings option MAX_KMAPENT = 3072 option NKMEMPAGES = 32768 |