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

ОБЛАСТЬ ПРИМЕНЕНИЯ: База данных 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;

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 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 для PostgreSQL — гибкий сервер.