Re: PostgreSQL Setup and Tuning
Re: PostgreSQL Setup and Tuning
- Subject: Re: PostgreSQL Setup and Tuning
- From: Andy 'Dru' Satori <email@hidden>
- Date: Thu, 28 Jul 2011 11:36:14 -0400
This is correct, and it will require tweaking based upon your usage (connection counts primarily).
It really matters more about *how* your site uses connections than anything else. The shared memory issues revolve around the preallocated memory per connection. If you expect to pool 10 connections, it really is not an issue. If you are going to need 1000 connections, then you will have issues.
My practice has become the following, for my development laptop, I use an on demand instance of PostgreSQL (the application format that I mentioned earlier). When I am going to be working with those databases, I fire up the application and let it run in the background. It is set to be optimized for 5 connections. This is a number low enough that if you are running iChat, it will not interfere (iChat is notorious for consuming large chunks of shared memory).
Once I am ready to move to production, my configuration decision begin with the expected loads and connection handling. Modifying the SysCtl file and the .conf files to suit the needs. pgtune is a good tool to help along the way.
I do not generally prematurely optimize though. My general choice is to roll with defaults watching for problems during startup:
FATAL: could not create shared memory segment:
Cannot allocate memory
DETAIL: Failed system call was shmget(key=1,size=1499136, 03600)
Is a pretty clear indicator that that it is time to modify the SysCtl. As a baseline, the most common recommendation is:
kern.sysv.shmmax=33554432
kern.sysv.shmmin=1
kern.sysv.shmmni=256
kern.sysv.shmseg=64
kern.sysv.shmall=8192
You may have to tweak to find a balance that works for you, I find that for most of my work I don't need the above unless I have a very high connection count server.
Outside of that, most of the configuration options you will want are going to be found in the PostgreSQL.conf file in your data folder, which can be a number of places depending upon how you installed it.
It is probably worth noting that, PostgreSQL 9 and Lion have changed some of this. I have seen a noticeable speedup on Lion Server (using a Core2Duo Mini Server) with the installation of Lion, and a further speed up when that was upgraded to PG 9 beta releases.
Also in the interests of full disclosure, I am the guy that has (when time permits) maintained the work at www.postgresqlformac.com for the last 6 years :-).
--
Andy 'Dru' Satori
On Thursday, July 28, 2011 at 10:47 AM, John Huss wrote:
For production on OS X at least you have to modify your shared memory settings in sysctl.conf -- there are instructions about this in the README in the installer. Otherwise, I have found the program 'pgtune' to be useful for configuring postgresql.
John
On Thu, Jul 28, 2011 at 9:27 AM, Kieran Kelleher
<email@hidden> wrote:
So, which of you WO devs has the blog post (or even a link to somewhere with experienced insight) on setup, tuning, gotchas with regard to PostgreSQL on OS X for dev, on Linux for deploy, replication, etc.??
-Kieran
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden