PG_Autotune 0.1

Folks, Justin,

Hey, I've been tinkering with PG_autotune in an effort to make it usable on my
installation.
http://gborg.postgresql.org/project/pgautotune/projdisplay.php

First off, thank you Justin for getting inspired and writing the starter
version. This is something that would probably have remained *way* down the
Postgres TODO list, were it not for you.

Since it's such a great idea, I'd like to make it bulletproof so that it can
become part of the standard Postgres distribution. I'm hoping that people
on this list can help.

Problems, Bugs, & Suggestions:
1) The program makes the assumption that the Postgres superuser is named
"pgsql", forcing me to do a search-and-replace on the source to make it work
at all on my system, where the superuser is named "postgres". This should
be a configuration option. Places I've identified where this is an issue:
a. the connection to the "metrics" database, b. the calls to Postgres
executables (which are also sometimes made as the console user, causing them
to fail if you run the program as "root").

2) The program also assumes that all Postgres binaries are symlinked in
/usr/local/bin. Since this symlinking isn't done by Postgres-make-install,
wouldn't it be better to reference $PGHOME/bin?

3) For that matter, it would be nice if the program would test $PGDATA and
$PGHOME, and prompt the user if they are empty.

4) The shell scripts need to have error-checking so that they exit if anything
blows up. I can write this if Justin can explain what the shell scripts are
supposed to do, exactly, and where errors are acceptable.

5) We need installation docs. I can write these. Sometime soon, really!

Questions & Suggestions for Enhancement:

6) The shared_buffers param is capped at 500. Isn't this awfully low for a
production server? What's the logic here?

7) Any ideas on how to get around/adjust memory maximums for the host OS?
This is easy on Linux, but other *nixes are not so easy.

8) What will be the difficulties in expanding the script to adjust more
Postgresql.conf params, such as checkpoint_segments? Can we use feedback
from the log to adjust these?

9) I *love* the idea of letting the benchmarking script run custom queries.
However, I would dearly like to expand it, letting it randomly grab from a
list of 10 custom queries entered by the user into a file or files. This
would allow the user to create a realistic mix of simple and complex queries,
including some data manipulation and procedures.

10) Can we eventually adjust the program to get feedback from system tools and
give the user hints on hardware limitations? For example, have the program
test if, at maximum settings, queries are slow but CPU and RAM are only 10%
utilized and tell the user "Your hard drives are probably too slow"?

I can help with: documentation, shell scripting, Linux system issues. Other
volunteers to help?

--
-Josh Berkus
Aglio Database Solutions
San Francisco