Hi,
I have done all my Postgres optimization configuration via sysctl or the
postgresql.conf no kernel recompilation was performed.
I did benchmarks at complete default FreeBSD / Postgres configuration
and benchmarks after.
I found raising the values to probably not much more then 1/4 of what
they are now list below gave around 30% increase in performance. But
increase the numbers after that did nothing. I still left them way past
the mark anyway because this server has 4gigs of ram that would other
wise go to waste.
The Postgres DB setup here is only meant to handle a few connections at
a time unlike yours.
sysctl.conf
kern.ipc.shmmax=1073741824
# x32 the default ( 8192 )
kern.ipc.shmall=262144
postgresql.conf
# shared_buffers = 108000 32 x default = 884,736,000 bytes
shared_buffers = 108000 # min 16, at least
max_connections*2, 8KB each
work_mem = 65536 # min 64, size in KB
max_stack_depth = 4096 # min 100, size in KB
max_fsm_pages = 40000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 2000 # min 100, ~50 bytes each
Also note that I have a MySQL server with a similar table setup and it
performs a lot better.
Claus Guttesen wrote:
Hi.
I recently lowered max_connections from 1024 to 384 in
/usr/local/pgsql/data/postgresql.conf. The server is a quad opteron @
2 GHz and 4 GB of RAM.
This decreased the SIZE and RES values in top and it seems that the
current max_connections is more than adequate. To see how many
concurrent connections the db-server had I did a 'netstat -na|grep -i
establi|wc -l' which showed some 230 established connections during
peak.
In order to use max_connections set to 1024 I had to raise the
following values in the kernel:
options SHMMAXPGS=327680
options SEMMNI=200
options SEMMNS=1200
options SEMUME=200
options SEMMNU=600
The values were taken from the postgresql-ports-installation-notes and
were simply increased to fit the max_connections-parameter in
postgresql.conf.
Will more RAM become available to postgresql if I change the
kernel-values mentioned above to half the size?
The server does occasionally become loaded with load-averages around 6
or 7 but usually stays below 4. Disk-io is not a problem, usually
remains below 1 MB/s, cpu-idle is approx. 5-10 % during peak and
around 80 % idle otherwise.
regards
Claus
_______________________________________________
freebsd-performance@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[EMAIL PROTECTED]"
_______________________________________________
freebsd-performance@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[EMAIL PROTECTED]"
