Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

The database restored by gbak has a substantial loss of performance [CORE4509] #4828

Open
firebird-automations opened this issue Aug 5, 2014 · 4 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Lorenzo Iania (lorian60)

I submitted this question some time ago, but now the problem is very serious.
I was using in freebsd 8 and 9 firebird server 2.5.1. It was configured so that every night I used gbak to save the database and then gbak -r to restore it to remove all limbo transactions and garbage and to optimize the database. Recently I upgraded firebird to firebird25-server-2.5.2_5 that is the last available for freebsd. After this upgrade everything is not working as first. Some queries require 80 times the time than before.
So I have tried to repeat the same query on the database not restored. The behaviour was as before. So I tried on other machines in linux (ubuntu) and on windows but the result was always the same. So I have tried to transfer all the data from the restored database to the one created by the previous version and also in this case the behaviour is good. So I have tried to delete and remake all the indices on the restored database, run gfix -sweep, gfix -validate full, but the result is always the same. So I think that there is something wrong in gbak when it creates the database from scratch. I don't know if the problem is the same when I create a new database, but now I have to use the old database and, if I need to restore an old version, I have to transfer all the records from the restored version to the good database. Both the databases are in the format:
"ISQL Version: FB-V2.5.2.26540 Firebird 2.5
Server version:
Firebird/FreeBSD/amd64 (access method), version "FB-V2.5.2.26540 Firebird 2.5"
on disk structure version 11.2"
so the question in not related on the disk structure. Further the same thin happens on linux (ubuntu 3.13) an on windows 7 with Firebird 2.5.3.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

priority: Critical [ 2 ] => Major [ 3 ]

security: Developers [ 10012 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

I believe this should better be posted to firebird-support. Also, there's absolutely no need in backup/restore for the goals you mentioned. Anyway, I'd test the following combinations:

original v2.5.1 database: FB 2.5.1 vs FB 2.5.2
original v2.5.1 database with recalculated statistics for all indices: FB 2.5.1 vs FB 2.5.2
backup restored with v2.5.1: FB 2.5.1 vs FB 2.5.2
backup restored with v2.5.2: FB 2.5.1 vs FB 2.5.2

before making any conclusions. And it makes a lot of sense to compare plans for the queries that became slow.

@firebird-automations
Copy link
Collaborator Author

Commented by: Lorenzo Iania (lorian60)

Now I can confirm that the problem is on the initial structure of the database: these are the operations that I've done last night:

At first I have created, by the FB 2.5.2_5, a new database and I've copied all the tables, the data and indices, from the original database. The result is not working in the right way.
Then I have copied the file created by the previous version (2.5.1) and then I've removed all the data from it and finally I've transferred all the data from the same original database (broken). The result now is working in the right way.

The plans of the two queries are the same as also all the tables, all the indices and all the data: the only difference is in the initial structure of the database when it was created.

@firebird-automations
Copy link
Collaborator Author

Commented by: Lorenzo Iania (lorian60)

I confirm that the problem on my database has been solved by compiling the old version of FB (2.5.1) and using the gbak_static 2.5.1 instead of gbak from 2.5.2. This, however, is only a trick to get around the problem, however, I believe that the problem resides in the function of creating the database in the new versions.
So the problem is not only gbak but the overall function of FB database creation.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant