Re: improvements to pgtune - Mailing list pgsql-hackers

FYI, I can help if you need javascript assistance.

---------------------------------------------------------------------------

Greg Smith wrote:
> Shiv wrote:
> >  So my exams are over now and am fully committed to the project in 
> > terms of time. I have started compiling a sort of personal todo for 
> > myself. I agree with your advice to start the project with small steps 
> > first. (I have a copy of the code and am trying to glean as much of it 
> > as I can)
> 
> I just fixed a couple of bugs in the program that were easier to correct 
> than explain.  The code changes have been pushed to the github repo.  
> I've also revised the output format to be a lot nicer.  There's a UI 
> shortcut you may find useful too; the program now takes a single input 
> parameter as the input file, outputting to standard out.
> 
> So a sample run might look like this now:
> 
> $ ./pgtune postgresql.conf.sample
> [old settings]
> #------------------------------------------------------------------------------
> # pgtune wizard run on 2011-05-08
> # Based on 2060728 KB RAM in the server
> #------------------------------------------------------------------------------
> 
> default_statistics_target = 100
> maintenance_work_mem = 120MB
> checkpoint_completion_target = 0.9
> effective_cache_size = 1408MB
> work_mem = 12MB
> wal_buffers = 8MB
> checkpoint_segments = 16
> shared_buffers = 480MB
> max_connections = 80
> 
> >  I would really appreciate your reply to Josh's thoughts. It would 
> > help me understand the variety of tasks and a possible ordering for me 
> > to attempt them.
> > Josh's comments :/ "What would you list as the main things pgtune 
> > doesn't cover right now?  I have my own list, but I suspect that yours 
> > is somewhat different./
> > /
> > /
> > /I do think that autotuning based on interrogating the database is 
> > possible.  However, I think the way to make it not be a tar baby is to 
> > tackle it one setting at a time, and start with ones we have the most 
> > information for.  One of the real challenges there is that some data 
> > can be gleaned from pg_* views, but a *lot* of useful performance data 
> > only shows up in the activity log, and then only if certain settings 
> > are enabled."/
> 
> I just revised the entire TODO file (which is now TODO.rst, formatted in 
> ReST markup:  http://docutils.sourceforge.net/rst.html ; test with 
> "rst2html TODO.rst > TODO.html and look at the result).  It should be 
> easier to follow the flow of now, and it's organized in approximately 
> the order I think things need to get finished in.
> 
> There are few major areas for expansion that might happen on this 
> program to choose from.  I was thinking about doing them in this order:
> 
> 1) Fix the settings validation and limits.  I consider this a good place 
> to start on hacking the code.  it's really necessary work eventually, 
> and it's easier to get started with than the other ideas.
> 
> 2) Improve internals related to tracking things like memory and 
> connections so they're easier to pass around the program.  Adding a 
> "platform" class is what I was thinking of.  See the "Estimating shared 
> memory usage" section of the TODO for more information.  Add PostgreSQL 
> version as another input to that.
> 
> 3) Improve the settings model used for existing parameters.  Right now 
> people have reported that the work_mem settings suggested in particular 
> are too high for many servers.  Ideas about why that is are in the 
> TODO.  (This really requires the platform change be done first, or the 
> code will be too hard to write/maintain)
> 
> 4) Estimate memory used by the configuration and output sysctl.conf 
> files.  (Needs platform change too)
> 
> 5) Add tuning suggestions for new parameters.  The most obvious ideas 
> all involve adding common logging changes.
> 
> 6) Create some new UIs for running the program.  A text-based program 
> that asked questions (a 'wizard') or a GUI program doing the same are 
> two common suggestions.
> 
> The ideas Josh was talking about for interrogating the database for 
> things are all a long ways off from the current state of the code being 
> able to support them.  If (1) through (3) here were done, that whole 
> direction starts with (5) and then runs further that way.  That might be 
> a valid direction to move next instead of the (4), (6) I've listed 
> here.  You'd have finished something that taught enough about how the 
> existing program works to be able to make some more difficult design 
> decisions about fitting new features into it.
> 
> If you really want to get right into live server analysis, there's no 
> way for that to fit into the current program yet.  And I don't think 
> you'll get enough practice to see how it would without doing some more 
> basic work first.  You might as well write something new if that's your 
> goal, and expect that you may not finish anything useful by the end of 
> the summer.  If you want to complete a project that results in code that 
> people absolutely will use, the more boring plan I've outlined goes that 
> way.  One of the secrets to software development is that ideas for 
> complicated features rarely result in software that gets released, while 
> working on simpler programs that don't aim so high leads to software 
> that ships to the world and finds users.  The only reason pgtune is now 
> available in packaged form on multiple operating systems is that I 
> ignored all advice about aiming for a complicated tool and instead wrote 
> a really simple one.  That was hard enough to finish.
> 
> -- 
> Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
> PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
> 
> 
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +