Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

OPTIMIZE TABLE like functionality for Firebird ?

Posted on 2007-11-29
4
Medium Priority
?
1,483 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 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: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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What we learned in Webroot's webinar on multi-vector protection.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

618 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