Wie bekomme ich einen performanten Firebird?

[Union]

Zwischenbericht
Dank der Hilfe von Euch Allen und besonders Holger Klemt konnte ich das Problem jetzt lokalisieren. Es lag an der Write-Cache-Policy des Controllers. Diese war sehr konservativ eingestellt. Der in IBExpert eingebaute Benchmark meldete nach dem Aktivieren einer mehr auf Performance setzende Policy 120-fach höhere Performance!

Der Import der Buchungstabelle war nach 5:53 abgeschlossen, also ca. doppelt so schnell wie vorher.

Ich habe nun den nächsten Importlauf gestartet, diesmal mit deaktivierten Indizes. Es sieht auf den ersten Blick so aus als wäre das nochmals ca. 5-10 Mal schneller.

Als nächsten Test werde ich einmal das ISql direkt auf dem Server starten und sehen ob durch den Wegfall der TCP-Übertragung noch etwas herauskommt.

Im letzten Schritt werde ich durch mein Export-Tool mal ein Script erzeugen lassen, das die EXTERNAL TABLE Definitionen für die zu importierende Tabelle erzeugt und eine Importdatendatei mit festen Feldlängen passend dazu.

Ich weiss nur noch nicht was ich mit evtl. vorhandenen Memos machen soll.

@Sir Rufo: Ja, der Server ist leider auch ein DC. Der Cache wird schon ab Windows Server 2003 immer dann ausgeschaltet, wenn Windows auf der betreffenden Disk das AD hat und auf dem Controller keine BBU erkennen kann. Was dann passiert hängt in der Tat von der Qualität des Treibers ab. Ich habe daher beim Bootvorgang dskcache eingebunden um die Laufwerk-Richtlininen zu ändern. Wenn die Cache-Befehle nicht durchgereicht werden (dann gibt auch dskcache einen Fehler aus), der Treiber also eigentlich schlecht programmiert ist, dann ist alles in Ordnung.

[IBExpert]

Da merkt man ja, das mein Text durchaus einige Male gelesen wurde.

Lasst euch von den Hardwareheinis keinen vom Pferd erzählen, Ihr könnt tausende Euros für Pseudosicherheit ausgeben, die bei Datenbanksystemen so viele negative Einflüsse haben, das man das gar nicht glaubt. Meistens ist weniger mehr.

Falls Ihr schon mit Firebird arbeitet, dann gibt es noch einen anderen sehr einfach Test.

Erzeugt mit dem Befehl

Code:

"CREATE SHADOW 1 ''C:\db\db.shd'"

mal ein Shadow der Datenbank und messt die Zeit, die der dafür braucht. Das Shadow sollte auf der selben Platte liegern wie die DB. Nachdem das Shadow erzeugt wurde, könnt ihr mit

Code:

"DROP SHADOW 1"

das shadow wieder löschen. Achtung: das Shadow ist fast genau so groß wie die Orginal Datenbankdatei.

Gestern beim Kunden auf dem neuen Server aktuell gemessener Bestwert waren ca. 6 Sekunden pro GB, die 5,5GB große Datei wurde von Firebird 2.5 dabei page für page innerhalb von ca. 30 Sekunden kopiert. Mit einem Trick konnten wir den Wert sogar noch auf 20 Sekunden herunterbringen und das Shadow haben wir dann als automatisierte Datensicherung genommen, die ab sofort alle 30 oder 15 Minuten ausgeführt wird, obwohl beim Erstellen des Shadow aktive Verbindungen auf der Datenbank auch schreiben können, während das Shadow erstellt wird.

Falls euer Server also beim Shadow erzeugen die auf Festplatten und RAIDs üblichen 3-5MB pro Sekunde schafft, dann sollte Ihr die Eignung als Datenbakserver einfach mal hinterfragen. Meistens braucht man ja nur messbare Argmente, damit die Hardwareheinis mal aufhören mit deren Klugscheißereien und den Schweigefuchs zu machen: Öhrchen auf und Schnäuzchen zu.

[vagtler]

Zitat von Union:

[...] @Sir Rufo: Ja, der Server ist leider auch ein DC. [...]

Ein DC ist ein DC, kein Datenbankserver.

[Union]

Unabhängig davon ob es jetzt die "richtige" Maschine oder Server ist: Mir ging es darum FireBird genauso schnell oder schneller zu sehen als das bisher eingesetzte DB-System bei gleicher Hardware. Das ist gelungen.

8,364,632 records

Verbindung	Index	Dauer hh:mm
remote	aktiviert	05:53
remote	deaktiviert	02:05
lokal	deaktiviert	00:21

Den Test mit dem external file spare ich mir:

1. Wird die Datei riesengroß weil sie eine feste Feldlänge haben muss
2. Habe ich keine Idee wie ich dort Memos und NULL Werte verarbeiten kann

Vielen, vielen Dank nochmals an alle Helfer!

[jobo]

Spannende Infos von IBExpert!

Auch wenn ich auf einem Laptop mit einem kommerziellen Standard DB System auf ähnliche oder bessere Werte komme (jenachdem wie man's macht) als hier genannt (4 Mio records ca 6 Minuten oder 11 Minuten):

Zitat von Union:

8,364,632 records

Verbindung	Index	Dauer hh:mm
remote	aktiviert	05:53
remote	deaktiviert	02:05
lokal	deaktiviert	00:21

Es zeigt doch, wie sehr es sich lohnt, sich mit den Interna eines DB Systems auseinanderzusetzen!
Mich wundert immer wieder mal, wie egal das vielen hier scheinbar meist ist.

Mich würde für einen genaueren Vergleich noch der originale Tabellenaufbau interessieren und ob dort ein Primärschlüssel definiert ist. Oder ist "Index deaktiviert" gleich "nicht mal Primärschlüssel"?

[Union]

Untenstehend die DDL für die betreffende Tabelle. Die Indizes waren beim letzten Import komplett deaktiviert mit ALTER INDEX <name> INACTIVE. In der usprünglichen DB gibt es keine PK und keine RI. Grosse Integer werden durch DOUBLE dargestellt, da die ursprünglich verwendeten DB keine BIGINT bzw. INT64 unterstützen - nicht lachen, das System ist in den Ursprüngen knapp 30 Jahre alt.

Code:

CREATE TABLE BUCHUNG
(
 "ID" DOUBLE PRECISION,
 "VORG_NR" DOUBLE PRECISION,
 "LFD_BNR" DOUBLE PRECISION,
 "MAND_ID" DOUBLE PRECISION,
 "ART_ID" DOUBLE PRECISION,
 "PAL_NR" CHAR ( 10 ),
 "ZUG_NR" CHAR ( 20 ),
 "ZUG_POS" INTEGER,
 "ABG_NR" CHAR ( 20 ),
 "ABG_POS" INTEGER,
 "LIEFER_ID" DOUBLE PRECISION,
 "KD_ID" DOUBLE PRECISION,
 "BELEG_ID" DOUBLE PRECISION,
 "BUCH_DAT" DATE,
 "ZUG_DAT" TIMESTAMP,
 "MENGE" DOUBLE PRECISION,
 "PLATZ_ID" DOUBLE PRECISION,
 "DATUM" DATE,
 "ZEIT" CHAR ( 8 ),
 "VORGANG" CHAR ( 3 ),
 "AUTO_VORG" CHAR ( 3 ),
 "ART" CHAR ( 4 ),
 "USER" CHAR ( 8 ),
 "STATUS" CHAR ( 4 )
);
CREATE INDEX "BUCHUNG_ID" ON BUCHUNG (ID);
CREATE INDEX "BUCHUNG_MAND_ID" ON BUCHUNG (MAND_ID, ART_ID, DATUM, ZEIT);
CREATE INDEX "BUCHUNG_ART_ID" ON BUCHUNG (ART_ID, MAND_ID, DATUM, ZEIT);
CREATE INDEX "BUCHUNG_ZUG_NR" ON BUCHUNG (ZUG_NR, MAND_ID, ART_ID);
CREATE INDEX "VORG_NR" ON BUCHUNG (VORG_NR);
CREATE INDEX "BUCHUNG_PLATZ_ID" ON BUCHUNG (PLATZ_ID);
CREATE INDEX "VORGANG" ON BUCHUNG (VORGANG);
CREATE INDEX "BUCHUNG_STATUS" ON BUCHUNG (STATUS);

[IBExpert]

Abschliessend im Thread nur noch mal als Hinweis an alle, die viel
importieren und exportieren:

Der Weg über external files bringt bei Firebird erheblich mehr speed
als man denkt.

Die folgende Tabelle mit 20 Feldern, PK und 3 weiteren Indizes und
1.000.000 Datensätze braucht für die Übertragung von drinnen nach
draußen oder von draußen nach drinnen jeweils ca. 1 Minute (der server
hatte bei dem Test nur eine normale Festplatte eingebaut).

Ganz wichtig:

External files definieren geht immer, aber benutzen könnt Ihr die erst,
wenn Ihr in der Firebird.conf den Parameter ExternalFileAccess angepasst
habt und den Firebird Server noch mal neu startet.

Code:

ExternalFileAccess = Restrict C:\export

Bei diesem Beispiel könnt ihr dann im pfad c:\export dateien einlesen oder
auch von firebird erstellen lassen.

Das Feld crlf ist im external file nur definiert, damit man im Texteditor
auch zeilenvorschübe sehen würde, wenn man die denn dort öffnet. Bei allen
weiteren Typen am besten wegen Lesbarkeit auf char(m) umwandeln.

Die Datei sieht dann so aus (Die Boardsoftware schummelt da weitere crlf
rein, ist aber pro datensatz immer eine Zeile).

Code:

1                 JENNIFER                                          WOMMACK                                           MARTIN LUTHER KING JR AVE                         -                                                 PITTSFIELD                                        MO                                                14960             USA                                               3                 [e-mail]                        1(435)703-9855                                    4                 1389 4408 4192 3925                               6/2016                                            JENNIFER.WOMMACK1000001                           567373                                            36                45000             F
2                 MARIA                                             DOHERTY                                           NE NEFF ROAD                                      -                                                 ROCKVILLE                                         CT                                                67979             USA                                               5                 [e-mail]                           1(924)920-6861                                    1                 7739 7739 5985 5952                               12/2019                                           MARIA.DOHERTY1000002                              726542                                            31                79000             F
3                 RICKEY                                            HUFFMASTER                                        FIRST AVENUE EAST                                 -                                                 BERKELEY HEIGHTS                                  MN                                                12931             USA                                               11                [e-mail]                       1(659)169-7730                                    2                 4666 5413 7709 9617                               3/2016                                            RICKEY.HUFFMASTER1000003                          709595                                            31                41000             M
....

Es rechnet zeitlich sich sehr oft, wenn man Daten für einen Import bekommt, die mit
einem kleinen Delphi oder Lazarus Hilfsprogramm in ein passendes Fixed format zu
konvertieren und danach den Import in die Datenbank auf diesem Weg zu machen.

Beim Export kann man auf einfach csv Dateien erzeugen, in
dem man die Daten einfach als varchars trimt und mit trenner in
einen char(255) einträgt. Das füllt Firebird dann zwar bis zum Ende mit
Leerzeichen auf, ist aber für jedes csv Import normalerweise unkritisch
(char(2) für crlf nixht vergessen)

Direkter csv Import ohne externe Tools via external file ginge zwar auch,
ist aber relativ umständlich, in dem man das als Stored Procedure
auseinanderfrickelt.

Hier der Befehl, um Daten aus der internen Tabelle in die externe zu kopieren
[CODE]
insert into customer_ext_c (ID, FIRSTNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, REGION, EMAIL, PHONE,
CREDITCARDTYPE, CREDITCARD, CREDITCARDEXPIRATION, USERNAME, PASSWD, AGE, INCOME, GENDER,crlf)
select ID, FIRSTNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, REGION, EMAIL, PHONE, CREDITCARDTYPE,
CREDITCARD, CREDITCARDEXPIRATION, USERNAME, PASSWD, AGE, INCOME, GENDER,'
'
from CUSTOMER
[CODE]

Hier der Befehl, um Daten aus der externen Tabelle in die interne zu kopieren

Code:

insert into customer (ID, FIRSTNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, REGION, EMAIL, PHONE,
                      CREDITCARDTYPE, CREDITCARD, CREDITCARDEXPIRATION, USERNAME, PASSWD, AGE, INCOME, GENDER)
select ID, FIRSTNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, REGION, EMAIL, PHONE, CREDITCARDTYPE,
       CREDITCARD, CREDITCARDEXPIRATION, USERNAME, PASSWD, AGE, INCOME, GENDER
from customer_ext_c

Metadaten

Code:

/******************************************************************************/
/****              Generated by IBExpert 27.04.2014 20:58:34               ****/
/******************************************************************************/

/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/

CREATE TABLE CUSTOMER (
    ID                    NUMERIC(18,0) NOT NULL,
    FIRSTNAME             VARCHAR(50) NOT NULL,
    LASTNAME              VARCHAR(50) NOT NULL,
    ADDRESS1              VARCHAR(50) NOT NULL,
    ADDRESS2              VARCHAR(50),
    CITY                  VARCHAR(50) NOT NULL,
    STATE                 VARCHAR(50),
    ZIP                   INTEGER,
    COUNTRY               VARCHAR(50) NOT NULL,
    REGION                SMALLINT NOT NULL,
    EMAIL                 VARCHAR(50),
    PHONE                 VARCHAR(50),
    CREDITCARDTYPE        SMALLINT NOT NULL,
    CREDITCARD            VARCHAR(50) NOT NULL,
    CREDITCARDEXPIRATION  VARCHAR(50) NOT NULL,
    USERNAME              VARCHAR(50) NOT NULL,
    PASSWD                VARCHAR(50) NOT NULL,
    AGE                   SMALLINT,
    INCOME                NUMERIC(18,0),
    GENDER                VARCHAR(1)
);


CREATE TABLE CUSTOMER_EXT_C EXTERNAL 'C:\EXPORT\CUSTOMER.DAT' (
    ID                    CHAR(18),
    FIRSTNAME             CHAR(50),
    LASTNAME              CHAR(50),
    ADDRESS1              CHAR(50),
    ADDRESS2              CHAR(50),
    CITY                  CHAR(50),
    STATE                 CHAR(50),
    ZIP                   CHAR(18),
    COUNTRY               CHAR(50),
    REGION                CHAR(18),
    EMAIL                 CHAR(50),
    PHONE                 CHAR(50),
    CREDITCARDTYPE        CHAR(18),
    CREDITCARD            CHAR(50),
    CREDITCARDEXPIRATION  CHAR(50),
    USERNAME              CHAR(50),
    PASSWD                CHAR(50),
    AGE                   CHAR(18),
    INCOME                CHAR(18),
    GENDER                CHAR(1),
    CRLF                  CHAR(2)
);



/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE CUSTOMER ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX CUSTOMER_IDX1 ON CUSTOMER (FIRSTNAME);
CREATE DESCENDING INDEX CUSTOMER_IDX3 ON CUSTOMER (ID);
CREATE UNIQUE INDEX IX_CUSTOMER_USERNAME ON CUSTOMER (USERNAME);

[Union]

Acuh wieder sehr informativ, danke. Leider habe ich damit zwwei bereits geschilderte Probleme, nämlich NULL und BLOB SUB_TYPE TEXT korrekt importiert zu bekommen.

[IBExpert]

naja, nullwerte werde mit coalesce umgewandelt in Leerstrings. Falls im Ziel dann auch wieder nullwerte gebraucht werden, können die dort ja via trigger auch wieder von einem Spezialstring, z.B. 'NULL' wieder in einen Nullwert umgewandelt werden.

Blobs sind eine eigene Baustelle, da würde ich wenn es geht die blobdaten in einen varchar packen (geht ja bis 32765 Zeichen Länge, macht dann aber das external file sehr groß), dann mit substring(feldname from 1 for 32765) noch begrenzen.

Oder noch besser, aus dem original Import entfernen und später gesondert einspielen. Entweder baust du dir dafür ein eigenes Tools oder benutzt so was wie in IBExpert eingebaut http://ibexpert.net/ibe/index.php?n=...sIntoADatabase

[mensch72]

Tolle Ansätze auf aktuellem Stand... ist zwar noch nicht auf dem Level was Anwendungen in meinem Gebiet brauchen, aber als "gute" Referenzwerte zur Argumentation bei hyperschlauen Admins/IT-Beratern super zu verwenden.

1Mio Datensätze pro Minute ist für SQL schon nicht schlecht... Da ich pro Tabelle "sehr sehr viele" kleine Buchungsdatensätze(100..300Mio Records) in Simulationen schneller 1..2Mio Datensätze pro Sekunde incl. (Zeit)Index streamen/kopieren muss(das ist nochmal Faktor 100+ schneller!), geht das wohl weiter nur mit eigenen auf LowLevel programmierten binären File-Strukturen.


Wenn es einen "realistischen"/"bezahlbaren" SQL-Datenbankserver mit einer Transferleistung von 1Mio Records/Sekunde(oder besser gäbe, würde mich das interessieren... wäre für jegliche Vorschläge offen, egal ob hier oder als PM.