I am attempting to debug some strange behavior on a large table that exhibits the same problems on both FB 1.5 Classic and FB 2.5 Classic.  This is on a CentOS 7 server, with a ton of RAM and disk, etc.

The database has about 150 tables in it.  One table is the culprit.  All other tables, stored procedures, etc. work flawlessly and fast.  But this one table, which is used to load about 1 million rows each day for processing is problematic.  The table is large in terms of the number of columns, yet only has about 6 indexes on it.  Since it is only used as a "kill & fill" table to get data into the database, before that data is then processed into its destination tables, I would consider it temporary.  But unfortunately the data provider is unable to give me the delta of what has changed each night, so I'm forced to delete all rows in this table, then load them from this file.

My code that loads the data commits the transactions after the DELETE statement, and then at each 5,000 rows loaded.  I have checked this by watching the load and using IBExpert to count records, so I can confirm that the commits are happening.

After the loading has completed, what then occurs is strange.  Any attempt to interact with that table (and only that table) forces some form of sweep or some delay that shows up as a process on the server running at near 100% CPU, for about 4-5 hours.  Then it calms down and access to the table is pretty much instantaneous.  I thought that this might be a sweep that is occurring after the load, so I set the database to not auto sweep.  I have force dropped the table, and rebuilt it.  I have done a backup with sweep and a restore to refresh the database.  Nothing seems to make any difference.

This is what database statistics are showing for the database:

Database header page information:

        Flags                   0

        Checksum              12345

        Generation              995313

        Page size               16384

        ODS version             10.1

        Oldest transaction      93188

        Oldest active           990357

        Oldest snapshot         990354

        Next transaction        995298

        Bumped transaction      1

        Sequence number         0

        Next attachment ID      0

        Implementation ID       19

        Shadow count            0

        Page buffers            0

        Next header page        0

        Database dialect        3

        Creation date           May 8, 2019 23:37:46

        Attributes              force write

    Variable header data:

        Sweep interval:         0

        *END*

This is what statistics show for that one table:

CLAIM_LOAD (276)

    Primary pointer page: 711, Index root page: 712

    Average record length: 1475.73, total records: 962662

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

    Data pages: 93152, data page slots: 116597, average fill: 94%

    Fill distribution:

         0 - 19% = 1

        20 - 39% = 0

        40 - 59% = 0

        60 - 79% = 0

        80 - 99% = 93151