Hi,

Put info from gstat or send it to me priv
Also output from mon$ tables.

Regards,
Karol Bieniaszewski

----- Reply message -----
Od: "Tim Ward" <tdw@...>
Do: <firebird-support@yahoogroups.com>
Temat: Odp: [firebird-support] Performance with heavily updated table
Data: pon., sie 12, 2013 10:00
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]











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