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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
#!/usr/bin/python
"""
pg_generate_conf
Sample usage:
pg_generate_conf <config-file>
Reads that config file, updates a few key configuration settings, then
writes result to standard output (so far)
"""
import sys
import os
import datetime
class PGConfigLine:
"""
Stores the value of a single line in the postgresql.conf file, with the
following fields:
lineNumber : integer
originalLine : string
commentSection : string
setsParameter : boolean
If setsParameter is True these will also be set:
name : string
readable : string
raw : string This is the actual value
delimiter (expectations are ' and ")
"""
def __init__(self,line,num=0):
self.originalLine=line
self.lineNumber=num
self.setsParameter=False
# Remove comments and edge whitespace
self.commentSection=""
commentIndex=line.find('#')
if commentIndex >= 0:
line=line[0:commentIndex]
self.commentSection=line[commentIndex:]
line=line.strip()
if line == "":
return
# Split into name,value pair
equalIndex=line.find('=')
if equalIndex<0:
return
(name,value)=line.split('=')
name=name.strip()
value=value.strip()
self.name=name;
self.setsParameter=True;
# Many types of values have ' ' characters around them, strip
# TODO Set delimiter based on whether there is one here or not
value=value.rstrip("'")
value=value.lstrip("'")
self.readable=value
def outputFormat(self):
s=self.originalLine;
return s
# Implement a Java-ish interface for this class
def getName(self):
return self.name
def getValue(self):
return self.readable
def getLineNumber(self):
return self.lineNumber
def isSetting(self):
return self.setsParameter
def toString(self):
s=str(self.lineNumber)+" sets?="+str(self.setsParameter)
if self.setsParameter:
s=s+" "+self.getName()+"="+self.getValue()
# TODO: Include commentSection, readable,raw, delimiter
s=s+" originalLine: "+self.originalLine
return s
class PGConfigFile:
"""
Read, write, and manage a postgresql.conf file
There are two main structures here:
configFile[]: Array of PGConfigLine entries for each line in the file
settingLookup: Dictionary mapping parameter names to the line that set them
"""
def __init__(self, filename):
self.readConfigFile(filename)
def readConfigFile(self,filename):
self.filename=filename
self.settingsLookup={}
self.configFile=[]
lineNum=0;
for line in open(filename):
line=line.rstrip('\n')
lineNum=lineNum + 1
configLine=PGConfigLine(line,lineNum)
self.configFile.append(configLine)
if configLine.isSetting():
self.settingsLookup[configLine.getName()]=configLine
def updateSetting(self,name,newValue):
newLineText=str(name)+" "+str(newValue)+" # pg_generate_conf wizard
"+str(datetime.date.today())
newLine=PGConfigLine(newLineText)
if self.settingsLookup.has_key(name):
# Comment out old line
oldLine=self.settingsLookup[name]
oldLineNum=oldLine.getLineNumber()
commentedLineText="# "+oldLine.outputFormat()
commentedLine=PGConfigLine(commentedLineText,oldLineNum)
# Subtract one here to adjust for zero offset of array.
# Any future change that adds lines in-place will need to do something
# smarter here, because the line numbers won't match the array indexes
# anymore
self.configFile[oldLineNum-1]=commentedLine
self.configFile.append(newLine)
self.settingsLookup[name]=newLine
def writeConfigFile(self,fileHandle):
for l in self.configFile:
print l.outputFormat()
def debugPrintInput(self):
print "Original file:"
for l in self.configFile:
print l.toString()
def debugPrintSettings(self):
print "Settings listing:"
for k in self.settingsLookup.keys():
print k,'=',self.settingsLookup[k].getValue()
def totalMemory():
# Should work on UNIX and Mac OS platforms
physPages = os.sysconf("SC_PHYS_PAGES")
pageSize = os.sysconf("SC_PAGE_SIZE")
totalMemory = physPages * pageSize
return totalMemory
def wizardTune(config):
mb=1024*1024
osOverhead=256 * mb;
memAvail=totalMemory() - osOverhead;
if memAvail>=(osOverhead / 2):
# The net effect of the above is that if you don't have at least 384MB
# of physical memory, we don't do any tuning suggestions right now.
# The simple formulas below presume a relatively modern system with at
# least that much RAM available
sharedMB=(memAvail / 4) / mb;
cacheSizeMB=(memAvail * 3 / 4) / mb;
config.updateSetting('shared_buffers',"%d" % sharedMB+"MB")
config.updateSetting('effective_cache_size',"%d" % cacheSizeMB+"MB")
if __name__=='__main__':
debug=False
configFile=sys.argv[1]
config=PGConfigFile(configFile)
if (debug):
config.debugPrintInput()
print
config.debugPrintSettings()
wizardTune(config)
config.writeConfigFile(sys.stdout)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
