search

FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.23k stars 213 forks source link

shrink db via api [CORE2226] #2654

Open firebird-automations opened 15 years ago

firebird-automations commented 15 years ago

Submitted by: Miha Vrhovnik (mvrhov)

AFAIK the only way to remove unused pages from DB is using backup/restore functionally using external executable. This is really bad for embedded version. I think that for embedded version there should be API which would compact/reorder data in database and remove unused pages so DB file after that would be smaller.

firebird-automations commented 15 years ago

Commented by: @dyemanov

Backup/restore can be performed using the Services API as well.

firebird-automations commented 15 years ago

Commented by: Staffan Bergbom (stab)

I really agree that this is a MUST. For quite a few years we(Metria, Land Survey of Sweden) have been using FireBird for customers in Health Care and have constantly had problems with growing database. The service we provide must be up 24 hours a day and data is inserted, updated and deleted all the time. We are forced to shut the service down to be able to make a db-shrink. A decision is threrfore taken th switch to PostgreSQL that doesn't have this problem. A comparison on the net between FirBird and PostgreSQL mentioned exactly a constantly growing db as the major drawback for FireBird.

Would be glad to hear from you about your plans to fix this

Kindly Staffan Bergbom

firebird-automations commented 15 years ago

Commented by: @dyemanov

If your database is growing in the described conditions, then I'm afraid something is wrong with transaction management in your system. Firebird itself doesn't have such a problem, it just requires some discipline from client applications, i.e. not keeping snapshot transactions active for a long time and caring about the sweep.

firebird-automations commented 14 years ago

Commented by: @livius2

I suppose that you use some bad driver or like Dimitry say your transaction management is bad If you use e.g. dbExpress driver for Interbase with Firebird it start some internal transaction and never end it. This stop garbage collector and your database grow and grow - also speed is degraded becaouse of record versions

you can check long running transactions in FB2 using monitoring tables