Skip site navigation (1) Skip section navigation (2)

Simple postgresql.conf wizard

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Simple postgresql.conf wizard
Date: 2008-11-02 02:02:35
Message-ID: Pine.GSO.4.64.0811012101220.17619@westnet.com (view raw or flat)
Thread:
Lists: pgsql-hackers
One of the long-terms goals I'm working toward is wrapping a "wizard" 
interface around the tuning guidelines described by 
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server now that 
those have gone through a fair amount of peer review.  Attached is a first 
simple version of such a wizard, implemented in Python.  Right now what it 
does is look the amount of memory in your system and adjust shared_buffers 
and effective_cache_size.  So if we started with this stock configuration:

shared_buffers = 32MB                   # min 128kB or 
#effective_cache_size = 128MB

And run it like this (from my system with 8GB of RAM):
./pg-generate-conf /home/gsmith/data/guc/postgresql.conf

You'd get a new file with these lines in it printed to standard out:

# shared_buffers = 32MB                 # min 128kB or 
#effective_cache_size = 128MB
shared_buffers 1931MB # pg_generate_conf wizard 2008-11-01
effective_cache_size 5793MB # pg_generate_conf wizard 2008-11-01

While I've got a pretty clear vision for what I'm doing with this next and 
will kick off a pgfoundry project real soon, I wanted to throw this out as 
a WIP for feedback at this point.  I was ultimately hoping to one day have 
something like this shipped as a contrib/ module to address the constant 
requests for such a thing.  I know it would be unreasonable to expect 
something in this state to make it into the 8.4 contrib at this late hour. 
But since it's independant of the core database stuff I figured I'd make 
it available right at the wire here today in the off chance that did seem 
a reasonable proposition to anybody.  It is already a big improvement over 
no tuning at all, and since it's a simple to change script I will rev this 
based on feedback pretty fast now that the most boring guts are done.

Possible feedback topics:

-Setting the next round of values requires asking the user for some input 
before making recommendations.  Is it worth building a curses-based 
interface to updating the values?  That would be really helpful for people 
with only ssh access to their server, but barring something like WCurses 
I'm not sure it would help on Windows.

-How about a GUI one with Python's Tkinter interface?  Now Windows isn't a 
problem, but people using ssh aren't going to be as happy.

-I'm not sure if there's any useful replacement for the os.sysconf 
interface I'm using to grab the memory information on the popular Windows 
Python ports.  Some of the other projects I looked at that tried to 
abstract that OS interaction more didn't seem much better here (i.e. the 
PSI library which doesn't support Windows either)

-Stepping back a bit from this particular code, is something in Python 
like this ever going to be appropriate to ship as a contrib module? 
There seems to be a bit more traction in this community for using Perl for 
such things; I might do a Perl port of this one day but that's not going 
to happen soon.

I think that's enough flametastic material now, and I do plan to join in 
on patch review in penance for the disruption I've introduced here.  The 
next specific things I'm doing with this regardless is making it read and 
respect the min/max values for settings as well as the rest of the 
information avaialable from pg_settings.  I eventually want to support all 
the syntax suggested for pg_generate_config described at 
http://wiki.postgresql.org/wiki/GUCS_Overhaul but for the moment I'm not 
being so ambitious.  Some of that is aimed at making a pg_generate_conf 
that is capable of replacing the sample postgresql.conf file, which is a 
couple of steps away from where I'm at right now.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Attachment: pg-generate-conf-v1
Description: text/plain (5.1 KB)

Responses

pgsql-hackers by date

Next:From: Josh BerkusDate: 2008-11-02 02:14:05
Subject: Re: Well done, Hackers
Previous:From: Tom LaneDate: 2008-11-02 01:49:17
Subject: Re: Any reason to have heap_(de)formtuple?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group