Как работает COPY и почему он намного быстрее, чем INSERT?

Сегодня я провел свой день, улучшая производительность моего скрипта Python, который помещает данные в мою базу данных Postgres. Я ранее вставлял записи как таковые:

query = "INSERT INTO my_table (a,b,c ... ) VALUES (%s, %s, %s ...)";
for d in data:
    cursor.execute(query, d)

Затем я переписал свой сценарий так, чтобы он создал файл в памяти, чем тот, который используется для Postgres COPY Команда, которая позволяет мне копировать данные из файла в мою таблицу:

f = StringIO(my_tsv_string)
cursor.copy_expert("COPY my_table FROM STDIN WITH CSV DELIMITER AS E'\t' ENCODING 'utf-8' QUOTE E'\b' NULL ''", f)

COPY метод был ошеломительно быстрее.

METHOD      | TIME (secs)   | # RECORDS
=======================================
COPY_FROM   | 92.998    | 48339
INSERT      | 1011.931  | 48377

Но я не могу найти информацию о том, почему? Как это работает иначе, чем мультилинии INSERT такой, что это делает его намного быстрее?

Смотрите также этот тест:

# original
0.008857011795043945: query_builder_insert
0.0029380321502685547: copy_from_insert

#  10 records
0.00867605209350586: query_builder_insert
0.003248929977416992: copy_from_insert

# 10k records
0.041108131408691406: query_builder_insert
0.010066032409667969: copy_from_insert

# 1M records
3.464181900024414: query_builder_insert
0.47070908546447754: copy_from_insert

# 10M records
38.96936798095703: query_builder_insert
5.955034017562866: copy_from_insert
Источник

3 ответа

Решение

Здесь работает ряд факторов:

  • Задержка в сети и двусторонние задержки
  • Издержки на утверждение в PostgreSQL
  • Переключение контекста и задержки планировщика
  • COMMIT стоит, если для людей, делающих один коммит за вставку (вы не делаете)
  • COPYспецифические оптимизации для массовой загрузки

Сетевая задержка

Если сервер удаленный, вы, возможно, "платите" фиксированную "цену" за каждый оператор, скажем, 50 мс (1/20 секунды). Или многое другое для некоторых облачных баз данных. Поскольку следующая вставка не может начаться до тех пор, пока последняя не завершится успешно, это означает, что ваша максимальная скорость вставок составляет 1000 строк в секунду с задержкой в ​​обоих направлениях. При задержке 50 мс ("время пинга") это 20 строк в секунду. Даже на локальном сервере эта задержка не равна нулю. Тогда как COPY просто заполняет окна отправки и получения TCP и передает строки так быстро, как БД может их записать, а сеть может их передать. Это не сильно влияет на задержку и может вставлять тысячи строк в секунду в одно и то же сетевое соединение.

Стоимость выписки в PostgreSQL

Есть также затраты на анализ, планирование и выполнение оператора в PostgreSQL. Он должен принимать блокировки, открывать файлы отношений, искать индексы и т. Д. COPY пытается сделать все это один раз, в начале, а затем просто сосредоточиться на загрузке строк как можно быстрее.

Затраты на переключение задач / контекстов

Дополнительные затраты времени оплачиваются из-за того, что операционной системе приходится переключаться между postgres, ожидающими строку, пока ваше приложение готовит и отправляет ее, а затем ваше приложение ожидает ответа postgres, пока postgres обрабатывает строку. Каждый раз, когда вы переключаетесь с одного на другое, вы тратите немного времени. Потеря большего времени может привести к потере времени на приостановку и возобновление различных состояний ядра низкого уровня, когда процессы входят и выходят из состояний ожидания.

Отсутствует оптимизация COPY

Помимо всего этого, COPY имеет некоторые оптимизации, которые он может использовать для некоторых видов нагрузок. Если нет сгенерированного ключа и любые значения по умолчанию являются, например, константами, он может предварительно рассчитать их и полностью обойти исполнитель, быстро загружая данные в таблицу на более низком уровне, что полностью пропускает часть нормальной работы PostgreSQL. если ты CREATE TABLE или же TRUNCATE в той же транзакции вы COPYон может сделать еще больше трюков для ускорения загрузки, минуя обычный учет транзакций, необходимый в многопользовательской базе данных.

Несмотря на это, PostgreSQL COPY может еще многое сделать, чтобы ускорить процесс, то, что он еще не знает, как сделать. Он может автоматически пропускать обновления индексов, а затем перестраивать индексы, если вы изменяете более определенной части таблицы. Это может сделать обновления индекса в пакетном режиме. Еще больше.

Совершать расходы

И последнее, что нужно учитывать, это зафиксировать затраты. Это, вероятно, не проблема для вас, потому что psycopg2 по умолчанию открывается транзакция и не фиксируется, пока вы не скажете это. Если вы не сказали ему использовать автокоммит. Но для многих драйверов БД по умолчанию используется автокоммит. В таких случаях вы будете делать один коммит для каждого INSERT, Это означает одну очистку диска, когда сервер проверяет, что он записывает все данные в памяти на диск, и велит дискам записывать свои собственные кэши в постоянное хранилище. Это может занять много времени и сильно варьироваться в зависимости от аппаратного обеспечения. Мой ноутбук на базе SSD NVMe BTRFS может выполнять только 200 фсин / с против 300 000 несинхронизированных операций записи / с. Так что он будет загружать только 200 строк в секунду! Некоторые серверы могут делать только 50 кадров в секунду. Некоторые могут сделать 20000. Поэтому, если вам нужно делать коммиты регулярно, попробуйте загружать и фиксировать пакетами, делать многострочные вставки и т. Д. COPY только один коммит в конце, затраты на коммит незначительны. Но это также означает, COPY не может восстановиться после ошибок в данных; это отменяет всю массовую нагрузку.

Источник

Копирование использует массовую загрузку, что означает, что он вставляет несколько строк одновременно, тогда как простая вставка выполняет одну вставку за раз, однако вы можете вставить несколько строк со вставкой, следуя синтаксису:

insert into table_name (column1, .., columnn) values (val1, ..valn), ..., (val1, ..valn)

для получения дополнительной информации об использовании массовой загрузки см., например, самый быстрый способ загрузки 1-метровых строк в postgresql от Daniel Westermann.

вопрос о том, сколько строк вы должны вставлять за раз, зависит от длины строки, хорошее правило - вставлять 100 строк на оператор вставки.

Источник

Делать вставки в транзакции для ускорения.

Тестирование в bash без транзакции:

>  time ( for((i=0;i<100000;i++)); do echo 'INSERT INTO testtable (value) VALUES ('$i');'; done ) | psql root | uniq -c
 100000 INSERT 0 1

real    0m15.257s
user    0m2.344s
sys     0m2.102s

И с транзакцией:

> time ( echo 'BEGIN;' && for((i=0;i<100000;i++)); do echo 'INSERT INTO testtable (value) VALUES ('$i');'; done && echo 'COMMIT;' ) | psql root | uniq -c
      1 BEGIN
 100000 INSERT 0 1
      1 COMMIT

real    0m7.933s
user    0m2.549s
sys     0m2.118s
Источник
Другие вопросы по тегам