I would be very suspicious of the config of the new VM.

Are there any non Firebird performance benchmark programs you can run on the new and old VMs for comparison?

Can you do a restore of the DB onto the old server and run you FB tests again for comparison? A restore will recalculate the selectivity of all the indexes, collects garbage etc. After a restore you should be comparing like for like.

I have seen inexplicable changes to query optimisation between database instances, but that was FB 1.5. Break up you SP into component queries and look at the PLAN generated for each one in an interactive SQL tool.

Such big performance differences are likely to be due to index selection or VM configuration. I’ve had many bad experiences with VM config so I’d look there first.

Another interesting comparison would be to build a test server on an ordinary PC and see how that compares to the VM.

 

Hi

I need some input to a problem I struggle with.

I have a Firebird 2.5 database.

The size is somewhat 220 Gb. 

This database has just been moved to a new databaseserver.

This new server is a virtual server with the fastest storage available to us (Where we host our servers, they call it storage T400).

 - Windows Server 2012R2 (both new and old server)

 - 32 Gb RAM (both new and old server)

 - C Drive with Windows on  (both new and old server)

 - E Drive with Firebird database on. Size of drive is 500GB. NEW Server is some SSD storage of some - Old was a Fusion I/O something external storage

 - F Drive is where the dump is made every night. Size 300GB. NEW Server is some SSD storage of some - Old was a Fusion I/O something external storage

 - Firebird 2.5.7 Classic server. Old server was running 2.5.2. Firebird.conf file from old server was copied to new server to ensure same settings

 - New restored database with 16K pagesize. Old had 8K page size. (Os partition has been formatted with 8K pagesize).

 - Usual connections: 150-200

 

The programs are the same, so the only changes is the server and its hardware.

Some comparisions:

OLD SERVER: 

 - Dump database: 3½ hours .

 - Restore database: 4½ hours

 

NEW SERVER

 - Dump database: 1 hour 12 minutes

 - Restore database: 2 hours 34 monutes

 

I have then made several different usually routines on both old and new server, and routines in our program are approx. at least 2 times faster. 

This new server has now been running for 3 days. 

I have only heard about 1 complaint so far. 

We have a routine, which makes new items in our database. 

Lets say I create 10 new items. This customer has 58 departments.

So there must be created 58 * 10 new records.

To this I have a stored procedure. This creates a record to an item to all departments. Like this:

CREATE OR ALTER PROCEDURE OPRET_VARERDETALJEDETAIL4 (

  PENGROSPRIS Numeric(18,2), 

  PVAREPLU_ID VarChar(30), 

  PFARVE_NAVN VarChar(30), 

  PLAENGDE_NAVN VarChar(30), 

  PSTOERRELSE_NAVN VarChar(30), 

  PV509INDEX VarChar(30), 

  PMINBEHOLDNING Numeric(18,2), 

  PGENBESTILLING Numeric(18,2), 

  PNORMALSTKANTAL Numeric(18,2), 

  PVALUTA_NAVN VarChar(10), 

  PPROVISION Numeric(18,2), 

  PMOMSSATS VarChar(25), 

  PVEJETKOSTPRISSTK Numeric(18,2), 

  PSALGSPRISSTK Numeric(18,2), 

  PENHEDSNOGLE Integer, 

  PLEVERID VarChar(30), 

  PVAREGRPID VarChar(30), 

  POPRETTELSESDATO Date, 

  PAFDELING_ID VarChar(30)) AS 

DECLARE VARIABLE AFDNR VARCHAR(15);             

Declare Variable lMomsSatsNavn VarChar(25); 

Declare variable lValuta Varchar(10); 

BEGIN

     For

      SELECT

        A.AFDELINGSNUMMER,

        STAMDATA_PRG_EXT.STDMOMS,

        STAMDATA_PRG_EXT.STDVALUTA

      FROM

        AFDELING A

        INNER JOIN STAMDATA_PRG_EXT ON STAMDATA_PRG_EXT.AFDELING_ID = A.AFDELINGSNUMMER

      WHERE

        A.AFDELINGSNUMMER<>:PAFDELING_ID

     INTO

       :AfdNr, :lMomsSatsNavn, :lValuta

     do  

     Begin 

       if (not exists (select v509Index from VAREFRVSTR_DETAIL where 

                                                       VarePlu_ID=:PVarePlu_ID and 

                                                       Afdeling_ID=:AfdNr and          

                                                       Farve_Navn=:PFarve_Navn and 

                                                       Stoerrelse_Navn=:PStoerrelse_Navn and 

                                                       Laengde_Navn=:PLaengde_Navn)) then 

       begin 

          Insert into VareFrvStr_Detail ( 

            VarePlu_ID, 

            Farve_Navn, 

            Laengde_Navn, 

            Stoerrelse_Navn, 

            V509Index, 

            MinBeholdning, 

            Genbestilling, 

            NormalStkAntal, 

            Valuta_Navn, 

            Provision, 

            MomsSats, 

            VejetKostPrisStk, 

            SalgsPrisStk, 

            EngrosPris, 

            EnhedsNogle, 

            OprettelsesDato, 

            LeverID, 

            VareGrpID, 

            Afdeling_ID) 

          Values ( 

            :PVarePlu_ID, 

            :PFarve_Navn, 

            :PLaengde_Navn, 

            :PStoerrelse_Navn, 

            :PV509Index, 

            :PMinBeholdning, 

            :PGenbestilling, 

            :PNormalStkAntal, 

            :lValuta, 

            :PProvision, 

            :lMomsSatsNavn, 

            :PVejetKostPrisStk, 

            :PSalgsPrisStk, 

            :PEngrosPris, 

            :PEnhedsNogle, 

            :POprettelsesDato, 

            :PLeverID, 

            :PVareGrpID, 

            :AfdNr); 

       end 

     end 

END ^^

Short:

It will itereate through the departments and insert a record per item.

On the old server this called took less than ½ seconds. 

On the new server, this is 3+ seconds. 

This means that creating 10 new items on the old server took 2-4 seconds. Now it takes 30+ seconds. 

If I do the same on a copy I have on a very slow server, it takes some 10 seconds. This is just my testserver, and this is expected.

When doing this item creation, there are a lot of the inserts and updates made, but this one is the only of them, which are significanly slower. 

Table AFDELING contains 58 records. Table STAMDATA_PRG_EXT contains 58 records.

Table VareFrvStr_Detail contains at least 57.500.000 records.

I have checked thegab betweens oldest active transaction and newest one. This is always between 0 and 50.000. This is a the server always is and has been for many years. 

I have a hard time finding a reason why this call suddenly takes a lot longer than before. 

I would have suspected this to be faster. 

What so I look into?