Вставка 10 000 записей в одной транзакции?

Есть приложение, которое генерирует определённую структуру данных и по завершении укладывает её в БД.

Запись в базу происходит одной транзакцией.

Транзакция содержит около 10 000 INSERT-запросов, данные занимают чуть более 2Мб.


Собственно вопрос: это вполне нормальная транзакция или же не стоит здесь её использовать? Может быть лучше производить запись данных в БД по мере их генерации, а в случае ошибки удалить всё что успел вставить?

UPD: На конкретном сервере такая транзакция успешно проходит. Суть вопроса сводится к нюансам, которые необходимо учесть при выполнении запроса такого объёма.

UPD: таблицы InnoDB
  • Вопрос задан
  • 9701 просмотр
Решения вопроса 1
StepanTomsk
@StepanTomsk
Agent_J

Повторно уточняю — 2Мб в транзакции это нестрашно, если она единична в один момент времени, если у вас одновременно 100 DML команд, то тогда надо «тюнить» InnoDB.

До версии 5.6.8 размер лога транзакций составлял суммарно 10Мб, в старших версиях 100Мб (Документация).

Вот три переменных которые влияют на файлы лога, изменять значения только на остановленном сервере и с удалением предыдущих файлов лога (вначале потренируйтесь на кошках):

innodb_log_files_in_group:

Число журналов в группе файла регистрации.. InnoDB обеспечивает ротацию журналов. Рекомендуется использовать значение 3.

innodb_log_file_size:

Размер каждого журнала в файле регистрации групп в мегабайтах. Разумные значения располагаются от 1M до размера буферного пула, определенного ниже. Чем больше значение, тем меньше будет число контрольных точек сброса данных в буферном пуле, что уменьшит медленный дисковый ввод-вывод. Но большие журналы также означают, что восстановление будет медленнее в случае аварийного отказа. Ограничение размера файла такое же, что и у файла данных.

innodb_log_buffer_size:

Размер буфера, который InnoDB использует, чтобы писать файлы регистрации на диск. Разумные значения располагаются от 1M до половины объединенного размера журналов. Большой буфер файлов регистрации позволяет большим транзакциям выполняться без необходимости писать файл регистрации на диск, пока транзакция не закончится. Таким образом, если Вы имеете большие транзакции, увеличение буфера файла регистрации уменьшит медленный дисковый ввод-вывод.
Ответ написан
Комментировать
Пригласить эксперта
Ответы на вопрос 6
StepanTomsk
@StepanTomsk
bdmalex

INSERT DELAYED работает только с MyISAM, MEMORY, ARCHIVE. В версии 5.6 это уже depricated, в версии 5.7 — исключён. Применение находило для отложенной записи от множества клиентов, не от одного. Минус инструкции — отсутствие гарантии записи и в повышенном потреблении ресурсов сервера т.к. на каждую отложенную запись открывался свой поток, который закрывался когда несколько потоков объединялись в единичную запись в таблицу. По сути, несколько delayed трансформировались в последовательный список обычных insert (это если говорить образно).

Agent_J

1) 2МБ в запросе для innodb «из коробки» вполне допустимый размер.

2) Для повышения производительности Вы можете использовать один INSERT с несколькими VALUES:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Документация поясняет что на запрос в условных единицах тратится (без учета коннекта):

Sending query to server: (2)
Parsing query: (2)
Inserting row: (1 × size of row)
Inserting indexes: (1 × number of indexes)

Отправку можно опустить, но 10000 INSERT разделённых «точкой с запятой» на уровне логики БД это 10000 отдельных запросов, для которых будет применяться парсер, причём не синтаксический а именно единичного запроса, который формирует команду из SQL в низкоуровневую команду хранилища, что дает 20к единиц и это при том что вставка данных потребует только 10к единиц.

3) В случае если есть возможность записать файл на диск с доступом чтения для сервера mysql то: LOAD DATA INFILE — прирост производительности в 20 раз (источник та же документация).
Ответ написан
script88
@script88
Транзакция это 1 атомарная единица, то есть, либо у Вас 10к запросов типа INSERT выполнятся, либо нет. Для этого и была создана транзакция.
Пример
START TRANSACTION;
INSERT INTO `ID` (`ID`) VALUES ('1');
INSERT INTO `ID` (`ID`) VALUES ('2');

INSERT INTO `ID` (`ID`) VALUES ('N');
COMMIT;

Если сервер не достаточно мощный + не оптимизированы как сам сервер так и БД, то это может привести к деградации системы.
Какой тип таблиц используете?
Ответ написан
StepanTomsk
@StepanTomsk
YourChief
почему бы не расширить bulk_insert_buffer_size и не вставлять единственным инсёртом?

1) bulk_insert_buffer_size относится к MyISAM а не к InnoDB!
2) Приложение работает с БД через ORM, многие (включая меня) начали думать о производительности запроса, а вопрос стоял в отказоустойчивости транзакции на DML в 2Мб.
Ответ написан
Комментировать
@SergeyGrigorev
Главное, чтобы размера журнала при этом хватило. Т.к. в случае переполнения журнала транзакций, все вылетит с ошибкой и вы не сможете сохранить эти 10 000 записей.
Ответ написан
@bdmalex
В MySQL же вроде есть «INSERT DELAYED ...» cинтаксис, это нельзя использовать для вашей задачи?
Ответ написан
@YourChief
почему бы не расширить bulk_insert_buffer_size и не вставлять единственным инсёртом?
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы