Hi,

you write that you have FB2.5 – why you have ODS version 11.1 not 11.2?
I have 2.5.3 and all DB have ODS11.2

regards,
Karol Bieniaszewski


From: Tim Ward
Sent: Monday, August 12, 2013 4:56 PM
To: firebird-support@yahoogroups.com
Subject: Re: Odp: [firebird-support] Performance with heavily updated table


I believe the relevant part of the gstat output for the table in
question to be as below, please let me know if I've got that wrong.
(Names changed but otherwise unaltered.) My interpretation, which may be
faulty, of

Average version length: 0.00, total versions: 0, max versions: 0

is that there are no versions of records lying around, so none of the
explanations of the performance so far, all of which I think involve
multiple versions in some way, is correct.

What would you like to see from the MON$ tables?

--------------------------------------------

[root@mymachine db]# gstat mydb -t "MYTABLE" -r

Database "mydb"
Database header page information:
Flags 0
Checksum 12345
Generation 3976577
Page size 16384
ODS version 11.1
Oldest transaction 3966272
Oldest active 3966273
Oldest snapshot 3966273
Next transaction 3966275
Bumped transaction 1
Sequence number 0
Next attachment ID 10293
Implementation ID 19
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Jul 25, 2013 14:17:13
Attributes

Variable header data:
Sweep interval: 0
*END*

Database file sequence:
File mydb is the only file

Analyzing database pages ...
MYTABLE(269)
Primary pointer page: 636, Index root page: 637
Average record length: 125.93, total records: 129599
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 1441, data page slots: 1849, average fill: 98%
Fill distribution:
0 - 19% = 1
20 - 39% = 4
40 - 59% = 0
60 - 79% = 2
80 - 99% = 1434

On 12/08/2013 15:42, mailto:liviuslivius%40poczta.onet.pl wrote:
>
> Hi,
>
> Put info from gstat or send it to me priv
> Also output from mon$ tables.
>
> Regards,
> Karol Bieniaszewski
>
> ----- Reply message -----
> Od: "Tim Ward" <mailto:tdw%40telensa.com <mailto:tdw%40telensa.com>>
> Do: <mailto:firebird-support%40yahoogroups.com
> <mailto:firebird-support%40yahoogroups.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
>

--
Tim Ward

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






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