Как ускорить производительность вставки в PostgreSQL

Question

Я тестирую производительность вставки Postgres. У меня есть таблица с одним столбцом с номером в качестве типа данных. На это также есть индекс. Я заполнил базу данных с помощью этого запроса:

insert into aNumber (id) values (564),(43536),(34560) ...

Я вставил 4 миллиона строк очень быстро по 10000 за один раз с запросом выше. После того, как база данных достигла 6 миллионов строк, производительность резко снизилась до 1 миллиона строк каждые 15 минут. Есть ли хитрость для увеличения производительности вставки? Мне нужна оптимальная производительность вставки в этом проекте.

Использование Windows 7 Pro на машине с 5 ГБ ОЗУ.

Answer 1

См. Заполнение базы данных в руководстве по PostgreSQL, отличную, как обычно, статью Depesz по этой теме, и этот SO вопрос.

(Обратите внимание, что этот ответ касается массовой загрузки данных в существующую БД или создания новой. Если вам интересно, восстановите производительность БД с помощью pg_restore или же psql исполнение pg_dump вывод, большая часть этого не применяется, так как pg_dump а также pg_restore уже делать такие вещи, как создание триггеров и индексов после завершения схемы + восстановление данных).

Есть много чего сделать. Идеальным решением было бы импортировать в UNLOGGED таблицу без индексов, затем измените ее на logged и добавьте индексы. К сожалению, в PostgreSQL 9.4 нет поддержки смены таблиц из UNLOGGED чтобы войти. 9,5 добавляет ALTER TABLE ... SET LOGGED чтобы позволить вам сделать это.

Если вы можете перевести свою базу данных в автономный режим для массового импорта, используйте pg_bulkload,

Иначе:

• Отключить любые триггеры на столе

• Удалите индексы перед началом импорта, затем создайте их заново. (Построение индекса за один проход занимает гораздо меньше времени, чем постепенное добавление к нему тех же данных, и полученный индекс гораздо более компактен).

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

• Если возможно, используйте COPY вместо INSERT s

• Если вы не можете использовать COPY рассмотреть возможность использования многозначных INSERT с практичным. Вы, кажется, уже делаете это. Не пытайтесь перечислить слишком много значений в одном VALUES хоть; эти значения должны уместиться в памяти пару раз, так что держите их на несколько сотен на оператор.

• Пакетные вставки в явные транзакции, делая сотни тысяч или миллионы вставок за транзакцию. Практического ограничения AFAIK нет, но пакетирование позволит вам восстановиться после ошибки, отметив начало каждого пакета в ваших входных данных. Опять же, вы, кажется, уже делаете это.

• использование synchronous_commit=off и огромный commit_delay уменьшить затраты fsync(). Это не сильно поможет, если вы сгруппировали свою работу в большие транзакции.

• INSERT или же COPY параллельно из нескольких соединений. Сколько зависит от дисковой подсистемы вашего оборудования; как правило, вам нужно одно соединение на физический жесткий диск, если используется хранилище с прямым подключением.

• Установить высокий checkpoint_segments значение и включить log_checkpoints, Посмотрите журналы PostgreSQL и убедитесь, что они не жалуются на слишком часто возникающие контрольные точки.

• Если и только если вы не против потерять весь ваш кластер PostgreSQL (вашу базу данных и все остальные в одном кластере) из-за катастрофического повреждения, если система потерпит крах во время импорта, вы можете остановить Pg, установить fsync=off, запустите Pg, сделайте ваш импорт, затем (жизненно) остановите Pg и установите fsync=on снова. Смотрите конфигурацию WAL. Не делайте этого, если в какой-либо базе данных вашей установки PostgreSQL уже есть данные, которые вас интересуют. Если вы установите fsync=off Вы также можете установить full_page_writes=off; опять же, не забудьте включить его снова после импорта, чтобы предотвратить повреждение базы данных и потерю данных. Смотрите недолговечные настройки в руководстве по Pg.

Вы также должны посмотреть на настройку вашей системы:

• Максимально используйте твердотельные накопители хорошего качества для хранения. Хорошие твердотельные накопители с надежным, защищенным питанием кэшем обратной записи делают скорость фиксации невероятно высокой. Они менее полезны, если вы последуете совету, приведенному выше, - что уменьшит количество сбросов диска / количество fsync() s - но все еще может быть большой помощью. Не используйте дешевые твердотельные накопители без надлежащей защиты от сбоя питания, если только вы не заботитесь о сохранности своих данных.

• Если вы используете RAID 5 или RAID 6 для хранилища с прямым подключением, остановитесь сейчас. Создайте резервную копию данных, реструктурируйте массив RAID в RAID 10 и попробуйте снова. RAID 5/6 безнадежен для массовой записи - хотя хороший RAID-контроллер с большим кешем может помочь.

• Если у вас есть возможность использовать аппаратный RAID-контроллер с большим резервным кэшем с резервным питанием от батареи, это действительно может улучшить производительность записи для рабочих нагрузок с большим количеством коммитов. Это не очень помогает, если вы используете async commit с commit_delay или если вы делаете меньше больших транзакций во время массовой загрузки.

• Если возможно, храните WAL (pg_xlog) на отдельный диск / дисковый массив. Нет смысла использовать отдельную файловую систему на одном диске. Люди часто предпочитают использовать пару RAID1 для WAL. Опять же, это больше влияет на системы с высокой частотой фиксации и мало влияет, если в качестве цели загрузки данных вы используете незафиксированную таблицу.

Вы также можете быть заинтересованы в Оптимизации PostgreSQL для быстрого тестирования.

Answer 2

Я потратил около 6 часов на тот же вопрос сегодня. Вставки идут с "обычной" скоростью (менее 3 секунд на 100 КБ) вплоть до 5-ти строк (из 30-ти общих), а затем производительность резко падает (вплоть до 1 минуты на 100 КБ).

Я не буду перечислять все вещи, которые не сработали, и сократить прямо на встречу.

Я сбросил первичный ключ на целевой таблице (который представлял собой GUID), и мои 30MI или строки благополучно перенаправились к месту назначения с постоянной скоростью менее 3 секунд на 100 КБ.

Answer 3

Использование COPY table TO ... WITH BINARY который согласно документации " несколько быстрее, чем текстовые и CSV-форматы". Делайте это только в том случае, если у вас есть миллионы строк для вставки и если вы знакомы с двоичными данными.

Вот пример рецепта на Python с использованием psycopg2 с двоичным вводом.

Answer 4

В дополнение к отличной публикации Крейга Рингера и публикации в блоге depesz, если вы хотите ускорить вставку через интерфейс ODBC ( psqlodbc) с помощью вставок подготовленных операторов внутри транзакции, вам нужно сделать несколько дополнительных вещей, чтобы сделать ее работать быстро:

1. Установите уровень отката при ошибках равным "Транзакция", указав Protocol=-1 в строке подключения. По умолчанию psqlodbc использует уровень "Statement", который создает SAVEPOINT для каждого оператора, а не для всей транзакции, делая вставки медленнее.
2. Используйте подготовленные операторы на стороне сервера, указав UseServerSidePrepare=1 в строке подключения. Без этой опции клиент отправляет весь оператор вставки вместе с каждой вставляемой строкой.
3. Отключите автоматическую фиксацию для каждого оператора, используя SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0);
4. Как только все строки были вставлены, передайте транзакцию, используя SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);, Нет необходимости явно открывать транзакцию.

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

Answer 5

Если вам удалось вставить столбцы с UUID (что не совсем ваш случай) и добавить в ответ @Dennis (я еще не могу комментировать), посоветуйте, чем использование gen_random_uuid() (требуется PG 9.4 и модуль pgcrypto) (a лот) быстрее, чем uuid_generate_v4()

=# explain analyze select uuid_generate_v4(),* from generate_series(1,10000);
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4) (actual time=11.674..10304.959 rows=10000 loops=1)
 Planning time: 0.157 ms
 Execution time: 13353.098 ms
(3 filas)

против

=# explain analyze select gen_random_uuid(),* from generate_series(1,10000);
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4) (actual time=252.274..418.137 rows=10000 loops=1)
 Planning time: 0.064 ms
 Execution time: 503.818 ms
(3 filas)

Кроме того, это рекомендуемый официальный способ сделать это.

Запись

Если вам нужны только случайно сгенерированные (версия 4) UUID, рассмотрите возможность использования вместо этого функции gen_random_uuid() из модуля pgcrypto.

Это уменьшило время вставки с ~2 часов до ~10 минут для 3,7 млн ​​строк.

Answer 6

Для оптимальной производительности вставки отключите индекс, если это вариант для вас. Помимо этого, лучшее оборудование (диск, память) также полезно

Answer 7

Я также столкнулся с этой проблемой производительности вставки. Мое решение - порождение некоторых процедур go для завершения работы по вставке. В это время, SetMaxOpenConns должен быть указан правильный номер, в противном случае слишком много открытых ошибок соединения будут предупреждены.

db, _ := sql.open() 
db.SetMaxOpenConns(SOME CONFIG INTEGER NUMBER) 
var wg sync.WaitGroup
for _, query := range queries {
    wg.Add(1)
    go func(msg string) {
        defer wg.Done()
        _, err := db.Exec(msg)
        if err != nil {
            fmt.Println(err)
        }
    }(query)
}
wg.Wait()

Скорость загрузки намного выше для моего проекта. Этот фрагмент кода только что дал представление о том, как он работает. Читатели должны иметь возможность легко изменить его.