Оптимизация производительности

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

Все описанные параметры, кроме autovacuum_work_mem и temp_buffers, можно установить в разделе ADPG configurations дерева Configuration на странице Clusters → Кластер ADPG → Services → ADPG → Primary configuration в пользовательском интерфейсе ADCM.

После изменения описанных настроек выполните действие Reconfigure & Restart сервиса ADPG, чтобы применить изменения.

В таблице ниже перечислены параметры, связанные с операциями ввода-вывода, изменение значений которых может значительно влиять на производительность.

Функции SQL наиболее просты в использовании и обычно рекомендуется применять их. Они перечислены в таблице Database object size functions.

Например, функция pg_relation_size() возвращает указанный размер таблицы (в данном примере — pgbench_accounts) в байтах. Вы можете использовать psql для выполнения следующего запроса:

Результат:

Функция pg_size_pretty() преобразует размер в байтах в более удобный для чтения формат с единицами измерения: байты, КБ, МБ, ГБ или ТБ:

Результат:

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

Результат:

Можно выполнить запросы в psql, чтобы увидеть использование диска таблицей. Для достоверности результатов над таблицей предварительно должна быть выполнена операция VACUUM или ANALYZE. Следующий запрос определяет количество страниц (столбец relpages), принадлежащих таблице pgbench_accounts:

Результат:

Каждая страница обычно имеет размер 8 КБ. Запрос вернул тот же результат, который возвращает функция pg_relation_size(): КБ. Обратите внимание, что relpages обновляется только с помощью VACUUM, ANALYZE и нескольких команд DDL, таких как CREATE INDEX. Путь к файлу (столбец pg_relation_filepath) представляет интерес, если вы хотите напрямую просмотреть файл таблицы на диске.

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

Результат:

Запрос ниже отображает размер индекса для таблицы pgbench_branches:

Результат:

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

Результат:

Утилита oid2name помогает администраторам проверять файловую структуру, используемую ADPG/PostgreSQL.

oid2name подключается к целевой базе данных и извлекает OID, файловый узел (filenode — уникальный числовой идентификатор, используемый для именования файлов на диске, принадлежащих отношению) и/или информацию об имени таблицы:

Результат:

Например, можно рассмотреть базу данных example из списка, приведенного выше. Для этого измените текущий каталог на каталог базы данных:

где 19191 — Oid базы данных example из вывода команды, приведенного выше.

Отобразите первые 5 объектов базы данных в табличном пространстве по умолчанию, отсортированные по размеру (самые большие):

Результат:

Определите, что за файл 19244:

Результат:

Для утилиты oid2name требуется работающий сервер базы данных с неповрежденными системными каталогами. В ситуациях со значительными повреждением базы данных её использование ограничено.

При использовании нескольких операторов INSERT отключите автофиксацию и выполните одну фиксацию в конце транзакции. В обычном SQL это означает выполнение BEGIN в начале и COMMIT в конце. Некоторые клиентские библиотеки могут делать это автоматически, в этом случае нужно проверить, что это действительно так. Если вы разрешите фиксацию каждого оператора INSERT отдельно, PostgreSQL проделает большую работу для каждой добавленной строки. Дополнительным преимуществом выполнения всех вставок за одну транзакцию является то, что в случае сбоя добавления одной строки, вставка всех строк будет отменена. Таким образом, вы не получите базу с частично загруженными данными.

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

Если невозможно использовать COPY, будет полезно применить PREPARE для создания подготовленного оператора INSERT, а затем использовать EXECUTE столько раз, сколько необходимо. Это позволит избежать накладных расходов на повторный анализ и планирование INSERT.

Обратите внимание, что загрузка большого количества строк с помощью COPY почти всегда происходит быстрее, чем с помощью INSERT, даже если используется PREPARE и несколько вставок объединяются в одну транзакцию.

Если параметр wal_level имеет значение minimal, команда COPY работает быстрее всего, когда выполняется в той же транзакции, что и команды CREATE TABLE или TRUNCATE. В таких случаях писать WAL не нужно, поскольку в случае ошибки файлы, содержащие вновь загруженные данные, будут удалены.

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

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

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

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

Временное увеличение параметра конфигурации maintenance_work_mem при загрузке больших объемов данных может улучшить ситуацию с производительностью. Это помогает ускорить выполнение команд CREATE INDEX и ALTER TABLE ADD FOREIGN KEY, но не влияет на скорость выполнения команды COPY, поэтому данный совет полезен только в том случае, если вы используете один или оба описанных выше метода. maintenance_work_mem находится в разделе ADPG configurations дерева Configuration на странице Clusters → Кластер ADPG → Services → ADPG → Primary configuration в пользовательском интерфейсе ADCM. См. Конфигурационные параметры.

Временное увеличение параметра конфигурации max_wal_size также может ускорить загрузку больших данных. Загрузка большого объема данных приводит к тому, что контрольные точки возникают чаще, чем обычно (задается параметром конфигурации checkpoint_timeout). При возникновении контрольной точки все "грязные" страницы должны быть сброшены на диск. Количество необходимых контрольных точек можно уменьшить, увеличив max_wal_size во время массовой загрузки данных. max_wal_size также находится в разделе ADPG configurations дерева Configuration на странице Clusters → Кластер ADPG → Services → ADPG → Primary configuration в пользовательском интерфейсе ADCM.

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

Чтобы отключить архивирование WAL, установите следующие параметры конфигурации:

Чтобы задать эти параметры, используйте раздел ADPG configurations на вкладке Clusters → Кластер ADPG → Services → ADPG → Primary configuration в пользовательском интерфейсе ADCM. См. Конфигурационные параметры.

Чтобы отключить создание бэкапов, выключите переключатель Enable backups, отображаемый на вкладке Primary Configuration сервиса ADPG (Clusters → Кластер ADPG → Services → ADPG → Primary configuration).

Для применения изменений этих параметров, выполните действие Reconfigure & Restart.

Эти настройки также ускоряют выполнение некоторых команд, поскольку команды не записывают WAL, если для параметра wal_level установлено значение minimal.

Если вы существенно изменили распределение данных в таблице, настоятельно рекомендуется запустить команду ANALYZE. Выполнение ANALYZE (или VACUUM ANALYZE) гарантирует, что у планировщика будет актуальная статистика о таблице. При отсутствии статистики или устаревшей статистике планировщик может принять неправильные решения во время планирования запроса, что приведет к снижению производительности. Обратите внимание, что если autovacuum включен, он может автоматически запустить ANALYZE.

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

По умолчанию pg_dump использует COPY. Кроме того, когда генерируется полный дамп схемы и данных, данные загружаются перед созданием индексов и внешних ключей. Таким образом, некоторые рекомендации применяются автоматически. Все, что вам нужно учитывать, это следующее:

Дамп данных (data-only dump) также использует COPY, но он не удаляет и не воссоздает индексы и по умолчанию не работает с внешними ключами. Таким образом, при загрузке дампа, содержащего только данные, вы несете ответственность за удаление и воссоздание индексов и внешних ключей. По-прежнему полезно увеличивать max_wal_size при загрузке данных, но не maintenance_work_mem. maintenance_work_mem стоит менять только в том случае, если вы вручную воссоздаете индексы и внешние ключи. Также рекомендуется запустить ANALYZE в конце восстановления.