Warum verbessert TRANSACTION / COMMIT die Leistung mit PHP/MySQL (InnoDB) so sehr?

Lesezeit: 4 Minuten

Benutzer-Avatar
jjwdesign

Ich habe mit dem Importieren großer CSV-Datendateien gearbeitet; normalerweise weniger als 100.000 Datensätze. Ich arbeite mit PHP und MySQL (InnoDB-Tabellen). Ich musste PHP verwenden, um einige Felder umzuwandeln und vor dem MySQL Text zu verarbeiten INSERTs (Teil von process_note_data() im Code unten). MySQLs LOAD DATA war nicht machbar, also bitte nicht vorschlagen.

Ich habe kürzlich versucht, die Geschwindigkeit dieses Prozesses zu verbessern, indem ich MySQL-Transaktionen verwende START TRANSACTION und COMMIT. Die Leistungssteigerung war überraschend. Die Bearbeitungszeit(en) sanken um den Faktor 20. Ein 20-minütiger Prozess dauerte also nur etwa 1 Minute.

FRAGEN.

1.) Versteht jemand warum es eine solche Leistungssteigerung gab (20 min auf 1 min)?

2.) Muss ich mir Sorgen machen, wie groß die Transaktion bei 100.000 Datensätzen werden kann?

3.) Sollte ich mich mit einer großen Anzahl von Einfügungen und/oder Aktualisierungen in der Transaktion befassen?

/*
 * Customer Notes Data:
 * Rows are either a meeting, call or note!
 */
$row = 1;
$data = array();
$fields = array();
$line="";

$db->query('SET autocommit=0;');
$db->query('START TRANSACTION;');

if (($handle = fopen("modules/".$currentModule."/Data/customernote.csv", "r")) !== FALSE) {
  while (($data = fgetcsv($handle, 4096, ',', '"')) !== FALSE && $row < 999000) {
    //Row 1 - CSV header row with field names
    if ($row == 1) {
      $csv_fields = $data;
    } elseif ($row > 1) {
      $fields = $this->process_note_data($data, $csv_fields, $row);
    }
    $row++;
  } // end while
  fclose($handle);
}

$db->query('COMMIT;');
$db->query('SET autocommit=1;');

Hinweis: Die Text-/Feldverarbeitung erfolgt im Aufruf an $this->process_note_data() die dann eine andere Hilfsklasse aufruft, die die hat INSERT Anweisungscode. Ich hatte nicht genug Platz, um den gesamten Code aufzunehmen. $db->query() ist ein typisches Datenbankobjekt für MySQL-Abfragen.

  • Ich sehe hier kein Update/Insert, vielleicht führte das Entfernen dieser zu einer schnelleren Ausführung;)

    – dev-null-Bewohner

    3. Februar 2013 um 17:35 Uhr

  • Die Text-/Feldverarbeitung erfolgt im Aufruf von $this->process_note_data(), das dann eine andere Hilfsklasse mit den INSERTs aufruft. Ich hatte nicht genug Platz, um den gesamten Code aufzunehmen. Beachten Sie, dass alle Datensätze ordnungsgemäß eingefügt werden.

    – jjwdesign

    3. Februar 2013 um 17:38 Uhr


  • Transaktion ~= in memory processing; begehen ~= flush to storage

    – जलजनक

    3. Februar 2013 um 17:44 Uhr

  • Du musst (und solltest) nicht SET autocommit=0; und wieder zurück; aus hier: “Bei START TRANSACTION bleibt Autocommit deaktiviert, bis Sie die Transaktion mit COMMIT oder ROLLBACK beenden.”

    – BlueRaja – Danny Pflughoeft

    11. Juni 2013 um 20:11 Uhr

Benutzer-Avatar
MiGro

  1. Bitte überprüfen Sie diesen Link:

    https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-transaction-management.html

    InnoDB muss das Protokoll bei jedem Transaktionscommit auf die Festplatte leeren, wenn diese Transaktion Änderungen an der Datenbank vorgenommen hat. Wenn auf jede Änderung ein Commit folgt (wie bei der standardmäßigen Autocommit-Einstellung), begrenzt der E/A-Durchsatz des Speichergeräts die Anzahl möglicher Operationen pro Sekunde.

  2. Große Transaktionen können die Leistung während des Commit beeinträchtigen (siehe oben)

  3. Nur im Falle eines Rollbacks, kann jedoch durch einige Einstellungen optimiert werden (siehe Link)

  • Würden Sie empfehlen, etwa alle 1.000 INSERTS ein COMMIT durchzuführen, um die Verarbeitung bei COMMIT zu reduzieren, oder mache ich mir darüber ein bisschen zu viele Sorgen?

    – jjwdesign

    3. Februar 2013 um 18:27 Uhr

  • 1k sollte ok sein, aber es hängt von H/W ab. Ich würde vorschlagen, hier einige Tests durchzuführen. Wie auch immer – seien Sie vorsichtig mit der Datenkonsistenz (dh Sie haben 20.000 von 100.000 Datensätzen geladen und das System ist abgestürzt).

    – MiGro

    3. Februar 2013 um 18:32 Uhr

  • Es gibt nicht viel zu verarbeiten COMMIT Zeit, also gibt es keinen Grund, sich regelmäßig dafür zu verpflichten. Wenn das System jedoch abstürzt, während eine sehr große Transaktion ausgeführt wird, kann das Rollback sehr lange dauern (und dies wird während des Serverstarts durchgeführt, während keine Anforderungen entgegengenommen werden). Bei nur 100.000 Zeilen sollten Sie sich darüber jedoch wahrscheinlich keine Sorgen machen.

    – Jeremycole

    21. Februar 2013 um 1:44 Uhr

Benutzer-Avatar
Herr Calvin

Mein eigener kleiner Test in .Net (4 Felder pro Datensätze):

INSERT 1 Datensatz, keine Transaktion:60 Frau

INSERT 1 Datensatz, mit Transaktion:158 Frau

INSERT 200 Datensätze mit Transaktionen, Commit nach jedem Datensatz:17778
Frau

INSERT 200 Datensätze ohne Transaktionen:4940 Frau

INSERT 200 Datensätze mit Transaktionen, nur nach dem letzten Datensatz festschreiben:4552 Frau

INSERT 1000 Datensätze mit Transaktionen, nur nach dem letzten Datensatz festschreiben:21795 Frau

Client in Dänemark, Server in Belgien (Google Cloud f1-micro).

Ich wollte das in einen Kommentar schreiben, aber die Formatierung ist nicht gut …. also hier ist meine Entschuldigung im Voraus 😉

  • Bei nur 200 Datensätzen werden Sie keinen großen Unterschied feststellen. Bei mehr als 100.000 Datensätzen sollten Sie einen großen Leistungsvorteil erkennen.

    – jjwdesign

    29. Januar 2016 um 14:03 Uhr

  • Interessant der Unterschied zwischen Commit-Position mit Transaktionen … Danke!

    – J. Fdez

    3. Oktober 2018 um 11:44 Uhr

  • Führen Sie nun den gleichen Test für SELECT durch

    – Moritz

    8. November 2021 um 19:00 Uhr

1227610cookie-checkWarum verbessert TRANSACTION / COMMIT die Leistung mit PHP/MySQL (InnoDB) so sehr?

This website is using cookies to improve the user-friendliness. You agree by using the website further.

Privacy policy