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

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

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

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

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

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

Шаги по отправке массовых данных

Ниже приведены шаги по массовой отправке данных в гибкий экземпляр сервера Базы данных Azure для PostgreSQL.

Шаг 1. Подготовка данных

Убедитесь, что данные чисты и правильно отформатированы для базы данных.

Шаг 2. Выбор метода загрузки

Выберите подходящий метод загрузки на основе размера и сложности данных.

Шаг 3. Выполнение метода загрузки

Запустите выбранный метод загрузки, чтобы передать данные в базу данных.

Шаг 4. Проверка данных

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  CREATE UNLOGGED TABLE <tablename>;
  

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

  ALTER TABLE <tablename> SET UNLOGGED;
  

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

• auto vacuum': It's best to turn off автозагрузка во время начальной загрузки данных. После завершения начальной загрузки рекомендуется запустить вручную VACUUM ANALYZE во всех таблицах в базе данных, а затем включить auto vacuum.

Примечание.

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

• 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 гибкий экземпляр сервера, рекомендуется:

• Отключите высокий уровень доступности на сервере. Его можно включить после завершения начальной загрузки на первичном сервере.
• Создайте реплики чтения после завершения начальной загрузки данных.
• Сделайте ведение журнала минимальным или отключите все вместе во время начальной загрузки данных (например, отключите 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;

number_of_scansСтолбцы tuples_readи 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 ();

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

SELECT pg_current_wal_lsn ();

с. Используйте два результата, чтобы проверить разницу в ГБ:

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

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

Связанный контент

• Устранение неполадок с высокой загрузкой ЦП в Базе данных Azure для PostgreSQL.
• Устранение неполадок с высокой загрузкой памяти в Базе данных Azure для PostgreSQL.
• Устранение неполадок и определение медленных запросов в Базе данных Azure для PostgreSQL.
• Параметры сервера в Базе данных Azure для PostgreSQL.
• Настройка autovacuum в Azure Database для PostgreSQL.