changing max_connections in postgresql on FreeBSD 5.4

[Michael VInce]

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 at freebsd.org mailing list
>http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>To unsubscribe, send any mail to "freebsd-performance-unsubscribe at freebsd.org"
>  
>