[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

OPTIMIZE TABLE like functionality for Firebird ?

Posted on 2007-11-29
4
Medium Priority
?
1,502 Views
Last Modified: 2013-12-09
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
Comment
Question by:jjthomas3
  • 2
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
Nick Upson earned 2000 total points
ID: 20375704
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
 
LVL 1

Author Comment

by:jjthomas3
ID: 20385480
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
 
LVL 19

Expert Comment

by:Nick Upson
ID: 20388694
if that's what it does then garbage collection is the equivalent but it's on a database basis
0
 
LVL 1

Author Comment

by:jjthomas3
ID: 20866715
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question