Re: changing max_connections in postgresql on FreeBSD 5.4
From: Michael VInce (mv_at_roq.com)
Date: 08/08/05
- Previous message: Suleiman Souhlal: "Re: Tarball of ported libmicro 0.3 available for testing..."
- In reply to: Claus Guttesen: "changing max_connections in postgresql on FreeBSD 5.4"
- Next in thread: Jim C. Nasby: "Re: changing max_connections in postgresql on FreeBSD 5.4"
- Reply: Jim C. Nasby: "Re: changing max_connections in postgresql on FreeBSD 5.4"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Mon, 08 Aug 2005 11:26:37 +1000 To: Claus Guttesen <kometen@gmail.com>
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 "freebsd-performance-unsubscribe@freebsd.org"
>
>
_______________________________________________
freebsd-performance@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "freebsd-performance-unsubscribe@freebsd.org"
- Previous message: Suleiman Souhlal: "Re: Tarball of ported libmicro 0.3 available for testing..."
- In reply to: Claus Guttesen: "changing max_connections in postgresql on FreeBSD 5.4"
- Next in thread: Jim C. Nasby: "Re: changing max_connections in postgresql on FreeBSD 5.4"
- Reply: Jim C. Nasby: "Re: changing max_connections in postgresql on FreeBSD 5.4"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
|
