(Please Note: This is an amended version to the previous article on Sweep Interval. There was a syntax error in the DOS commands)
On occasions sites can suffer performance issues with users noting that sometimes Ostendo is slow to respond and then for no apparent reason it can resolve itself and speed up again. One likely cause of this is the automated Firebird process called 'Sweep'. This process runs in background and maintains what is known as 'garbage'. Whilst this process is running, it is quite possible that the database may run slow, and then when completed, it will return to an acceptable state.
This article describes what garbage is and how it is maintained in a Firebird database. We also provide some recommendations on how to eliminate the impact of the sweep process during everyday activities.
(NB: The following information is also available as Knowledge Base articles on the Partners Help Desk Portal - search for the keyword performance)
Overview: Garbage Collection: This is the ongoing cleaning of the database and is performed in the background around the clock. This constantly reorganises the memory space used by the database. If you don't clean up the database performance will slowly but surely degrade.
When performing a garbage collection, Firebird does nothing other than remove outdated data sets and index files, which results in a smaller database. Outdated data sets are stored by Firebird for the following reason: Firebird is a multi-generational databases. When a data set is altered, this alteration is stored in the database as a new copy. The old values remain in the database as a back version, which is the rollback protocol. If the transaction is rolled back after the update, the old value is ready to resume its function as the valid value. If the transaction is however committed, and not rolled back, this back version becomes superfluous. In databases with a lot of update operations this can result in a lot of garbage.
What is a Database Sweep? The sweep facility runs through the database and removes 'garbage'. This prevents the database from growing too big and helps reduce the time it takes to start a new transaction on the database. The potential downside of this process, is that it can affect everyday performance of the database whilst the Sweep process is running when users are accessing the database. A default sweep interval of 20000 records is defined for all databases initially. This means once 20000 garbage records have been stored, (on some databases this may occur frequently during the day) the database will automatically initiate the sweep routine to purge these unwanted records. This process can then cause the database performance at that moment to be adversely affected. This is potentially why a database may run slow sometimes and then suddenly perform well.
Amending the Database Sweep Interval Setting and Managing the Sweep outside of the automated process (Recommended):
Stop the sweep process entirely (by setting the Sweep Interval to Zero) and manage the Sweep process by manually initiating the Sweep at a time when users will not be affected.
Refer to this section describing Manually Running A Database Sweep
Important:
Irrespective of how frequently the sweep process is run, you must run a regular Firebird Backup and Restore which will not only remove the garbage but also maintain table Indexes, database error checking and re-compact the database size.
For more information on Sweep please refer to this article http://www.firebirdfaq.org/faq312/ Determining the Sweep Interval and Amending it: From Build 266 of the Ostendo Update 242 executable file, the sweep interval setting specific to the database you are logged onto is displayed from the Ostendo About screen. (Help -> About)
Modifying the Database Sweep Interval Setting:
You must have exclusive access to do this database. Ensure you stop any Ostendo API, Web or Queue Services before amending this setting
Run cmd (Run as Administrator)Define the variables USER and PASSWORD, it makes life easier, in that you do not have to type in the user name and password every time you issue a command. Substitute the Firebird 'bin' folder path appropriate to its location on your server. cd C:\Program Files\Firebird\Firebird_2_5\bin SET ISC_USER=SYSDBASET ISC_PASSWORD=masterkey Substitute the Firebird 'bin' folder path appropriate to its location on your server along with database path and name as necessary cd C:\Program Files\Firebird\Firebird_2_5\bin gfix -h 0 c:\Database\Ostendo.fdb Now check to see the Sweep Interval was change successfully by logging back onto Ostendo and re-checking Help->About Finally run the dbutiles.exe to perform the following: - Validation
- Backup
- Restore
Manually Running a Database Sweep
The default database sweep value is 20000. If this value has been amended to Zero, it will be necessary to empty out accumulated 'garbage'. This can be achieved by either performing a Firebird Validate / Backup and Restore of the database, or manually initiating a sweep. In the case of manually initiating a sweep it should be noted, that, despite the garbage being maintained, you should still be conducting a 'regular' Firebird Backup and Restore to maintain table indexes, database error checking and re-compacting the database size.
For more information on setting the Sweep Interval, please refer to: Modifying The Database Sweep Interval Setting
A manual sweep does not require exclusive database access. however this should only be conducted when the database is 'quiet', eg: overnight when the database is not being fully utilised.
Determining the Current Garbage Size (optional):
To determine the current garbage size run the gstat -h command from a Command Prompt. Substitute the Firebird 'bin' folder path appropriate to its location on your server along with database path and name as necessary
eg:
cd C:\Program Files\Firebird\Firebird_2_5\bin
SET ISC_USER=SYSDBA
SET ISC_PASSWORD=masterkey
gstat -h "H:\Ostendo\Ostendo DB\Demo.fdb"
This will produce various statistics for for this database. The difference between the Oldest Transaction and the Oldest Active Transaction will give you the approx garbage size being stored in the database.
Running a Manual Sweep:
From the Command Prompt run the gfix -sweep command against the database. Substitute the Firebird 'bin' folder path appropriate to its location on your server along with database path and name as necessary
eg:
cd C:\Program Files\Firebird\Firebird_2_5\bin
SET ISC_USER=SYSDBA
SET ISC_PASSWORD=masterkey
gfix -sweep "H:\Ostendo\Ostendo DB\Demo.fdb"
Automating The Manual Sweep Process:
The above DOS Commands relating to a manual sweep can be included in a Batch File which could be scheduled to run via Windows Task Scheduler on a nightly basis | 