Лекция 10. Обслуживание СУБД

В этой главе:

Мониторинг дискового пространства
Автоочистка
Статистика планировщика
Мониторинг производительности
Журналы отчета

Мониторинг дискового пространства

Средства операционной системы

[postgres@p620 ~]$ df -h /dev/mapper/ro_p620-root
Файловая система             Размер        Использовано        Дост           Использовано% Cмонтировано в 
/dev/mapper/ro_p620-root      27G              14G             12G                 54%            /
[postgres@p620 ~]$ du -sh $PGDATA 
195M       /pgdata/06/data

Основные средства Unix - подобных операционных систем: команда df, показывающая оставшееся свободное место в файловой системе, и команда du - выводящая объем, занятый файлами в каталогах, указанных аргументами командной строки.

Если команду df вызвать без аргументов, она показывает свободное место во всех смонтированных файловых системах. Опция -h задает вывод информации в удобных единицах (human readable format). См. man df.

Команда du по умолчанию рекурсивно выводит занимаемое подкаталогами указанного в качестве аргумента каталога дисковое пространство. Опция -s вместо этого выдает суммарное дисковое пространство, занимаемое всеми файлами в этом каталоге. См. man du.

Если в файловой системе используется квотирование дискового пространства, то сведения об израсходованном пространстве и количестве файлов предоставляет команда quota. См. man quota.

Средства СУБД

Функции:

pg_relation_size() - размер слоя в байтах;
pg_indexes_size() - суммарный размер всех индексов таблицы;
pg_table_size() - размер таблицы с учетом TOAST;
pg_total_relation_size() - общий размер таблицы, всех ее индексов и TOAST;
pg_database_size() - размер, занимаемый базой данных.

Расширение pgstattuple:

точное определение размера отношений;
определение количества мертвых версий строк;
оценка свободного пространства для вставки данных;
оценка плотности;
аппроксимации.

О функциях, возвращающих размеры таблиц, индексов, TOAST и баз данных целиком подробно говорилось в главе 9. https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT.

https://www.postgresql.org/docs/15/disk-usage.html.

Очистка

Одна из регулярных задач обслуживания баз данных заключается в исключении их раздувания, вызванным механизмом MVCC, используемым в PostgreSQL. Так как в результате обновления строк старые версии помечаются как удаленные, а при этом появляются новые версии строк, то каждая команда UPDATE удваивает количество обработанных ею версий строк. Команда DELETE просто помечает удаленные строки с помощью xmax. Удаленные версии строк, не входящие ни в один снимок данных, просто занимают место на диске и ни для чего не нужны. Они обязательно должны подвергаться очистке, иначе дисковое пространство исчерпается в результате раздувания таблиц и индексов. Очистку вручную выполняет команда VACUUM и автоматически - подсистема autovacuum. В командной строке ОС используется команда vacuumdb.

В результате очистки вместо мертвых версий строк образуется пустое пространство на страницах, пригодное для вставки новых версий строк. Своевременная очистка - правильно настроенный autovacuum, плюс отсутствие долгих транзакций, позволяют избежать раздувания. Помимо этого при очистке строятся карты видимости и свободного пространства. Еще одна важная задача, выполняемая очисткой - предотвращение зацикливания счетчика транзакций. Это делается с помощью заморозки. Очистка не блокирует операции чтения и записи, но операции, требующие блокировки таблицы, например, CREATE INDEX, будут дожидаться освобождения блокировки. https://www.postgresql.org/docs/15/routine-vacuuming.html.

Освобождение места в файловой системе

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

Значительно уменьшить размер файла можно попытаться перестроением командой VACUUM FULL. Этак команда полностью блокирует таблицу и последовательно копирует актуальные версии строк в новые файлы данных. После завершения этого процесса OID таблицы меняется, а старые файлы данных становятся пустыми. В процессе работы VACUUM FULL на диске требуется свободное место, не меньшее, возможно, чем исходный размер перестраиваемой таблицы. https://www.postgresql.org/docs/15/sql-vacuum.html.

Команда CLUSTER выполняет похожую работу, но дополнительно еще и упорядочивает строки по ключу при перестроении таблицы. https://www.postgresql.org/docs/15/sql-cluster.html.

Индексы можно перестроить командой REINDEX. У этой команды имеется менее блокирующий вариант REINDEX CONCURRENTLY. https://www.postgresql.org/docs/15/sql-reindex.html.

В Pangolin 6.2.0 поставляется расширение pg_repack, позволяющее в менее блокирующем режиме, чем VACUUM FULL выполнить перестройку таблицы с уплотнением.

Автоочистка

postgres=# SELECT pid, backend_type FROM pg_stat_activity;
  pid | backend_type 
------+------------------------------
  919 | autovacuum launcher
  920 | autounite launcher
  921 | integrity check launcher
  922 | logical replication launcher
 7351 | client backend 
  904 | background writer 
  903 | checkpointer
  917 | walwriter
(8 строк)

Вызов VACUUM вручную и по календарю (cron или аналог) не предохраняет от раздувания.
Подсистема autovacuum действует автоматически и при правильной настройке минимизирует раздувание.
Автоочистка включается параметром autovacuum, он настоятельно рекомендуется.
Процесс autovacuum проверяет собранную статистику по количеству изменений в базах, поэтому должен быть включен параметр track_counts.
Работу по автоочистке запускает процесс autovacuum lancher, а собственно очисткой занимаются autovacuum workers.

Очистка, выполняемая с помощью систем календарного планирования, например, cron, не может учесть текущее количество изменений в базе данных. С этим гораздо лучше справляется подсистема автоочистки - autovacuum. Хоть она и опциональна, но крайне рекомендуется ее включать. Для ее работы требуется включить два параметра (по умолчанию они включены):

postgres=# \dconfig autovacuum|track_counts
Список параметров конфигурации
   Параметр   | Значение
--------------+----------
 autovacuum   | on
 track_counts | on
(2 строки)

Подсистема автоочистки представлена двумя разновидностями процессов:

autovacuum launcher - отслеживает количество изменений в базах данных и при достижении заданного параметрами уровня изменений параллельно запускает процессы очистки;
autovacuum worker - процесс очистки, который выполняет такую же работу, как обычный VACUUM - очищает мертвые версии строк. https://www.postgresql.org/docs/15/routine-vacuuming.html.

Важнейшие параметры автоочистки

Правильная настройка автоочистки критически важна для предотвращения распухания баз данных. Она влияет также и на подсистему ввода-вывода.

Автоочистка запускает процесс очистки для конкретной базы данных при достижении порога, определяемого формулой: количество_измененных_строк = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * количество_строк_в _таблице Периодичность попыток запуска очистки определяется параметром autovacuum_naptime.

Запущенный процесс очистки работает с паузами для снижения общей нагрузки на систему. Для VACUUM vacuum_cost_limit, для автоочистки autovacuum_vacuum_cost_limit определяют суммарные затраты на действия по очистке, по достижении которых необходимо временно приостановить очистку на vacuum_cost_delay (для обычной очистки) или autovacuum_vacuum_cost_delay (для автоочистки).

В любой момент времени может быть запущено не более autovacuum_max_workers процессов. https://www.postgresql.org/docs/15/routine-vacuuming.html#AUTOVACUUM.

autovacuum_vacuum_scale_factor - пороговый процент измененных строк, требуемый для срабатывания очистки;
autovacuum_vacuum_threshold - базовое пороговое значение количества измененных строк, ниже которого очистка не запустится;
autovacuum_vacuum_cost_limit - ограничение количества операций, выполненных очисткой, по достижении которых необходимо сделать паузу;
autovacuum_vacuum_cost_delay - время, на которое необходимо сделать паузу;
autovacuum_max_workers - максимально количество процессов очистки в один момент времени;
autovacuum_naptime - периодичность запуска процессов очистки для каждой базы данных.

Статистика планировщика

Обновление статистики планировщика

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

Планировщику (оптимизатору запросов) необходимы статистические сведения о данных в объектах базы для выработки правильного плана запроса. Так как данные в отношениях базы изменяются, причем нередко весьма значительно, задача сбора статистики - одна из важнейших регулярных задач. Как и очистку эту задачу в ранних версиях PostgreSQL доверяли системам календарного выполнения задач типа cron. Однако, гораздо эффективнее эту задачу решать с помощью автоочистки. Процесс очистки последовательно просматривает страницы отношений для поиска мертвых версий строк. Разумно вместе с этим сразу заботиться и о сборе статистических сведений. https://www.postgresql.org/docs/15/routine-vacuuming.html#VACUUM-FOR-STATISTICS.

Качественное планирование запросов невозможно без сбора статистических сведений о данных в отношениях.
Время выполнения запросов сильно растет, иногда на порядки, если нет оптимального плана запроса.
Ручной сбор статистики:
- ANALYZE или VACUUM ANALYZE
- vacuumdb --analyze или vacuumdb --analyze-only в командной строке операционной системы
Процесс автоочистки собирает статистику автоматически на регулярной основе.

Параметры автоочистки для сбора статистики

student=# \dconfig autovacuum_analyze* Список параметров конфигурации
                        Параметр | Значение 
---------------------------------+----------
autovacuum_analyze_scale_factor  | 0.1
autovacuum_analyze_threshold     | 50 
(2 строки)

autovacuum_analyze_scale_factor - пороговый процент измененных строк, требуемый для срабатывания очистки;
autovacuum_analyze_threshold - базовое пороговое значение количества измененных строк, ниже которого очистка не запустится.

Автоочистка собирает статистику для конкретной базы данных при достижении порога, определяемого формулой: количество_измененных_строк = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * количество_строк_в _таблице. https://www.postgresql.org/docs/15/routine-vacuuming.html#VACUUM-FOR-STATISTICS.

Мониторинг производительности

Кумулятивная статистика

В PostgreSQL 15 была значительно изменена система сбора статистики о работе (не путать со статистикой планировщика). Раньше имелся специальный процесс stats collector. Теперь все процессы экземпляра самостоятельно собирают статистические данные о своей работе и запоминают их в своей локальной памяти. Периодически эти данные передаются в общую память.

Статистика является кумулятивной, то есть она накапливает подсчитанные значения количества:

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

Отдельно включается подсчет обращений к пользовательским функциям, можно фиксировать время, затраченное на их выполнение. https://www.postgresql.org/docs/15/monitoring-stats.html.

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

Управление сбором статистики

track_activities - включает сбор кумулятивной статистики в процессах экземпляра;
track_counts - включает сбор сведений об обращениях к таблицам и индексам;
track_functions - сбор сведений о пользовательских функциях (выключен по умолчанию);
track_io_timing - отслеживание времени, затрачиваемого на чтение и запись блоков данных (выключен по умолчанию);
track_wal_io_timing - включает отслеживание времени, затрачиваемого на операции записи в WAL (выключен по умолчанию).

Сбор кумулятивной статистики настраивают параметры, указанные на слайде:

student=# \dconfig+ track_*
                       Список параметров конфигурации
         Параметр          | Значение |   Тип   |  Контекст  |
---------------------------+----------+---------+------------+-
track_activities          | on        | bool    | superuser  |
track_activity_query_size | 1kB       | integer | postmaster |
track_commit_timestamp    | off       | bool    | postmaster |
track_counts              | on        | bool    | superuser  |
track_functions           | none      | enum    | superuser  |
track_io_timing           | off       | bool    | superuser  |
track_wal_io_timing       | off       | bool    | superuser  | 
(7 строк)

Параметр track_activity_query_size не показан на слайде, так как он отвечает просто за размер памяти, зарезервированной для хранения кода исполняемых сейчас команд для каждой сессии, выводимых в столбце pg_stat_activity.query. https://www.postgresql.org/docs/15/monitoring-stats.html#MONITORING-STATS-SETUP.

Динамические представления производительности

pg_stat_activity - для каждого процесса экземпляра содержит одну строку со сведениями о текущей работе процесса, его состоянии и обрабатываемом запросе;
pg_stat_* - индивидуальные представления с информацией о:
- процессах репликации
- восстановления
- логической подписки
- состояния SSL
- состояния аутентификации GSSAPI
- выполнении сбора статистики планировщика (ANALYZE)
- выполнении создания индекса
- выполнении процесса очистки (VACUUM)
- выполнении перестроения таблицы (VACUUM FULL или CLUSTER)
- выполнении физического копирования
- работе команды COPY

Динамические представления производительности информируют о происходящих в системе процессах в текущий момент времени:

pg_stat_activity
pg_stat_replication
pg_stat_wal_receiver
pg_stat_recovery_prefetch
pg_stat_subscription
pg_stat_ssl
pg_stat_gssapi
pg_stat_progress_analyze
pg_stat_progress_create_index
pg_stat_progress_vacuum
pg_stat_progress_cluster
pg_stat_progress_basebackup
pg_stat_progress_copy https://www.postgresql.org/docs/15/monitoring-stats.html#MONITORING-STATS-DYNAMIC-VIEWS-TABLE.

Активность транзакций

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

В транзакции можно получить ее собственную статистику:

pg_stat_xact_all_tables - обращения ко всем таблицам
pg_stat_xact_sys_tables - обращения только к системным таблицам
pg_stat_xact_user_tables - обращение к пользовательским таблицам
pg_stat_xact_user_functions - обращение к пользовательским функциям

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

                          Список отношений
   Схема    |             Имя             |      Тип      | Владелец
------------+-----------------------------+---------------+----------
 pg_catalog | pg_stat_xact_all_tables     | представление | postgres
 pg_catalog | pg_stat_xact_sys_tables     | представление | postgres
 pg_catalog | pg_stat_xact_user_functions | представление | postgres
 pg_catalog | pg_stat_xact_user_tables    | представление | postgres
(4 строки)

Некоторые представления собранной статистики

pg_stat_database - статистика на уровне БД;
pg_stat_bgwriter - статистика фоновой записи и контрольных точек;
pg_stat_all_tables - статистика обращений к таблицам;
pg_stat_all_indexes - статистика обращений к индексам;
pg_stat_io - статистика ввода-вывода;
pg_statio_all_tables - статистика обращений к таблицам в блоках;
pg_statio_all_indexes - статистика обращений к индексам в блоках.

Полный список представлений для отображения кумулятивной статистики: https://www.postgresql.org/docs/15/monitoring-stats.html#MONITORING-STATS-VIEWS.

Представления pg_statio_* и pg_stat_io используются для определения эффективности работы буферного кеша. С Pangolin 6.2.0 поставляются также и расширения для сбора статистики:

student=# SELECT name FROM pg_available_extensions WHERE name ~ 'stat';
name
--------------------
pg_stat_statements
pgstattuple
pg_stat_kcache
(3 строки)

Заголовки процессов и имя приложения

Параметры отображения заголовков:

cluster_name - имя кластера;
update_process_title - обновлять заголовок процесса при выполнении новой SQL команды.

Идентификация источника

application_name - имя приложения, которое будет отображаться в pg_stat_activity.

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

Параметр update_process_title включает обновление заголовка процесса при выполнении следующей команды SQL.

https://www.postgresql.org/docs/15/runtime-config-logging.html#id-1.6.7.11.8.

Для удобного отображения имени процесса в представлении pg_stat_activity можно задать параметр application_name.

https://www.postgresql.org/docs/15/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT.

Журнал отчета

Способ ведения журнала

PostgreSQL обладает возможностью вести отчеты с сообщениями разными способами. Способ сохранения сообщений, записываемых в журнал, определяет параметр log_destination:

stderr - в простейшем случае поток сообщений передается в стандартный поток вывода ошибок и непосредственно записывается в некоторый файл или через конвейер передается для дальнейшей обработки, этот способ подходит для подключения системы log_collector (сборщик сообщений);
csvlog - сообщения будут записываться в CSV файл, формат которого описан в документации;
jsonlog - сообщения будут записываться в JSON файл, формат которого описан в документации;
eventlog предназначен для передачи сообщений системе сбора журналов в MS Windows;
syslog - передача сообщений по прикладному сетевому протоколу SYSLOG, при это необходимо указать параметр syslog_facility (канал журналирования), по умолчанию LOCAL0.

Передача сообщений по протоколу SYSLOG подходит для построения централизованных систем сбора сообщения от разных устройств. Часто в PostgreSQL используется свой собственный сборщик сообщений, способный выполнять ротацию журналов без дополнительного ПО. В таком случае log_destination = stderr и logging_collector = on. https://www.postgresql.org/docs/15/runtime-config-logging.html.

Сборщик сообщений

При включенном параметре logging_collector = on поток сообщений из stderr передается специальному процессу - сборщику сообщений.

Сборщик сообщений самостоятельно записывает сообщения в файл журнала и может выполнять его ротацию.

Каталог расположения журнала задается параметром log_directory.

Шаблон для имен журнальных файлов задается параметром log_filename в формате strftime, причем суффикс имени файла может:

.log - задан шаблоном по умолчанию и может меняться;
автоматически меняется на .csv при log_destination = csvlog;
автоматически меняется на .json при log_destination = jsonlog;

Включение сборщика сообщений требует рестарт сервера.

postgres=# ALTER SYSTEM SET logging_collector TO on;
postgres=# ALTER SYSTEM SET log_directory = '/pgerrorlogs/06';
postgres=# \q
[student@p620 ~]$ sudo systemctl restart postgresql
[student@p620 ~]$ psql -d student -U postgres -h localhost
postgres@student=# SELECT pg_current_logfile();
                pg_current_logfile
--------------------------------------------------
 /pgerrorlogs/06/postgresql-2024-10-21_143500.log
(1 строка)
postgres=# \q
[student@p620 ~]$ sudo lsof /pgerrorlogs/06/postgresql-2024-10-
21_143500.log
COMMAND    PID     USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
postgres 14732 postgres    7w   REG  253,0     1533 1048581
/pgerrorlogs/06/postgresql-2024-10-21_143500.log

После необходимого изменения параметров конфигурации и рестарта системы проверить, в какой файл отчета пишется журнал сообщений сервера можно функцией pg_current_logfile(). Команда ОС lsof подтверждает, что файл журнала открыт процессом postgres.

Ротация файлов журналов отчета

При ротации файл переименовывается или удаляется и сборщик сообщений продолжает запись сообщений в следующий файл.

Ротация вынуждает для параметра log_filename использовать шаблон, а не статичное имя файла.

Настройки ротации:

log_filename - шаблон должен соответствовать периодичности ротации;
log_file_mode - права, устанавливаемые на новый файл журнала отчета;
log_rotation_age - время жизни файла журнала отчета до наступления его ротации;
log_rotation_size - размер файла журнала отчета по достижении которого будет выполнена его ротация;
log_truncate_on_rotation - переписывать содержимое файла вместо создания нового файла.

https://www.postgresql.org/docs/15/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE.

Некоторые настройки сообщений

Когда генерировать сообщения в журнал:

– log_min_messages - порог отбрасывания сообщений недостаточной важности; – log_min_duration_statement - запись сообщений о командах, работающих дольше, чем заданное этим параметром время; – log_startup_progress_interval - сообщать о задержке процесса startup при превышении заданного параметром времени.

О чем сообщать:

– log_checkpoints - контрольная точка; – log_connections/log_disconnections - подключения/отключения; – log_duration - включать в сообщение длительность команды; – log_error_verbosity - уровень подробностей сообщения; – log_line_prefix - префикс строк сообщений; – log_hostname - включать имя хоста сервера; – log_lock_waits - сообщать о превышении deadlock_timeout при ожидании; – log_statement - о каких командах писать сообщения (off,ddl,mod,all); – log_temp_files - информировать об использовании временных файлов.

На слайде приведены некоторые настройки журналирования, определяющие в связи с чем писать сообщения и какие в них подробности должны быть. https://www.postgresql.org/docs/15/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN.

https://www.postgresql.org/docs/15/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT.

Итоги

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

Мониторинг дискового пространства​

Средства операционной системы​

Средства СУБД​

Очистка​

Освобождение места в файловой системе​

Автоочистка​

Важнейшие параметры автоочистки​

Статистика планировщика​

Обновление статистики планировщика​

Параметры автоочистки для сбора статистики​

Мониторинг производительности​

Кумулятивная статистика​

Управление сбором статистики​

Динамические представления производительности​

Активность транзакций​

Некоторые представления собранной статистики​

Заголовки процессов и имя приложения​

Журнал отчета​

Способ ведения журнала​

Сборщик сообщений​

Ротация файлов журналов отчета​

Некоторые настройки сообщений​

Итоги​