Как добиться баланса между скоростью и производительностью при импорте ~200к строк в базу данных?

Question

[fman42f]

Всем привет.

Вводные: есть база(MySQL), .csv-файл с ~200к строк (каждая строка по 10-15 символов примерно), нужно их дополнить данными и загнать в базу данных

Сейчас иду таким способом: обхожу все эти строки, формируя массив из 1-2к ORM моделей с дополнительными данными (типа какого-то name и тд), затем вставляю с помощью saveMany в базу, подчищаю массив и так до окончания строк. И в целом все ок, но не устраивает скорость импорта. Пошел ради интереса на крайности, начал формировать массив по 30к элементов и все равно скорость не особо отличается. Предполагаю, узкое горлышко как раз в стыке с базой. Массив из 30к быстро формируется, ~1 секунда. В базу же вставляется долго, около минуты (проверял на горячую, вставка идет понемножку, отсюда и лочится процесс, что в будущем может привести к падению скрипта, например)

Щас в идеях есть разве что вставкой через insert. На скорую руку попробовал, результат все равно не тот. Хотелось бы услышать кто что думает, у кого какие идеи в какую сторону копать. Возможно, здесь вообще подход стоит сменить?

Comments

[Vitsliputsli]

Вставка в одну таблицу? Другие таблицы ссылаются на текущую? Сколько записей в текущей таблице? Партиционирование, триггеры или что-то еще?
Исключите чисто диск, создайте новую таблицу и проверьте скорость на ней.

[Akina]

нужно их дополнить данными

Откуда берутся эти дополнительные данные?

[fman42f]

Akina, на данный момент определяются в коде (условно валидность / не валидность строки). Поэтому в таблиице получаем структуру вида

строка | какой-то флаг (валидность / не валидность)

[fman42f]

Vitsliputsli, да, в одну. Записей 0. Это на локале я пробую. Ниже результаты описал. С LOAD Data и через вставку пачками

[Vitsliputsli]

fman42f, собственно вопросы выше, поэтому что, если нет еще каких-то неизвестных тормозов в виде foreign keys, например, то вероятно уперлись в производительность InnoDB на конкретном диске. Можно попробовать loader и что-нибудь извращенное: либо "оптимизация" InnoDB (по-сути убивание отказоустойчивости), либо загрузку через промежуточную таблицу в MyISAM (с которой работает в разы быстрее почему-то).

проверял на горячую, вставка идет понемножку

в смысле понемножку? весь чанк в 30k, если вы его вставляете одним insert, должен появляться мновенно, а не понемножку.

[Akina]

на данный момент определяются в коде (условно валидность / не валидность строки).

Я верно понимаю, что это определение может выполнить только приложение, его нельзя провести на стороне SQL-сервера?
Если так, то загрузка всего CSV в программу, расчёт дополнительных данных, сброс результата в новый CSV и его загрузка в таблицу через LOAD DATA будет скорее всего наиболее быстрым решением. Тут если что и может затормозить - так это файловые операции.. правда, у LOAD DATA есть один недостаток - если при загрузке произойдёт ошибка, то уже загруженная начальная часть останется в данных (LOAD DATA не транзакционный запрос).

Answer 1

[mayton2019]

1) 200k строк это вобщем-то мало для современных БД
2) Загрузи как есть в новую таблицу вот как тут пишут
https://stackoverflow.com/questions/14127529/impor...
3) С помощью alter add column добавь нужные колонки и обнови через UPDATE.

Никуда больше копать не надо. Загрузка данных - уже 30 лет как решенная коробочная задача.
И ее делают встроенные в БД утилиты и сама БД если если ей доступен диск с csv-файлом.

Никакой ORM тебе тоже не нужен. ORM вообще - противопоказан для задач аналитики и ETL.

Comments

[JhaoDa]

ужно их дополнить данными

Не заметил? Та самая T из ETL.

[mayton2019]

JhaoDa, я могу еще раз медленно написать что ORM нельзя использовать в задачах массовой загрузки.
Трансформация здесь определенно есть. Автор об этом пишет. Но нужно поробовать сначала ее реализовать
в БД уже после загрузки. Или можно сделать enrichment csv файла. Но это все тоже не имеет прямого
отношения к ORM.

ORM пока для меня выглядит бантиком сбоку.

[fman42f]

Да, согласен что 200к это в целом не число для нашего времени.

Была тоже мысль через мускул загнать csv, но мне нужно в рантайме проводить обработку какую-то элемента и определять ему атрибуты (валидный / не валидный / etc..). Через БД процедурой таким заниматься? или считывать базу и апдейтить ?

[JhaoDa]

mayton2019, ORM, а именно Eloquent (надеюсь, автор её юзает), при записи не создаёт никаких накладных расходов.

[mayton2019]

JhaoDa, надо это доказать. Пока у него - проблемы с перформансом.

[JhaoDa]

mayton2019, ну доказывай, если хочешь. Я это и так знаю.

[mayton2019]

Да я просто подожду. Как Конфуций советовал.

[fman42f]

mayton2019, через файл вставка проходит быстро - 2,5 секунды в среднем. То, что надо

Я правильно понял с enrichment csv, что я могу сперва обработать csv-файл, трансформировать данные как мне надо, сделать своего вида csv и импортировать его в базу ?

В моем случае можно и после вставки уже пробежаться по элементам и трансформировать. Интересно по подходу

[Akina]

через файл вставка проходит быстро - 2,5 секунды в среднем

Вставка 200к записей по 15 байт на рыло из CSV за 2.5 секунды? это ну очень долго..

Answer 2

[JhaoDa]

Щас в идеях есть разве что вставкой через insert. На скорую руку попробовал, результат все равно не тот.

Как попробовал? Просто заменить сохранение модели на инсёрт или пачку записей одним инсёртом?

Вставку чанка (1-2к/30к/етц) записей пробовал завернуть в транзакцию? Если да и лучше не стало, то, может, БД не затюнена по настройкам. Или пора чанки в параллель обрабатывать/сохранять.

Comments

[fman42f]

Не, всю пачку из 30к одним insert вставлял. Через транзакцию нет, не пробовал. Гляну

[Ипатьев]

а разве saveMany не использует внутри транзакцию?

[JhaoDa]

Ипатьев, ты можешь это легко узнать, заглянув в код.

[Ипатьев]

ну так не я же рекомендую использовать транзакцию там, где она уже используется.

[JhaoDa]

Ипатьев, т.е. ты гарантируешь, что она там используется?

[Ипатьев]

ахаха не использует.
Всё-таки ларавля не устает удивлять.
Как, КАК можно не использовать транзакцию для записи множества строк?
Загадка.

[JhaoDa]

Ипатьев, а ORM вот прям должна её использовать?

[Дмитрий]

Ипатьев, а что тут удивительного? такие вещи на откуп программистов. я вполне могу хотеть что бы у меня данные вставлялись не в рамках одной транзакции.

[Vitsliputsli]

И на кой ему еще и транзакция, если один insert проходит долго?

[Дмитрий]

Vitsliputsli, потому что по умолчанию в mysql innodb autocommit = 1 и после каждого insert идет commit, а это накладные расходы, и оборачивание в одну транзакцию эти расходы снижает.

[Vitsliputsli]

Дмитрий, так то да, если бы коммитились после каждой записи, но при чанке в 30к скорее больше будет накладных расходов на поддержание snapshot. И чем активнее используется таблица, тем дольше будут блокиовки. Тут скорее может немного улучшить ситуацию уменьшение размера чанка.

[Дмитрий]

Vitsliputsli, ну легко проверить - мне лень что то писать, по этому сделаем проще:

делаем дамп с балк инсертом
mysqldump -u root data > /tmp/bulk.sql

делаем дамп с автокоммитом и кучей insert
mysqldump --extended-insert=FALSE -u root data > /tmp/no_bulk_no_commit.sql

делаем дамп без автокоммита и кучей insert
mysqldump --extended-insert=FALSE --no-autocommit -u root data > /tmp/no_bulk_commit.sql

ls -lah /tmp/ | grep sql
-rw-r--r--  1 vagrant vagrant 2.8M May 12 07:41 bulk.sql
-rw-r--r--  1 vagrant vagrant 3.9M May 12 07:39 no_bulk_commit.sql
-rw-r--r--  1 vagrant vagrant 3.9M May 12 07:36 no_bulk_no_commit.sql

time mysql -u root temp < /tmp/bulk.sql 
real	0m2.375s
user	0m0.035s
sys	0m0.008s

time mysql -u root temp < /tmp/no_bulk_no_commit.sql 

real	1m11.508s
user	0m0.341s
sys	0m0.586s

time mysql -u root temp < /tmp/no_bulk_commit.sql 

real	1m0.423s
user	0m0.427s
sys	0m0.589s

у меня выигрыш в 11 секунд.

[Vitsliputsli]

Дмитрий, как выше и писал, "если бы коммитились после каждой записи," то да, выигрыш будет. Но автор же 30к одним запросом добавляет? Или нет?

[Дмитрий]

Vitsliputsli, saveMany тупо работает через цикл и херачит отдельными insert. И не важно чанк какого размера вы туда передаете - это банальный цикл и это ровно коммит после каждого insert

/**
     * Save an array of new models and attach them to the parent model.
     *
     * @param  \Illuminate\Support\Collection|array  $models
     * @param  array  $pivotAttributes
     * @return array
     */
    public function saveMany($models, array $pivotAttributes = [])
    {
        foreach ($models as $key => $model) {
            $this->save($model, (array) ($pivotAttributes[$key] ?? []), false);
        }

        $this->touchIfTouching();

        return $models;
    }

[Vitsliputsli]

Дмитрий, вроде как автор писал, что пробовал отдельным insert слать без ORM. Если я его не правильно понял, тогда извиняюсь, тогда резон есть.
Хотя лучше, конечно, все равно одним insert, даже если включен prepared statements.

Answer 3

[Дмитрий]

В данный конкретный вариант у вас похоже реализован самый медленный вариант вставки - а именно куча отдельных insert, с коммитом после каждой, ибо никакой магии в saveMany нету - в цикле обходим массив и по одному вставляем.

1. Использовать номальный bulk insert. Это вроде как делается просто

Model::insert([
  [row1], [row2], [row3], [row4], 
]);

тогда на выходе вы должны получить запрос вида insert into table() values(row1), (row2), (row3);

2. Обернуть в транзакцию как советовал JhaoDa . Это хуже чем первый вариант, но лучше вашего.

Comments

[fman42f]

Я вчера сам JhaoDa ввел в заблуждение. У меня опечатка была в коде, не заметил вечером. Вставка пачками давала результат значимый. Щас потестил вариант с вставкой по 6к за секунд 6-7 заполняется база из 160к записей

В любом случае, это выглядит тоже как решение. Лично, предполагаю, пойду по варианту от mayton2019

[Дмитрий]

fman42f, попробуйте сначала вариант с bulk insert. оно должно быть еще быстрее, не взлетит - попробуете варианты с LOAD DATA

Answer 4

[Stalker_RED]

LOAD DATA... INFILE 'myfile.csv'...
https://dev.mysql.com/doc/refman/8.0/en/load-data.html