Какой «длины» может быть INSERT? Как импортировать 1 млн строк?

Question

[Александр]

Привет!

Необходимо регулярно парсить большой xml-файл на 1.5 млн строк (2ГБ). Я его благополучно "разбираю" за 20 минут, получаю все строки в массив. И теперь необходимо заполнить таблицу в БД.

Сейчас делаю инсерты циклом по 2000 (число выбрал на угад) строк за раз:

INSERT INTO tbl (field1, field2) VALUES ('1', '2'),('3', '4'),('5', '6') ...

Получается 750 итераций цикла. Занимает этот весь процесс 5 часов. Необходимо уложиться в хотя бы 1-2 часа.

Подскажите в какую сторону копать? Можно ли сделать один инсерт сразу на 1.5 миллиона строк?

Что лучше: больше итераций цикла, на более "коротких" инсертах или наоборот?

P.S. Сервер - 4ГБ ОЗУ, 2 ядра.

Спасибо!

Answer 1

[romy4]

индексы выключайте перед вставкой. 1млн — это мало. вы не там ищите

Comments

[Александр]

Выключил индексы. Увеличил "длину" инсерта с 2 до 20 тысяч строк за одну итерацию цикла. Это позволило сократить общее время с 5 до 2.5 часов. Что еще оптимизировать - непонятно.

[romy4]

Александр: > Можно ли сделать один инсерт сразу на 1.5 миллиона строк?
можно. есть настройка максимального размера запроса. (найдите, я не помню)

[romy4]

Кандрашкин Алексей: конечно, в размере запроса надо учитывать конфигурацию железа сервера, настройку буфферов и т.д.. но это такие тонкости, которые приходят с опытом. а вникать в конкретную конфигурацию на этом форуме вряд ли кто-то будет. я так точно нет.

Answer 2

[Sumor]

Прочитайте раздел документации и выполните рекомендации
optimizing-innodb-bulk-data-loading

Нужно максимально отключить дополнительную работу БД: автоинкременты, индексы, триггеры, констрейнты, если есть возможность загружать без транзакций (Bulk load).
Может будет лучше загрузить во временную таблицу в памяти через load xml, а затем вставить в свою таблицу ерез insert into ... select ...

Comments

[Александр]

Спасибо за ссылку, почитаю. По поводу второй идеи - подскажите, я пробовал load xml - но у меня структура xml-файла немного не совпадает с необходимой структурой таблицы (в файле есть теги без имени: url1url2url3 - и не получается их в БД корректно вставить). И еще вопрос, если делать insert into ... select - это будет быстрее, чем текущее мое решение?

[Александр]

побились теги: < photo >url1< /photo > ....

[Sumor]

Сравнение insert ... values и select лучше делать на практике.
Теоретически при обработке values должен отработать синтаксический анализатор по всему запросу, а в случае select просто перекладываются уже подготовленные значения.

Answer 3

[Макс]

я недавно столкнулся с задачей загрузить 45млн строк из файла с инсертами, грузил в AWS RDS. Напрямую ориентировочное время загрузки было около 2 суток. после конвертации sed-ом в csv LOAD DATA INFILE отработал за 20 минут. Оказывается, так МНОГО быстрее.
я это к чему... если структура не совпадает - структуру можно создать на этапе парсинга. Или загрузить как есть во временную таблицу, а потом уже сделать INSERT SELECT FROM

Comments

[Александр Сисюкин]

Тем же самым методом гружу свои данные 2 мил. строк залитают за 10 сек. Интересно что за xml такое что 20 мин парсит... 1.5мил строк хмл и так долго

Answer 4

[Rsa97]

Если структура XML совпадает, со структурой базы, то LOAD XML
Если же нет, то можете попробовать перед вставкой отключить все индексы, после вставки включить их обратно.

Comments

[Александр]

Пробовал это решение, к сожалению структура не совпадает. С индексами сейчас попробую, спасибо!