Re: improvements to pgtune

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Greg Smith <greg(at)2ndquadrant(dot)com>
Cc: Shiv <rama(dot)theone(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: improvements to pgtune
Date: 2011-05-10 14:51:58
Message-ID: 201105101451.p4AEpw314918@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)2ndQuadrant(dot)com Baltimore, MD
> PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Cédric Villemain 2011-05-10 14:58:18 Re: the big picture for index-only scans
Previous Message Robert Haas 2011-05-10 14:47:43 collateral benefits of a crash-safe visibility map