Re: improvements to pgtune - Mailing list pgsql-hackers

That's some great starting advice there. I have a couple of final exams in the next 36 hours. Will get to work almost
immediatelyafter that.<br />I will definitely take small steps before going for some of the tougher tasks. I would
of-courselike this conversation to go on, so I can see a more comprehensive TODO list.<br /> One of my first tasks on
GSoCis to make sure I create a good project specification document. So there can be definite expectations and targets.
Thisconversation helps me do that!<br clear="all" />Regards,<br />Shiv<br /><br /><br /><div class="gmail_quote">On
Thu,Apr 28, 2011 at 9:50 AM, Greg Smith <span dir="ltr"><<a href="mailto:greg@2ndquadrant.com"
target="_blank">greg@2ndquadrant.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex"> Shiv wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex">  On the program I hope to learn as much about professional software
engineeringprinciples as PostgreSQL. My project is aimed towards extending and hopefully improving upon pgtune. If any
ofyou have some ideas or thoughts to share. I am all ears!!<br /></blockquote><br /> Well, first step on the software
engineeringside is to get a copy of the code in a form you can modify.  I'd recommend grabbing it from <a
href="https://github.com/gregs1104/pgtune"target="_blank">https://github.com/gregs1104/pgtune</a> ; while there is a
copyof the program on <a href="http://git.postgresql.org" target="_blank">git.postgresql.org</a>, it's easier to work
withthe one on github instead.  I can push updates over to the copy on <a href="http://postgresql.org"
target="_blank">postgresql.org</a>easily enough, and that way you don't have to worry about getting an account on that
server.<br/><br /> There's a long list of suggested improvements to make at <a
href="https://github.com/gregs1104/pgtune/blob/master/TODO"
target="_blank">https://github.com/gregs1104/pgtune/blob/master/TODO</a><br/><br /> Where I would recommend getting
startedis doing some of the small items on there, some of which I have already put comments into the code about but
justnot finished yet.  Some examples:<br /><br /> -Validate against min/max<br /> -Show original value in output<br />
-Limitshared memory use on Windows (see notes on shared_buffers at <a
href="http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server"
target="_blank">http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server</a>for more information)<br /> -Look for
postgresql.conffile using PGDATA environment variable<br /> -Look for settings files based on path of the pgtune
executable<br/> -Save a settings reference files for newer versions of PostgreSQL (right now I only target 8.4) and
allowpassing in the version you're configuring.<br /><br /> A common mistake made by GSOC students is to dive right in
totrying to make big changes.  You'll be more successful if you get practice at things like preparing and sharing
patcheson smaller changes first.<br /><br /> At the next level, there are a few larger features that I would consider
valuablethat are not really addressed by the program yet:<br /><br /> -Estimate how much shared memory is used by the
combinationof settings.  See Table 17-2 at <a href="http://www.postgresql.org/docs/9.0/static/kernel-resources.html"
target="_blank">http://www.postgresql.org/docs/9.0/static/kernel-resources.html</a>; those numbers aren't perfect, and
improvingthat table is its own useful project.  But it gives an idea how they fit together.  I have some notes at the
endof the TODO file on how I think the information needed to produce this needs to be passed around the inside of
pgtune.<br/><br /> -Use that estimate to produce a sysctl.conf file for one platform; Linux is the easiest one to start
with. I've attached a prototype showing how to do that, written in bash.<br /><br /> -Write a Python-TK or web-based
front-endfor the program.<br /><br /> Now that I know someone is going to work on this program again, I'll see what I
cando to clean some parts of it up.  There are a couple of things it's easier for me to just fix rather than to
describe,like the way I really want to change how it adds comments to the settings it changes.<br /><font
color="#888888"><br/> -- <br /> Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD<br /> PostgreSQL
Training,Services, and 24x7 Support  <a href="http://www.2ndQuadrant.us" target="_blank">www.2ndQuadrant.us</a><br
/><br/><br /></font><br />#!/bin/bash<br /><br /> # Output lines suitable for sysctl configuration based<br /> # on
totalamount of RAM on the system.  The output<br /> # will allow up to 50% of physical memory to be allocated<br /> #
intoshared memory.<br /><br /> # On Linux, you can use it as follows (as root):<br /> #<br /> # ./shmsetup >>
/etc/sysctl.conf<br/> # sysctl -p<br /><br /> # Early FreeBSD versions do not support the sysconf interface<br /> #
usedhere.  The exact version where this works hasn't<br /> # been confirmed yet.<br /><br /> page_size=`getconf
PAGE_SIZE`<br/> phys_pages=`getconf _PHYS_PAGES`<br /><br /> if [ -z "$page_size" ]; then<br />  echo Error:  cannot
determinepage size<br />  exit 1<br /> fi<br /><br /> if [ -z "$phys_pages" ]; then<br />  echo Error:  cannot
determinenumber of memory pages<br />  exit 2<br /> fi<br /><br /> shmall=`expr $phys_pages / 2`<br /> shmmax=`expr
$shmall\* $page_size`<br /><br /> echo \# Maximum shared segment size in bytes<br /> echo kernel.shmmax = $shmmax<br />
echo\# Maximum number of shared memory segments in pages<br /> echo kernel.shmall = $shmall<br /><br
/></blockquote></div><br/>