Рекомендации по массовой отправке данных в База данных Azure для PostgreSQL — гибкий сервер

  • Статья

Область применения: гибкий сервер Базы данных Azure для PostgreSQL

В этой статье рассматриваются различные методы массовой загрузки данных в База данных Azure для PostgreSQL гибком сервере, а также рекомендации по загрузке исходных данных в пустых базах данных и добавочных нагрузках данных.

Методы загрузки

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

  • Выполните команду с одной записью INSERT .
  • Пакетная служба в 100–1000 строк на фиксацию. Вы можете использовать блок транзакций для упаковки нескольких записей на фиксацию.
  • Запустите INSERT с несколькими значениями строк.
  • Выполните команду COPY.

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

Рекомендации по начальной загрузке данных

Удаление индексов

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

Ограничения удаления

Основные ограничения удаления описаны здесь:

  • Ограничения уникальных ключей

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

  • Ограничения внешнего ключа

    Рекомендуется удалить ограничения внешнего ключа перед начальной загрузкой данных и повторно создать их после завершения загрузки данных.

    session_replication_role Изменение параметра replica для отключения всех проверка внешнего ключа. Однако следует помнить, что внесение изменений может оставить данные в несогласованном состоянии, если оно не используется должным образом.

Незалогированные таблицы

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

Использование незалогированных таблиц ускоряет загрузку данных. Данные, записанные в незалогированные таблицы, не записываются в журнал перед записью.

Недостатки использования незалогированных таблиц:

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

Чтобы создать нелогированную таблицу или изменить существующую таблицу на нелогированную таблицу, используйте следующие параметры:

  • Создайте новую нелогированную таблицу с помощью следующего синтаксиса:

    CREATE UNLOGGED TABLE <tablename>;

  • Преобразуйте существующую таблицу с журналом в нелогированную таблицу с помощью следующего синтаксиса:

    ALTER TABLE <tablename> SET UNLOGGED;

Настройка параметра сервера

  • autovacuum: во время начальной загрузки данных рекомендуется отключить autovacuum. После завершения начальной загрузки рекомендуется запустить вручную VACUUM ANALYZE во всех таблицах в базе данных, а затем включить autovacuum.

Примечание.

Следуйте рекомендациям только в том случае, если достаточно памяти и места на диске.

  • maintenance_work_mem: можно задать не более 2 гигабайт (ГБ) на гибком экземпляре сервера База данных Azure для PostgreSQL. maintenance_work_mem помогает ускорить автоматическое создание ключа, индекса и внешнего ключа.

  • checkpoint_timeout: на гибком экземпляре checkpoint_timeout сервера База данных Azure для PostgreSQL значение может быть увеличено не более 24 часов с параметра по умолчанию 5 минут. Рекомендуется увеличить значение до 1 часа перед загрузкой данных на экземпляр гибкого сервера База данных Azure для PostgreSQL.

  • checkpoint_completion_target: Рекомендуется значение 0,9.

  • max_wal_size: можно задать максимально допустимое значение для гибкого экземпляра сервера База данных Azure для PostgreSQL, что составляет 64 ГБ при выполнении начальной загрузки данных.

  • wal_compression: можно включить. Включение этого параметра может повлечь за собой дополнительные затраты на ЦП, потраченные на сжатие во время ведения журнала накануне записи (WAL) и распаковки во время воспроизведения WAL.

База данных Azure для PostgreSQL гибкие рекомендации по серверу

Прежде чем начать начальную загрузку данных на База данных Azure для PostgreSQL гибкий экземпляр сервера, рекомендуется:

  • Отключите высокий уровень доступности на сервере. Его можно включить после завершения начальной загрузки на первичном сервере.
  • Создайте реплика чтения после завершения начальной загрузки данных.
  • Сделайте ведение журнала минимальным или отключите его в целом во время начальной загрузки данных (например, отключите pgaudit, pg_stat_statements, хранилище запросов).

Повторное создание индексов и добавление ограничений

При условии, что индексы и ограничения были удалены до начальной загрузки, рекомендуется использовать высокие значения maintenance_work_mem (как упоминание ранее) для создания индексов и добавления ограничений. Кроме того, начиная с PostgreSQL версии 11, следующие параметры можно изменить для ускорения параллельного создания индекса после начальной загрузки данных:

  • max_parallel_workers: задает максимальное количество рабочих ролей, которые система может поддерживать параллельные запросы.

  • max_parallel_maintenance_workers: управляет максимальным числом рабочих процессов, в которых можно использовать CREATE INDEX.

Вы также можете создать индексы, сделав рекомендуемые параметры на уровне сеанса. Ниже приведен пример того, как это сделать:

SET maintenance_work_mem = '2GB';
SET max_parallel_workers = 16;
SET max_parallel_maintenance_workers = 8;
CREATE INDEX test_index ON test_table (test_column);

Рекомендации по добавочным нагрузкам данных

Таблицы секционирования

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

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

Поддержка актуальной статистики таблиц

Мониторинг и обслуживание статистики таблиц важно для производительности запросов в базе данных. Это также включает в себя сценарии, в которых у вас есть добавочные нагрузки. PostgreSQL использует процесс управляющей программы autovacuum для очистки мертвых кортежей и анализа таблиц для обновления статистики. Дополнительные сведения см. в разделе "Автовакум" для мониторинга и настройки.

Создание индексов ограничений внешнего ключа

Создание индексов внешних ключей в дочерних таблицах может оказаться полезным в следующих сценариях:

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

Определение неиспользуемых индексов

Определите неиспользуемые индексы в базе данных и удалите их. Индексы — это затраты на нагрузку данных. Чем меньше индексов в таблице, тем лучше производительность во время приема данных.

Неиспользуемые индексы можно определить двумя способами: хранилище запросов и запросом на использование индекса.

Хранилище запросов

Функция хранилище запросов помогает определить индексы, которые можно удалить на основе шаблонов использования запросов в базе данных. Пошаговые инструкции см. в разделе Хранилище запросов.

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

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Использование индекса

Можно также использовать следующий запрос для идентификации неиспользуемых индексов:

SELECT 
    t.schemaname, 
    t.tablename, 
    c.reltuples::bigint                            AS num_rows, 
    pg_size_pretty(pg_relation_size(c.oid))        AS table_size, 
    psai.indexrelname                              AS index_name, 
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, 
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique", 
    psai.idx_scan                                  AS number_of_scans, 
    psai.idx_tup_read                              AS tuples_read, 
    psai.idx_tup_fetch                             AS tuples_fetched 
FROM 
    pg_tables t 
    LEFT JOIN pg_class c ON t.tablename = c.relname 
    LEFT JOIN pg_index i ON c.oid = i.indrelid 
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid 
WHERE 
    t.schemaname NOT IN ('pg_catalog', 'information_schema') 
ORDER BY 1, 2;

tuples_readСтолбцы number_of_scansи tuples_fetched столбцы указывают значение индекса usage.number_of_scans значение столбца нулевой точки в качестве индекса, который не используется.

Настройка параметра сервера

Примечание.

Следуйте рекомендациям в следующих параметрах, только если достаточно памяти и дискового пространства.

  • maintenance_work_mem: этот параметр можно задать не более 2 ГБ на База данных Azure для PostgreSQL гибком экземпляре сервера. maintenance_work_mem помогает ускорить создание индекса и добавление внешних ключей.

  • checkpoint_timeout: на гибком экземпляре checkpoint_timeout сервера База данных Azure для PostgreSQL значение может быть увеличено до 10 или 15 минут с параметра по умолчанию 5 минут. Увеличение checkpoint_timeout до большего значения, например 15 минут, может уменьшить нагрузку ввода-вывода, но недостатком является то, что для восстановления требуется больше времени, если произошел сбой. Прежде чем вносить изменения, рекомендуется тщательно рассмотреть эту рекомендацию.

  • checkpoint_completion_target: Рекомендуется значение 0,9.

  • max_wal_size: это значение зависит от номера SKU, хранилища и рабочей нагрузки. Один из способов получить правильное значение для max_wal_size показан в следующем примере.

    Во время пиковых рабочих часов прибыть к стоимости, выполнив следующие действия:

    a. Выполните текущий номер последовательности журналов WAL (LSN), выполнив следующий запрос:

    SELECT pg_current_wal_lsn ();

    b. checkpoint_timeout Подождите количество секунд. Выполните текущий LSN WAL, выполнив следующий запрос:

    SELECT pg_current_wal_lsn ();

    c. Используйте два результата для проверка разницы в ГБ:

    SELECT round (pg_wal_lsn_diff('LSN value when run second time','LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;

  • wal_compression: можно включить. Включение этого параметра может нести некоторые дополнительные затраты на ЦП, потраченные на сжатие во время ведения журнала WAL и распаковки во время воспроизведения WAL.

Следующие шаги