вСЕМ доброго времени
вобщем сегодня вспомнил вопрос, который мне задавали на собеседовании при приеме на работу.
и так вопрос из сабжа: как вставить 72 миллиона записей в таблицу.
Не знаю в нужном ли форуме опубликовал тему. но вроде приближенно
так как я за пол года не сталкивался с такими задачками. может есть мысли у знающих
мысль 1:
проблемы:
длительное время выполнения. для выполнения нужно отключать максимальное время выполнения скрипта. А если дело обстоит на хостинге, запрещающем такие вещи?
мысль 2:
может есть способ написать хранимую процедуру или чтото типа того чтобы СУБД сама выполнила задание без помощи пхп.
формализуя задачу - значимость данных для этой задачи не предусмотрена, поэтому пусть есть таблица tbl: id - автоинкремент, data
нужно вставить данные в data числа от 1 до 72*10e6
и так, господа знатоки. есть решения?
1. Panoptik - 30 Марта, 2012 - 23:38:01 - перейти к сообщению
2. altermann - 31 Марта, 2012 - 00:10:17 - перейти к сообщению
Поставить неограниченное время выполнения скрипта.
Добавить больше памяти для маневров.
Собирать многострочный инсерт с некоторым ограничением строк, допустим по 500 или более на сколько не жалко памяти.
Вставить в базу.
Почистить переменные.
Повторить.
Если кого-то осенило это делать через браузер, то нужно чтоб скрипт не прекращал работу если браузеру надоело ждать ответа ignore_user_abort = On.
Как обойти настройки хостера точно не знаю, можно попробовать расспараллеливание скрипта типа pcntl_fork.
Добавить больше памяти для маневров.
Собирать многострочный инсерт с некоторым ограничением строк, допустим по 500 или более на сколько не жалко памяти.
Вставить в базу.
Почистить переменные.
Повторить.
Если кого-то осенило это делать через браузер, то нужно чтоб скрипт не прекращал работу если браузеру надоело ждать ответа ignore_user_abort = On.
Как обойти настройки хостера точно не знаю, можно попробовать расспараллеливание скрипта типа pcntl_fork.
3. Panoptik - 31 Марта, 2012 - 00:16:05 - перейти к сообщению
а что вы подразумевали под "выделить побольше памяти" это на какие такие маневры? сформировать запрос, размером в 70Мб?)
4. DlTA - 31 Марта, 2012 - 00:28:47 - перейти к сообщению
можно воспользоваться дампером, они нынче даже из экселя(/csv) умеют данные читать
но честно говоря виртуальный хост от длительной нагрузки по любому падает, особенно если объем каждой записи неприлично большой. приходится делать с остановками (пока сервер баз данных сходит на переку))
но в любом случае процесс происходит за множественное количество обращений.
пробовал как то на локальных хост производить добавление в таблицу 10 000 записей путем чтения "ручками" данных из файла и выполнения запроса на добавление
по непонятным мне причинам навернулась база.
но честно говоря виртуальный хост от длительной нагрузки по любому падает, особенно если объем каждой записи неприлично большой. приходится делать с остановками (пока сервер баз данных сходит на переку))
но в любом случае процесс происходит за множественное количество обращений.
пробовал как то на локальных хост производить добавление в таблицу 10 000 записей путем чтения "ручками" данных из файла и выполнения запроса на добавление
по непонятным мне причинам навернулась база.
5. Panoptik - 31 Марта, 2012 - 00:33:29 - перейти к сообщению
самое интересное что мне задали этот вопрос на собеседовании и я до сих пор не вижу нормального решения кроме как поставить безлимитное время выполнения скрипта
если речь идет о вставке через цикл на php
если речь идет о вставке через цикл на php
6. Okula - 31 Марта, 2012 - 01:08:26 - перейти к сообщению
Порциями выполнять запросы.
Если через хостинг - тек же порциями, но что бы не обременять пользователя обновлением скрипта выставить крон задание.
Если есть доступ SSH то тогда всё намного упрощается.
Если через хостинг - тек же порциями, но что бы не обременять пользователя обновлением скрипта выставить крон задание.
Если есть доступ SSH то тогда всё намного упрощается.
7. Panoptik - 31 Марта, 2012 - 01:14:42 - перейти к сообщению
спасибо. вариант с кроном мне понравился. вроде как самый красивый
8. altermann - 31 Марта, 2012 - 10:19:38 - перейти к сообщению
Panoptik пишет:
а что вы подразумевали под "выделить побольше памяти" это на какие такие маневры? сформировать запрос, размером в 70Мб?)
Да, пусть не 70М, но и не по одной строке вставлять.
Panoptik пишет:
спасибо. вариант с кроном мне понравился. вроде как самый красивый
Я подозреваю что он единственный
9. EuGen - 31 Марта, 2012 - 11:16:20 - перейти к сообщению
Собеседование? Подозреваю, что имелось ввиду:
LOAD DATA INFILE
(это в любом случае наиболее быстрый способ)
LOAD DATA INFILE
(это в любом случае наиболее быстрый способ)
10. roxoman - 20 Октября, 2015 - 21:57:17 - перейти к сообщению
Если еще интересно, то у меня задача была такая - заливать в базу гигантские прайс-листы, загружаемые пользователями на сервер в формате 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 - быстрее никак.
Загружаем файл csv на сервер естественно, создаем еще один временный файл csv, затем начинаем читать загруженный файл функцией fgetcsv построчно - разбираем строку, оставляем то, что нужно, и записываем это дело в массив. Массив конечно растет с каждой итерацией, а выделять на эту процедуру несколько гигабайт памяти мы не можем, так как одновременно могут грузиться десятки прайс-листов разными людьми, а у нас VDS за 1000 руб. в месяц. Поэтому пользуемся функцией memory_get_usage - она поможет нам определить, что мы пододвигаемся к пределу выделяемой на скрипт памяти - как только пододвинулись сбрасываем данные из массива во временный CSV-файл, который мы создали вначале. Таким методом обходим весь загруженный файл.
После обхода у нас получился новый файл CSV с уже обработанными данными, готовыми к заливке в базу. И тут нам на помощь приходит LOAD DATA INFILE - это самый быстрый способ загрузить данные в базу.
В итоге у нас вот что: памяти хавает скрипт столько, сколько Вы укажите - хоть 12 Мб, скорость при этом отличная - прайс-лист с миллионом строк загружается в базу за пару минут (над данными еще множество сложных обработок производится по ходу разбора), причем при увеличении объема скорость растет именно в арифметической прогрессии, то есть 10 миллионов строк - будет 20 минут, а 72 миллиона - около 2-х часов (но опять-таки, если данные никак не обрабатывать, а просто заливать как есть, то возможно, и часа с головой хватит).
Если в чем-то не прав - поправьте, так как мне самому актуальна эта тема, потому что проект этот работает, и если есть варианты улучшить этот процесс, то готов выслушать.
Во всяком случае за полгода постоянной битвы над этой задачей я пришел именно к такому варианту.
Естественно это пример рабочей программы, а если Вам задавали конкретный вопрос "КАК?" и хотели услышать короткий ответ, то это, как писали выше, LOAD DATA INFILE - быстрее никак.