Перед стартом

Обслуживание баз данных - это не единственный вопрос, который следует решать администратору СУБД. И при этом не самый первый, если речь идет о сопровождении сервера.

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

• Настроили под себя оптимальным образом настройки PostgreSQL, например, с помощью сервиса pgtune.
• Решены все инфраструктурные вопросы в части работы сети, железа, дисков и так далее.
• Вы понимаете как работает PostgreSQL в части модификации строк, как работает версионирование, что такое “мертвые” строки и так далее.

Сосредоточимся только на базовых вопросах обслуживания. И так, поехали!

Нужно ли обслуживание?

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

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

Напомню:

• VACUUM - операция, которая удаляет “мертвые” строки, появившиеся в результате операций UPDATE или DELETE. В результате место внутри файла базы данных освобождается, но не возвращается операционной системе. В этом нет ничего страшного, т.к. в будущем PostgreSQL снова будет его использовать. Для возвращения освобожденного пространства обратно к ОС нужно выполнить VACUUM FULL, что потребует полного пересоздания таблицы, поэтому его выполнение не является регулярным шагом обслуживания и запускается только при крайней необходимости. “Обычный” VACUUM является частью регулярного обслуживания, но это не значит, что его нужно постоянно запускать вручную, т.к. это делает AUTOVACUUM, но об этом ниже.
• ANALYZE - операция обновления статистики, которая используется оптимизатором запросов для построенния эффеткивных планов выполнения. Обычно также не стоит запускать этот шаг вручную, т.к. с этим справляется AUTOVACUUM, но есть нюансы о которых ниже.

Ручной запуск операций VACUUM и ANALYZE имеет следующие нюансы:

• Это может быть избыточной операцией, т.к. механизмы автоочистки AUTOVACUUM и так это выполняют. Состояние таблиц в части “мертвых” строк и актуальности статистики может и так быть идеальным, а ручной запуск лишь съест дополнительные ресурсы IO / CPU.
• Ручной запуск ANALYZE не обновляет статистику (как это делает механизм автоочистки), а полностью ее перестраивает, что может создать излишнюю нагрузку на систему при ручных запусках.

Ручной запуск в нашем примере - это не запуск руками администратора, а запуск собственными скриптами через планировщики (cron, pgAgent и так далее). То есть любой запуск вне механизма AUTOVACUUM.

Вопрос - как настроить AUTOVACUUM для эффективной работы?

Настройка AUTOVACUUM

Первым делом проверим настройки AUTOVACUUM в файле конфигурации сервера postgresql.conf. Описание всех параметров есть в официальной документации.

Параметры VACUUM

В части очистки от “мертвых” строк есть два параметра:

• autovacuum_vacuum_threshold (значение по умолчанию 50) - мин. количество измененных или удаленных строк, при котором будет выполняться VACUUM для таблицы.
• autovacuum_vacuum_scale_factor (значение по умолчанию 0.2) - процент от размера таблицы, оторый добавляется к autovacuum_vacuum_threshold при вычислении порога срабатывания команды VACUUM.

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

pg_stat_user_tables.n_dead_tup > (pg_class.reltuples x autovacuum_vacuum_scale_factor)  + autovacuum_vacuum_threshold

Для мелких и средний таблиц работает хорошо. Например, если в таблице 1000 записей, то порог запуска VACUUM будет:

1000 * 0.2 + 50 = 250

То есть должно быть изменено 250 записей в таблице, прежде чем запустится очистка. Для такой таблицы это нормально. Но не все таблицы такие маленькие, что есть в таблице миллион записей? Тогда порог срабатывания будет:

1000000 * 0.2 + 50 = 200050

Существенный объем изменений должен произойти, что приведет к росту таблицы на 20% за счет “мертвых” строк, а статистика потеряет актуальность еще задолго до запуска механизма автоочистки. При этом когда автоочистка все же запустится, она будет работать дольше обычного из-за накопившихся изменений, что также приведет к повышенной нагрузке в этот период. В идеале автоочистка должна работать регулярно и распределять нагрузку между небольшими порциями очистки.

Параметры ANALYZE

В части поддержки актуальности статистики также есть два параметра:

• autovacuum_analyze_threshold (значение по умолчанию 50) - мин. количество измененных или удаленных строк, при котором будет выполняться ANALYZE для таблицы.
• autovacuum_analyze_scale_factor (значение по умолчанию 0.1) - процент от размера таблицы, который добавляется к autovacuum_analyze_threshold при вычислении порога срабатывания команды ANALYZE.

Работает аналогично параметрам для команды VACUUM и имеет те же самые проблемы для больших таблиц.

Как оптимизировать автоочистку

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

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

ALTER TABLE <table_name> 
SET (autovacuum_analyze_threshold = <threshold rows>)

Можно изменить все перечисленные выше параметры для каждой таблицы индивидуально и не менять поведение всего PostgreSQL в этой части. Анализ можно выполнить следующими скриптами:

• Анализ состояния индексов
• Анализ разбухания индексов
• Анализ ‘мертвых’ строк по таблицам

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

Но зато можно дать рекомендации по дополнительной настройке AUTOVACUUM:

• Можно ускориить работу автоочистки, увеличив параметр autovacuum_max_workers c 3 до, например, 5. Это даст возможность автоочистке запускать в 5 потоков. Но это еще не все!
• Также нужно изменить параметр autovacuum_vacuum_cost_limit, который задает ограничения для работы заданий автоочистки, некоторый предел стоимости (по умолчанию 200). При увеличении количества потоков предел стоимости для каждого из них вычисляется по формуле:

Индивидуальный порог стоимости для потока = autovacuum_vacuum_cost_limit / autovacuum_max_workers

То есть при увеличении количества потоков порог для каждого потока снизится. Поэтому с увеличением количества потоков механизма автоочистки рекомендую увеличить параметр autovacuum_vacuum_cost_limit например до 2000.

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

Как считается стоимость работы

На расчет стоимости работы потока автоочистки влияют несколько параметров:

• vacuum_cost_page_hit (по умолчанию 1) - стоимость очистки буфера, оказавшегося в общем кеше.
• vacuum_cost_page_miss (по умолчанию 10) - стоимость очистки буфера, который нужно прочитать с диска.
• vacuum_cost_page_dirty (по умолчанию 20) - стоимость очистки, при которой изменяется блок, не модифицированный ранее.

Изменять эти параметры глобально обычно смысла не имеет. Нужно стараться обновлять их индивидуально для каждой таблицы:

ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_limit = <large_value>)
ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_delay = <lower_cost_delay>)

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

Что в итоге с автоочисткой

Можно подвести такие итоги:

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

Общие параметры для анализа работы автоочистки:

• Количество записей в каждой таблице.
• Количество ‘мертвых’ строк в каждой таблице.
• Время последнейго запуска автоочистки.
• Время последнейго запуска обновления статистики.
• Статистика операций INSERT/UPDATE/DELETE для каждой таблицы.
• Время автоочистки для каждой таблицы (информацию можно посмотреть в логах PostgreSQL или сохранить в таблицу базы для анализа).
• Список таблиц, для которых не срабатывает автоочистка.
• Список проблемных запросов в базе данных, которые можно оптимизировать более частым обновлением статистики и автоочисткой.
• Проверить производительность этих запросов при ручном VACUUM и ANALYZE.

Но неужели ручного запуска операций обслуживания не нужно?

Берем особые случаи под контроль

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

В этих случаях можно организовать ручной запуск операций VACUUM и / или ANALYZE.

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

При ручном запуске нужно учитывать следующие моменты:

• Рекомендуется выполнять запуск в периоды минимальной активноси в базе данных, чтоб не помешать основной работе.
• Учитывать работу механизма AUTOVACUUM.
• Анализировать эффект от ручного запуска, о чем писали выше.
• Выполнять мониторинг работы баз данных и в случае изменения окружения или особенностей работы - адаптировать скрипты обслуживания.

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

Просто ручной VACUUM

Просто по расписанию несколько раз в день запускать:

VACUUM ANALYZE;

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

Запуск VACUUM для всех таблиц и всех баз

С помощью простого bash-скрипта это достаточно легко организовать.Оригинал скрипта.

#! /bin/bash
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres # Пользователь, от которого запустится обслуживание
export PGPASSWORD=postgres # Пароль этого пользователя

# Получаем список баз данных
dblist=`psql -d postgres -c "copy (select datname from pg_stat_database) to stdout"`
for db in $dblist ; do

    # Игнорируем служебные базы данных
    if [[ $db == template0 ]] ||  [[ $db == template1 ]] || [[ $db == postgres ]] ; then
        continue
    fi

    # Проводим сборку мусора и анализ базы данных
    # Подробнее: https://www.postgresql.org/docs/9.1/sql-vacuum.html
    psql -d $db -e -a -c "VACUUM;"
done

Запуск очистки для конктерной базы

На этот раз это простой скрипт для запуска через SQL-запрос.

/*
Обслуживание базы данных в части удаления "мертвых" строк в таблицах и обновления статистики для оптимизатора запросов.
*/

-- Подробнее: https://postgrespro.ru/docs/postgrespro/9.5/sql-vacuum

-- VACUUM высвобождает пространство, занимаемое «мёртвыми» кортежами. При обычных операциях кортежи, 
-- удалённые или устаревшие в результате обновления, физически не удаляются из таблицы; они сохраняются в ней, 
-- пока не будет выполнена команда VACUUM. 

-- VERBOSE - выводит подробный отчет о результатах работы
-- ANALYZE - обновляет статистику для оптимальной работы планировщика (эффективного построения планов запросов)

VACUUM (VERBOSE, ANALYZE);

Перестроение индексов

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

Скрипт ниже анализирует состояние индексов и если фрагментация выше указанного показателя, выполняет перестроение.Оригинал скрипта.

/*
Перестроение индексов с целью борьбы с их "разбуханием" и фрагментацией.
Для работы скрипта в базе необходимо установить расширение pgstatindex командой:
CREATE EXTENSION pgstattuple;
Может использоваться для генерации скриптов или для выполнения непосредственно перестроения индекса с учетом параметров:
 - index_frag_threshold_to_rebuild - минимальный % фрагментации индекса для перестроения.
 - use_index_concurrently_rebuild - использовать перестроение без эксклюзивной блокировки (онлайн-перестроение).
    Внимаание! При включенной опции возможно только генерация скриптов, т.к. выполнение онлайн-перестроения через 'EXECUTE %SQL%' недоступно.
 - generate_script_only - при установке в true будет сгенерирован скрипт, а его выполнение будет пропущено. 
    При false будет выполнена попытка выполнения скритпа сразу.
*/

do $$
declare
 index_frag_threshold_to_rebuild integer := 30;
 use_index_concurrently_rebuild boolean := false;
 generate_script_only boolean := false;
 sql_index_rebuild_result text default '';
 index_info record;
 indexes_cursor cursor for
  select
      n.nspname as "SchemaName",
      ti.relname as "TableName",
      i.indexrelid::regclass as "IndexName",
      case when s.leaf_fragmentation = 'NaN' then 0 else s.leaf_fragmentation end as "LeafFragmentation"
  FROM pg_index AS i
     JOIN pg_class AS t ON i.indexrelid = t.oid
     JOIN pg_opclass AS opc ON i.indclass[0] = opc.oid
     JOIN pg_am ON opc.opcmethod = pg_am.oid
     CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s
     join pg_class ti ON ti.oid = i.indrelid
     LEFT JOIN pg_namespace n ON n.oid = ti.relnamespace
  WHERE t.relkind = 'i'
    AND pg_am.amname = 'btree'
    and ti.relkind = ANY (ARRAY['r', 't'])
    and case when s.leaf_fragmentation = 'NaN' then 0 else s.leaf_fragmentation end >= index_frag_threshold_to_rebuild
  order by "LeafFragmentation" desc;
begin
 open indexes_cursor;

 loop
     fetch indexes_cursor into index_info;
     exit when not found;
     
     sql_index_rebuild_result := sql_index_rebuild_result 
      || chr(10) 
      || case when use_index_concurrently_rebuild then 'REINDEX INDEX CONCURRENTLY ' else 'REINDEX INDEX ' end
      || index_info."IndexName"::text;
    
     if generate_script_only is not true then     
      EXECUTE format('%s  %s', 
       case when use_index_concurrently_rebuild then 'REINDEX INDEX CONCURRENTLY' else 'REINDEX INDEX' end, 
       index_info."IndexName"::text);
  end if;
  end loop;

 close indexes_cursor;

 RAISE NOTICE '%', sql_index_rebuild_result;
end
$$ language 'plpgsql';

Тяжелое обслуживание

Может потребоваться для ручного запуска при каких-либо массовых изменениях в базе. Например, если разработчики применили огромные миграции базы данных. Скрипт полностью перестраивает таблицы, удаляет все “мертвые” строки и обновляет статистику.Оригинал скрипта.

#! /bin/bash
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres # Пользователь, от которого запустится обслуживание
export PGPASSWORD=postgres # Пароль этого пользователя

# Получаем список баз данных
dblist=`psql -d postgres -c "copy (select datname from pg_stat_database) to stdout"`
for db in $dblist ; do

    # Игнорируем служебные базы данных
    if [[ $db == template0 ]] ||  [[ $db == template1 ]] || [[ $db == postgres ]] ; then
        continue
    fi

    # Выполняем сборку мусора
    psql -d $db -e -a -c "VACUUM;"
    # Перестраиваем системные индексы
    psql -d $db -e -a -c "REINDEX SYSTEM $db;"
    # Сохраняем список таблиц во временный файл
    cp /dev/null tables.txt
    psql -d $db -c "copy (select '"'||tables.schemaname||'".' || '"'||tables.tablename||'"' from (select nspname as schemaname, relname as tablename from pg_catalog.pg_class, pg_catalog.pg_namespace, pg_catalog.pg_roles where pg_class.relnamespace = pg_namespace.oid and pg_namespace.nspowner = pg_roles.oid and pg_class.relkind='r' and (pg_namespace.nspname = 'public' or pg_roles.rolsuper = 'false' ) ) as tables(schemaname, tablename)) to stdout;" > tables.txt
    
    while read line; do
        
        # Экранируем в именах таблицы служебный символ $
        line=`echo $line |sed 's/\$/\\\$/g'`
        
        # Выполняем полную очистку
        psql -d $db -e -a -c "VACUUM FULL $line;"
        # Перестраиваем индексы таблицы
        psql -d $db -e -a -c "REINDEX TABLE $line;"

    done <tables.txt
done

Как автоматизировать запуск

Для автоматизации запуска скриптов есть несколько путей:

• Cron - использовать стандартный планирощик *.nix-систем. (Елси вы на Windows, то можно использовать стандартный планировщик ОС).
• pgAgent - планировщик задач, созданный специально для PostgreSQL. Позволяет запускать как SQL-скрипты, так и bash/cmd-скрипты (в зависимости от ОС). Этот вариант обычно проще в обслуживании и настройке.Подробнее об этом можете прочитать здесь.

Вместо заключения

Это лишь базовая информация в части обслуживания баз данных PostgreSQL. Тему можно развивать и описать множество нюансов обслуживания. Причем, чем “хуже” софт в части работы с базой данных, тем больше этих нюансов возникает.

При этом эффективную настройку как СУБД, так и облуживания можно сделать только при организации постоянного мониторинга СУБД и инфораструктуры. В противном случае все действия будут выполняться вслепую. А это непрофессионально, ведь метод “тыка” это не для администрирования и разработки, это совсем другая сфера деятельности :).

Информацию по обслуживанию и другие интересности в части работы PostgreSQLВы можете найти здесь.