dear vlad,

many thanks for you strong and clear explanation !

--- In firebird-support@yahoogroups.com, "hvlad" <hvlad@...> wrote:
>
> > also something interesting
> >
> > if i do (wee yyy a completely new value)
> > delete ... where xxx= yyy
>
> Delete should do
>
> a) purge old record(s) version(s) with the xxx = yyy (which you deleted and committed before).
> This also removes entries for this old record(s) from all indices.
> This is not cheap operation.
> Note, this will not happen if there is concurrent live transaction interested in some of this old record(s) version(s).
>
> b) create delete stub for currently alive record(s) with xxx = yyy.
> This is relatively cheap, especially if data page with record have some free space. Else backversion will be placed at another data page and it adds some cost.
>
>
> > Insert ... (xxx= yyy)
>
> Insert should do
>
> a) create new record somewhere
> This is usually cheap
>
> b) add entries for all indices
> This is costly.
>
> > commit => 650 ms (isql show read 2, write 297)
>
> Commit will write all dirty pages on disk. Cost of this operation can be very different in dependance of
>
> a) how many pages should be written
>
> b) how far this pages on disk from each other
>
> c) in which order this pages should be written : because of careful write strategy we have always consistent database image on disk but we can't fully optimize writes order.
>
> For example, write 32 consecutive pages (1, 2, ..., 32) could have almost the same cost as write 2 pages (1, 32).
> And write of the same 2 pages but in reverse order (32, 1) have much bigger cost.
>
> Of course, if there is another activity on the same HDD whole picture could be very different.
>
> > and i do again (exactly the same as previous)
> > delete ... where xxx= yyy
> > Insert ... (xxx= yyy)
> > commit => 600 ms (isql show read 2, write 294)
> >
> > and i do again (exactly the same as previous)
> > delete ... where xxx= yyy
> > Insert ... (xxx= yyy)
> > commit => 40 ms (isql show read 2, write 148)
> >
> > and it's always like this, at the 3th time (only) it's go fastly !
> > why for the exact same intructions the commit need to write more in The 1rt and 2nd call than in the Third call ?
>
> I'm afraid if i can't reproduce it by myself i can't said much about it. Probably, if you'll show separate stats for delete, insert and commit it will be more clear.
>
> Regards,
> Vlad
>