Bulk Load

Themen rund um den praktischen Einsatz von Firebird. Fragen zu SQL, Performance, Datenbankstrukturen, etc.

Moderator: thorben.braun

bfuerchau
Beiträge: 485
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Ich habe nun mal deinen Bench auf einer 2.5 FB ausprobiert:
GTT 24783 Sätze/Sek.
Table 25056 Sätze/Sek.

Nach dem nun der .Net-Treiber mit der Satzlänge von 64K nicht zurechtkommt musste ich die Domainlänge mal auf 400 kürzen und die Anzahl Felder reduzieren..
Ob es am Treiber liegt oder an der Kommunilkation (isc-Interface) weiß ich nicht. Ich bekam halt eine Limit exceed-Meldung.
Dadurch kommt der Bench-Insert nun auf
129701 Sätze/Sek.
Aber da ja nur konstante Werte verwendet werden, entspricht dies halt nicht einem realen Umfeld.
Ich habe daher mal eine kleine Änderung eingebaut:

insert into t_ram(id, f1) values (:i, 'A');

Somit kommt der Bench schon nur noch 21088 Sätze/Sekunde. Was sich halt durch die Parameterversorgung des Inserts erklärt.

Der Anwendungs-Insert, Kopie einer View (aus T_RAM auf die 2 Felder) kommt nun auf 4.900 Zeilen/Sekunde Singelinsert.
Auf Grund der Satzlänge und der Limits bei der Prozedur, kann ich nur 1 Insert gleichzeitig per Execute Block erstellen.

Ein CSV-Export und wiederum Import halte ich für technisch eben Unsinn, da das Bereitstellen der CSV genauso lange dauert wie das direkte Kopieren.

Was die anderen Angaben angeht:
Anzahl Spalten 92, Bytelänge 894, 4 Inserts/Block, 2200 Inserts/Sekunde
Anzahl Spalten 73, Bytelänge 778, 6 Inserts/Block, 3400 Inserts/Sekunde
Anzahl Spalten 41, Bytelänge 577, 10 Inserts/Block, 5750 Inserts/Sekunde

Dadurch lässt sich leicht eine Linearität ermitteln.
Der .Net-Treiber unterstützt nur noch TCP, aber via Localhost ist das Netz immer noch schneller als die Datenbank.
vr2
Beiträge: 214
Registriert: Fr 13. Apr 2018, 00:13

GTT 24783 Sätze/Sek.
Table 25056 Sätze/Sek.
Das ist schon mal die HW-Hausnummer, eine CPU im oberen Mittelfeld, was single-thread-rating angeht. So sind die anderen Messergebnisse besser einzuordnen. Schlechte sind so bei 11K/sec, gute bei 33K/sec.
Ein CSV-Export und wiederum Import halte ich für technisch eben Unsinn, da das Bereitstellen der CSV genauso lange dauert wie das direkte Kopieren.
Nein, es ging darum, dass externe Daten als csv (allgemeinstes tabellarisches Format) vorliegen und in die DB müssen. Es sollte hier nicht aus der DB csv erzeugt und dann wieder importiert werden.
Anzahl Spalten 41, Bytelänge 577, 10 Inserts/Block, 5750 Inserts/Sekunde
Da müsste mehr gehen. Bei einem einzelnen insert statement hast Du ~ 600 Bytes Nutzlast. Der erzeugte execute Block kann max 32K lang sein oder max 255 inserts enthalten, welche Grenze früher gerissen wird. Bei einem insert statement kommt overhead für die Trenner, Quotes, Klammern und den vorderen Teil hinzu, das insert into <tabellenname> <feldliste> values ... Klar, wenn die 41 Spalten durchschnittlich 6 Zeichen lange Namen haben, sind es schon ~ 300 Zeichen für den columns-Teil eines insert-statement, plus ~ 20 Zeichen für insert into <tabellenname>. Zusammen sagen wir 1000 Bytes pro insert. Von denen müsstest Du aber mehr als 10 in den execute block bekommen. Mit 30 insert statements müsste der nochmal einiges schneller sein als 5K inserts/sek. Und in dem Sonderfall, dass die angelieferten Daten die volle Breite der Zieltabelle haben, könnte die Feldliste entfallen.

Grüße, vr2
bfuerchau
Beiträge: 485
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Vielen Dank für die Antwort. Meine Erfahrungen sind da ein wenig anders:
Eine Feldliste gebe ich beim Insert nicht an, da ich immer alle Felder verwende. Wenn die Quelle nicht alle Felder enthält, setze ich je nach dem ob NULL erlaubt ist einen Default oder NULL.

Mache ich nun einen Insert mit Textwerten bekomme ich eben weniger Inserts in den Block:

execute block
begin
insert into table value('F1','F2',0,3.7,....);
:
end

Ich bilde aber Parameterlisten für den Block:

execute block(
P1 char(n) character set xxx=?
,P2 varchar(n) character set xxx=?
,P3 double precision=?
,P4 integer=?
,P5 char(n) character set xxx=?
,P6 varchar(n) character set xxx=?
,P7 double precision=?
,P8 integer=?
:
)
begin
insert into table values(:P1,:P2,:P3,:P4);
insert into table values(:P5,:P6,:P7,:P8);
:
end

Damit erhalte ich mehr Inserts in dem Block, allerdings zum Nachteil, dass die Summe aller Parameterwerte die Satzgrenze von 64K nicht übersteigen darf. Aber wer hat schon so lange Zeilen.

Der Durchsatz errechnet sich auch auf die benötigte Gesamtzeit, da ich ja Daten lesen muss und direkt an den BulkCopy weitergebe.
Im .Net-Debugger gemessen:
- Lesen + Füllzeit der Parameter => ca. 15 Millisekunden
- Ausführungszeit .Net ExecuteNonQuery() => ca. 10 Millisekunden

Zum Vergleich mit den Text-Inserts:
- Lesen + Aufbereiten als String + generieren der Inserts => ca. 25 Millisekunden
- Ausführungszeit .Net ExecuteNonQuery() => ca. 20 Millisekunden
Die Anzahl der Inserts oder Größe des Blocks spielte dabei nur eine untergeordnete Rolle.

Dies erklärt sich eben aus den Rechenzeiten die nötig sind um
1) im Client alle Werte als String aufzubereiten, bei Zeichenfeldern die Hochkommata zu verdoppeln und die Entscheidung, den Text oder NULL zu übergeben.
2) Im Server den SQL zu analysieren, die Werte in den Spaltentyp wieder umzuwandeln bevor der tatsächliche Insert stattfinden kann.

Eine Parallelisierung über 2 Threads mit Queue war kontraproduktiv, da die Synchronisation zwischen den Threads wieder zu viel Zeit gekostet hat und der Durchsatz entsprechend sank.

Klar ist, wenn ich ähnlich wie im Bench-Test immer den selben Insert wiederhole kann ich tatsächlich auch mehr Daten abgeben.
Aber wie das mit der EDV halt so ist, es geht ja auch um den Part DV=Datenverarbeitung, die dabei nicht zu vernachlässigen ist.

Execute-Block kann in 2.5 bis 64K lang sein, in 3.0 bis 10MB.
Das habe ich ausprobiert und klappt auch.
Allerdings war in fast allen Fällen mit 255 Inserts ja sowieso Schluss und die größte Blocklänge in FB 3.0 lag bei ca. 120K.
Die Parameterbytes darf allerdings weiterhin nicht über 64 K liegen.
Bei Unicodedaten reduziert sich die Blocklänge auf 1 Viertel (1 UTF8-Zeichen = 4Bytes), dies ist den Übertragungsprotokollen geschuldet.

Aber ich bin in soweit zufrieden, da der Bulkcopy im Verhältnis zu meinen vorherigen ETL-Methoden Faktoren zwischen 1,5 und 8 erzeugt.

Mal sehen, wann der .Net-Treiber das XNET-Protokoll (Shared-Memory) implementiert um ggf. bei lokalen Verbindungen noch schneller sein zu können (shared embedded).
bfuerchau
Beiträge: 485
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Finales Ergebnis:
Nach einigen Tests und praktischen Einsätzen habe ich nun das finale Ergebnis.
Mittels minimalem Eingriff mittels Parallelisierung konnte ich die Insert-Rate nun auf 8.000 - 15.000 / Sekunde steigern.
Das macht unsere ETL-Prozesse um durchschnittlich den Faktor 8,5 schneller.
Erreicht wird dies durch den .Net-Vorteil von Tasks an Stelle von Threads.
Tasks werden aus einem Pool entnommen während Threads erstellt werden müssen und einer zusätzlichen Synchronisation bedürfen.

Somit stellt sich die Schleife relativ simpel dar (in Kurzform):

Task exec = null;
while (Read()) {
exec?.Wait();
exec = Task.Run(() => insertCmd.Execute());
}
exec?.Wait();

Der Insert dauert im Schnitt 10ms und zwar fast immer identisch.
Dies wird dadurch begründet, dass das Produkt aus Anzahl Spalten mal Anzahl Inserts nahezu identisch ist.
Es hat sich auch herausgestellt, dass ein parametrierter Execute Block um mehrere Faktoren schneller ist als ein Execute Block mit Insert's und Konstanten.
Also ein

Execute block (parameterliste)
insert into mytable values(:P1, ..., :Pn);
:

ist in der Wiederholung durch die Möglichkeit eines Prepare schneller als

execute block
insert into mytable values(1, 1.5, 'T1', ...);

Ich sehe es ja immer wieder, dass ungern mit Parametern in SQL's gearbeitet wird. Das erfordert ja etwas mehr Programmieraufwand, löst aber 2 Probleme:
- Wiederholungen werden schneller, da nur 1x prepared wird
- SQL-Injection ist absolut unmöglich

Wer Interesse an der Lösung hat, darf sich ruhig bei mir melden.
Benutzeravatar
martin.koeditz
Beiträge: 443
Registriert: Sa 31. Mär 2018, 14:35

Hallo bfuerchau,

danke für das Feedback. Interessant wäre, ob sich das auf anderen Sprachen ebenfalls abbilden lässt. Ich werde das bei Gelegenheit für PHP testen.

Gruß
Martin
Martin Köditz
it & synergy GmbH
bfuerchau
Beiträge: 485
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Ich kann dir die Quelle gerne zur Verfügung stellen.
Benutzeravatar
martin.koeditz
Beiträge: 443
Registriert: Sa 31. Mär 2018, 14:35

Das wäre super.
Lieber gut kopiert als schlecht selbst gemacht...
Gruß
Martin
Martin Köditz
it & synergy GmbH
Antworten