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?
