Re: Volunteer to build a configuration tool - Mailing list pgsql-performance

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>