| Subject | Re: [firebird-support] Re: Database was emptied no data in the tables - how this can happen ? |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2010-06-28T16:21:15Z |
infoneto wrote:
improve after a backup/restore. As Alan McDonald said, one reason
is bad transaction control resulting in long transactions and a
build-up of old record versions. If that's the problem, running
just a backup (no restore) will help - especially if all user
connections are closed before you start the backup. But a better
solution is to find and fix long-running transactions - transactions
that are active for hours or across thousands of other transactions.
Gstat will tell you whether your database has lots of old record
versions.
Another possible source of problems is badly set selectivity on
indexes. Firebird uses a stored selectivity value when choosing
which indexes to use in a query. Index selectivity is set when
the index is created or by using the SET SELECTIVITY command.
Gbak creates indexes after storing data, so the indexes in a newly
restored database are accurate. If you create an empty database
with indexes, the selectivities will be bad until you reset them
either with the command or by deactivating and reactivating the
indexes or by backing up and restoring the database.
Another thing gbak does is store records from each table on
contiguous pages - in short, it defragments the database. That
might improve performance slightly, but as others have run
big databases for months without a backup/restore, I doubt
that the improvement would be significant.
I would try doing a nightly backup (not using -g) because it's
a good idea and will releases old record versions. After the
backup reset the selectivity on all indexes. Those two steps
should keep performance good.
Good luck,
Ann
>> Why do youThere are several reasons why databases get slower over time and
>> backup and restore every night? Frequent backups are good, but you
>> should not need to restore them nearly that often.
>>
>
> Ann we backup & restore because in the past the database stated to become slow, and a backup - restore actions seems to solve it. so we started to backup and restoring as well, this seems to keep the database faster and smaller.
>
> Is there another way ?
improve after a backup/restore. As Alan McDonald said, one reason
is bad transaction control resulting in long transactions and a
build-up of old record versions. If that's the problem, running
just a backup (no restore) will help - especially if all user
connections are closed before you start the backup. But a better
solution is to find and fix long-running transactions - transactions
that are active for hours or across thousands of other transactions.
Gstat will tell you whether your database has lots of old record
versions.
Another possible source of problems is badly set selectivity on
indexes. Firebird uses a stored selectivity value when choosing
which indexes to use in a query. Index selectivity is set when
the index is created or by using the SET SELECTIVITY command.
Gbak creates indexes after storing data, so the indexes in a newly
restored database are accurate. If you create an empty database
with indexes, the selectivities will be bad until you reset them
either with the command or by deactivating and reactivating the
indexes or by backing up and restoring the database.
Another thing gbak does is store records from each table on
contiguous pages - in short, it defragments the database. That
might improve performance slightly, but as others have run
big databases for months without a backup/restore, I doubt
that the improvement would be significant.
I would try doing a nightly backup (not using -g) because it's
a good idea and will releases old record versions. After the
backup reset the selectivity on all indexes. Those two steps
should keep performance good.
Good luck,
Ann