У меня есть приложение, которое должно запускать ежедневный скрипт; ежедневный скрипт состоит в загрузке CSV-файла с 1 000 000 строк и вставка этих строк в таблицу.
Я размещаю свое приложение в Dreamhost. Я создал цикл while, который проходит через все строки CSV и выполняет запрос INSERT для каждого из них. Дело в том, что я получаю «500 Internal Server Error». Даже если я разбиваю его на 1000 файлов с 1000 строк каждый, я не могу вставить более 40 или 50 тысяч строк в одном цикле.
Есть ли способ, которым я мог бы оптимизировать ввод? Я также рассматриваю возможность работы с выделенным сервером; что вы думаете?
Благодаря!
Pedro
Большинство баз данных имеют оптимизированный процесс вставки в массив – MySQL – это синтаксис LOAD DATA FILE .
Чтобы загрузить файл CSV, используйте:
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; Вставьте несколько значений вместо
insert into table values(1,2);
делать
insert into table values (1,2),(2,3),(4,5);
До соответствующего количества строк за раз.
Или массовый импорт, который является наиболее эффективным способом загрузки данных, см.
Обычно я бы сказал, просто используйте LOAD DATA INFILE, но, похоже, вы не можете использовать свою среду совместного размещения.
Я не использовал MySQL через несколько лет, но у них очень хороший документ, в котором описывается, как ускорить вставки для массовых вставок: http://dev.mysql.com/doc/refman/5.0/en/insert-speed .html
Несколько идей, которые можно почерпнуть из этого:
Отключить / включить ключи вокруг вставок:
ALTER TABLE tbl_name DISABLE KEYS; ALTER TABLE tbl_name ENABLE KEYS;
Используйте множество значений в ваших инструкциях вставки.
Т.е.: INSERT INTO table (col1, col2) VALUES (val1, val2), (.., ..), …
Если я правильно помню, вы можете иметь до 4096 значений для инструкции вставки.
Запустите команду FLUSH TABLES, прежде чем вы начнете, чтобы убедиться, что нет ожидающих записи на диске, которые могут повредить вашу производительность вставки.
Я думаю, что все будет быстро. Я бы предложил использовать LOCK TABLES, но я думаю, что отключение клавиш делает это спорным.
ОБНОВИТЬ
После этого я понял, что отключив ваши ключи, вы можете удалить проверки согласованности, которые важны для загрузки файла. Вы можете исправить это:
Вы можете создать скрипт cronjob, который добавляет x записей в базу данных по одному запросу. Сценарий Cronjob проверяет, не добавил ли последний импорт все нужные строки, он принимает еще одну строку x.
Таким образом, вы можете добавить столько строк, сколько потребуется.
Если у вас есть выделенный сервер, это проще. Вы просто запускаете цикл со всеми запросами на вставку.
Конечно, вы можете попытаться установить time_limit на 0 (если он работает на dreamhost) или сделать его больше.
Ваш PHP-скрипт, скорее всего, будет завершен, потому что он превысил срок действия скрипта. Поскольку вы находитесь на общем хосте, вам не повезло.
Если вы переключитесь на выделенный сервер, и если вы получите доступ к оболочке, лучшим способом будет использование средства командной строки mysql для вставки данных.
Предложение OMG Ponies замечательно, но я также «вручную» отформатировал данные в том же формате, который использует mysqldump, а затем загрузил его таким образом. Очень быстро.
Вы пробовали делать транзакции? Просто отправьте команду BEGIN в MySQL, сделайте все свои вставки, затем COMMIT . Это значительно ускорило бы его, но, как сказал Касабланка, ваш сценарий, вероятно, также устарел.
Я столкнулся с этой проблемой раньше, и nos в значительной степени справились с ней по голове, но вам нужно сделать немного больше, чтобы заставить ее работать лучше.
Я обнаружил, что в моей ситуации я не мог заставить MySQL принять один большой оператор INSERT, но обнаружил, что если я разложил его на группы из примерно 10 тыс. INSERTS в то время, например, как предлагалось, то это будет довольно быстро. Следует отметить, что при выполнении нескольких INSERT, подобных вам, вы, скорее всего, ударите ограничение времени ожидания PHP, но этого можно избежать, сбросив timout с помощью set_time_limit ($ seconds) , я обнаружил, что выполнение этого после каждого успешного INSERT работает очень хорошо ,
Вы должны быть осторожны в этом, потому что вы можете оказаться в цикле на случай аварии с неограниченным сроком, и для этого я бы предложил тестирование, чтобы убедиться, что каждый INSERT был успешным, либо проверяя ошибки, сообщаемые MySQL с помощью mysql_errno () или mysql_error () . Вы также можете ловить ошибки, проверяя количество строк, затронутых INSERT, с помощью mysql_affected_rows () . Затем вы можете остановиться после первой ошибки.
Было бы лучше, если бы вы использовали sqlloader. Вам понадобится первый файл управления, который определяет действия, которые должен выполнять SQL Loader, и второй файл csv, который вы хотите загрузить. Ниже приведена ссылка, которая поможет вам. http://www.oracle-dba-online.com/sql_loader.htm
Перейдите в phpmyadmin и выберите таблицу, в которую вы хотите вставить.
На вкладке «Операции», а затем в опции / разделе «Параметры таблицы» измените механизм хранения от InnoDB до MyISAM.
У меня когда-то была аналогичная задача. Хорошо тебе провести время.