Является ли фиксация коммитов в MySQL/PyMySQL жизнеспособной альтернативой вставке нескольких строк одновременно?

Question

Я занимаюсь разработкой приложения для сбора данных, которое предусматривает постоянную вставку в базу данных MySQL. Я использую Python и PyMySQL для достижения этой цели. Мне нужно вставить около 100 строк в основную таблицу в секунду. Процесс python постоянно работает и поддерживает постоянное соединение с базой данных MySQL, которая находится на удаленном сервере.

Я знаю, что в целом лучше вставлять данные в виде буферов (несколько строк одновременно), а не делать отдельные вставки. Будет ли выполнение коммитов (connection.commit() в PyMySQL) каждые 100 или около того вставок достигать тех же самых издержек, что и вставка больших объемов данных сразу?

По синтаксическим причинам легче разделить вставки строк на отдельные операции.

Answer 1

Вы должны обнаружить, что самая большая нагрузка на удаленный сервер - это не время обработки, а время кругового обхода для каждого запроса, отправляемого на сервер и возвращающего ответ... если сервер удален более чем на ~10 мс, вставляя 100 строк / сек по отдельности невозможно, потому что слишком много времени тратится на провод, ожидая.

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

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

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

Конечно, если есть какая-то причина, по которой отдельные запросы на вставку более желательны, то использование нескольких потоков в вашей программе и нескольких подключений к базе данных является возможной стратегией для повышения производительности, поскольку n подключений могут выполнять n запросов параллельно, уменьшая тем самым чистое практическое влияние времени прохождения туда-обратно t до значения, близкого к t / n