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:51:03 -0400
I guess I should give more detail on the application format. It is not publicly released yet, but will eventually be on the MAS for a relatively low price.
The application is called "PostgreSQL on Demand". What it is, is an application that presents a simple query editor and schema browser for the server. When the application is opened, it starts a PostgreSQL server instance as a part of the application and allows the user to run queries, create databases, etc in it. The instance is set to allow local connections and limits to 5 connections. Other than that, it is a fully functional PG install, all neatly encapsulated inside the application itself.
It stores it's Data in ~/Library/Application Support/PostgreSQL on Demand/ and though I still have some tweaking to do, will eventually support Lion's Sandboxing.
At this point, I consider it 'Alpha' level work. IT functions, there are issues, and it needs more work. I use it daily, so I am constantly tweaking it. I have a list of things I can/want to add to it that could keep me busy for a couple of years, one of those being the ability to have it install itself as a system wide PostgreSQL Server instance that is always on instead of a traditional installer (which I also maintain) and full management tools.
Unfortunately, this is very much the hobby side of what I do. My day job is primarily Windows and MSSQL where we are slowly migrating all of our systems and our customers systems to PostgreSQL, so everything you find at PostgreSQL for Mac is what I do when I am not at work or chasing my kids around.
--
Andy 'Dru' Satori
On Thursday, July 28, 2011 at 11:36 AM, Andy 'Dru' Satori wrote:
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