Как выполнять 200 000 запросов mysql insert в сутки?

#mysql

Есть необходимость выполнять 200 000 запросов mysql insert в сутки. Сейчас на это
уходит около 5 часов. Скрипт находится на VPS. Система ежедневно загружает строки из
CSV. Возможно ли ускорить процесс?
    

Ответы

Ответ 1

Зависит от того, как именно происходит процесс загрузки, однако обычно проблема медленной
вставки связана с индексами, констрейнтами и foreign key на таблицах. Базе нужно делать
дополнительные проверки для соблюдения всех этих ограничений. Также может помочь отключение
автокоммита (в случае с InnoDB), который будет писать в бинлог после каждого инсерта
чтобы обеспечить сохранность данных.

Общие рекомендации можно найти вот тут (InnoDB): https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

и вот тут (MyISAM): https://dev.mysql.com/doc/refman/5.5/en/optimizing-myisam-bulk-data-loading.html


Ответ 2

Подозреваю, что вы вставляете записи по одной.
Более быстрый способ — вставлять по много записей в одном insert

INSERT INTO table (col1,col2) VALUES (row11, row21), 
(row21, row22), ... (row1n, row2n);


Также хорошо отдельно сформировать sql файл, который затем выполнить используя souce.


Ответ 3

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

С помощью нехитрой математики можно понять, что скорость выполнения одного запроса
на INSERT у вас 90мс, или примерно 11 запросов на INSERT в секунду. Вообще INSERT очень
быстрая операция и может выполняться в среднем 0.2-1.0 мс, но не в вашем случае...

Давайте разберем, на что тратиться время при INSERT?


Подключение (или использование существующего соединения)
Посылка запроса на сервер
Синтаксический анализ запроса
Проверка ключей (если они есть)
Вставка записи
Вставка индексов
Закрытие


Так в чем же у Вас могут быть проблемы

1. Слабое аппаратное обеспечение или ошибки в нем

Если не вникать в структуру вашей базы данных, используемый движок, и т.д. можно
сказать, что у вас слабое аппаратное обеспечение, и если Вы хотите ускорить операцию
вставки вам нужен более мощный процессор, большее кол-во оперативной памяти и SSD диск
который имеет в разы большее IOPS. А возможно, раз это VPS, диск на который осуществляется
запись просто перегружен, проведите benchmark вашего железа в момент INSERT. Также
может быть сильная фрагментация на диске. 

2. Неправильно настроенная конфигурация базы данных

Далее, если с этим проблем нет, то значит у Вас плохо настроена конфигурация MySQL.
Обычно камнями преткновения в innoDB становятся следующие настройки


innodb_log_file_size
innodb_buffer_pool_size
innodb_flush_log_at_trx_commit
innodb_flush_method
log_bin and sync_binlog
innodb_buffer_pool_instances
innodb_write_io_threads 


Например innodb_log_file_size, хорошо влияет на производительность, но по умолчанию
его размер очень маленький.

А если данные можно перезапросить, то совсем необязательно напрягать диск постоянным
сбросом данных, поэтому можно использовать другое значение innodb_flush_log_at_trx_commit
(1 - безопаснее, 2 - быстрее)


  innodb_flush_log_at_trx_commit — жалуетесь, что InnoDB работает в 100
  раз медленнее MyISAM? Вероятно, Вы забыли про настройку
  innodb_flush_log_at_trx_commit. Значение по умолчанию «1» означает,
  что каждая UPDATE-транзакция (или аналогичная команда вне транзакции)
  должна сбрасывать буфер на диск, что достаточно ресурсоёмко.
  Большинство приложений, в особенности ранее использовавшие таблицы
  MyISAM, будут хорошо работать со значением «2» (т.е. «не сбрасывать
  буфер на диск, только в кэш ОС»). Лог, однако, всё равно будет
  сбрасываться на диск каждые 1-2 секунды, поэтому в случае аварии Вы
  потеряете максимум 1-2 секунды обновлений. Значение «0» повысит
  производительность, но Вы рискуете потерять данные даже при аварийной
  остановке mySQL-сервера, в то время как при установке значение
  innodb_flush_log_at_trx_commit в «2» Вы потеряете данные только при
  аварии всей операционной системы.


3. Сложная архитектура базы данных или большая таблица

Возможно у Вас огромная таблица и большое кол-во индексов или внешних ключей, из-за
которых при INSERT проверяется большое кол-во условий и обновляются файлы индексов.
Удалите все ненужные индексы. Возможно у Вас индексы, которые просто не помещается
в память и это означает, что InnoDB может при вставке подтягивает с диска данные постранично
в зависимости от записи и это очень медленно.

Возможно при больших обьемах данных Вам поможет партицирование.

Сложная и большая архитектура требует больших ресурсов для быстрой работы. 

4. Неправильное использование конструкций для вставки данных

Выполняете вставку по одному INSERT, да еще без транзакций и выполняется медленно?
А чего вы хотели? Думали будет быстро? Нет, большое кол-во данных нужно вставлять через
большой INSERT запрос или LOAD DATA INFILE. При сбрасывании на диск каждой операции,
последовательные INSERT будут выполняться очень медленно. Ускорить INSERT также можно
через какой-нибудь Handlersocket, это не даст ощутимого прироста, но уберет дополнительные
расходы на анализ запроса.

Перечислил основные причины. Без подробной информации о вашем сервере, конфигурации
и архитектуры больше не знаю, что еще сказать.

В любом случае нужно тестировать изменения конфигурации и архитектуры, вы можете
увеличить скорость вставки, но замедлить скорость поиска.