Загрузка большого файла на сервер и импорт его в БД

[SvS]

Загрузка большого файла на сервер и импорт его в БД

Описание проблемы:
Требуется импортировать файл достаточно большого размера (от 2Мб) в БД MySQL.
Файл формата CVS. С помощью скрипта файл загружается на сервер, затем функцией fgetcsv разбивается построчно, далее запросом INSERT INTO ... каждая строка вставляется в таблицу БД.

Проблема сосотоит в том, что на хостинге переменная max_execution_time = 30 (Максимальное возможное время выполнения сценария в секундах). А скрипт, естественно, выполняется гораздо дольше, и вследствие этого вылезает ошибка 504 Gateway Timeout.

Кроме того на сервере также установлена переменная upload_max_filesize = 2M (Макс. размер файла, который можно закачать). А файлы для закачки обычно больше.

Как обойти эти ограничения? Насчет второго, - в принципе можно загружать файлы через ФТП, хоть это и не очень удобно. А вот с первым все гораздо сложнее.. У кого какие идеи?

 

[Wicked]

Вообще имхо на чистые инсерты 30 секунд вполне должно хватать.

Если не хватает - оптимизировать. http://dev.mysql.com/doc/refman/4.1/en/insert-speed.html

 

[.des.]

самый быстрый способ LOAD DATA INFILE

 

[Dreammaker]

.des., единственный момент, что многие хостеры его не любят и параноидально боятся..

а так действительно влетает мгновенно.

 

[SvS]

Автор оригинала: Wicked
Вообще имхо на чистые инсерты 30 секунд вполне должно хватать.

В файле 2.5Мб помещается 41 тыс. строк... Мне кажется, может и не хватить...

самый быстрый способ LOAD DATA INFILE

Согласен, но он не подходит, потому что данные приходится изменять после считывания но перед добавлением в БД.

Вот кусок кода, который за это отвечает.. Да вспомнил.. Тут еще 1 инсерт и селект есть... Также некоторые данные изменяются.. На это тоже время уходит..
Естественно код не оптимизирован. Хотя опять же, оптимизация его врядли поможет в 30 сек. уложится, имхо.

while (($data=fgetcsv($file, 5000, "\t", "\r\n"))!=FALSE) {
    // Если строка имеет правильное кол-во полей
    if (count($data)==7) {
       if ($i!=0 && $i!=1) {
         // делим 1 ячейку на 3 поля..
         $razmery=array();
         $razmery=explode("x", $data[3]);
         
         $data[1]=str_replace(',', '.', $data[1]);

         $database->setQuery("INSERT INTO tovar(id, short_naim, price, massa, length, width, height, big_photo, small_photo)
                            VALUES(null, '$data[0]', $data[1], '$data[2]', '$razmery[0]', '$razmery[1]', '$razmery[2]',
                            '$data[4]', '$data[4]')");
         $database->query();
         	echo mysql_error();
         $tovar_inserted_id=mysql_insert_id();

         // Заносим в таблицу соответствия групп и товаров
         // Находим ИД группы
         $database->setQuery("SELECT id FROM grup WHERE label='$data[6]' LIMIT 1");
         $id_grup=$database->loadResult();
         	echo mysql_error();
         if (!$id_grup) $id_grup=0;

         $database->setQuery("INSERT INTO body_grup(id, id_grup, id_tovar, hit, no_pos)
                              VALUES(null, $id_grup, $tovar_inserted_id, 0, 0)");
         $database->query();
         echo mysql_error();
		   }

     } // if count==7
		   $i++;
	}

Как думаете, может такой вариант подойдет:
Файл заливается по ФТП, затем выполняется скрипт, который делит файл допустим на десяток файлов, а далее выполняется скрипт, который заносит в БД данные из каждого файла...

 

[Wicked]

Ну первое, что нужно сделать, это провести профайлинг. Т.е. замерить, сколько времени выполняются отдельные его части. Например, части можно сделать такими:
1) получение очередной "строки" из csv
2) разбивка полей + первый инсерт + mysql_insert_id()
3) селект
4) второй инсерт

А теперь общие рекомендации:
1) сделай, чтобы echo mysql_error() выполнялось только в случае ошибки.
2) можно ли сначала (да цикла while) получить все группы товаров в массив:
array('motherboards' => 9, 'cpu' => 12, 'ram' => 13, ...) ? Тогда селекты делать не нужно.
3) для второго инсерта можно использовать multiple value insert
4) я бы попробовал еще сделать так, но кому-то такой метод может не понравиться:
В таблице `tovar`создается поле label для $data[6]. В него multiple value insert'ом вставляются все товары. При этом нам не нужны их ID. Чтобы заполнить `body_grup`, делается

[sql]insert into `body_grup` (`id_grup`, `id_tovar`, `hit`, `no_pos`) select distinctrow `grup`.id, `tovar`.`id`, 0, 0 from `tovar` inner join `grup` on (`tovar`.`label` = `grup`.`label`) where not isnull(`tovar`.`label`);
update `tovar` set `tovar`.`label` = null;[/sql]

PS: естественно, нужно позаботиться, чтобы select в insert select'е был оптимальным (имхо, нужно создать индексы: двойной на (`tovar`.`label`, `tovar`.`id`), и другой двойной на (`grup`.`label`, `grup`.`id`))

 

[SvS]

Спасибо за рекомендации.. Обязательно попробую и сообщу о результатах.. А что думаете о таком варианте?

Файл заливается по ФТП, затем выполняется скрипт, который делит файл допустим на десяток файлов, а далее выполняется скрипт, который заносит в БД данные из каждого файла...

 

[Wicked]

ничего хорошего я бы оставил на самый последний случай, если уж ничего другого не поможет.

 

[Kelkos]

>>Как обойти эти ограничения?
ээ.. ммм.. а может просто ini_set ("max_execution_time", 300); а?

 

[dr-sm]

как я понял set_time_limit не работает (safe mode)?

вкурсе что можено сгенерить один жирный инсерт типа:

insert into xz(xz1,xz2)values('xz1','xz2'),('xz1','xz2'),('xz11','xz22'),('xz111','xz222')

 

[Dreammaker]

>Согласен, но он не подходит, потому что данные приходится изменять после считывания но перед добавлением в БД.

Никто не мешает построить работу так, чтобы данные изменялсиь после добавления в БД, силами уже самой БД.

 

[SvS]

вкурсе что можено сгенерить один жирный инсерт типа:

insert into xz(xz1,xz2)values('xz1','xz2'),('xz1','xz2'),('xz11','xz22'),('xz111','xz222')

для второго инсерта можно использовать multiple value insert

Какой длины может быть эта строка с инсертом? Можно ли в одну строку запихнуть 40 тыс. инсертов??

ээ.. ммм.. а может просто ini_set ("max_execution_time", 300); а?

Да, это работает. Теперь осталось оптимизировать код..
Из оптимизации сделал, чтобы mysql_error() выводился только в случае ошибки, также расставил индексы по совету Wicked.
Обработка файла в 1500 строк занимает 20 секунд. Значит обработка 40 тыс.строк - около 530 сек. Почти 10 минут.. многовато...

Никто не мешает построить работу так, чтобы данные изменялсиь после добавления в БД, силами уже самой БД.

Пожалуй придется переделывать БД таким образом, чтобы работало LOAD DATA INFILE, тогда данные вообще не надо будет изменять.

-~{}~ 21.04.06 19:31:

единственный момент, что многие хостеры его не любят и параноидально боятся.

Действительно... LOAD DATA INFILE запрещен на моем хостинге Придется оптимизировать скрипт... С удовольствием выслушаю Ваши замечания по оптимизации...

 

[dr-sm]

Автор оригинала: SvS
Какой длины может быть эта строка с инсертом? Можно ли в одну строку запихнуть 40 тыс. инсертов??

я не вкурсе, RTFM типа , но не обязательно же сразу 40к, пробуй.

так если у тебя работает ini_set ("max_execution_time" , 300), то почему бы не вызывать просто set_time_limit(300) в цикле.
она же сбрасывает текущее значение таймера, тогда вопрос времени отпадает сам собой.

сделай поле для data[6] в таблице товар (см совет №4 от Wicked) - это основная оптимизация. тогда будет один только инсерт в цикле, и если его делать большими кусками (штук по 100-1000), то по идее должно влетать быстро.
индексы создавай после того как данные зальешь в товар, а не сразу.

 

[Wicked]

SvS
засунуть 40000 в один инсерт скорее всего не получится. Я для себя писал класс, который сам наполняет запрос, и флашит его с нужной периодичностью по достижении длины запроса величины max_allowed_packet.

-~{}~ 21.04.06 19:44:

Только вот иногда, при близких к max_allowed_packet значениях, коннекшн разрывался. Лучше ограничиться максимальным размером 32/64/128КБ

 

[SvS]

Пока сделал первый "жирный" инсерт с 1000-ю рядами в каждом запросе... И о чудо!!! за 7 секунд всё загрузилось в БД - все 40000 строк!!!! Попробую теперь сделать второй инсерт и селект по вашим советам...

-~{}~ 24.04.06 16:17:

сделай поле для data[6] в таблице товар (см совет №4 от Wicked) - это основная оптимизация.

4) я бы попробовал еще сделать так, но кому-то такой метод может не понравиться:
В таблице `tovar`создается поле label для $data[6]. В него multiple value insert'ом вставляются все товары. При этом нам не нужны их ID. Чтобы заполнить `body_grup`, делается

Сделал все это.. Всё отлично работает.. Файл 2.5М грузится за 10 сек. При том, что индексы создал до заливки данных..
Всем ОГРОМНОЕ СПАСИБО!!!