| Subject | Re: [firebird-support] Re: Strange behavior on very large table |
|---|---|
| Author | Mark Rotteveel |
| Post date | 2019-02-22T12:50:13Z |
On 22-2-2019 05:33, myles@... [firebird-support] wrote:
(assuming that is suitable for your business needs). Assuming you don't
disable sweep as part of the backup.
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
> Thanks Helen. Just a few clarifications, that might help whittle thisIt could possibly behave better if you do that before the backup
> 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.
(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 fromWhy are you regularly performing gfix -mend? That in itself is a pretty
> 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.
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