Subject Slow Sweep speed on large database - Long post
Author Eduardo A. Salgado
Longish question that deals with speed, of course.

Firebird running in an 8 processor machine with 2 Gig
RAM. Affinity, after reading here that it was academic to use all
the processors, has been set to the first processor suing
SuperServer. Again, can we gain anything from letting SS use all the

Have a large database of 4.5+ Gig. Has 5 tables. One has 4.1
million records, the others are very small. Max Index Depth: 3 with
some 8 Indexes (see header stats below).

Doing a "Select Count(*)" for the one large table, takes over 10
minutes!!! Reports 4.1 million records. The records consist of a
few fields and a blob each.

The database gets about 100,000 records (50,000 twice a day -morning
and around noon) from external data input (not users). The database
gets some 30,000 records a day deleted.

Validates take about 1 hour to do and report no errors. Just in case
ran a Mend in about 1 1/2 hour and still no errors.

However, running a Sweep, with no users attached, last night was
stopped after 5 hours and did not complete! Now, as I write this
note, I have had a Sweep of a copy of the database going for 3 hours
and still not completed.

What causes that slowness in Sweep? Could there be something damaged
that is not reported by Validate nor Mend?

Now doing a back up and restore takes too long. We do this overnight
and it is time for the users to need access to the database and
backup alone has not finished.

What do we have to do to speed up the back up and restore? The

Are we getting to some limits on FB? (not that we can see in Ms.
Borrie's book)

Would we benefit from going to the latest 1.5.x server?




08/03/2005 10:59 PM 4,730,314,752 My.fdb
08/04/2005 03:02 AM 876,576,768 My.gbk
08/04/2005 12:40 AM 1,486,667,776 Daily.FDB
08/04/2005 12:39 AM 409,600 My_sox.fdb
4 File(s) 7,093,968,896 bytes
3 Dir(s) 486,281,519,104 bytes free

Database "d:\data\My.fdb"

Database header page information:
Flags 0
Checksum 12345
Generation 9674
Page size 8192
ODS version 10.1
Oldest transaction 2069
Oldest active 9585
Oldest snapshot 9585
Next transaction 9667
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 16
Shadow count 0
Page buffers 16000
Next header page 0
Database dialect 3
Creation date Jul 1, 2005 8:04:36
Attributes force write

Variable header data:
Sweep interval: 20000