We are using 2.1.5 for production. I just used 2.5 for an experiment.

On 12/08/2013 19:15, liviuslivius@... wrote:
>
> 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
> <mailto:firebird-support%40yahoogroups.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]
>
>


--
Tim Ward



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