Как сделать INSERT на 72 миллиона строк

вСЕМ доброго времени

вобщем сегодня вспомнил вопрос, который мне задавали на собеседовании при приеме на работу.
и так вопрос из сабжа: как вставить 72 миллиона записей в таблицу.

Не знаю в нужном ли форуме опубликовал тему. но вроде приближенно

так как я за пол года не сталкивался с такими задачками. может есть мысли у знающих
мысль 1:
проблемы:
длительное время выполнения. для выполнения нужно отключать максимальное время выполнения скрипта. А если дело обстоит на хостинге, запрещающем такие вещи?
мысль 2:
может есть способ написать хранимую процедуру или чтото типа того чтобы СУБД сама выполнила задание без помощи пхп.

формализуя задачу - значимость данных для этой задачи не предусмотрена, поэтому пусть есть таблица tbl: id - автоинкремент, data
нужно вставить данные в data числа от 1 до 72*10e6


и так, господа знатоки. есть решения?

-----
Just do it

Поставить неограниченное время выполнения скрипта.
Добавить больше памяти для маневров.
Собирать многострочный инсерт с некоторым ограничением строк, допустим по 500 или более на сколько не жалко памяти.
Вставить в базу.
Почистить переменные.
Повторить.

Если кого-то осенило это делать через браузер, то нужно чтоб скрипт не прекращал работу если браузеру надоело ждать ответа ignore_user_abort = On.
Как обойти настройки хостера точно не знаю, можно попробовать расспараллеливание скрипта типа pcntl_fork.

-----
Я прирожденный кузнец, я не могу не куя!

а что вы подразумевали под "выделить побольше памяти" это на какие такие маневры? сформировать запрос, размером в 70Мб?)

-----
Just do it

можно воспользоваться дампером, они нынче даже из экселя(/csv) умеют данные читать

но честно говоря виртуальный хост от длительной нагрузки по любому падает, особенно если объем каждой записи неприлично большой. приходится делать с остановками (пока сервер баз данных сходит на переку))

но в любом случае процесс происходит за множественное количество обращений.

пробовал как то на локальных хост производить добавление в таблицу 10 000 записей путем чтения "ручками" данных из файла и выполнения запроса на добавление
по непонятным мне причинам навернулась база.

самое интересное что мне задали этот вопрос на собеседовании и я до сих пор не вижу нормального решения кроме как поставить безлимитное время выполнения скрипта
если речь идет о вставке через цикл на php

-----
Just do it

Порциями выполнять запросы.
Если через хостинг - тек же порциями, но что бы не обременять пользователя обновлением скрипта выставить крон задание.
Если есть доступ SSH то тогда всё намного упрощается.

спасибо. вариант с кроном мне понравился. вроде как самый красивый

-----
Just do it

Да, пусть не 70М, но и не по одной строке вставлять.


Я подозреваю что он единственный

-----
Я прирожденный кузнец, я не могу не куя!

Собеседование? Подозреваю, что имелось ввиду:
LOAD DATA INFILE
(это в любом случае наиболее быстрый способ)

-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.

Если еще интересно, то у меня задача была такая - заливать в базу гигантские прайс-листы, загружаемые пользователями на сервер в формате CSV. Единственное условие - неограниченное время выполнения скрипта (хотя можно это обойти - немного коряво, и скорость естественно будет ниже раз в 50, но при большом желании можно работать и с ограничением в 30 секунд - здесь нам поможет обработка кусками и AJAX).

Загружаем файл csv на сервер естественно, создаем еще один временный файл csv, затем начинаем читать загруженный файл функцией fgetcsv построчно - разбираем строку, оставляем то, что нужно, и записываем это дело в массив. Массив конечно растет с каждой итерацией, а выделять на эту процедуру несколько гигабайт памяти мы не можем, так как одновременно могут грузиться десятки прайс-листов разными людьми, а у нас VDS за 1000 руб. в месяц. Поэтому пользуемся функцией memory_get_usage - она поможет нам определить, что мы пододвигаемся к пределу выделяемой на скрипт памяти - как только пододвинулись сбрасываем данные из массива во временный CSV-файл, который мы создали вначале. Таким методом обходим весь загруженный файл.
После обхода у нас получился новый файл CSV с уже обработанными данными, готовыми к заливке в базу. И тут нам на помощь приходит LOAD DATA INFILE - это самый быстрый способ загрузить данные в базу.
В итоге у нас вот что: памяти хавает скрипт столько, сколько Вы укажите - хоть 12 Мб, скорость при этом отличная - прайс-лист с миллионом строк загружается в базу за пару минут (над данными еще множество сложных обработок производится по ходу разбора), причем при увеличении объема скорость растет именно в арифметической прогрессии, то есть 10 миллионов строк - будет 20 минут, а 72 миллиона - около 2-х часов (но опять-таки, если данные никак не обрабатывать, а просто заливать как есть, то возможно, и часа с головой хватит).

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

Естественно это пример рабочей программы, а если Вам задавали конкретный вопрос "КАК?" и хотели услышать короткий ответ, то это, как писали выше, LOAD DATA INFILE - быстрее никак.

(Отредактировано автором: 20 Октября, 2015 - 22:04:11)