On 29-5-2019 16:19, myles@... [firebird-support] wrote:

> 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.

[..]

> 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.

To me this sounds like the cooperative garbage collection kicks in when
you select from the table. You may want to consider dropping the table
entirely and recreating it. Alternatively, switch to SuperServer and set
its garbage collection policy to background (I believe this was
introduced in Firebird 2.0 or 2.1, so won't help with Firebird 1.5).

Mark
--
Mark Rotteveel