On 22-2-2019 05:33, my...@techsol.org [firebird-support] wrote: > Thanks Helen. Just a few clarifications, that might help whittle this > down a bit... > > >Given that this table is "temporary" storage, one supposes that you > are deleting rows from it regularly. Do you happen to be deleting > 900,000 rows each day before you load up the latest batch of 900,000? > > Yes, that is correct. Each morning it does a "kill & fill" on this > table. All 900,000 rows are deleted, then a new set of that data is > loaded from a CSV file via an external application. This happens about > 2 hours after the system has completed its backup procedures.
It could possibly behave better if you do that before the backup (assuming that is suitable for your business needs). Assuming you don't disable sweep as part of the backup. > >If you 9;re not doing any particular housework on it (restoring from > backup and/or resetting the indexes periodically), then it would be > normal to expect degrading performance until the next time that > housekeeping is done. > > For about the last 6 months we have put the database through a regular > gfix mend process once a week, following with a backup of the database > and then a restore. This doesn't seem to have changed the behavior, > however. Why are you regularly performing gfix -mend? That in itself is a pretty bad sign. Using -mend can be destructive. From the gfix documentation: """ The option required to fix a corrupted database is the gfix -m[end] command. However, it cannot fix all problems and may result in a loss of data. """ and """ gfix -m[end] database_name This causes the corruptions in data records to be ignored. While this sounds like a good thing, it is not. Subsequent database actions (such as taking a backup) will not include the corrupted records, leading to data loss. """ and """ Using the -mend option can lead to silent deletions of data because gfix doesn't care about internal database constraints like foreign keys etc, the -mend option simply says to gfix "go ahead and clean out anything you don't like". """ If you are doing anything regularly, it should be gfix -sweep (consider trying that after loading the data). Alternatively, consider dropping and recreating the table before populating. This will avoid any possible overhead associated with garbage collection. This is of course only a viable course of action if there are no dependencies on the table. -- Mark Rotteveel
