Bob Murdoch wrote:
I have a Firebird 1.5 classic dialect 1 database running on a 1GB RAM Windows 2003 server with dual Xeon HT-enabled processors. The system contains mirrored hard drives for the operating system, and the database resides on three drives set up as a hardware raid-5 array.
Since mid-September, the database has steadily grown from 29.6GB (restored size) to 33.5GB. During that same time frame, backups have taken between 1.25 hours and 3.5 hours, while restores have taken between 5 hours and 9 hours. The process starts at midnight every night, and there are no other users or processes running on this server during that time frame.
However, those times have not coincided with the growth of the database. From one day to the next, there could be a swing of more than an hour one way or the other, for either backup or restore.
I have just heard from someone else that they have a 25GB database with average backup times of 15 minutes (!) and restore times of 1.5 hours (!!).
Of course he is running on Linux and I on Windows, but can there be that much of a difference? Is there any way to speed up this process? I am already running the restore using the FB embedded rather than using the regular classic server, which increased restore times by about 25%. I need more though.
Ann W. Harrison answers:
The major variable in backup time is the amount and type of garbage collection. With the -g switch, your backups will be much faster, but your databases will not be cleaned up. If you plan to restore the backup and replace the database, always use the -g switch. There's no point in cleaning up your house before you burn it down.
The second issue is the type of garbage collection. Removing entries from indexes with long duplicate chains is expensive in Firebird versions before 2.0. Making the indexes more nearly unique can save backup time when doing garbage collection.
A major cost of restoring a database is recreating the indexes. That is done bu sorting each table once for each index, then building the index from the bottom up. That's a fast way to build indexes, but still an expensive operation. Especially if you use the default memory settings for sort in the configuration file. Try giving the sort much larger runs and larger total memory.