I am a Java Software architect, DBA, and project manager for the
University of Illinois, Department of Web Services. We use PostgreSQL
to serve about 2 million pages of dynamic content a month; everything
from calendars, surveys, forms, discussion boards, RSS feeds, etc. I am
really impressed with this tool.



The only major problem area I have found where PostgreSQL is really
lacking is in "what should my initial configuration settings be?" I
realize that there are many elements that can impact a DBA's specific
database settings but it would be nice to have a "configuration tool"
that would get someone up and running better in the beginning.



This is my idea:



A JavaScript HTML page that would have some basic questions at the top:

1) How much memory do you have?

2) How many connections will be made to the database?

3) What operating system do you use?

4) Etc...



Next the person would press a button, "generate", found below the
questions. The JavaScript HTML page would then generate content for two
Iframes at the bottom on the page. One Iframe would contain the
contents of the postgresql.conf file. The postgresql.conf settings
would be tailored more to the individuals needs than the standard
default file. The second Iframe would contain the default settings one
should consider using with their operating system.



My web team would be very happy to develop this for the PostgreSQL
project. It would have saved us a lot of time by having a
configuration tool in the beginning. I am willing to make this a very
high priority for my team.



Thanks,



Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

On 6/18/07, Campbell, Lance <lance@uiuc.edu> wrote:
>
> Mario,
> The JavaScript configuration tool I proposed would not be in the install
> of PostgreSQL. It would be an HTML page. It would be part of the HTML
> documentation or it could be a separate HTML page that would be linked
> from the HTML documentation.
>
> Thanks,
>
> Lance Campbell
> Project Manager/Software Architect
> Web Services at Public Affairs
> University of Illinois
> 217.333.0382
> http://webservices.uiuc.edu
>
> -----Original Message-----
> From: Mario Gonzalez [mailto:gonzalemario@gmail.com]
> Sent: Monday, June 18, 2007 10:16 AM
> To: Campbell, Lance
> Cc: pgsql-docs@postgresql.org; pgsql-performance@postgresql.org
> Subject: Re: [DOCS] Volunteer to build a configuration tool
>
> On 18/06/07, Campbell, Lance <lance@uiuc.edu> wrote:
> >
> > Next the person would press a button, "generate", found below the
> questions.
> > The JavaScript HTML page would then generate content for two Iframes
> at the
> > bottom on the page. One Iframe would contain the contents of the
> > postgresql.conf file. The postgresql.conf settings would be tailored
> more
> > to the individuals needs than the standard default file. The second
> Iframe
> > would contain the default settings one should consider using with
> their
> > operating system.
> >
>
> I think it could be a great help to newbies. IMVHO a bash script in
> dialog could be better than a javascript file. There are many
> administrators with no graphics navigator or with no javascript.
>
> >
>
> --
> http://www.advogato.org/person/mgonzalez/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

EXCELLENT idea Lance.

--
Yudhvir Singh Sidhu
408 375 3134 cell

> This is my idea:
>
> A JavaScript HTML page that would have some basic questions at the top:
>
> 1) How much memory do you have?
>
> 2) How many connections will be made to the database?
>
> 3) What operating system do you use?
>
> 4) Etc…
>
> Next the person would press a button, “generate”, found below the
> questions. The JavaScript HTML page would then generate content for
> two Iframes at the bottom on the page. One Iframe would contain the
> contents of the postgresql.conf file. The postgresql.conf settings
> would be tailored more to the individuals needs than the standard
> default file. The second Iframe would contain the default settings one
> should consider using with their operating system.
>
> My web team would be very happy to develop this for the PostgreSQL
> project. It would have saved us a lot of time by having a
> configuration tool in the beginning. I am willing to make this a very
> high priority for my team.
>

Hi Lance,

I agree that having a page that can assist in generating a base
configuration file is an excellent way to start off with a good
configuration that can assist a system administrator in getting half way
to a good configuration. We've recently gone through a process of
configuring a machine and it is a time consuming task of testing and
benchmarking various configuration details.

My thoughts:
Using the browser is a great idea as a universal platform. I can
foreseen a problem in that some users won't have GUI access to the
machine that they are setting up. I don't have much direct experience in
this field, but I suspect that a great number of installations happen
'headless'? This can easily be circumvented by hosting the configuration
builder on a public internet site, possibly postgresql.org?

Also, Javascript isn't the easiest language to use to get all the
decisions that need to be made for various configuration options. Would
it not be a better idea to host a configuration builder centrally,
possible on postgresql.org and have the documentation reference it,
including the docs that come packaged with postgresql (README, INSTALL
documentation?). This would mean that you wouldn't be able to package
the configuration builder, but you would be able to implement more
application logic and more complex decision making in a hosted
application. Of course, I have no idea of the skills that your team
already have



To add ideas: perhaps a more advanced tool would be able to add comment
indicating a suggested range for the particular setting. For example,
with 2Gb of RAM, it chooses a workmem of, say, 768Mb, with a comment
indicating a suggested range of 512Mb - 1024Mb.


Thanks for taking the time to put this together and for offering the
services of your team.

Kind regards,
James











---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Mon, 18 Jun 2007, Campbell, Lance wrote:

> The postgresql.conf settings would be tailored more to the individuals
> needs than the standard default file. The second Iframe would contain
> the default settings one should consider using with their operating
> system.

I'd toyed with making a Javascript based tool for this but concluded it
wasn't ever going to be robust enough for my purposes. It wouldn't hurt
to have it around through, as almost anything is an improvement over the
current state of affairs for new users.

As far as prior art goes here, there was an ambitious tool driven by Josh
Berkus called Configurator that tried to address this need but never got
off the ground, you might want to swipe ideas from it. See
http://cvs.pgfoundry.org/cgi-bin/cvs.../configurator/ for
some documents/code and
http://pgfoundry.org/docman/index.php?group_id=1000106 for a handy
Open-Office spreadsheet.

If you want this to take off as a project, make sure you can release the
code under a free software license compatible with the PostgreSQL project,
so others can contribute to it and it can be assimilated by the core
project if it proves helpful. I know I wouldn't spend a minute working on
this if that's not the case.

I'd suggest you try and get the basic look fleshed out with some
reasonable values for the parameters, then release the source and let
other people nail down the parts you're missing. Don't get stressed about
making sure you have a good value to set for everything before releasing a
beta, it's a lot easier for others to come in and help fix a couple of
parameters once the basic framework is in place.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

one thing to point out to people about this idea is that nothing says that
this page needs to be served via a webserver. If all the calculations are
done in javascript this could be a local file that you open with a
browser.

do any of the text-mode browsers implement javascript? if so then you have
an answer even for the deeply buried isolated headless servers.

David Lang

On Mon, 18 Jun 2007,
Campbell, Lance wrote:

>
> I am a Java Software architect, DBA, and project manager for the
> University of Illinois, Department of Web Services. We use PostgreSQL
> to serve about 2 million pages of dynamic content a month; everything
> from calendars, surveys, forms, discussion boards, RSS feeds, etc. I am
> really impressed with this tool.
>
>
>
> The only major problem area I have found where PostgreSQL is really
> lacking is in "what should my initial configuration settings be?" I
> realize that there are many elements that can impact a DBA's specific
> database settings but it would be nice to have a "configuration tool"
> that would get someone up and running better in the beginning.
>
>
>
> This is my idea:
>
>
>
> A JavaScript HTML page that would have some basic questions at the top:
>
> 1) How much memory do you have?
>
> 2) How many connections will be made to the database?
>
> 3) What operating system do you use?
>
> 4) Etc...

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Jun 18, 2007, at 4:09 PM, david@lang.hm wrote:

> one thing to point out to people about this idea is that nothing
> says that this page needs to be served via a webserver. If all the
> calculations are done in javascript this could be a local file that
> you open with a browser.
>
> do any of the text-mode browsers implement javascript? if so then
> you have an answer even for the deeply buried isolated headless
> servers.

It doesn't really matter.

The implementation is likely to be trivial, and could be independently
knocked out by anyone in their favorite language in a few hours.

The tricky bits are going to be defining the problem and creating the
alogrithm to do the maths from input to output.

If that's so, the language or platform the proof-of-concept code is
written for isn't that important, as it's likely to be portable to
anything
else without too much effort.

But the tricky bits seem quite tricky (and the first part, defining the
problem, is something where someone developing it on their
own, without some discussion with other users and devs
could easily end up way off in the weeds).

Cheers,
Steve

>
> David Lang
>
> On Mon, 18 Jun 2007, Campbell, Lance wrote:
>
>> I am a Java Software architect, DBA, and project manager for the
>> University of Illinois, Department of Web Services. We use
>> PostgreSQL
>> to serve about 2 million pages of dynamic content a month; everything
>> from calendars, surveys, forms, discussion boards, RSS feeds,
>> etc. I am
>> really impressed with this tool.
>>
>>
>>
>> The only major problem area I have found where PostgreSQL is really
>> lacking is in "what should my initial configuration settings be?" I
>> realize that there are many elements that can impact a DBA's specific
>> database settings but it would be nice to have a "configuration tool"
>> that would get someone up and running better in the beginning.
>>
>>
>>
>> This is my idea:
>>
>>
>>
>> A JavaScript HTML page that would have some basic questions at the
>> top:
>>
>> 1) How much memory do you have?
>>
>> 2) How many connections will be made to the database?
>>
>> 3) What operating system do you use?
>>
>> 4) Etc...
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Mon, 18 Jun 2007, david@lang.hm wrote:

> do any of the text-mode browsers implement javascript?

http://links.twibright.com/

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Mon, Jun 18, 2007 at 04:35:11PM -0700, Steve Atkins wrote:
>
> On Jun 18, 2007, at 4:09 PM, david@lang.hm wrote:
>
> The tricky bits are going to be defining the problem and creating the
> alogrithm to do the maths from input to output.


Why not methodically discuss the the alogrithms on pgsql-performance,
thus improving the chance of being on target up front. Plus, us newbies
get to see what you are thinking thus expanding our universe. I know I'd
read every word.

Thanks for doing this, btw.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

It would be cool if someone started a generic configuration+benchmark
utility that could be used with virtually any software. Something like
this:

1. Create a configuration file parser for your specific application, be
it PostgreSQL, MySQL, Apache, whatever.

2. Create a min/max or X,Y,Z configuration option file that determines
which options to try. ie:

shared_buffers = 1000-20000[1000] //1000 is the increment by
wal_buffers = 8,16,32
....

3. Create start/stop scripts for the specific application

4. Create a benchmark script for the application that returns relevant
metrics. In PGSQL's case, it would be tied in to PG bench probably. In
Apache's case AB. This utility would of course need to know how to read
the metrics to determine what is "best".

5. Run the utility. Ideally it would use some sort of genetic algorithm
to benchmark the application initially to get base numbers, then
one-by-one apply the different configuration options and re-run the
benchmark. It would output the metrics for each run and once it is done,
pick the best run and let you know what those settings are.

I don't think something like this would be very difficult at all to
write, and it would be modular enough to work for virtually any
application. For a database it would take a while to run depending on
the benchmark script, but even that you could have a "fast" and "slow"
benchmark script that could be easily run when you first install
PostgreSQL. This way too your not worrying about how much memory the
system has, or how many disks they have, etc... The system will figure
out the best possible settings for a specific benchmark.

Not to mention people could easily take a SQL log of their own
application running, and use that as the benchmark to get "real world"
numbers.

Any other sort of configuration "suggestion" utility will always have
the question of what do you recommend? How much data do you try to get
and what can be determined from that data to get the best settings? Is
it really going to be that much better then the default, at least enough
better to warrant the work and effort put into it?

On Mon, 2007-06-18 at 10:04 -0500, Campbell, Lance wrote:
> I am a Java Software architect, DBA, and project manager for the
> University of Illinois, Department of Web Services. We use PostgreSQL
> to serve about 2 million pages of dynamic content a month; everything
> from calendars, surveys, forms, discussion boards, RSS feeds, etc. I
> am really impressed with this tool.
>
>
>
> The only major problem area I have found where PostgreSQL is really
> lacking is in “what should my initial configuration settings be?� I
> realize that there are many elements that can impact a DBA’s specific
> database settings but it would be nice to have a “configuration tool�
> that would get someone up and running better in the beginning.
>
>
>
> This is my idea:
>
>
>
> A JavaScript HTML page that would have some basic questions at the
> top:
>
> 1) How much memory do you have?
>
> 2) How many connections will be made to the database?
>
> 3) What operating system do you use?
>
> 4) Etc…
>
>
>
> Next the person would press a button, “generate�, found below the
> questions. The JavaScript HTML page would then generate content for
> two Iframes at the bottom on the page. One Iframe would contain the
> contents of the postgresql.conf file. The postgresql.conf settings
> would be tailored more to the individuals needs than the standard
> default file. The second Iframe would contain the default settings
> one should consider using with their operating system.
>
>
>
> My web team would be very happy to develop this for the PostgreSQL
> project. It would have saved us a lot of time by having a
> configuration tool in the beginning. I am willing to make this a very
> high priority for my team.
>
>
>
> Thanks,
>
>
>
> Lance Campbell
>
> Project Manager/Software Architect
>
> Web Services at Public Affairs
>
> University of Illinois
>
> 217.333.0382
>
> http://webservices.uiuc.edu
>
>
>
>
--
Mike Benoit <ipso@snappymail.ca>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)

iD8DBQBGeHJfMhKjsejwBhgRAn8WAJ4zuS6lC9CkK24K6jwBNM c4aoxfkACgmNKk
g3jPipWvNGleFkeVkw3J6iA=
=9uxm
-----END PGP SIGNATURE-----

On Wed, 20 Jun 2007, Mike Benoit wrote:

> It would be cool if someone started a generic configuration+benchmark
> utility that could be used with virtually any software.

It would be cool. It would also be impossible.

> Create a benchmark script for the application that returns relevant
> metrics. In PGSQL's case, it would be tied in to PG bench probably. In
> Apache's case AB. This utility would of course need to know how to read
> the metrics to determine what is "best".

The usual situation in these benchmarks is that you get parameters that
adjust along a curve where there's a trade-off between, say, total
throughput and worse-case latency. Specifying "best" here would require a
whole specification language if you want to model how real tuning efforts
work. The AB case is a little simpler, but for PostgreSQL you'd want
something like "With this database and memory sizing, I want the best
throughput possible where maximum latency is usually <5 seconds with 1-30
clients running this transaction, while still maintaining at least 400 TPS
with up to 100 clients, and the crash recovery time can't take more than
10 minutes". There are all sorts of local min/max situations and
non-robust configurations an automated tool will put you into if you don't
force an exhaustive search by being very specific like this.

> I don't think something like this would be very difficult at all to
> write

Here I just smile and say that proves you've never tried to write one
It's a really hard problem that gets harder the more you poke at it.
There's certainly lots of value to writing a utility that automatically
tests out multiple parameter values in a batch and compares the results.
If you're not doing that now, you should consider scripting something up
that does. Going beyond that to having it pick the optimal parameters
more automatically would take AI much stronger than just a genetic
algorithm approach.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend