svanderclock wrote:

>

Reworked your data as a chart. For 40 inserts and 40 deletes:

Page Size Page Buffers Execute Time Commit Time

4096 75 9252ms 78ms
4096 2048 156ms 2543ms
16384 2048 172ms 1529ms

>
>
> why with a lower page buffer the commit time is very fast but the execution time is very slow and with a more larger buffer it's the opposite ?

With 75 buffers, you were writing out most of the pages before
the commit. At most, you wrote 75 pages on commit. With 2048
pages, you're writing that much more on commit. When you go
to a large page size, you write 1/4 as many pages, each of which
is 4 times larger. Bigger writes are slightly more expensive,
but fewer larger writes seems help on commit. In all cases,
probably got several upper level index pages that don't change,
so the actual cost per page written on commit depends on page
size.

The depth of the index matters much less for writes - finding
the location is relatively cheap compared with writing back
a page. Index depth shows up as a problem mostly on reads.


Good luck,

Ann