Solved

OPTIMIZE TABLE like functionality for Firebird ?

Posted on 2007-11-29
4
1,462 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
NickUpson earned 500 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:NickUpson
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

707 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