IDCT Bartosz Pachołek

Uwaga: Ciasteczka!

Ta witryna korzysta z cookies: Informujemy, że ta witryna korzysta z własnych, technicznych oraz należących do podmiotów zewnętrznych ciasteczek ("cookies") celem śledzenia aktywności oraz zapewnienia pełnej funkcjonalności. Kontynuując akceptujesz użycie cookies. Aby korzystać z pewnych części witryny konieczna jest akceptacja.

PHP + MySQL: Masowy import, najlepsze rozwiązania

Opublikowano 2020-01-21

Przetwarzanie danych często kończy się koniecznością zapisu ich w jakiejś formie bazy danych, często tę funkcję realizuje kompatybilny z MySQL serwer: np. MariaDB albo MySQL Community Server. Nasze ulubione środowisko, czyli PHP, oferuje kilka metod łączenia się z takimi serwerami: interfejsami MySQLi, PDO, ostatnio popularność zdobywa DBAL z ekosystemu Doctrine. W zależności od konkretnego środowiska oraz projektu różne interfejsy mogą sprawować się lepiej lub gorzej, ale artykuł nie będzie konkretnie o tym, który sterownik jest najlepszy, a o próbie znalezienia najbardziej efektywnej metody zapisu przetworzonych danych w bazie. W przypadku małych ilości danych wybór metody często jest mniej istotny, gdyż to czy INSERT wykona się w 80 lub 95 milisekund nie gra większej roli, jednak gdy musimy zapisać miliony wpisów wtedy każda milisekunda robi się cenna.

Dane wejściowe

W celu znalezienia najlepszej metody do obsłużenia kilku milionów rekordów skorzystamy z generatora danych testowych z jednego z poprzednich artykułów: https://idct.pl/article/parsing-huge-xml-files-with-php

Znajdziecie tam kod, który generuje plik xml z identyfikatorami obiektów, datami oraz informacjami o tym czy obiekt jest dostępny lub nie. Na potrzeby tego testu wygenerujemy dane zawierające 10000 obiektów po 300 dat, czyli 3000000 rekordów.

Dane wyjściowe

Aby przechowywać dane stworzymy prostą tabelę z trzema kolumnami reprezentującymi trzy atrybuty:

CREATE TABLE `article_test` (
  `object_id` int(8) NOT NULL,
  `date` datetime NOT NULL,
  `vacancy` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Utworzymy też indeks na object_id:

ALTER TABLE `article_test`
  ADD KEY `object_id` (`object_id`) USING BTREE;
COMMIT;

ponieważ indeksowanie wpływa na czas wykonywania i korzystając z różnych metod wywołamy je w różnych momentach.

Użyliśmy InnoDB celem skorzystania z transakcji.

Połączenie

Na potrzeby testów skorzystamy z PDO, utworzyłem użytkownika article_test, który ma dostęp do bazy article_test korzystając z hasła article_test.

$db = new PDO('mysql:host=localhost;dbname=article_test',
    'article_test',
    'article_test');

Metodologia + najprostsze podejście

Uwzględnimy czas przetwarzania danych we wszystkich testach, gdyż w przynajmniej jednym scenariuszu nie możemy tego pominąć, dlatego celem wyrównania szans zostanie to uwzględnione zawsze.

Najprostszym podejściem jest utworzenie obiektu typu PDOStatement, czyli przygotowanego do wykonania zapytania, które może być uruchomione wielokrotnie z różnymi wartościami zmiennych:

$stmt = $db->prepare('insert into article_test (object_id, date, vacancy) values (:object_id, :date, :vacancy)');

oraz wywołanie go w trakcie pętli przetwarzającej dane z innymi wartościami zmiennych, używając execute.

W naszych testach oczekujemy wykonania całości importu poprawnie albo anulowania, więc skorzystamy z transakcji:

$db->beginTransaction();
....
$db->commit();

aby uniknąć cacheowania oraz spowolnienia związanego z rozrostem tabeli wyczyścimy ją przed każdym testem:

$db->exec('truncate article_test');

Każdy test zostanie powtórzony trzy razy, najlepszy wynik z trzech będzie wynikiem danego rozwiązania.

Kod pierwszego rozwiązania:

<?php
$xml = XMLReader::open('data.xml');
$lastObjectId = null;

$db = new PDO('mysql:host=localhost;dbname=article_test',
    'article_test',
    'article_test');

$db->exec('truncate article_test');

$start = time();
$stmt = $db->prepare('insert into article_test (object_id, date, vacancy) values (:object_id, :date, :vacancy)');
$db->beginTransaction();

$i = 0;
while($xml->read()) {
    if ($xml->nodeType === \XmlReader::ELEMENT) {
        switch($xml->depth) {
            case 2: //we are in `room`
                //get the object id 
                $lastObjectId = intval($xml->getAttribute('id'));
                ++$i;
                if ($i % 1000 == 0) {
                    echo "Objects progress: " . $i . "\n";
                }
            break;
            case 3: //we are in `vacancy
                $date = $xml->getAttribute('date');
                $objectId = $lastObjectId;   
                //now we need to jump one more time to the actual value...
                if (!$xml->isEmptyElement) {
                    $xml->read();
                }
                $value = intval($xml->value);
                $stmt->bindParam(':object_id', $objectId, PDO::PARAM_INT); 
                $stmt->bindParam(':date', $date, PDO::PARAM_STR);
                $stmt->bindParam(':vacancy', $value, PDO::PARAM_BOOL);
                $stmt->execute();
            break;
        }
    }
}

$db->commit();
$time = time() - $start;
var_dump('everything took: ' . $time);

Takie podejście wykorzystuje najmniej pamięci po stronie aplikacji, ale jednocześnie przesuwając tę odpowiedzialność na system zarządzający bazą danych.

Wyniki:

  • 251s
  • 247s
  • 258s

Ponieważ w kolejnych rozwiązaniach często porzucimy informacje o typie zmiennych to dla ścisłości oraz bycia fair powinniśmy przetestować proste rozwiązanie również bez korzystania z tej funkcjonalności.

Uwaga: wszystkie testy mogą mieć nieco inne zachowania w zależności od silnika obsługującego tabelę: np. z MyISAM, czyli silnika bez obsługi transakcji każde wywołanie nie tylko zakłada krótką blokadę na tabelę, ale też powoduje modyfikację indeksów. Miejcie na uwadze, że korzystając z MyISAM powinniście ręcznie zablokować tabelę oraz np. wyłączyć indeksowanie aż do zakończenia procesu celem uzyskania lepszych oraz spójnych rezultatów (oczywiście bez obsługi mechanizmu rollback).

Kod po zmianach:

<?php
$xml = XMLReader::open('data.xml');
$lastObjectId = null;

$db = new PDO('mysql:host=localhost;dbname=article_test',
    'article_test',
    'article_test');

$db->exec('truncate article_test');

$start = time();
$stmt = $db->prepare('insert into article_test (object_id, date, vacancy) values (?, ? ,?)');
$db->beginTransaction();

$i = 0;
while($xml->read()) {
    if ($xml->nodeType === \XmlReader::ELEMENT) {
        switch($xml->depth) {
            case 2: //we are in `room`
                //get the object id 
                $lastObjectId = intval($xml->getAttribute('id'));
                ++$i;
                if ($i % 1000 == 0) {
                    echo "Objects progress: " . $i . "\n";
                }
            break;
            case 3: //we are in `vacancy
                $date = $xml->getAttribute('date');
                $objectId = $lastObjectId;   
                //now we need to jump one more time to the actual value...
                if (!$xml->isEmptyElement) {
                    $xml->read();
                }
                $value = intval($xml->value);
                $stmt->execute([ $objectId, $date, $value ]);
            break;
        }
    }
}

$db->commit();
$time = time() - $start;
var_dump('everything took: ' . $time);

Wyniki:

  • 256s
  • 249s
  • 262s

Jak widać podanie informacji o typach nie wpływa na czas wykonywania.

Potencjalne optymalizacje

Jeśli tabele mają skonfigurowane sprawdzanie kluczy obcych lub jednoznacznych indeksów to warto wyłączyć na czas transakcji if weryfikację, gdyż InnoDB sprawdza je po dodaniu każdego nowego rekordu: Z http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html:

InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit.

Warto wyłączyć mechanizm AUTOCOMMIT, gdyż domyślnie powoduje on zapisanie na dysku wszelkich zmian (nawet tymczasowych): SET autocommit=0. Jeśli nie korzystasz z mechanizmu transakcji to pamiętaj, aby ręcznie wywołać komendę COMMIT po imporcie, aby przywrócić AUTOCOMMIT dla kolejnych zapytań wykonywanych w ramach tego samego połączenia.

Jeśli jesteście pewni, że nie mogą powodować problemów lub sami weryfikujecie klucze to możecie wyłączyć ich sprawdzanie w trakcie importu: SET unique_checks=0 SET foreign_key_checks=0

I reaktywować po: SET unique_checks=1 SET foreign_key_checks=1

Korzystając z MyISAM możecie też wyłączyć/włączyć indeksy:

$db->exec("ALTER TABLE `article_test` DISABLE KEYS;");
...
$db->exec("ALTER TABLE `article_test` ENABLE KEYS;");

Jedno duże zapytanie

Korzystając ze składni oferowanej przez MySQL możemy utworzyć jedno duże zapytanie ze wszystkimi wartościami z naszego importu, ale rodzi to pewne nowe problemy: po pierwsze musimy przechować całość w zapytania w zmiennej (pamięci), po drugie utworzymy olbrzymią ilość wiązanych zmiennych (bound variables) a to potencjalnie może wpłynąć na pracę sterownika połączenia (pewnym rozwiązaniem tego jest porzucenie wiązanych zmiennych na rzecz ręcznie zweryfikowanych ciągów znaków, ale raczej odradzam takie podejścia), a trzecią kwestią jest maksymalny rozmiar zapytania, określony przez parametr konfiguracyjny serwera max_allowed_packet.

Spróbujmy

<?php
$xml = XMLReader::open('data.xml');
$lastObjectId = null;

$db = new PDO('mysql:host=localhost;dbname=article_test',
    'article_test',
    'article_test');

$db->exec('truncate article_test');

$start = time();
$query = 'insert into article_test (object_id, date, vacancy) values ';

//will store the collected values
$values = [];

$i = 0;
while($xml->read()) {
    if ($xml->nodeType === \XmlReader::ELEMENT) {
        switch($xml->depth) {
            case 2: //we are in `room`
                //get the object id 
                $lastObjectId = intval($xml->getAttribute('id'));
                ++$i;
                if ($i % 1000 == 0) {
                    echo "Objects progress: " . $i . "\n";
                }
            break;
            case 3: //we are in `vacancy
                $date = $xml->getAttribute('date');
                $objectId = $lastObjectId;   
                //now we need to jump one more time to the actual value...
                if (!$xml->isEmptyElement) {
                    $xml->read();
                }
                $value = intval($xml->value);
                $values[] = $objectId;
                $values[] = $date;
                $values[] = $value;
            break;
        }
    }
}

//now we need to create a query which will have the same amount of bound variables
$variables = count($values) / 3; 
$query .= join(',', array_fill(0, $variables, '(?, ?, ?)'));

$db->beginTransaction();
$stmt = $db->prepare($query);
$stmt->execute($values);
$db->commit();
$time = time() - $start;
var_dump('everything took: ' . $time);

Pierwsze podejście zakończyło się błędem:

PHP Warning:  Error while sending QUERY packet. PID=10673

ponieważ wartość max_allowed_packet była zbyt niska w moim systemie. Zwiększyłem do 256MiB i wykonałem ponownie:

  • 31s
  • 28s
  • 31s

Amazing, right? Well yes and no: be sure to remember that you actually need to a) store the values in big variable (or variables) before sending to the DBMS, b) you are limited by max_allowed_packet and c) you would need to incorporate chunking in case of fixed maximum allowed packet in the DBMS to which you export.

Świetne wyniki, prawda? Tak i nie: pamiętajcie, że musicie faktycznie najpierw przechować wartości w zmiennej (w pamięci), jesteście ograniczeni przez max_allowed_packet i ostatecznie gdybyście nie mogli zmienić tego parametru konfiguracyjnego musielibyście wprowadzić samodzielnie wykonywanie partiami.

Czy to najlepsze rozwiązanie?

Zależy. Dokumentacja MySQL poleca nam jeszcze jedno: https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.html

Wg opisu, polecenie LOAD DATA może oferować nawet do 20x szybsze wprowadzenie danych!

LOAD DATA z fputcsv

LOAD DATA ładuje wcześniej przygotowany plik CSV do bazy danych. W sytuacjach gdy musimy wykonać jedno lub kilka operacji nie jest to warte zachodu, ale otwiera nowe możliwości dla większych i bardziej skomplikowanych: jednak wymaga dostępu do przestrzeni dyskowej. Spróbujmy, utworzymy plik korzystając z fputcsv:

<?php
$xml = XMLReader::open('data.xml');
$lastObjectId = null;

$db = new PDO('mysql:host=localhost;dbname=article_test',
    'article_test',
    'article_test');

$db->exec('truncate article_test');

$start = time();

//opening the file
$file = fopen('out.csv', 'w');

$i = 0;
while($xml->read()) {
    if ($xml->nodeType === \XmlReader::ELEMENT) {
        switch($xml->depth) {
            case 2: //we are in `room`
                //get the object id 
                $lastObjectId = intval($xml->getAttribute('id'));
                ++$i;
                if ($i % 1000 == 0) {
                    echo "Objects progress: " . $i . "\n";
                }
            break;
            case 3: //we are in `vacancy
                $date = $xml->getAttribute('date');
                $objectId = $lastObjectId;   
                //now we need to jump one more time to the actual value...
                if (!$xml->isEmptyElement) {
                    $xml->read();
                }
                $value = intval($xml->value);

                //adding data
                fputcsv($file, [ $objectId, $date, $value ]);
            break;
        }
    }
}

//closing
fclose($file);

$db->beginTransaction();

$statement = <<<HEREDATA
LOAD DATA LOW_PRIORITY INFILE "out.csv"
INTO TABLE article_test
CHARACTER SET utf8
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
HEREDATA;

$db->exec($statement);
$db->commit();
$time = time() - $start;
var_dump('everything took: ' . $time);

Wyniki:

  • 39s
  • 38s
  • 34s

Nieco dłużej niż jedno długie zapytanie, dlaczego? Odpowiedzi są dość proste: dysk twardy jest wolniejszy od pamięci oraz wykonujemy więcej operacji zapisu i odczytu: musimy zapisać dane do pliku, a standardowe buforowanie przy operacjach na plikach w PHP pozostawia wiele do życzenia. stream_set_write_buffer w przypadku plików lokalnych nie odgrywa żadnej roli (przynajmniej w momencie pisania tego artykułu), możecie spróbować sami:

$file = fopen('out.csv', 'w');
stream_set_write_buffer($file, 16384);

So where is the benefit over single big query? You operate on a file therefore your PHP app is less likely to hit any potential memory limits. What are the problems? The biggest ones are: To gdzie zysk w porównaniu z jednym dużym zapytaniem? Operujecie na pliku więc jest o wiele mniej prawdopodobne, że przekroczycie zasoby pamięci w trakcie wykonwywania.

Jakie są problemy tego rozwiązania? Największe to:

  • wymagany dostęp do przestrzeni dyskowej
  • specjalne uprawnienia: konieczne jest posiadanie uprawnienia FILE jeśli plik jest na tym samym urządzeniu co serwer
  • wydajność związaana z wydajnością samego dysku

Można jakoś pominąć kwestie związane z operacjami na dysku?

Tak.

LOAD DATA z fputcsv w pamięci

Jesli macie dostęp administratora do swojego systemu to możecie utworzyć "Ramdysk", czyli fikcyjny dysk w pamięci.

mkdir /mnt/ramdisk
mount -t tmpfs -o size=512m tmpfs /mnt/ramdisk

I operować na nim jak na plikach w innych folderach.

To powinno pomóc, ale czy jest rozwiązanie, które nie wykracza poza samo PHP? Tak.

LOAD DATA z plikiem CSV buforowanym

Zamiast zapisywać dane CSV prosto na dysk najpierw zapiszemy je w pamięci i dopiero na samym końcu zrzucimy do pliku:

Najpierw musimy otworzyć strumień do pliku w pamięci:

$file = fopen('php://memory', 'w');

A przed jego zamknięciem najpierw zrzucić do pliku i ten plik później wykonać jak we wcześniejszym kodzie:

$realfile = fopen('out.csv', 'w');
fflush($file);
rewind($file);
stream_copy_to_stream($file, $realfile);
fclose($realfile);
fclose($file);

Wyniki:

  • 19s
  • 20s
  • 19s

Wspaniale, prawda? Niestety to podejście też ma wadę: obciążenie pamięci. Do czasu zrzucenia na dysk musimy przechować całość danych w pamięci, podobnie jak w "jednym dużym zapytaniu". To może również być dodatkowo problematyczne i wymagać zapisu partiami na dysku, aby uniknąć przepełnienia.

Możemy to jakoś rozwiązać? Tak, możemy. (~~~ Barrack Obama).

Komponent PHP DB CSV Writer

Możecie skorzystać z mojego komponentu PHP Db Csv writer, który to oparty jest na innej z moich bibliotek: buforowanym Csv Writerze. Pozwala na tworzenie plików CSV w pamięci oraz zapis partiami na dysk w momencie przepełnienia bufora.

Dostępny na Github: https://github.com/ideaconnect/php-db-csv-writer

Aby zainstalować ten komponent skorzystać z Composera:

composer require idct/php-db-csv-writer

załącz zainstalowane biblioteki oraz utwórz instancję:

include "vendor/autoload.php";
use IDCT\CsvWriter\DbCsvWriter;
$dbCsvWriter = new DbCsvWriter();

Otwórz nową kolekcję (plik):

$dbCsvWriter->startCollection('out', ['object_id', 'date', 'vacancy']);

Przekaż obiekt PDO:

$dbCsvWriter->setPdo($db);

Opcjonalnie ustaw rozmiar bufora:

$dbCsvWriter->setBufferSize(33554432); //32 MiB

Aby dodać dane skorzystaj z:

$dbCsvWriter->appendData([ ... data ... ]);

I aby zapisać w bazie wywołaj:

$dbCsvWriter->storeCollection($tableName);

Kod testowy wygląda tak:

<?php
include "vendor/autoload.php";

$xml = XMLReader::open('data.xml');
$lastObjectId = null;

$db = new PDO('mysql:host=localhost;dbname=article_test',
    'article_test',
    'article_test');

$db->exec('truncate article_test');
$start = time();
use IDCT\CsvWriter\DbCsvWriter;
$dbCsvWriter = new DbCsvWriter();
$dbCsvWriter->startCollection('out', ['object_id', 'date', 'vacancy'])
            ->setPdo($db, false)
            ->setTmpDir('./')
            ->setBufferSize(1024*1024); //1 MiB

$i = 0;
while($xml->read()) {
    if ($xml->nodeType === \XmlReader::ELEMENT) {
        switch($xml->depth) {
            case 2: //we are in `room`
                //get the object id 
                $lastObjectId = intval($xml->getAttribute('id'));
                ++$i;
                if ($i % 1000 == 0) {
                    echo "Objects progress: " . $i . "\n";
                }
            break;
            case 3: //we are in `vacancy
                $date = $xml->getAttribute('date');
                $objectId = $lastObjectId;   
                //now we need to jump one more time to the actual value...
                if (!$xml->isEmptyElement) {
                    $xml->read();
                }
                $value = intval($xml->value);
                $dbCsvWriter->appendData([ $objectId, $date, $value ]);
            break;
        }
    }
}

$db->beginTransaction();
$dbCsvWriter->storeCollection('article_test');
$db->commit();
$time = time() - $start;
var_dump('everything took: ' . $time);

Wyniki:

  • 28s
  • 29s
  • 28s

Z buforem 1MiB.

Czyli minimalnie szybciej niż jedno duże zapytanie, ale z zachowaniem zużycia pamięci pod kontrolą.

Podsumowanie

Jak widzicie wybranie odpowiedniej metody importu przy dużej ilości danych odgrywa istotne znaczenie: z 249s udało nam się zejść do 19s, a utrzymać około 30s korzystając z metod, które pozwalają na aktywne zarządzanie wykorzystaniem pamięci. Zachowania te mogę się nieco różnić w zależności od samych danych, środowiska (w tym sprzętu: np. HDD vs SSD), dlatego poza posiadaniem ogólnej wiedzy na temat najlepszych metod wprowadzania danych do bazy polecam zawsze wykonać profilowanie dla środowisk, w których pracować będzie dane oprogramowanie.