I have a client with a Firebird 1.5 Classic server that is in the process of being upgraded to a later version.  The database is quite complex and this upgrade will take some time.  However the database has been working well for the past 8 years, and just in the past few weeks we have seen something unusual.

The database server is runnnig on CentOS Linux, and has 16GB of RAM.  The disk capacity is at about 60%, so it has plenty of space to grow.  It has about 75 users, but even in peak times we see no more than about 3 active threads in the process logs on Linux.  I'm not seeing this being bound by CPU at all.  The system has sweep turned off, and is backed up once every 24 hours with no settings to stop sweep at the time of backup.

The issue is with one very large table that contains about 900,000 rows.  This table is used as a temporary storage of data that is loaded every 24 hours from a CSV file, via an external program.  The loading takes about an hour to run, but works reliably.  We are not seeing this process changing in terms of time when it is run.

What we are seeing is that for a period of about 3-4 hours after the morning data load is completed (about 4AM our time), any attempt to use that table seems to be triggering some very long loading or reindexing process.  This appears on doing certain queries that involve indexes, or to re-create an index.  It seems to show some sort of caching between RAM and disk going on.  I have set the sort size in firebird.conf from the default of 500mb to about 5GB to handle this, but that does not seem to have made a difference.

All other tables are working just fine.  It is only this one table and this problem only has appeared in the past 3 weeks or so.  Prior to that, it ran without issue.  This suggests to me that it has something to do with us reaching some critical volume (as the row count has increased at about 20% per year and continues to).

Has anyone seen this behavior before, and was able to remedy it?  Clearly this is an old and no longer supported server version, so we are migrating to FB 2.5 Classic however this will take time and I was hoping that there might be a solution we can implement as an interim measure.

Thanks in advance for any suggestions.

Myles