Thread: Re :MySQL Benchmark page - Problem with vacuum() in PostgreSQL

Dear all,

Please humour me if this is a silly comment , or has been made before  -
but....

when Monty posted the original message I had a few thoughts  - ........

I understand that there is a issue with

loop many times
  perform a set a updates/inserts
  run vauuum
end loop

I would not peform a test this way.... let Postgresql MVCC sort out the
relevant rows to manage... so run vacuum once only....after all on a real
system you try to minimize the number of times vacuum ( or its related
equivalents in other dbms's - e.g : analyze ) are run ( as they are typically
expensive ).

So I guess I am saying that if the Benchmark requires multiple runs of vacuum
/ analyze type functions then maybe the design of the benchmark should be
examined a bit...


regards

Mark

Sounds eminently reasonable.... my misunderstanding sorry :-(

regards,
Mark

On Saturday 18 August 2001 00:15, Michael Widenius wrote:
> Hi!
>
> The idea is to run the same benchmarks two different ways:
>
> Normal:  Run without any vacuum commands
> Fast:    Run vacuum after each massive update/insert batch
>
> The reason for the 'fast' option is to get more accurate times for
> system that is mostly 'read' oriented.
>
> The test is more done like this:
>
> - Do a lot of inserts
> - vacuum
> - Do a lot of updates/deletes
> - vacuum
> - Do a lot of selects
> - drop tables
>
> Vacuum is never performed in a loop.
>
> In our tests we have noticed that we get a total speedup of 9 times
> when doing vacuum this way.
>
> Regards,
> Monty
>
> PS: The patch / new file we got from Tom Lane to fix the problem with
>     vacuum didn't help.  I will do a complete bug report about this
>     later today.

Hi!

>>>>> "Mark" == Mark kirkwood <markir@slingshot.co.nz> writes:

Mark> Dear all,
Mark> Please humour me if this is a silly comment , or has been made before  -
Mark> but....

Mark> when Monty posted the original message I had a few thoughts  - ........

Mark> I understand that there is a issue with

Mark> loop many times
Mark>   perform a set a updates/inserts
Mark>   run vauuum
Mark> end loop

Mark> I would not peform a test this way.... let Postgresql MVCC sort out the
Mark> relevant rows to manage... so run vacuum once only....after all on a real
Mark> system you try to minimize the number of times vacuum ( or its related
Mark> equivalents in other dbms's - e.g : analyze ) are run ( as they are typically
Mark> expensive ).

Mark> So I guess I am saying that if the Benchmark requires multiple runs of vacuum
Mark> / analyze type functions then maybe the design of the benchmark should be
Mark> examined a bit...

The idea is to run the same benchmarks two different ways:

Normal:  Run without any vacuum commands
Fast:    Run vacuum after each massive update/insert batch

The reason for the 'fast' option is to get more accurate times for
system that is mostly 'read' oriented.

The test is more done like this:

- Do a lot of inserts
- vacuum
- Do a lot of updates/deletes
- vacuum
- Do a lot of selects
- drop tables

Vacuum is never performed in a loop.

In our tests we have noticed that we get a total speedup of 9 times
when doing vacuum this way.

Regards,
Monty

PS: The patch / new file we got from Tom Lane to fix the problem with
    vacuum didn't help.  I will do a complete bug report about this
    later today.