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

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

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

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

использование Windows 7 Pro на машине с 5 ГБ оперативной памяти.

6 153
sql postgresql bulkinsert sql-insert

6 ответов:

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

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

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

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

в противном случае:

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

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

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

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

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

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

  • использовать synchronous_commit=off и огромное commit_delay для снижения затрат fsync (). Однако это не очень поможет, если вы объединили свою работу в крупные транзакции.

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

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

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

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

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

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

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

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

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

380

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

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

10

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

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

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

8

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

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

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

2

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

0

Я также столкнулся с этой проблемой производительности вставки. Мое решение-создать некоторые процедуры 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()

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

0