As far as I can tell from gstat output there are no old versions, which
suggests that either I am misunderstanding what gstat is telling me or
this explanation is not the answer to my problem.

I see zero writes doing the count(*).

On 09/08/2013 20:15, Ann Harrison wrote:
>
> > Karol Bieniaszewski wrote
>
> >
> > If this is really big update count and pages of that table are
> filled near 100% then row version go to another page and to process
> one row engine must read all pages as many times as versions pages
> >
> > But may be i'am wrong - i do not know super internal details about
> Firebird work
> >
>
> Karol is exactly right - at least I believe he is.
>
> When you do a "count (*)" on a table with no back versions, the pages
> of the table are read one by one, so the load on the page cache is
> minimal - some metadata, parts of the RDB$PAGES table, a pointer page,
> and one active data page. So the total number of reads is about the
> total number of pages in the table and nothing is ever forced out of
> cache.
>
> When you update a record, the old version (or the difference between
> the two versions) is written elsewhere. In the best case, elsewhere is
> on the same page - if the full back version is preserved, then it's
> not re-written at all, Firebird just rearranges some pointers. If you
> update so many record on a page that there is no place for the old
> version (full or difference) the the old version goes on an overflow
> page. If you do all your updates in storage order, then all the old
> versions from page "n" go to the same overflow page. If, as is more
> likely, you update the records in random order, each overflow page
> will have records from many different primary data pages. In your
> case, I'd guess that each overflow page has back versions from about
> 100 different data pages.
>
> So, my guess is that you read a record, which causes Firebird to chase
> down the old versions to see if any can be removed. That brings in an
> overflow page, but there's still an older version on a different
> overflow page. That's three page reads for that record. The next
> record also has two back versions on two different overflow pages ...
> two more page reads. And so on. Sooner or later, the cache fills and
> pages get released. Naturally, given the way the world works, the next
> overflow page you need is the one that was just released.
>
> question: How many writes do you see when you run the "count (*)"?
>
> Good luck,
>
> Ann
>
> <irrelevant>Sorry this is so late, Jim and I have beens sailing on the
> Saint John River in Canada, warm fresh water, out beyond the reach of
> cell phones. We're back in civilization temporarily so he can replace
> the wireless mouse my dog ate and resume coding his next
> project.</irrelevant>
>
>


--
Tim Ward



[Non-text portions of this message have been removed]