Thread: 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