Re: PG_Autotune 0.1

Josh Berkus wrote:
>
> 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.

Thats cool. :)

> 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.

Hopefully. :)

> 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").

Good point. It was developed on FreeBSD, and the PostgreSQL superuser
on FreeBSD (using the default installation method) is called "pgsql".
On at least Solaris and Linux the most common name for the superuser
appears to be "postgres".

> 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?

Yep.

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

Good point.

> 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.

Ok, no problem.

There are really only two shell scripts and a template. Forgot to
include the template i n the downloadable version. :( Have to fix that
soon.

The shell scripts all reside in the $PGDATA directory and work like
this:

1) The main shell script is called by the pg_autotune executable, and
all it does is adjust the settings for a couple of variables in the
postgresql.conf file. At present the variables it adjusts are
max_connections, sort_mem, vacuum_mem and shared_buffers. Others could
definitely be added in, but this is a start. The method used for
adjusting the variables is to have a template postgresql.conf file with
tokens for the settings to be replaced, and then parsing them with sed
or awk or something. Can't remember offhand how, but I do remember it
was a quick&ugly hack. :-/ Needs to be done properly down the track.

2) Restarts the PostgreSQL database (pg_ctl stop; pg_ctl start). Sure,
long approach, but it works reliably. :)

3) The second shell script exists only to catch the output from the
"pg_ctl start" command and then exit, as if you don't pipe the output to
a valid process then the "pg_ctl start" doesn't appear to work
properly. This was the only way I could see that would consistently
work and not leave open filehandles around.

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

Cool. Lets do it. :)

> 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?

They're just values that were ok to test with whilst making the program
work.

> 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.

Probably the best approach initially is to detect memory failure related
errors where possible and then advise the user how to adjust them.
Pointing to the relevant section of the PostgreSQL manual in the
PostgreSQL Interactive Docs might be the way to go here.

> 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?

Good idea. As this tool is a reasonably brute force tester, the more
parameters added could increase the time needed for testing, unless
someone comes up with some bright ideas. :)

> 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.

Hey good idea. The section of the code in place for letting the user
run custom queries isn't yet finished, but it wouldn't take a
half-decent coder long to do.

> 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"?

Very good thought, and very worthwhile. Any idea how to start with it?

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

Hopefully.

:)

Regards and best wishes,

Justin Clift

> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi