Issue Details (XML | Word | Printable)

Key: CORE-4509
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Lorenzo Iania
Votes: 0
Watchers: 1

If you were logged in you would be able to see more operations.
Firebird Core

The database restored by gbak has a substantial loss of performance

Created: 05/Aug/14 10:55 AM   Updated: 06/Aug/14 10:20 AM
Component/s: GBAK
Affects Version/s: 2.5.3
Fix Version/s: None

Environment: freebsd, windows and linux

 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 05/Aug/14 12:16 PM
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.

Lorenzo Iania added a comment - 06/Aug/14 08:03 AM - edited
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.

Lorenzo Iania added a comment - 06/Aug/14 08:31 AM - edited
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.