Wie bekomme ich einen performanten Firebird?

[Union]

Ich habe zum Test auf einem unserer Server Firebird als SuperClassic installiert. Um die Performance zu testen wollte ich aus einer anderen Datenbank Testdaten übertragen und dann einge Quälscripts laufen lassen.

Leider bin ich jetzt der Gequälte.

Nach der Anlage der Datenbank und Tabellen füge ich Testdaten per iSQL-Script hinzu. Die Laufzeit ist aber ziemlich unterirdisch. Für 100 Records werden ca. 3-5 Sekunden benötigt. Das ist meherere hundert Male langsamer als ich das von anderen DB-Servern kenne.

Was kann ich wo und wie drehen, damit die Performance besser wird? Systemauslastung ist bei unter 1%.

System:

Windows 2008 Server R2
2x Intel Xeon E6520 (zus. 16 Kerne)
24 GB Speicher

[mkinzler]

-Alle Inserts innerhalb einer Transaktion? Wie sieht das Skript aus (Insert, Insert or Update, Merge?)
-Werden mehrere Kerne verwendet? ( Parameter -m, AffinityMask)
-Aktive Trigger?

[Perlsau]

Ich mach das immer so:

1. Daten einer Firebird-Tabelle im IbExpert anzeigen lassen.
   
2. Links oben auf Tabelle/Exportiere Daten in Script
   
3. Felder und Dateiname auswählen und exportieren
   
4. Script in der Zieldatenbank laden und starten.

Schneller geht's nicht.

[mkinzler]

Zitat:

....Daten einer Firebird-Tabelle im IbExpert anzeigen lassen....

Es handelt sich wohl um Datn eines anderen DBMS.

[Union]

Das Script sieht so aus (mit 50000 inserts):

Code:

Insert into Bestand values(21654631, 23, '1918298', 23899, null, null, null, '20', '2020682276', 21649245, 747, 21649993, null, null, 'IT', '/', 'IT', 40.34, 'Ft', null, null, 147390, 2573092, null, null, null, '2008-06-12', '11:12:05', '2009-03-25', null, 6, 0, null, null);
Insert into Bestand values(22138465, 24, '2543234', 19510874, null, null, null, '0001', '7022371544', 22110364, 220, 22110386, null, null, 'CN', null, null, 0, null, null, null, 147390, 144627, null, null, null, '2009-03-25', '15:17:32', null, null, 2, 0.03696, null, null);
Insert into Bestand values(22156974, 23, '2577937', 64322, null, null, null, '20', '2020682334', 22128344, 399, 22128744, null, null, 'ES', '/', 'ES', 8.86, 'Ft', null, null, 69667, 105578, null, null, null, '2009-04-14', '15:59:51', null, null, 4, 0, null, null);
-- ... Alle 100 records ein commit:
commit;

Die Installation habe ich mit der Batchdatei aus dem Zipkit gemacht, ohne irgendwas zu ändern.

@perlsau: das insert script habe ich mit einem selbsterstellten tool aus einer Produktions-DB eines anderen DB-Servers generiert.

[mkinzler]

Gibt es aktive Trigger/Constraints?
Massenimport als externe Tabelle ist die schnellste Alternative

[Union]

Keine Trigger, keine Constraints, keine Relationen. Aber 8 Indizes:

Code:

CREATE INDEX "BESTAND_ID" ON BESTAND (ID);
CREATE INDEX "BESTAND_MAND_ID" ON BESTAND (MAND_ID, ART_ID);
CREATE INDEX "BESTAND_ART_ID" ON BESTAND (ART_ID, MAND_ID);
CREATE INDEX "BESTAND_ZUG_NR" ON BESTAND (ZUG_NR, MAND_ID, ART_ID);
CREATE INDEX "BESTAND_PAL_NR" ON BESTAND (MAND_ID, PAL_NR);
CREATE INDEX "BESTAND_FIFO" ON BESTAND (MAND_ID, ART_ID, ZUG_DAT, ZUG_ZEIT);
CREATE INDEX "BESTAND_PLATZ_ID" ON BESTAND (PLATZ_ID, ART_ID, ZUG_DAT);
CREATE INDEX "BESTAND_KUND_ID" ON BESTAND (KUNDE_ID);

[HHennig]

Schalte mal alle nicht absolut benötigten (alles ausser Primary Indices) aus. Massenimport und viele Indices ist ganz schlecht - bei jedem DBMS.

Edit: Nach beendetem Import dann alle vorher deaktivierten Indices neu aufbauen lassen.

[mkinzler]

Diese würde ich temporär deaktivieren

SQL-Code:

alter index <Indexname> inactive;

und am Ende neu berechnen lassen.

SQL-Code:

SET statistics INDEX <Indexname>;

[Union]

Klar dass ein Indexupdate Zeit braucht. Aber ein Durchsatz von nur 5 Records/s ist doch etwas fragwürdig. In der Zeit schreibe ich normalerweise ein komplette SAP-SHPORD mit mehreren 100 records. Und Massenupdates sind auch in der Produktion an der Tagesordnung, wenn Material Master Updates laufen. Da liegt die Geschwindigkeit bei > 1000 IDoc / min. inklusive Analyse.

[HHennig]

Zitat:

Zitat von mkinzler (Beitrag 1256808)

Diese würde ich temporär deaktivieren

SQL-Code:

alter index <Indexname> inactive;

und am Ende neu berechnen lassen.

SQL-Code:

SET statistics INDEX <Indexname>;

Ist richtig, um es aber explizit darzustellen: nach

SQL-Code:

alter index <Indexname> inactive;

und Import zunächst

SQL-Code:

alter index <Indexname> active;

und erst dann

SQL-Code:

SET statistics INDEX <Indexname>;

[mkinzler]

Stimmt. Davon bin ich implizit ausgegangen ohne es explizit zu schreiben.

[Perlsau]

Zitat:

Zitat von Union (Beitrag 1256800)

@perlsau: das insert script habe ich mit einem selbsterstellten tool aus einer Produktions-DB eines anderen DB-Servers generiert.

Firebird generiert nur alle 1000 Datensätze ein COMMIT WORK;

Ich würde zusätzlich zu den anderen Tipps auch danach schauen, ob man in deinem Tool einstellen kann, wie häufig ein Commit ausgeführt wird. Keine Ahnung, wieviele Datensätze Firebird ohne Commit speichern kann, aber ich denke, es sind mehr als 1000.

[mkinzler]

Zitat:

Keine Ahnung, wieviele Datensätze Firebird ohne Commit speichern kann, aber ich denke, es sind mehr als 1000.

Da dürfte es keine Beschränkung geben.

Wie führst Du das Skript aus? Eventuell funkrt Dir da doch noch ein AutoCommit dazwischen.

[Union]

Es scheint an den "mehrfelder" Indizes zu liegen. Ich habe jetzt (nach 2:45h für 50000 Records) den Import für die nächste Test-Tabelle gestartet. Für diese sind nur einfache Indizes definiert. Dort hat er den Import von 110000 Sätzen nach 6 min. Auch noch nicht berauschend, aber es geht.

Die scripts führe ich mit

Code:

input

in der iSQL-Kommandozeile aus.

gstat sagt folgendes:
Database header page information:

Zitat:

Flags 0
Checksum 12345
Generation 5086
Page size 4096
ODS version 11.2
Oldest transaction 4989
Oldest active 4990
Oldest snapshot 4990
Next transaction 4992
Bumped transaction 1
Sequence number 0
Next attachment ID 92
Implementation ID 26
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Apr 24, 2014 11:11:12
Attributes force write

[Perlsau]

Zitat:

Zitat von mkinzler (Beitrag 1256814)

Wie führst Du das Skript aus? Eventuell funkrt Dir da doch noch ein AutoCommit dazwischen.

Wie oben angegeben: Im Scripteditor (Ctrl-F12) laden und ausführen oder gleich von Platte ausführen, was etwas langsamer ist, aber bei riesigen Scripts unumgänglich, wenn man nicht genug Arbeitsspeicher hat. Keinesfalls im SQL-Editor laden, das ist viel langsamer. Von irgendwelchen AutoCommits weiß ich nichts.

Wenn ich wüßte, wie man via Server Manager ein Insert-Script einer MSSQL-Tabelle generiert, würde ich mal testweise meine riesige 900000 DS große Ortsdatenbank exportieren und in Firebird einlesen, um zu sehen, wie lange das dauert.

[Union]

Zitat:

Zitat von Perlsau (Beitrag 1256816)

Wenn ich wüßte, wie man via Server Manager ein Insert-Script einer MSSQL-Tabelle generiert, würde ich mal testweise meine riesige 900000 DS große Ortsdatenbank exportieren und in Firebird einlesen, um zu sehen, wie lange das dauert.

Siehe Bild.

[Perlsau]

Zitat:

Zitat von Union (Beitrag 1256817)

Zitat:

Zitat von Perlsau (Beitrag 1256816)

Wenn ich wüßte, wie man via Server Manager ein Insert-Script einer MSSQL-Tabelle generiert, würde ich mal testweise meine riesige 900000 DS große Ortsdatenbank exportieren und in Firebird einlesen, um zu sehen, wie lange das dauert.

Siehe Bild.

Das kenne ich selbstverständlich bereits, aber das erzeugt mir kein Insert-Script aller 900000 DS, sondern lediglich das:

Code:

USE [GeoNames]
GO

INSERT INTO [dbo].[Plaetze]
           ([ISO_Code]
           ,[Postal_Code]
           ,[Place_Name]
           ,[State]
           ,[State_Code]
           ,[Provinz]
           ,[Provinz_Code]
           ,[Kommune]
           ,[Kommune_Code]
           ,[Latitude]
           ,[Longitude]
           ,[Genau])
     VALUES
           (<ISO_Code, char(2),>
           ,<Postal_Code, nvarchar(50),>
           ,<Place_Name, nvarchar(180),>
           ,<State, nvarchar(100),>
           ,<State_Code, nvarchar(50),>
           ,<Provinz, nvarchar(100),>
           ,<Provinz_Code, nvarchar(50),>
           ,<Kommune, nvarchar(100),>
           ,<Kommune_Code, nvarchar(50),>
           ,<Latitude, numeric(7,4),>
           ,<Longitude, numeric(7,4),>
           ,<Genau, tinyint,>)
GO

Hab nur gerade keine Lust, ein Programm zu schreiben, das mir das Insert-Script generiert, ich schau mir nämlich nebenher einen Film an und gehe danach zu Bett, weil ich eigentlich schon saumäßig müde bin. Für das Anlegen einer Tabelle in FB würd's aber grad noch reichen :)

[Dejan Vu]

Zitat:

Zitat von Perlsau (Beitrag 1256816)

Wenn ich wüßte, wie man via Server Manager ein Insert-Script einer MSSQL-Tabelle generiert, würde ich mal testweise meine riesige 900000 DS große Ortsdatenbank exportieren und in Firebird einlesen, um zu sehen, wie lange das dauert.

Gar nicht. Das geht mit dem Import/Export-Wizzard und dann wird das per ODBC/OLE (je nachdem, was man auswählt) mehr oder minder performant (meist mehr) durchgezogen.

[Perlsau]

Genau das hatte ich befürchtet: Da hat man nun ein gewaltig aufgeblähtes Werkzeug und kann nicht mal Insert-Scripts erzeugen. Okay, vielleicht mach ich das morgen mal mit einer Stringliste. Bis dann ...

[Union]

Es geht doch. Dazu im Kontextmenü der Datenbank Tasks->Scripts generieren auswählen und im "Erweitert" Dialog auch auswählen, dass man Daten exportieren möchte. Nur wass Du mit TSQL in FB anfangen willst.... Lieber die Variante mit der Stringlist (wenn die bei 1 Mio nicht überläuft).

Hier mal als Vorlage mein primitivst-QD:

Delphi-Quellcode:

procedure TForm1.ExtractData(ATableName: string);
   function QuoteValue(AValue : string) : string;
   begin
      if pos(#33, AValue) > 0 then
         result := StringReplace(AValue, #33, #33#33, [rfReplaceAll])
      else
         result := AValue;
   end;

   function BoolStr(AField : TField) : string;
   begin
      if AField.AsBoolean then
         result := 'T'
      else
         result := 'F';
   end;
var
   i : integer;
   LCount : integer;
   LRecords : integer;
   LLine : string;
   LValue : string;
   sl : TStringList;
begin
   AdsQuery1.SQL.Text := 'Select * from '+ATableName;
   sl := TStringList.Create;
   try
      AdsQuery1.Active := True;
      LRecords := AdsQuery1.RecordCount;
      ProgressBar1.Position := 0;
      ProgressBar1.Step := 1;
      ProgressBar1.Max := LRecords;
      sl.Capacity := LRecords;
      DecimalSeparator := '.';

      LCount := AdsQuery1.FieldCount;
      while not AdsQuery1.Eof do
      begin
         LLine := 'Insert into '+ATableName+' values(';
         for i := 0 to LCount-1 do
         begin
            if AdsQuery1.Fields[i].IsNull then
               LLine := LLine + 'null'
            else
            begin
               case AdsQuery1.Fields[i].DataType of
               ftDate : LLine := LLine + QuotedStr(FormatDateTime('yyyy-mm-dd', AdsQuery1.Fields[i].AsDateTime));
               ftDateTime : LLine := LLine + QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', AdsQuery1.Fields[i].AsDateTime));
               ftTimeStamp : LLine := LLine + QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz', AdsQuery1.Fields[i].AsDateTime));
               ftBoolean : LLine := LLine + QuotedStr(BoolStr(AdsQuery1.Fields[i]));
               ftString :
               begin
                  LValue := QuoteValue(AdsQuery1.Fields[i].AsString);
                  LLine := LLine + QuotedStr(LValue);
               end;
               else
                  LLine := LLine + AdsQuery1.Fields[i].AsString;
               end;
            end;
            LLine := LLine + ', ';
         end;
         LLine := copy(LLine, 1, length(LLine)-2) + ');';
         sl.Add(LLine);
         AdsQuery1.Next;
         if (sl.Count mod 101 = 0) or AdsQuery1.Eof then
            sl.add('commit;');
         ProgressBar1.StepIt;
      end;
      sl.SaveToFile(ATableName+'.sql');
   finally
      sl.Free;
      AdsQuery1.Active := False;
   end;
end;

[Dejan Vu]

Oh. Nett. Danke.

[Perlsau]

Zitat:

Zitat von Union (Beitrag 1256824)

Es geht doch. Dazu im Kontextmenü der Datenbank Tasks->Scripts generieren auswählen und im "Erweitert" Dialog auch auswählen, dass man Daten exportieren möchte. Nur wass Du mit TSQL in FB anfangen willst....

Stimmt, die SQL-Dialekte sind nicht kompatibel :? Umgekehrt geht's aber schon ... glaube ich ... ungetestet :oops:

Tatsächlich hatte ich jedoch auch eine FB-Version dieser Tabelle (als View), deren Daten ich exportieren konnte. Dabei wurden 923168 Datensätze mit dieser Struktur exportiert:

Code:

    P_IDX      BIGINT NOT NULL,
    P_LAND     VARCHAR(100),
    P_PLZ      VARCHAR(50),
    P_ORT      VARCHAR(180),
    P_STATE    VARCHAR(100),
    P_PROVINZ  VARCHAR(100),
    P_KOMMUNE  VARCHAR(100),
    P_LAT      NUMERIC(7,4),
    P_LON      NUMERIC(7,4),
    P_GENAU    SMALLINT

Nun hab ich mir in FB eine ebensolche Tabelle in einer Testdatenbank angelegt, wobei P_IDX auf AutoInc steht und daher automatisch erzeugt wird. Selbstverständlich hab ich zuvor im Export P_IDX nicht ausgewählt. Der Import via Script-Editor (von Datei ausführen) dauerte knapp 7 Minuten (6:55), das war eine knapp 206 MB große SQL-Datei. Berechnungen, wie lange ein DS benötigte, könnt ihr sicher selbst anstellen.

[mkinzler]

Interessant wäre es jetzt, wie lange ein Import der selben Daten als EXTERNAL TABLE dauern würde:
Anlage externe Tabelle

SQL-Code:

CREATE TABLE _IMPORT EXTERNAL FILE '<Pfad zur externen Datei>' (
    P_IDX     BIGINT NOT NULL,
    P_LAND    CHAR(100),
    P_PLZ     CHAR(50),
    P_ORT     CHAR(180),
    P_STATE   CHAR(100),
    P_PROVINZ CHAR(100),
    P_KOMMUNE CHAR(100),
    P_LAT     NUMERIC(7,4),
    P_LON     NUMERIC(7,4),
    P_GENAU   SMALLINT,
    CRLF CHAR(2)
);
COMMIT;

Testexport in externe Datei:

SQL-Code:

INSERT INTO _IMPORT
    SELECT <Felder vorhande Tabelle>, ascii_char(13) || ascii_char(10) FROM <vorhandene Tabelle>;

Import:

SQL-Code:

INSERT INTO <geleerte Tabelle/neue Tabelle im identischer Struktur>
  SELECT <Felder ohne CRLF> from _IMPORT;

[Union]

So, nachdem ich die Daten importiert bekam, habe ich einen Test gemacht. Die Lesegeschwindigkeit ist wirklich gut. Aber das Schreiben. Ich habe es jetzt mal mit IBExpert probiert. Da ist die Geschwindigkeit genauso schlecht bei Insert.

Bei jedem Commit hängt er eine ganze Weile - es ändert auch nichts wenn ich das ganze ohne Indizes mache. Das eigentliche Senden der Datensätze an den Server geht rasend schnell. Was muß ich denn nun wo drehen damit das besser wird?

@perlsau: Danke für Deinen Test. Wie ist denn Deine Konfiguration?

edit:

Ich habe das Performanceproblem nun gelöst indem ich

Code:

gfix -write async

auf die Datenbank losgelassen habe. Daraufhin wurden die 50000 Testdatensätze in 50 Sekunden geschrieben.

Wie stelle ich das beim Erzeugen der Datenbank ein oder als Standard im Dienst? Und in der Doku wird davor gewarnt, dass es sein kann dass die Daten dann nie geschrieben werden und es Verluste bei einem Serverabstirz geben kann.

[Perlsau]

Hab gleich einen Arzttermin und daher keine Zeit mehr. DB und SQL-Datei kann man sich dort herunterladen.

[RWarnecke]

Zitat:

Zitat von Union (Beitrag 1256883)

Bei jedem Commit hängt er eine ganze Weile - es ändert auch nichts wenn ich das ganze ohne Indizes mache. Das eigentliche Senden der Datensätze an den Server geht rasend schnell. Was muß ich denn nun wo drehen damit das besser wird?

Suche mal nach dem Forum-Mitglied IBExpert, der hat hier im Forum schon des öfteren was zu Firebird und Performance geschrieben und in seinen Beträgen auch einige Links zu anderen Seiten gesetzt, wo es auch über das Thema Performance und Firebird ging.

[Union]

Das mit dem async ist leider auch keine allgemeingültige Lösung. Wahrscheinlich fehlen irgendwelche Buffer. Ich importiere nun gerade eine Tabelle mit > 8 Mio. records und ab ca. 300000 wird es wieder ekelhaft langsam beim Commit.

[tsteinmaurer]

Welche Art von Commit machst du?

[tsteinmaurer]

Verwendest du auch die üblichen Praktiken wie Prepared Statements etc.?

[Union]

Nichts von alledem. Ich führe nur ein Script aus, das aus 8 Mio einzelnen Inserts besteht und nach jeweils 100 Inserts ein commit. Leider wird von FB ja (soweit ich weiss) die Syntax von meheren Value-Gruppen nicht untertstützt (Values (Values-1) [, (Values-n)]).


Es sind auch nicht sonderlich große Datensätze:

Code:

Insert into buchung values(147393, null, null, 23, 14299, '1', '35842011', 0, null, null, null, 0, null, '2005-05-02', null, 21, 145646, '2005-05-02', '17:46:55', 'ZUG', null, null, 'BWirth', null);
Insert into buchung values(147396, null, null, 23, 14302, '2', '35842011', 1, null, null, null, 0, null, '2005-05-02', null, 21, 145647, '2005-05-02', '18:03:21', 'ZUG', null, null, 'BWirth', null);
Insert into buchung values(147399, null, null, 23, 14302, '3', '35842011', 1, null, null, null, 0, null, '2005-05-02', null, 21, 145648, '2005-05-02', '18:03:42', 'ZUG', null, null, 'BWirth', null);

Momentan habe ich über gfix async und buffers 8000 eingestellt. Damit komme ich auf ca. 1 Mio records / h = ~ 300/s. Und je mehr es werden, desto länger dauert das commit, wobei dier Performance ab ca. 300000-500000 Sätzen schlagartig in den Keller geht. Die Übertragung der Daten ist aber schnell.

[Perlsau]

Zitat:

Zitat von Union (Beitrag 1256900)

... ab ca. 300000 wird es wieder ekelhaft langsam beim Commit.

Nach je 100 Datensätzen ein Commit? Probier's doch mal damit, nur alle 5000 DS ein Commit zu machen. In meinem Beispiel scheint das Insert mit vollkommen gleichmäßiger Geschwindigkeit zu verlaufen. Soweit ich das erkennen kann, ist da nach 1000 DS jeweils ein Commit Work; eingebaut. Die Geschwindigkeit lag bei mir bei 0,45 Sekunden für 1000 Datensätze, und zwar auf einem Win7/64 ohne SSD, Double Core Intel à 3 GHz und mit 4 GB Arbeitsspeicher. FB-Server:

Server Version: WI-V2.5.2.26540 Firebird 2.5
Server Implementation: Firebird/x86-64/Windows NT
Service Version: 2

Lade dir das Beispiel doch mal runter und probier's selber aus.

[Union]

Es ist auch nicht jedes 100er commit so langsam, nur ca. alle 4000 records.

[tsteinmaurer]

Ohne einem parametrisierten INSERT oder über den bereits erwähnten Weg via EXTERNAL TABLE wirst du für Batch-Inserts nie eine akzeptable Performanz erreichen.

Nochmal zu meiner Frage: Welche Art von Commit verwendest du? :-D

[Union]

K.A. ich habe ei fach in jeder 100. Zeile des scripts ein

Code:

commit;

[Perlsau]

Zitat:

Zitat von Union (Beitrag 1256921)

K.A. ich habe ei fach in jeder 100. Zeile des scripts ein

Code:

commit;

Wie langsam oder schnell läuft denn mein Beispiel auf deiner Kiste, wenn du das im Script-Editor von IbExpert ausführst?

Beispiel-Insert-Zeile:

Code:

INSERT INTO V_PLAETZE (P_LAND, P_PLZ, P_ORT, P_STATE, P_PROVINZ, P_KOMMUNE, P_LAT, P_LON, P_GENAU)
               VALUES ('Mexiko', '99312', 'El Vergel', 'Zacatecas', 'Jerez', 'Jerez (Jerez De Garcia Salinas)', 22.5, -103, 4);

[Union]

Ich habe mir das heruntergeladen, danke schon einmal. Ausführen kann ich das aber erst heute Abend, da ich momentan noch den Buchungsimport am Laufen habe - der wird noch ca. 6h dauern, ist nach 2h gerade bei 2 Mio. Dein Beispielbestand hat allerdings auch nur 900k records. Aber es wird reichen zum Testen, denn der Einbruch erfolgt ja schon nach wenigen 100k.

[jsheyer]

Hallo,

was mich wundert, wenn die DB im async Mode geht es erst einmal erheblich schneller, das es erst einmal schneller ist ist ja in Ordnung, trotzdem habe ich die Vermutung das der FB Server nicht schnell genug auf der Platte schreiben kann.
Ich hatte so einen Fall bei einem Kunden, super schnelle Platten, aber ab einer gewissen Menge an Daten ging die Plattenperfomance im Keller, konnte man auch mit einfachen Kopieren von Dateien nachstellen.
Auch schon Antiviren Software, etc geprüft? Das die evtl. Irgendetwas ausbremsen?

Gruß
Jörg

[Union]

Auf dem Server läuft keine. Wenn ich auf der WS ausschalte, gibt es keinerlei Unterscheid - hatte ich bereits probiert.

[hstreicher]

ein bisschen mehr zur Hardware wäre nützlich

Raid ? wenn ja was für eines
ggf Stripset size ?
Ram Buffer auf dem Controller ? Größe ?

? ? ?