Laden...

Wie kann ich grosse Pakete in eine MySQL Datenbank einfügen (ohne Timeout)?

Erstellt von Exception234 vor 3 Jahren Letzter Beitrag vor 3 Jahren 836 Views
E
Exception234 Themenstarter:in
10 Beiträge seit 2020
vor 3 Jahren
Wie kann ich grosse Pakete in eine MySQL Datenbank einfügen (ohne Timeout)?

verwendetes Datenbanksystem: <MySQL>

Huhu, ich befülle parallel 2 Tabellen. Plan war die Insert Statements dafür solange aufzubauen bis sie in den Bereich der max_allowed_packet Größe kommt ... dann die beiden Executes abfeuern und einmal commiten.
Dann wieder von vorn bis alle Insert Statements weggeschrieben sind.
Dabei passiert was doofes. Mein Platte arbeitet nach den ersten paar Commits am Limit (jetzt ned lachen ... 1,4 MB/s) und kommt mit der Abarbeitung der Folge-Commits nicht nach. Irgendwann meldet die DB dann nen Timeout.

Hat vielleicht jemand einen Vorschlag, der den nächsten Commit erst losschickt, wenn der vorherige komplett abgearbeitet ist? Die zusammengehörigen Inserts der beiden Tabellen sollten dabei zeitgleich commitet werden. Ich hab akt. das so gelöst …


using MySql.Data.MySqlClient;

using (MySqlConnection con = new MySqlConnection(myConnString))
{
   con.Open();
   foreach(Element element in Elmements)
   {
      //fill string statement_tab1
      //fill string statement_tab2               

      if ((bytesInStatement > (max_allowed_packet/100)) || lastStatement)          
      {                       
         DateTime starttime = DateTime.Now;                      
         using (MySqlTransaction trans = con.BeginTransaction())
         {
            using (MySqlCommand cmd1 = new MySqlCommand(statement_tab1, con))
            {
               cmd1.Transaction = trans;
               cmd1.CommandType = CommandType.Text;
               cmd1.ExecuteNonQuery();                                                              
            }                           
            using (MySqlCommand cmd2 = new MySqlCommand(statement_tab2, con))
            {
                cmd2.Transaction = trans;
                cmd2.CommandType = CommandType.Text;
                cmd2.ExecuteNonQuery();                               
            }
            trans.Commit();

            DateTime endtime = DateTime.Now;
            double timespan = (DateTime.Now - starttime).TotalSeconds;                          
            Console.WriteLine(statementsize + " bytes in " + timespan.ToString("0.00")  + " seconds");
         }
         statement_tab1 = string.Empty;
         statement_tab2 = string.Empty;
         bytesInStatement = 0;
      }
   }  
   con.Close();
}


Danke euch

W
955 Beiträge seit 2010
vor 3 Jahren

Warum werden beide Tabellen parallel und nicht seriell befüllt?
Was ist mit Bulk Inserts?

T
2.219 Beiträge seit 2008
vor 3 Jahren

Und sinnvoller wäre es bei größeren DAtenmengen auch auf Bulk Insert zu setzen anstelle von einzelnen Inserts.
Gerade wenn du viele Einträge in die Tabellen schreiben willst, ist das eine enorme Ersparnis für die DB.

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

E
Exception234 Themenstarter:in
10 Beiträge seit 2020
vor 3 Jahren

Auweh - danke für den Hinweis mit Bulk.
Kannte ich bisher leider nicht. Das das passe ich noch an.

Aber das eigentliche Problem ist ja, das ein einzelner commit wohl asynchron weiterläuft und die Platte so belastet wird bis ein Folgecommit den timeout wirft. Ich würde in dem Fall gerne solange warten bis ein Schreiben wieder sicher möglich ist und die Befüllung sauber zu Ende läuft. Lässt sich hier ein Event abfragen oder bin ich auf nem völlig falschen Dampfer?

16.807 Beiträge seit 2008
vor 3 Jahren

Hat vielleicht jemand einen Vorschlag, der den nächsten Commit erst losschickt, wenn der vorherige komplett abgearbeitet ist?

Das tut Dein Code bereits.

Aber das eigentliche Problem ist ja, das ein einzelner commit wohl asynchron weiterläuft und die Platte so belastet wird bis ein Folgecommit den timeout wirft.

Das bezweifle ich; allein deshalb, weil bei Dir nichts asynchron läuft.
Du hast keinerlei asynchronen Code, bei dem es irgendeine Race Condition geben könnte.

Auch befüllst Du nirgends parallel zwei Tabellen.
Du hast eine Transaktion, in der nacheinander zwei Befehle ausgeführt werden: da ist nichts parallel.

H
523 Beiträge seit 2008
vor 3 Jahren

Ich gehe mal davon aus, dass Du InnoDB verwendest.

Eine Optimierung der Datenbankkonfiguration könnte wohl helfen.
Das die Platte ausgelastet wird wundert mich nicht:

InnoDB must flush the log to disk at each transaction commit if that transaction made modifications to the database. When each change is followed by a commit (as with the default autocommit setting), the I/O throughput of the storage device puts a cap on the number of potential operations per second.

Kannst Du mal Deine my.cnf/my.ini anhängen?

T
2.219 Beiträge seit 2008
vor 3 Jahren

Das zu grunde liegende Problem ist eher, wie die Datenbank arbeitet.
Hier werden die Daten erst einmal ins Transaktionslog geschrieben, was den ersten Schreibvorgang auslöst.
Wenn deine Transaktion abgeschlossen ist, sind die Daten erst einmal nur im Transaktionslog angekommen.
Die eigentlichen Daten puffert die Datenbank als Dirty Pages.
Diese werden je nach Einstellung der Datenbank erst nach und nach rausgeschrieben.
Entsprechend hast du irgendwann also mehrere Prozesse, die DAten auf die Platte schreiben.

1.Jede Transaktion wird ins Transaktionslog geschrieben
2.Die Dirty Pages werden von der Datenbank aus dem RAM auf die Platte geschrieben.

Das ist bei Relationalen Datenbanken aber ein übliches Verhalten.
Sinnvoller wäre es eben, wenn du die Daten möglichst in einem Rutsch als Bulk Insert schreibst.
Da durch muss pro Tabelle im Bestfall nur einmal ins Transaktionslog und einmal die Daten aus dem RAM auf die Platte geschrieben werden.
Hier kannst du also die Schreibvorgänge selbst in einem Rutsch optimeiren.

Je nachdem was du als Hardware da hast, wäre auch eine umkonfigurierung sinnvoll.
Das Transaktionslog würde ich z.B. auf eine eigene SSD legen.
Die Daten dann z.B. auf eine SSD oder als HDD, wenn keine SSD in entsprechender Größe vorhanden ist.

Das Transaktionslog ist bei einer Relationalen Datenbank der schreibintensivere Vorgang, da dort je nach Einstellung alle Logischen Anweisungen für die Daten- und Schema Änderungen geloggt werden müssen.

Die eigentlichen Daten werden durch die Dirty Pages dann auch meist langsam und Schrittweise rausgeschrieben um eben die Belastung der Platte nicht zu hoch zu schrauben.

Nachtrag:
Abt und hypersurf waren mal wieder schneller 😃

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

87 Beiträge seit 2016
vor 3 Jahren

Hallo,

du kannst ja einfach mal ohne Transaktion testen. Dann siehst du, ob diese das Problem verursacht.

glandorf

E
Exception234 Themenstarter:in
10 Beiträge seit 2020
vor 3 Jahren

Danke euch – mit der Erläuterung zur Nachbereitung von Transaktionslog und Dirtypages ist nun klar was da blockiert. Gemäß my.ini wird das nach jedem Commit gemacht … passt auch exakt zum Verhalten.

[client]
port=3306
[mysql]
no-beep
[mysqld]
datadir=C:/ProgramData/MySQL/MySQL Server 8.0\Data
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="DREPC0595.log"
slow-query-log=1
slow_query_log_file="DREPC0595-slow.log"
long_query_time=10
log-error="DREPC0595.err"
log-bin="DREPC0595-bin"
server-id=1
lower_case_table_names=1
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
max_connections=151
table_open_cache=2000
tmp_table_size=135M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=259M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=48M
innodb_thread_concurrency=17
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
loose_mysqlx_port=33060

Bulk-Inserts werden unter MySQL scheinbar nicht so unterstützt wie von MS SQL Server. Wenn ich es richtig verstehe basiert das Ganze auf fileinput. Da das auch wieder IO lastig ist, würde es das Problem nur verlagern. Falls sich SQL Statement:

LOAD DATA LOCAL INFILE '<filepath>' INTO TABLE <table>

auch mit einem gepufferten file ausfüren lässt, würde ich die Variante in die engere Auswahl nehmen. Negativ ist, dass die Ausführung an Bedingungen geknüpft ist.

Error Code: 3948. Loading local data is disabled; this must be enabled on both the client and server sides. 

Bei späterem Nichtbeachten käme es zu Ausfällen.

In jedem Fall würde die Behandlung eines von der Platte verschuldeten TimeOut dann aber so ausschauen, den betroffenen Commit beim Neuversuch solange zu verzögern bis wieder Ressourcen vorhanden sind

T
2.219 Beiträge seit 2008
vor 3 Jahren

Wäre es im ersten Schritt nicht erstmal möglich, die Daten auf eine eigene Platte zu verlagern?
Bei einem DB Server ist es denkbar schlecht, die Daten auf der Systemplatte zu lagern.
Dadurch lastet die Datenbank das System zusätzlich aus, umgekehrt aber auch das System die Datenbank.

Ansonsten sieht die Config so aus, als könnte man noch an ein paar Stellschrauben drehen was die RAM Nutzung angeht.
Hier gibt es bestimmt ein paar Tools um dort etwas feintuning zu betreiben.
Bei MySQL bin ich vor Jahren ausgestiegen, da nach der Übernahme von Oracle die Entwicklung immer weiter eingeschlafen ist.

Ansonsten kann ich dir nur empfehlen, die Daten und das Transaktionslog mindestens auf eigene Platten jeweils zu verschieben.
Dadurch lasten sich die Prozesse nicht gegenseitig aus.
Wie/ob dies bei MySQL so einfach machbar ist, weiß ich nicht, kann nur mit PostgreSQL Erfahrung aufwarten.

Dort gibt es auch einen richtigen Bulk Insert, wobei ich sicher war, dass dies auch von MySQL mal unterstüzt wurde.
Ansonsten müsstest du mal googeln, was es dort noch für Ansätze gibt.

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

E
Exception234 Themenstarter:in
10 Beiträge seit 2020
vor 3 Jahren

Ja aber Hardwarebeschaffung is ne andere Kostenstelle – kennst du bestimmt 😃 Ich hab ja gezielt nach stabilen Inserts gefragt und bin an sich richtig dankbar über die schwache Platte. Mir wäre die Fehleranfälligkeit sonst nie aufgefallen. Da das gern ewig laufen darf geht mir Stabi über Performance. Wenn der Performance-Wunsch nachher kommt, ist auslagern ne feine Sache ohne das Code angepasst werden muss. Das schöne ist, dass man den Flaschenhals beim Schreiben im Taskmanager eindeutig sieht ... und hören tu ich ihn unterm Tisch auch 😃

T
2.219 Beiträge seit 2008
vor 3 Jahren

Wenn das ein privates Projekt ist, wären die Kosten an und für sich überschaubar.
SSD und Festplatten kosten heute nicht mehr extrem viel.
Im beruflichen Umfeld kann es da schon wieder anders aussehen.

Dann wäre aber noch die Frage, was für eine Anwendung du hier hast.
Ist dies eine Web oder Desktop Anwendung?
Auch wäre wichtig welche Art von Daten du in den Tabellen speicherst?
Ggf. gibt es einen anderen Ansatz als einen lokalen DB Server?

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

H
523 Beiträge seit 2008
vor 3 Jahren

Bulk-Inserts werden unter MySQL scheinbar nicht so unterstützt wie von MS SQL Server. Wenn ich es richtig verstehe basiert das Ganze auf fileinput. Da das auch wieder IO lastig ist, würde es das Problem nur verlagern.

Bulk-Inserts werden unterstützt:

INSERT INTO table_name (column_list)
VALUES
	(value_list_1),
	(value_list_2),
	...
	(value_list_n);

Ich nutze das tagtäglich 😃

Teste mal folgende Änderungen an der my.ini:

innodb_flush_log_at_trx_commit=2
max_allowed_packet=16M

Dann wäre es zusätzlich interessant die Tabellenstruktur inkl. Indizes zu kennen, da "zu viele" Indizes die Performance beim Einfügen von Datensätzen negativ beeinflussen können.

E
Exception234 Themenstarter:in
10 Beiträge seit 2020
vor 3 Jahren

Dann wäre aber noch die Frage, was für eine Anwendung du hier hast.
Ist dies eine Web oder Desktop Anwendung?
Auch wäre wichtig welche Art von Daten du in den Tabellen speicherst?
Ggf. gibt es einen anderen Ansatz als einen lokalen DB Server?

T-Virus

Die Struktur ist fix vorgegeben. Es sind einfache Statusinfos, die täglich von anderen Systemen aufgezeichnet werden. Bei Anbindung neuer Quellen besteht jeodch die Möglichkeit dass einmalig große Mengen anfallen.

Bulk-Inserts werden unterstützt:

INSERT INTO table_name (column_list)
VALUES
(value_list_1),
(value_list_2),
...
(value_list_n);

mmh ja das geht. Ich rede hier wohl von was anderem 😦 Microsoft bietet die Klasse SqlBulkCopy („Ermöglicht den effizienten Massenladevorgang einer SQL Server-Tabelle mit Daten aus einer anderen Quelle.“) Hierzu hab ich kein MySQL Äquivalent gefunden.
Die Annahme war, dass es sich bei dem SQL Befehl LOAD DATA LOCAL INFILE '<filepath>' INTO TABLE <table> genau darum handelt. Damit sollte bei Verwendung in einer Transaktion bereits ein Transaktionslogeintrag vorhanden sein der besser aufbereitet ist ... hab ich aber noch nicht getestet.

16.807 Beiträge seit 2008
vor 3 Jahren

Microsoft bietet die Klasse SqlBulkCopy („Ermöglicht den effizienten Massenladevorgang einer SQL Server-Tabelle mit Daten aus einer anderen Quelle.“) Hierzu hab ich kein MySQL Äquivalent gefunden.

"Echte" Bulk-Inserts erfolgen bei MySQL mit CSV-Dateien.
Es gibt keinen Bulk-Insert bei MySQL über "normale" SQL Befehle.

Erster Google Treffer: High-speed inserts with MySQL

Das ist also der Teil aus Deiner Annahme, den Du nur nicht korrekt angewandt hast.

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

ist einfach nur ein Multi-Insert.

Side Note:
MySQL ist halt in 99% der Fälle leider die völlig falsche Datenbank-Technologie für Schreibintensive Aufgaben wie Status-Infos.
Aber vermutlich wirst Du diese Entscheidung nicht selbst getroffen haben.