New PostgreSQL Tool available : pg_autotune
Hi everyone,
Have been putting together a tool called "pg_autotune" for automatically
tuning a PostgreSQL database (either local or remote). It does this by
repetitively benchmarking PostgreSQL (using Tatsuo's pgbench code) with
different buffer settings, then fine tuning those settings depending on
the results returned. All of the data generated is stored into a
separate PostgreSQL database for further aggregate analysis later on.
This should be a default tool for all new PostgreSQL installations.
The URL for pg_autotune is:
http://gborg.postgresql.org/project/pgautotune
It was created on a FreeBSD system, but should also work on at least
Linux, Solaris, and MacOS X.
This is a time & load intensive tool, so you'll need to ensure you only
run it when you have a couple of hours to wait for the results.
Overnight is good. :)
Regards and best wishes,
Justin Clift
--
"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
Justin Clift <justin@postgresql.org> writes:
Have been putting together a tool called "pg_autotune" for automatically
tuning a PostgreSQL database (either local or remote). It does this by
repetitively benchmarking PostgreSQL (using Tatsuo's pgbench code) with
different buffer settings, then fine tuning those settings depending on
the results returned.
You should have chosen a better foundation. pg_bench is notorious for
producing results that are (a) nonrepeatable and (b) not relevant to
a wide variety of situations. All it really tells you about is the
efficiency of a large number of updates to a small number of rows.
I'd take the results with a large grain of salt.
regards, tom lane
Tom Lane wrote:
Justin Clift <justin@postgresql.org> writes:
Have been putting together a tool called "pg_autotune" for automatically
tuning a PostgreSQL database (either local or remote). It does this by
repetitively benchmarking PostgreSQL (using Tatsuo's pgbench code) with
different buffer settings, then fine tuning those settings depending on
the results returned.You should have chosen a better foundation. pg_bench is notorious for
producing results that are (a) nonrepeatable and (b) not relevant to
a wide variety of situations. All it really tells you about is the
efficiency of a large number of updates to a small number of rows.
Hi Tom,
You're totally right about this. Have been forced to ensure that each
client connection does a minimum of 200 transactions per connection,
etc, just to get anything in the way of reliable results.
It's just that this started out as playing around with pgbench, then
grew from that. However, it's been put together so that other tests can
be added easily, and it doesn't even have to use Tatsuo's pgbench code.
Was thinking of asking Andy Riebs if we'd be ok to use his OSDB code, as
we'd need him to ok this in order to have it still be under the BSD
license.
I'd take the results with a large grain of salt.
It takes the inaccuracy of Tatsuo's pgbench code into account.
:-)
Regards and best wishes,
Justin Clift
regards, tom lane
--
"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
You should have chosen a better foundation. pg_bench is notorious for
producing results that are (a) nonrepeatable and (b) not relevant to
a wide variety of situations. All it really tells you about is the
efficiency of a large number of updates to a small number of rows.
You might want to try -N option of pgbench. It avoids updates to
branches and tellers tables.
--
Tatsuo Ishii
Tatsuo Ishii wrote:
You should have chosen a better foundation. pg_bench is notorious for
producing results that are (a) nonrepeatable and (b) not relevant to
a wide variety of situations. All it really tells you about is the
efficiency of a large number of updates to a small number of rows.You might want to try -N option of pgbench. It avoids updates to
branches and tellers tables.
Cool. Do you feel this will noticeable increase the consistency of the
measurements?
The inconsistency of the internal benchmark results means that
pg_autotune has been using 5-run averages, and using a large tolerance
factor by default. It would be good to improving on that.
:-)
Regards and best wishes,
Justin Clift
--
Tatsuo Ishii
--
"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