JRogerPanton
asked on
OPTIMIZE TABLE like functionality for Firebird ? how to clean up uncommited transactions
We have a database that continually grows and in the process, sufferers a performance hit once it gets a little big. We found that every 3 or 4 weeks there is a pressing need to stop the database, run GBAK to perform a backup/restore. This shrinks the database quit a bit and greatly improves our performance. Is there a way to manually perform the same functions as QBAK, through stored procedures or SQL calls to do the same job, but with out having to stop our database ? We've already verified that we're properly committing everything, and garbage collections are running, this is just not enough on its own.
One of our tables has a extremely high rate of record deletions, does firebird have an equivalent to Optimize table ?
If the case is you have 200,000 uncommited transactions how can you get these either to commit or how are you able to remove them, we have a fb 1.5.3 database that has doubled in size it looks as though this may be the problem.
One of our tables has a extremely high rate of record deletions, does firebird have an equivalent to Optimize table ?
If the case is you have 200,000 uncommited transactions how can you get these either to commit or how are you able to remove them, we have a fb 1.5.3 database that has doubled in size it looks as though this may be the problem.
ASKER
We backedup and restored the database would this have had the same effect as these.
I didnt make any test about that, I've used firebird in production but never had to do a manual sweep.
But taking out transactions from limbo yes, it makes a difference....and you dont have downtime
But taking out transactions from limbo yes, it makes a difference....and you dont have downtime
The real solution is to find why you have all those uncommited transactions and make code changes in the application so that they are either commited or rolledback.
ASKER
The problem is I really did want to avoid changing the code the system has been in operation a while and not exhibited this problem before.
That is the only way to solve it, you might be able to tell people to change how they use the application to avoid it, one or more people must have changed how they use it in order for this to appear. first problem is to locate where the problem is
I agree with Nick on this. This kind of thing doesn't appear unless long-running transactions aren't correctly committed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To commit or rollback
gfix -commit all <db>
gfix -rollback all <db>
To manually run garbage collector if required
gfix -sweep <db>
I hope that helps you