On Sun, 2 Nov 2008, Josh Berkus wrote:
I'd start with command-line switches, e.g.
config --memory=32GB --type=DW --size=500GB --connections=20
Attached version takes all its input via command line switches. If you
don't specify an explict number of connections, it also implements setting
max_connections via some of the logic from your calcfactors spreadsheet.
I think using more of the ideas from there will drive me crazy unless I
put that into a text file it pulls in instead of a bunch of if statements.
I'm not sure if that much of the complexity of that larger model is
warranted at this point; could use a second opinion on that.
Here's the syntax it accepts now:
$ ./pg-generate-conf --help
Usage: pg-generate-conf [options]
Options:
--version show program's version number and exit
-h, --help show this help message and exit
-i INPUTCONFIG, --input-config=INPUTCONFIG
Input configuration file
-o OUTPUTCONFIG, --output-config=OUTPUTCONFIG
Output configuration file, defaults to standard output
-M TOTALMEMORY, --memory=TOTALMEMORY
Total system memory, will attempt to detect if
unspecified
-T DBTYPE, --type=DBTYPE
Database type, defaults to Mixed, valid options are
DSS, OLTP, Web, Mixed, Desktop
-c CONNECTIONS, --connections=CONNECTIONS
Maximum number of expected connections, default
depends on database type
-D, --debug Enable debugging mode
I've realized I need to duplicate all the memory setting parsing stuff
from the GUC code (as well as the code in SHOW that picks a reasonable
output size) as my next step here, that will be in my next update to this
program.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
#!/usr/bin/python
"""
pg_generate_conf
Sample usage shown by running with "--help"
"""
import sys
import os
import datetime
import optparse
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 updateIfLarger(self,name,newValue):
if self.settingsLookup.has_key(name):
# TODO This comparison needs all the values converted to numeric form
# and converted to the same scale before it will work
if (True): #newValue > self.settingsLookup[name].getValue():
self.updateSetting(name,newValue)
def writeConfigFile(self,fileHandle):
for l in self.configFile:
fileHandle.write(l.outputFormat()+"\n")
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 totalMem():
# Should work on UNIX and Mac OS platforms
physPages = os.sysconf("SC_PHYS_PAGES")
pageSize = os.sysconf("SC_PAGE_SIZE")
totalMem = physPages * pageSize
return totalMem
def ReadOptions():
parser=optparse.OptionParser(
usage="usage: %prog [options]",
version="1.0",
conflict_handler="resolve")
parser.add_option('-i','--input-config',dest="inputConfig",default=None,
help="Input configuration file")
parser.add_option('-o','--output-config',dest="outputConfig",default=None,
help="Output configuration file, defaults to standard output")
parser.add_option('-M','--memory',dest="totalMemory",default=None,
help="Total system memory, will attempt to detect if unspecified")
parser.add_option('-T','--type',dest="dbType",default="Mixed",
help="Database type, defaults to Mixed, valid options are DSS, OLTP, Web,
Mixed, Desktop")
parser.add_option('-c','--connections',dest="connections",default=None,
help="Maximum number of expected connections, default depends on database
type")
parser.add_option('-D','--debug',action="store_true",dest="debug",
default="False",help="Enable debugging mode")
(options,args)=parser.parse_args()
if options.debug==True:
print "Command line options: ",options
print "Command line arguments: ",args
return (options,args)
def wizardTune(config,options):
# We expect the following options are passed into here:
#
# dbType: Defaults to Mixed
# connections: If missing, will set based on dbType
# totalMemory: If missing, will detect
dbType=options.dbType.lower()
if dbType=="mixed":
rconn=100
minconn=50
elif dbType=="dss":
rconn=10
minconn=10
elif dbType=="web":
rconn=300
minconn=100
elif dbType=="oltp":
rconn=200
minconn=50
elif dbType=="desktop":
rconn=10
minconn=5
else:
print "Error: unexpected setting for dbType"
os.exit(1)
if options.connections==None:
connections=rconn
else:
connections=options.connections
if options.totalMemory==None:
totalMemory=totalMem()
else:
totalMemory=options.totalMemory
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.updateIfLarger('shared_buffers',"%d" % sharedMB+"MB")
config.updateIfLarger('effective_cache_size',"%d" % cacheSizeMB+"MB")
config.updateSetting('max_connections',connections)
if __name__=='__main__':
(options,args)=ReadOptions()
configFile=options.inputConfig
if configFile==None:
print "Can't do anything without an input config file; try --help"
sys.exit(1)
# TODO Show usage here
config=PGConfigFile(configFile)
if (options.debug==True):
config.debugPrintInput()
print
config.debugPrintSettings()
wizardTune(config,options)
outputFileName=options.outputConfig
if outputFileName==None:
outputFile=sys.stdout
else:
outputFile=open(outputFileName,'w')
config.writeConfigFile(outputFile)
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
