Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1494
  • Last Modified:

OPTIMIZE TABLE like functionality for Firebird ?

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 ?
0
jjthomas3
Asked:
jjthomas3
  • 2
  • 2
1 Solution
 
NickUpsonCommented:
firebird will reuse space from deleted rows once there are no processes that still consider them 'interesting'
a backup/restore will normally use less space than the original due to general compaction but a database should read a 'stead-state' where, except for additions to perhaps an audit table or new records, the size remains the same or grows slowly as more data is held.

Your description is a classic one of a database that has one or more uncommited transactions hanging around. At least a week after it's restore please post the output from gstat (just the header stuff for now)

please also post your backup command line as you may be able to add garbage-collection to it for some improvement.

Equivalent to Optimise table - what does that actuall do?
0
 
jjthomas3Author Commented:
NickUpson,

 Thank you for your response. I was, unfortunately, out sick... but will be able to post the requested GSTAT output and our backup command-line monday morning when I return to work.

As far as Optimize Table, I always assumed it cleared out "white" space or performed some level of clean up. Unfortunately I'm not too experienced with Database servers, but we've lost our DB Admin so I have to step up until a new one is found.
0
 
NickUpsonCommented:
if that's what it does then garbage collection is the equivalent but it's on a database basis
0
 
jjthomas3Author Commented:
Based off of NiclUpson's suggestions I went back to the programmers and the DBAs. We found that there was a bug in the Firebird ODBC Drivers that was causing some of our queries to not "Auto-commit". This was causing upwards of 200,000 transactions to remain interesting to the system. This in turn was causing garbage collection to be essentially pointless.

Thank you for you time
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now