MYSQL Insert — импорт json файла с большим количеством данных — как?

Question

[Fyodor]

Продолжая тему - импортирую в базу MySQL данные получаемые по API от партнера.
Данные он отдает в формате JSON. Данные - информация по отелям в конкретном городе - полные- название, адрес, ссылки на фотографии и т.д.

И вот допустим беру Москву - в json для импорта 1301 отелей, 32 тысячи записей о фотографиях.

И вот не могу эту информацию в базу занести - не хватает мощности что ли, или настройки какие не те где-то.

Импорт делаю на VPS
Xeon 2600 series, 1 ядро
1024 Мб RAM
32 Гб SSD

Импорт следующий
api удаленное, получаю файл через file_get_contents, далее json_decode
и дальше циклом for прохожусь по массиву полученному.

В каждой итерации - insert на добавление отеля
потом еще несколько insert для опций отеля в следующие отдельные таблицы и самый большой -
insert с множеством строк для фотографий.

Так вот - сервер похоже не вывозит выпадая с 500 ошибкой. график нагрузки до 97% растет - подозреваю что из-за этого как раз и поэтому обрубается все.

Сервер настраивал сам - вернее развернул VESTA, для php лимит времени до 120 увеличил и конфиг mysql крутил.

В общем как быть? Это только Москва, а городов больше 30тыс. ясно что во многих городах отелей много меньше, но все равно крупных городов хватает.

Answer 1

[Anton Kuzmichev]

Из личного опыта:
- "сервер похоже не вывозит выпадая с 500 ошибкой" - а лог ошибок на что? Там все написано, почему падает.
- самое страшное - делать запросы на изменение данных вне явно открытой транзакции в транзакционную таблицу (InnoDB). set autocommit=0, стартуете транзакцию и можно циклом вставлять данные. Неявная транзакция на каждый запрос замедляет работу раз в 100-200.
- вариант @zelenin'а c пакетным запросом - вообще не пробовал, но может быть хорошей альтернативой явным транзакциям.
- нет никакой нужды выполнять такое через веб, и иметь нюансы, с ним связанные - таймлимиты php и самого веб-сервера. Выполняйте импорт, запуская скрипт из командной строки. Я так демонов делал, работали по принципу "запустил и забыл", но это уже совсем другая история.
Вообще ~1300 записей о отелях и 32000 фотки (чисто url'ы с описаниями, наверно?) - не так много данных, скорее всего узкое место у вас - именно передача данных из PHP в MYSQL.

Comments

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

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

[Fyodor]

@Assargin в логе нет об этом, видимо как раз потому как по таймлимиту отрубается
про транзакции пока для меня вообще новое что-то. нужно почитать.
- пакетный запрос первым делом попробую, так как это самое простое в реализации.
- я пока отлаживаю, так мне удобнее пока и нагляднее что ли - через веб. потом скрипты обновления через крон дергать буду, но нужно чтобы они работали нормально. а то один город из 30тыс обрабатывать под завязку загружая сервак и в 120секунд не укладываясь - за месяц обновление всей базы не сделать.
- вот мне тоже непонятно было. допустим Sypex Dumper справлется же с таким объемом данных только так, а тут вот копаюсь как что.

[Fyodor]

@zelenin ваш вариант самым первым буду пробовать как написал выше.

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

@Richard_Ferlow насколько помню Sypex Dumper аяксом выполняет запросы по кусочкам

[Fyodor]

@zelenin все равно как, он всю базу может отработать и еще и сжать ее и не падает ничего - соответственно я сделал вывод что не в мощности сервера вероятнее всего у меня проблемы =)

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

@Richard_Ferlow не в мощности, а в не оптимизированности процесса. Sypex Dumper оптимизирует аяксом. Сервер не может лечь, т.к. не кончается тайм-лимит.

Answer 2

[portfelio]

Если абстрагироваться от возможных недочетов в области правильной настройки софта, то правильнее было бы json превращать в sql-файл и потом загружать его в mysql через dev.mysql.com/doc/refman/5.1/en/load-data.html

Comments

[Fyodor]

Спасибо, ценнее всего советы как делать правильно. Сейчас изучу вопрос.

Answer 3

[hOtRush]

500 ошибка - это ошибка в коде может быть, какой текст ошибки?

я бы делегировал часть процессов другим скриптам. например можно использовать какой нибудь сервер очередей вроде gearman, и инсерты в базу отправлять в background задание, а скрипту оставить только парсинг json. это растянет все во времени, зато снизит ресурсоемкость и увеличит стабильность.

плюс есть либы для "построчного" чтения json, может тоже поможет.

и какая версия пхп, может течет что-нибудь?

Comments

[Fyodor]

500 - Internal Server Error - в error log не записывается

PHP Version 5.4.26

я этот же скрипт для Новосибирска пробую - он "прожевывает", там в разы меньше данных.

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

[hOtRush]

тут необязательно дело в объеме данных. api может возвращать к примеру невалидные данные и ваш скрипт сыпется. уделите больше внимания валидации.
может 120 секунд мало, попробуйте set_time_limit(0) хотя бы ради интереса.

Answer 4

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

самая банальная ошибка - инсерты в цикле. В цикле нужно собирать 1 большой инсерт вида:
insert table (id,name,value) values (1,x,x),(2,x,x) и тд. А после цикла уже его выполнять. Если данных очень много, можно не одним инсертом вставлять данные, а несколькими (по 1-10т записей за раз).

Comments

[Fyodor]

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

[Fyodor]

@zelenin

[Fyodor]

@zelenin можно еще вопрос вам.

SELECT * FROM sd_hotellook_hotels WHERE cityId = '12153'; выполняется махом...

DELETE FROM sd_hotellook_hotels WHERE cityId = '12153';
- а удаление так грузит сервер по полной и вылетает по таймауту если через phpmydmin пробовать

на cityid index

QUICK пробовал добавить - лучше не стало

перед добавление инфо по отелю удаляю из базы запись о нем(как о самом так и фото для этого города так же по cityid)

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

@Richard_Ferlow если индекс есть, то мыслей нет - небольшой спец во внутреннем устройстве

Answer 5

[Сергей]

Память для php увеличивали ?

Comments

[Fyodor]

128M стоит - думаете мало?

[tushev]

А вы посмотрите, сколько памяти в пике кушает PHP при обработке 1000 тестовых записей. И сразу поймете, сколько вам надо памяти для полноценной обработки.

[tushev]

Выделите скрипту импорта данных памяти побольше. А остальным скриптам оставьте более мелкое ограничение памяти.

[Fyodor]

@tushev не могли бы толкнуть в каком направлении посмотреть - как смотреть потребление памяти в пике?

[tushev]

В PHP memory_get_peak_usage(), memory_get_usage() - узнаете сколько памяти жрет скрипт.
И параметр memory_limit устанавливает ограничения на потребление памяти. Можно поставить нужное значение для каких нибудь отдельных особо жручих скриптов. Можно динамически менять через ini_set()

Answer 6

[tushev]

При массовой вставке или апдейте записей в MySQL очень сильное ускорение можно получить если завернуть пачку INSERT-ов или UPDATE-ов в транзакцию. Можно вообще вообще весь импорт сделать за одну транзакцию, можно разрезать на несколько более мелких транзакций.

Ну а вообще, разберитесь в каком месте у вас все падает или жрет слишком много времени и ресурсов. Разбейте задачу на несколько кусков, и выполните каждую по отдельности.
- чтение данных из удаленного источника
- json парсинг
- формирование SQL команд
- выполнение SQL команд

Тогда поймете, в каком именно месте у вас проблема.

Comments

[Fyodor]

Я с транзакциями не работал никогда - не могли бы пояснить что это и почему должно помочь? или имеется ввиду собрать все insert update в один запрос?

[tushev]

Если кратко, то делаете так
START TRANSACTION;
INSERT ...;
INSERT ...;
...
INSERT ...;
COMMIT;
При этом пачка инсертов выполнится за один раз целиком, или не выполнется вообще ничего (ибо атомарность).
Идея транзакции в том, что она НЕ может выполниться частично. Она выполнится или целиком, или вообще не выполнится.
Ну а в данном случае, ее побочный эффект, то что операции в рамках одной транзации могут выполниться с меньшими накладными расходами, чем если их выполнять по очереди.

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