[Перевод] PostgreSQL: настройка и оптимизация производительности. Часть 2
Продолжаем разбираться в способах повышения производительности PostgreSQL. На этот раз обсуждаем настройку запросов, логирования, автоочистки и параметров клиентского подключения. А также рассказываем об особенностях конфигурации на основе анализа рабочей нагрузки.
Примечание: мы даём общие рекомендации, которые станут неплохой отправной точкой для большинства развёртываний.
PostgreSQL: настройка и оптимизация производительности. Часть 1
Настройка запросов
random_page_cost
Параметр даёт оптимизатору PostgreSQL подсказку о стоимости чтения случайной страницы с диска, тем самым позволяя выбирать, когда использовать индексное сканирование, а когда последовательное. При использовании SSD-дисков оптимальное значение — 1,1. Для спиннинг-дисков обычно подходит значение по умолчанию.
effective_cache_size
Оптимальное значение:
меньше или равно 0,75 * общий объём оперативной памяти;
равно сумме буфера/кэша, свободной оперативной памяти и общих выходных данных команды free.
Подсказывает PostgreSQL, сколько общего места в кэше доступно. Относится к кэшу основной памяти, а не к кэшу процессора.
В примере effective_cache_size будет наименьшим — 64304 * .75, 58113 + 5808 + 8192. При условии, что shared_buffers равен 8 ГБ или 48228 МБ.
cpu_tuple_cost
Указывает относительную стоимость обработки каждой строки во время запроса. По умолчанию значение равно 0,01. Но опыт показывает, что его лучше увеличить до 0,03 для более реалистичной стоимости.
Логирование
logging_collector
Параметр должен быть включён, если log_destination содержит stderr или csvlog, чтобы собирать выходные данные в логи.
log_directory
Если logging_collector включен, это должно быть указано вне каталога данных. Логи не являются частью базовых резервных копий.
log_checkpoints
Параметр должен быть включён в диагностических целях. В частности, чтобы убедиться, что контрольные точки выполняются по checkpoint_timeout, а не по max_wal_size.
log_line_prefix
Определяет формат префикса, добавляемого к строкам логов. Префикс должен содержать время, идентификатор процесса, номер строки, пользователя и базу данных, а также название приложения, чтобы помочь в диагностике.
Рекомендуемое значение: '%m [%p-%l] %u@%d app=%a '
Примечание: не забудьте пробел в конце.
log_lock_waits
Параметр необходим для диагностики медленных запросов. Должен быть включён.
log_statement
Рекомендуемое значение — ddl. Параметр поможет определить, когда произошла ошибка, связанная с человеческим фактором. Например, удаление неправильной таблицы.
log_temp_files
Оптимальное значение — 0. Зарегистрирует все созданные временные файлы, полагая, что work_mem настроен неправильно.
timed_statistics (EPAS)
Управляет сбором временных данных для функции Dynamic Runtime Instrumentation Tools Architecture (DRITA). Если установлено значение on, собираются данные о времени. Параметр должен быть включён.
Autovacuum
log_autovacuum_min_duration
Рекомендуемое значение — 0. В этом случае будут записываться все действия автоочистки (autovacuum).
autovacuum_max_workers
Количество воркеров, которыми располагает autovacuum. Значение по умолчанию равно 3, для обновления требуется перезапуск сервера базы данных. Над каждой таблицей может работать только один воркер, поэтому увеличение количества воркеров полезно только при параллельной очистке таблиц.
Значение по умолчанию низкое. Рекомендуется увеличить его до 5 в качестве отправной точки.
autovacuum_vacuum_cost_limit
Чтобы предотвратить чрезмерную нагрузку на сервер базы данных из-за автоочистки, Postgres устанавливает квоту ввода-вывода. Каждое чтение/запись приводит к истощению этой квоты, и как только она исчерпана, процессы автоочистки приостанавливаются на фиксированное время.
Эта конфигурация увеличивает предел квоты, тем самым увеличивая количество операций ввода-вывода, которые может выполнять autovacuum. Значение по умолчанию низкое, поэтому рекомендуем увеличить его до 3000.
Клиентское подключение
idle_in_transaction_session_timeout
Таймер завершает сеансы, которые остаются неактивными слишком долго, поэтому приложение должно быть готово к восстановлению. Рекомендуемое значение — 10 минут.
lc_messages
Анализаторы логов понимают только непереведенные сообщения. Установите значение »C», чтобы избежать перевода.
shared_preload_libraries
Добавление pg_stat_statements — это низкие расходы и высокая ценность. Рекомендуется, но необязательно (подробнее раскроем ниже).
«PostgreSQL База»
Настройка на основе анализа рабочей нагрузки: ищем медленные запросы
Существует два основных способа поиска медленных запросов:
log_min_duration_statement — параметр времени (детализация в миллисекундах), который указывает, как долго должен выполняться запрос, прежде чем он будет отправлен в логи. Чтобы получить все запросы, установите значение 0, , но помните, что это может вызвать много операций ввода-вывода.
Как правило, для этого параметра устанавливают значение »1 с» (одна секунда), а затем все запросы оптимизируются. Значение постепенно снижается, и процесс повторяется до тех пор, пока не будет достигнут разумный порог. Далее оно сохраняется на этом уровне для постоянной оптимизации. Разумный порог полностью зависит от вашей рабочей нагрузки и не может быть определён в рамках статьи.
Это рабочий метод для поиска медленных запросов, но не самый эффективный. Скажем, у вас есть запрос, выполнение которого занимает 1 минуту, и выполняется он каждые десять минут. И у вас есть запрос, выполнение которого занимает 20 мс, но он выполняется двадцать раз в секунду. Какой из них важнее оптимизировать? При нормализации до десяти минут первый запрос занимает одну минуту времени вашего сервера, а второй — четыре минуты общего времени. Следовательно, второе значение важнее первого, но оно, скорее всего, останется незамеченным радаром log_min_duration_statement.
Недостаток использования модуля pg_stat_statements в том, что его необходимо устанавливать в shared_preload_libraries, а это требует перезапуска сервера. Однако его значение настолько велико, что это не так важно.
Этот модуль записывает каждый запрос, который выполняет сервер, нормализует его различными способами, а затем объединяет »одинаковые» запросы в единую точку данных со статистикой: общим временем выполнения, количеством вызовов, максимальным и минимальным временем выполнения, общим количеством возвращенных строк и др.
Два запроса считаются »одинаковыми», если их нормализованные внутренние структуры после синтаксического анализа совпадают. Например, SELECT * FROM t WHERE pk = 42; «тот же самый» запрос, что и SeLeCt * FrOm T wHeRe Pk=56; хотя значение pk отличаются.
Чтобы увидеть статистику, собранную модулем pg_stat_statements, сначала нужно установить расширение pg_stat_statements с помощью »CREATE EXTENSION pg_stat_statements; ».
Пару слов о безопасности. Модуль собирает статистику по всем запросам к серверу, независимо от того, с какой комбинацией пользователя или базы данных они выполнялись. Расширение можно установить в любую базу данных, даже в несколько баз данных, если это необходимо.
Переписываем запросы
Иногда переписывание некоторых частей запроса может значительно повысить производительность.
«Голые» столбцы
Распространенная ошибка — писать что-то вроде этого:
SELECT * FROM t
WHERE t.a_timestamp + interval ‘3 days’ < CURRENT_TIMESTAMP Вместо этого должно быть:
SELECT * FROM t
WHERE t.a_timestamp < CURRENT_TIMESTAMP — interval "3 days”
Результаты двух запросов будут одинаковыми — семантической разницы нет, но второй может использовать индекс t.a_timestamp, а первый — нет. Как правило, столбцы таблицы остаются »голыми» в левой части и помещают все выражения в правую сторону.
Никогда не используйте NOT IN с подзапросом
Существуют две формы предиката IN:
x IN (a, b, c);
x IN (SELECT…).
Для позитивной версии вы можете использовать любой из них. Для негативной — только первый. Смысл в том, как обрабатываются нули.
Смотрите:
demo=# select 1 in (1, 2);
?column?
----------
t
(1 row)
demo=# select 1 in (1, null);
?column?
----------
t
(1 row)
demo=# select 1 in (2, null);
?column?
----------
(null)
(1 row)При наличии нулей предикат IN будет возвращать только true или null; никогда false. Следовательно, что NOT IN вернёт только false или null; никогда true.
Легко определить наличие нулей и увидеть, что запрос никогда не даст желаемых результатов. Но если вы используете версию подзапроса, это не так просто. Что ещё более важно: даже если результат подзапроса гарантированно не содержит нулей, Postgres не оптимизирует его в Anti Join. Вместо этого используйте NOT EXISTS.
Используем EXPLAIN (ANALYZE, BUFFERS)
Если ваш запрос никогда не завершится (по крайней мере, до тех пор, пока вы не потеряете терпение), следует обратиться к эксперту или самому стать экспертом, чтобы понять простой план EXPLAIN. Во всех остальных случаях нужно использовать опцию ANALYZE для оптимизации запроса.
Bad Estimates
Наиболее распространенная причина плохой производительности — плохие оценки. Если статистика таблицы не актуальна, Postgres может предсказать, что будут возвращены только две строки, тогда как на самом деле будет возвращено двести строк. Для простого сканирования это не важно: у вас просто уйдёт чуть больше времени, чем планировалось. Но это не всё.
Настоящая проблема — эффект бабочки. Если Postgres считает, что сканирование выдаст две строки, он может выбрать вложенный цикл для объединения. Если бы он знал, что строк будет много, он бы выбрал хэш-соединение или объединение слиянием. Обновление статистики с помощью ANALYZE поможет устранить проблему.
External Sorts
Если для операции сортировки недостаточно work_mem, Postgres сбросит данные на диск. Поскольку оперативная память намного быстрее дисков (даже SSD), это может стать причиной медленных запросов. Подумайте об увеличении увеличить work_mem, если видите это:
demo=# create table t (c bigint);
CREATE TABLE
demo=# insert into t select generate_series(1, 1000000);
INSERT 0 1000000
demo=# explain (analyze on, costs off) table t order by c;
QUERY PLAN
----------------------------------------------------------------------
Sort (actual time=158.066..245.686 rows=1000000 loops=1)
Sort Key: c
Sort Method: external merge Disk: 17696kB
-> Seq Scan on t (actual time=0.011..51.972 rows=1000000 loops=1)
Planning Time: 0.041 ms
Execution Time: 273.973 ms
(6 rows)
demo=# set work_mem to '100MB';
SET
demo=# explain (analyze on, costs off) table t order by c;
QUERY PLAN
----------------------------------------------------------------------
Sort (actual time=183.841..218.555 rows=1000000 loops=1)
Sort Key: c
Sort Method: quicksort Memory: 71452kB
-> Seq Scan on t (actual time=0.011..56.573 rows=1000000 loops=1)
Planning Time: 0.043 ms
Execution Time: 243.031 ms
(6 rows)Разница здесь не сильно бросается в глаза из-за небольшого набора данных. Реальные запросы могут быть гораздо более заметными. В данном случае лучше всего добавить индекс, чтобы вообще избежать сортировки.
Чтобы предотвратить неуправляемые запросы, установите параметр temp_file_limit. Запрос, генерирующий такое количество временных файлов, автоматически отменится.
Hash Batches
Ещё один признак того, что установлено слишком низкое значение work_mem, — операция хеширования выполняется в пакетном режиме. В примере установим для work_mem минимально возможное значение перед выполнением запроса. Затем мы сбросим его и снова запустим запрос для сравнения планов.
demo=# create table t1 (c) as select generate_series(1, 1000000);
SELECT 1000000
demo=# create table t2 (c) as select generate_series(1, 1000000, 100);
SELECT 10000
demo=# vacuum analyze t1, t2;
VACUUM
demo=# set work_mem to '64kB';
SET
demo=# explain (analyze on, costs off, timing off)
demo-# select * from t1 join t2 using (c);
QUERY PLAN
------------------------------------------------------------------
Gather (actual rows=10000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (actual rows=3333 loops=3)
Hash Cond: (t1.c = t2.c)
-> Parallel Seq Scan on t1 (actual rows=333333 loops=3)
-> Hash (actual rows=10000 loops=3)
Buckets: 2048 Batches: 16 Memory Usage: 40kB
-> Seq Scan on t2 (actual rows=10000 loops=3)
Planning Time: 0.077 ms
Execution Time: 115.790 ms
(11 rows)
demo=# reset work_mem;
RESET
demo=# explain (analyze on, costs off, timing off)
demo-# select * from t1 join t2 using (c);
QUERY PLAN
------------------------------------------------------------------
Gather (actual rows=10000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (actual rows=3333 loops=3)
Hash Cond: (t1.c = t2.c)
-> Parallel Seq Scan on t1 (actual rows=333333 loops=3)
-> Hash (actual rows=10000 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 480kB
-> Seq Scan on t2 (actual rows=10000 loops=3)
Planning Time: 0.081 ms
Execution Time: 63.893 ms
(11 rows)Время выполнения сократилось вдвое за счёт выполнения только одного пакета.
Heap Fetches
Независимо от того, видна ли строка транзакции, выполняющей запрос, она сохраняется в строке таблицы. Карта видимости хранит по одному биту на страницу таблицы. Установленный бит означает, что все строки страницы видны всем транзакциям. Index Scan (сканирование по индексу) должен проверять таблицу, когда он находит соответствующую строку, чтобы увидеть, видна ли найденная строка или нет.
Index-Only Scan (сканирование только по индексу) использует карту видимости, чтобы избежать извлечения строки из таблицы, если это возможно. Если карта видимости показывает, что не все строки на странице видны, то Index-Only Scan в конечном счёте выполняет больше операций ввода-вывода. В худшем случае — превращается в обычное сканирование.
План объяснения покажет, сколько раз ему приходилось обращаться к таблице из-за того, что карта видимости не была обновлена.
demo=# create table t (c bigint)
demo-# with (autovacuum_enabled = false);
CREATE TABLE
demo=# insert into t select generate_series(1, 1000000);
INSERT 0 1000000
demo=# create index on t (c);
CREATE INDEX
demo=# analyze t;
ANALYZE
demo=# explain (analyze on, costs off, timing off, summary off)
demo-# select c from t where c <= 2000;
QUERY PLAN
---------------------------------------------------------------
Index Only Scan using t_c_idx on t (actual rows=2000 loops=1)
Index Cond: (c <= 2000)
Heap Fetches: 2000
(3 rows)В идеале это значение было бы равно нулю, но всё зависит от активности в таблице. Если вы постоянно меняете одни и те же страницы и запрашиваете их, это будет отражено. Если нет — необходимо обновить карту видимости с помощью VACUUM.
demo=# vacuum t;
VACUUM
demo=# explain (analyze on, costs off, timing off, summary off)
demo-# select c from t where c <= 2000;
QUERY PLAN
---------------------------------------------------------------
Index Only Scan using t_c_idx on t (actual rows=2000 loops=1)
Index Cond: (c <= 2000)
Heap Fetches: 0
(3 rows)Lossy Bitmap Scans
Если данные разбросаны повсюду, Postgres выполняет Bitmap Index Scan (сканирование по битовой карте). Он строит битовую карту страниц и смещает внутри страницы каждую найденную совпадающую строку. Затем он сканирует таблицу, получив все строки всего за одну выборку каждой страницы.
Но только если у него достаточно work_mem. Если нет — он «забудет» смещения и просто запомнит, что на странице есть хотя бы одна совпадающая строка. Сканирование должно будет проверить все строки и отфильтровать те, которые не совпадают.
demo=# create table t (c1, c2) as
demo-# select n, n::text from generate_series(1, 1000000) as g (n)
demo-# order by random();
SELECT 1000000
demo=# create index on t (c1);
CREATE INDEX
demo=# analyze t;
ANALYZE
demo=# explain (analyze on, costs off, timing off)
demo-# select * from t where c1 <= 200000;
QUERY PLAN
------------------------------------------------------------------
Bitmap Heap Scan on t (actual rows=200000 loops=1)
Recheck Cond: (c1 <= 200000)
Heap Blocks: exact=5406
-> Bitmap Index Scan on t_c1_idx (actual rows=200000 loops=1)
Index Cond: (c1 <= 200000)
Planning Time: 0.065 ms
Execution Time: 48.800 ms
(7 rows)
demo=# set work_mem to '64kB';
SET
demo=# explain (analyze on, costs off, timing off)
demo-# select * from t where c1 <= 200000;
QUERY PLAN
------------------------------------------------------------------
Bitmap Heap Scan on t (actual rows=200000 loops=1)
Recheck Cond: (c1 <= 200000)
Rows Removed by Index Recheck: 687823
Heap Blocks: exact=752 lossy=4654
-> Bitmap Index Scan on t_c1_idx (actual rows=200000 loops=1)
Index Cond: (c1 <= 200000)
Planning Time: 0.138 ms
Execution Time: 85.208 ms
(8 rows)Wrong Plan Shapes
Эту проблему сложнее всего обнаружить — это приходит только с опытом. Ранее мы видели, что недостаточное количество work_mem может привести к тому, что хэш будет использовать несколько пакетов. Но что, если Postgres решит, что дешевле вообще не использовать хэш-соединение и, возможно, вместо этого использовать вложенный цикл?
Увеличение work_mem вернёт Postgres к хеш-соединению. Изучение того, когда ваш запрос должен иметь определённую форму плана, и обнаружение, что он имеет другую, может предоставить действительно неплохие возможности оптимизации PostgreSQL.
Партиционирование
Есть две причины для партиционирования: техническое обслуживание и распараллеливание.
Когда таблица становится очень большой, количество пустых строк, разрешенных в соответствии с настройками autovacuum по умолчанию, увеличивается. Для таблицы, содержащей один миллиард строк, очистка не начнется до тех пор, пока 200 000 000 строк не будут обновлены или удалены. В большинстве рабочих нагрузок для этого требуется время. Когда же очистка всё-таки запустится, одиночный воркер должен просканировать всю таблицу, собрав список мертвых строк. Этот список использует 6 байт на каждую пустую строку, так что для его хранения требуется примерно 1,2 ГБ оперативной памяти. Он должен сканировать каждый индекс таблицы по одному и удалить записи, которые найдёт в списке. Затем он снова просканирует таблицу, чтобы удалить сами строки.
Если вы не можете выделить 1,2 ГБ autovacuum_work_mem, то весь процесс повторяется пакетно. Если в какой-то момент во время этой операции запрос потребует блокировки, конфликтующей с автоочисткой, последняя начнёт всё с самого начала. Однако, если автоочистка предназначена для предотвращения зацикливания, запросу придётся подождать.
Autovacuum использует карту видимости, чтобы пропускать большие участки таблицы, которые не меняли с момента последней очистки. 9.6 сделал ещё один шаг вперед для очистки с защитой от зацикливания, но такой оптимизации не существует в методах индексирования — они сканируются полностью каждый раз. Дыры, оставшиеся в таблице, могут быть заполнены будущими вставками/обновлениями, но повторно использовать пустое пространство в индексе гораздо сложнее, поскольку значения в нём упорядочены. Меньшее количество очисток означает, что индексы нужно чаще переиндексировать, чтобы сохранить их производительность. Вплоть до PostgreSQL 11 это требовало блокировки таблицы от записи, но PostgreSQL 12 может переиндексировать одновременно.
Разбивая ваши данные на более мелкие фрагменты, каждый раздел и его индексы могут обрабатываться разными воркерами.
Иногда разделение используют, чтобы вообще избавиться от необходимости очистки. Если ваша таблица содержит что-то вроде данных временных рядов, где вы действуете по принципу »вставили и забыли», вышеизложенное не считается проблемой. Как только старые строки будут заморожены, автоочистка больше никогда не будет их просматривать (начиная с версии 9.6). Проблема здесь в политиках хранения. Если данные хранятся только в течение десяти лет, а затем удаляются после возможного архивирования, это создаст дыру, которую в конечном счёте заполнят новые данные, и таблица станет фрагментированной. Это сделает любые индексы BRIN совершенно бесполезными.
Распространенное решение — разбивка по месяцам (или любой желаемой степени детализации). Далее процедура становится такой: отсоединить старый раздел, выгрузить его для архивов, удалить таблицу. Вообще не использовать vacuum.
Что касается распараллеливания, когда у вас большие таблицы, доступ к которым осуществляется произвольно, рекомендовано разделение по арендаторам. Каждого арендатора помещают в отдельное табличное пространство для улучшения ввода-вывода.
Заключение
Перечисленные рекомендации могут стать неплохой отправной точкой для большинства рабочих нагрузок OLTP. Мониторинг и настройка этих и других параметров необходимы для получения максимальной производительности PostgreSQL для вашей рабочей нагрузки.
«PostgreSQL База»
