thank for your and other responses,

I am using FB Classic server, I read note about garbage collection
included in FB installation doc.

ad 1 - as I understood in FB Classic garbage collector works in
cooperative mode only, so could
it be that in this table garbage will remain for ever ? When I suppose
that automatic sweep will not
run due the fact that in there will not be 20000 gap between OAT and OIT ?

ad 2 - I run it on FB Classic 2.0.3. There are 4 indicies (fifth indicie
I added just for received selection bellow it)

CLSID,SITE - 0.001605
ID,CLSID - 0.000000
ID,CLSID,SITE - 0.000000
ID,CLSID,CREATEDAT$DATE - 0.000000
SITE,STATUS - 0.111111

this is output of select count(*) group by SITE,STATUS
SITE STATUS COUNT
01 2 4461321
02 2 262218
03 2 212776
04 2 224228
05 2 163239
06 2 225608
07 2 171425
08 2 103517
09 2 129714

status is not important (is not present in any indicies), but site has
only 9 different values and from it 74.9 % belongs to one value. So
suppose based on remark of Ann W. Harrison in earlier response long
running garbage cleaning has something to do with indicies involving
SITE field. Our customer encountered aswell longer time when creating
those indicies on database filled by all data already, it took some hours.



ad 3 - I did some simple load test about this table - insert records,
make selection involving this table and delete records
on FB 1.5.5, 1.5.1 and 2.0.3 and found out that after 1000 loops delay
increased plus minus 2 secs in all cases, so
at this point seems it is not FB version related

tomas


Dmitry Yemanov wrote:

>
> kopaka wrote:
> >
> > 1.
> > I have table where is very often more records (up to 100) inserted and
> > deleted. Deleted records is garbage what will be removed by garbage
> > collector when recods will be read.
>
> It's a bit more complicated in v2.x SS. For example, the garbage
> produced by a committed delete will be removed as soon as there are no
> other interested transactions active.
>
> > 2.
> > When more records (in my case cca 5 milions) are deleted from table,
> > then next time when garbage collection is run, it will take very much
> > time. On my test db. I run for select count(*) from table and after 12
> > hours I broke it without result.
>
> Do you run it on v1.5? If so, this is a known issue which is addressed
> in ODS11 of v2.0. How many indices do you have on that table? What is
> they selectivities?
>
> > 3.
> > Now I am doing some test about speed between FB 1.5.2 and FB 2.0.3 and
> > seems to me, that when I am repeating an action which involves table I
> > spoke about it under point 1 (it's records are deleted and again
> > inserted so garbage is growing in this table ) then FB 2 speed is
> > constantly slowing - more garbage more slow, while under FB 1.5 not.
> > Does anyone have some suggestion about it ?
>
> Try setting GCPolicy to Background in firebird.conf of v2.0.
>
>
> Dmitry
>
>