Deep dive into PostgreSQL internal statistics. Алексей Лесовский05.06.2020 12:47

Расшифровка доклада 2015 года Алексея Лесовского «Deep dive into PostgreSQL internal statistics»

Disclaimer от автора доклада: Замечу что доклад этот датирован ноябрем 2015 года — прошло больше 4 лет и прошло много времени. Рассматриваемая в докладе версия 9.4 уже не поддерживается. За прошедшие 4 года вышло 5 новых релизов в которых появилась масса новшеств, улучшений и изменений относительно статистики и часть материала устарела и не актуальна. По мере ревью я постарался отметить эти места чтобы не вводить тебя читатель в заблуждения. Переписывать же эти места я не стал, их очень много и получится в итоге совсем другой доклад.

СУБД PostgreSQL — это огромный механизм, при этом состоит этот механизм из множества подсистем, от слаженной работы которых напрямую зависит производительность СУБД. В процессе эксплуатации обеспечивается сбор статистики и информации о работе компонентов, что позволяет оценить эффективность PostgreSQL и принять меры для повышения производительности. Однако, этой информации очень много и представлена она в достаточно упрощенном виде. Обработка этой информации и ее интерпретация порой совсем нетривиальная задача, а «зоопарк» инструментов и утилит запросто поставит в тупик даже продвинутого DBA.

[embedded content]

Добрый день! Меня зовут Алексей. Как Илья сказал, я буду рассказывать про статистику PostgreSQL.

Статистика активности PostgreSQL. У PostgreSQL есть две статистики. Статистика активности, про которую будет речь. И статистика планировщика о распределении данных. Я буду рассказывать именно о статистике активности PostgreSQL, которая позволяет нам судить о производительности и как-то ее улучшать.

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

Чего не будет в докладе? В докладе я не буду касаться статистики планировщика, т.к. это отдельная тема на отдельный доклад о том, как данные хранятся в базе и о том как планировщик запросов получает представление о качественных и количественных характериситках этих данных.

И не будет обзоров инструментов, я не буду сравнивать один продукт с другим. Никакой рекламы не будет. Отбросим это.

Я хочу вам показать, что использовать статистику — это полезно. Это нужно. Использовать ее нестрашно. Нам понадобится всего лишь обычный SQL и базовые знания о SQL.

И поговорим, какую статистику выбирать для решения проблем.

Если мы посмотрим на PostgreSQL и в операционной системе запустим команду для просмотра процессов, то увидим «черный ящик». Мы увидим какие-то процессы, которые что-то делают, и мы по названию можем примерно представить, что они там делают, чем занимаются. Но, по сути, это черный ящик, вовнутрь мы заглянуть не можем.

Мы можем посмотреть нагрузку на процессор в top, можем посмотреть утилизацию памяти какими-то системными утилитами, но заглянуть вовнутрь PostgreSQL мы не сможем. Для этого нам нужны другие инструменты.

И продолжая дальше я расскажу, куда тратится время. Если мы представим PostgreSQL в виде такой схемы, то можно будет ответить, куда тратится время. Это две вещи: это обработка клиентских запросов от приложений и фоновые задачи, которые выполняет PostgreSQL для поддержания своей работоспособности.

Если мы начнем рассматривать с левого верхнего угла, то мы можем проследить, как обрабатывается клиентские запросы. Запрос приходит от приложения и для дальнейшей работы открывается клиентская сессия. Запрос передается в планировщик. Планировщик строит план запроса. Отправляет его дальше на выполнение. Происходит какой-то блочный ввод-вывод данных связанный с таблицами и индексами. Необходимые данные читаются с дисков в память в специальную область «shared buffers». Результаты запроса, если это updates, deletes, фиксируются в журнале транзакций в WAL. Некоторая статистическая информация попадает в лог или в коллектор статистики. И результат запроса отдается уже клиенту обратно. После чего клиент может повторить все заного с новым запросом.

Что у нас с фоновыми задачами и с фоновыми процессами? У нас есть несколько процессов, которые обеспечивают работоспособность и поддерживают базу данных в нормальном рабочем режиме. Эти процессы также будут затрагиваться в докладе: это autovacuum, checkpointer, процессы, связанные с репликацией, background writer. Каждого из них я буду затрагивать по мере доклада.

Какие проблемы есть со статистикой?

Информации много. PostgreSQL 9.4 предоставляет 109 метрик для просмотра данных статистики. Однако, если в базе данных хранятся много таблиц, схем, баз, то все эти метрики придется умножить на соответствующее количество таблиц, баз. Т. е. информации становится еще больше. И утонуть в ней очень легко.
Следующая проблема — это то, что статистика представлена счетчиками. Если мы посмотрим эту статистику, то мы увидим постоянно увеличивающиеся счетчики. И если с момента сброса статистики прошло очень много времени, мы увидим миллиардные значения. И они нам ничего не говорят.
Нет истории. Если у вас произошел какой-то сбой, что-то упало 15–30 минут назад, не получится воспользоваться статистикой и посмотреть, что происходило 15–30 минут назад. Это проблема.
Отсутствие встроенного в PostgreSQL инструмента — это проблема. Разработчики ядра не предоставляют никакой утилиты. У них нет ничего такого. Они просто дают статистику в базе. Пользуйтесь, делайте к ней запрос, что хотите, то и делайте.
Так как встроенного в PostgreSQL инструмента нет, то это является причиной другой проблемы. Множество сторонних инструментов. Каждая компания, у которой есть более-менее прямые руки, пытается написать свою программу. И в итоге в community очень много инструментов, которыми можно пользоваться для работы со статистикой. И в одних инструментах есть одни возможности, в других инструментах нет других возможностей, либо есть какие-то новые возможности. И возникает ситуация, что нужно использовать два-три-четыре инструмента, которые друг друга перекрывают и обладают разными функциями. Это очень неприятно.

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

И нужны базовые знания SQL. Чтобы получить какие-то данные из статистики, нужно составить запросы SQL, т. е. вам нужно знать, как составляются select, join.

Статистика предлагает нам несколько вещей. Их можно разделить на категории.

Первая категория — это события, происходящие в базе. Это когда в базе происходит какое-то событие: запрос, обращение к таблице, автовакуум, коммиты, то это все события. Соответствующие этим события счетчики инкрементируются. И мы можем отследить эти события.
Вторая категория — это свойства объектов такие, как таблицы, базы. У них есть свойства. Это размер таблиц. Мы можем отследить рост таблиц, рост индексов. Можем посмотреть изменения в динамике.
И третья категория — это время, затраченное на событие. Запрос — это событие. У него есть своя конкретная мера длительности. Здесь запустился, тут закончился. Мы можем это отследить. Либо время чтения блока с диска или записи. Такие вещи тоже отслеживаются.

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

В разделяемой памяти (shared buffers) есть сегмент для размещения там статичтических данных, там есть и те самые счетчики, которые постоянно инкрементируются, когда происходит те или иные события, либо возникают какие-то моменты в работе базы.
Все эти счетчики не доступны пользователю и даже не доступны администратору. Это низкоуровневые вещи. Чтобы к ним обратиться PostgreSQL предоставляет интерфейс в виде SQL функций. Мы можем сделать select выброки с помощью этих функций и получить какую-то метрику (или набор метрик).
Однако использовать эти функции не всегда удобно, поэтому функции являются базой для представлений (VIEWs). Это виртуальные таблицы, которые предоставляют статистику по какой-то конкретной подсистеме, либо по какому-то набору событий в базе данных.
Эти встроенные представления (VIEWs) являются основным интерфейсом пользователя для работы со статистикой. Они доступны по-умолчанию без какой либо дополнительной настройки, можете сразу ими пользоваться, смотреть, брать оттуда информацию. А еще есть contrib’ы. Contrib’ы есть официальные. Вы можете установить пакет postgresql-contrib (например, postgresql94-contrib), подгрузили необходимый модуль в конфигурации, указать для него параметры, перезапустить PostgreSQL и можно пользоваться. (Примечание. В зависимости от дистрибутива, в последних версиях contrib пакет является частью основного пакета).
И есть неофициальные contrib. Они не поставляются в стандартной поставке PostgreSQL. Их нужно либо скомпилировать, либо установить как библиотеку. Варианты могут быть самые разные, в зависимости от того, что придумал разработчик этого неофициального contrib«а.

На этом слайде представлены все те представления (VIEWs) и часть тех функций, которые доступны в PostgreSQL 9.4. Как мы видим, их очень много. И довольно легко запутаться, если вы столкнулись с этим в первый раз.

Однако, если мы возьмем предыдущую картинку Как тратится время на PostgreSQL и совместим с этим списком, то получим вот такую картинку. Каждое представление (VIEWs), либо каждую функцию мы можем использовать в тех или иных целях для получения соответствующей статистики, когда у нас работает PostgreSQL. И можем получить уже какую-то информацию о работе подсистемы.

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

Что мы можем полезное оттуда взять? Начнем c самых простых вещей.

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

Первое, что мы можем посмотреть — это процент попадания в кэш. Процент попадания в кэш — это полезная метрика. Она позволяет оценить, какой объем данных берется из кэша shared buffers, а какой объем читается с диска.

Понятное дело, что чем большее у нас попадание в кэш, то тем лучше. Мы оцениваем эту метрику как процент. И, например, если у нас процентное отношение этих попаданий в кэш больше 90%, то это хорошо. Если оно опускается ниже 90%, значит, у нас памяти недостаточно для удержания горячей «головы» данных в памяти. И чтобы эти данные использовать, PostgreSQL вынужден обращаться к диску и это медленнее чем если бы данные читались из памяти. И нужно уже думать над увеличением памяти: либо shared buffers увеличивать, либо наращивать железную память (RAM).

select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;

Что можно еще взять из этого представления? Можно посмотреть аномалии происходящие в базе. Что здесь показано? Здесь есть commits, rollbacks, создание временных файлов, их объем, deadlocks и конфликты.

Мы можем воспользоваться этим запросом. Этот SQL довольно простой. И можем посмотреть вот эти данные у себя.

И здесь сразу пороговые значения. Мы смотрим соотношение commits и rollbacks. Commits — это успешное подтверждение транзакции. Rollbacks — это откат, т. е. транзакция делала какую-то работу, напрягала базу, что-то считала, а потом произошел сбой, и результаты транзакции отбрасываются. Т. е. количество rollbacks, постоянно увеличивающихся, это плохо. И следует как-то избегать их, и править код, чтобы такого не происходило.

Конфликты (conflicts) связаны с репликацией. И их тоже следует избегать. Если у вас какие-то запросы, которые выполняются на реплике и возникают конфликты, то нужно эти конфликты разбирать, смотреть, что происходит. Детали можно найти в логах. И устранять конфликтные ситуации, чтобы запросы приложения работали без ошибок.

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

Временные файлы (temp_files) — это тоже плохо. Когда пользовательскому запросу не хватает памяти для размещения оперативных, временных данных, он создает на диске файл. И все операции которые бы он мог выполнить во временном буфере в памяти, начинает выполнять уже на диске. Это медленно. Это увеличивает время выполнения запроса. И клиент, отправивший запрос к PostgreSQL получит ответ чуть позже. Если эти все операции будут выполняться в памяти, Postgres ответит гораздо быстрее и клиент будет меньше ждать.

Pg_stat_bgwriter — это представление описывает работу двух фоновых подсистем PostgreSQL: это checkpointer и background writer.

Для начала разберем контрольные точки, т.н. checkpoints. Что такое контрольные точки? Контрольная точка это позиция в журнале транзакций сообщающая что все изменения данных зафиксированные в журнала успешно синхронизированны с данными на диске. Процесс в зависимости от рабочей нагрузки и настроек может быть длительными и по большей части заключается в синхронизации грязных страниц в shared buffers с датфайлами на диске. Для чего это нужно? Если бы PostgreSQL все время обращался к диску и брал оттуда данные, и записывал данные при каждом обращении, это было бы медленно. Поэтому у PostgreSQL есть сегмент памяти, размер которого зависит от параметров в конфигурации. Postgres размещает в этой памяти оперативные данные для последующей обработки или выдачи по запросам. В случае запросов на изменение данных происходит их изменение. И мы получаем две версии данных. Одна у нас в памяти, другая на диске. И периодически нужно эти данные синхронизировать. Нам нужно то, что изменено в памяти, синхронизировать на диск. Для этого нужны checkpoint.

Checkpoint проходит по shared buffers, помечает грязные страницы, что они нужны для checkpoint. Потом запускает второй проход по shared buffers. И страницы, которые помечены для checkpoint, он их уже синхронизирует. Таким образом выполняется синхронизация данных уже с диском.

Есть два типа контрольных точек. Один checkpoint выполняется по тайм-ауту. Это checkpoint полезный и хороший — checkpoint_timed. И есть checkpoints по требованию — checkpoint required. Такая контрольная точка происходит когда у нас идет очень большая запись данных. Мы записали очень много журналов транзакций. И PostgreSQL считает, что ему нужно все это как можно быстрее синхронизировать, сделать контрольную точку и жить дальше.

И если вы посмотрели статистику pg_stat_bgwriter и увидели, что у вас checkpoint_req гораздо больше, чем checkpoint_timed, то это плохо. Почему плохо? Это значит, что PostgreSQL находится в постоянной стрессовой ситуации, когда ему нужно записывать данные на диск. Checkpoint по таймауту менее стрессовый и выполняется согласно внутреннему расписанию и как бы растянут по времени. У PostgreSQL есть возможность сделать паузы в работе и не напрягать дисковую подсистему. Это для PostgreSQL полезно. И запросы, которые выполняются во время checkpoint не будут испытывать стрессы от того, что дисковая подсистема занята.

И для регулировки checkpoint есть три параметра:

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

Внимание: Рассматриваемая в докладе версия 9.4 уже неактуальна. В современных версиях PostgreSQL параметр checkpoint_segments заменен параметрами min_wal_size и max_wal_size.

Следующая подсистема это фоновый писатель — background writer. Что он делает? Он работает постоянно в бесконечном цикле. Сканирует страницы в shared buffers и странички грязные, которые он нашел, сбрасывает на диск. Таким образом он помогает checkpointer’у делать меньше работы в процессе выполнения контрольных точек.

Для чего он еще нужен? Он обеспечивает потребность в чистых страницах в shared buffers если они вдруг потребуются (в большом количестве и сразу) для размещения данных. Предположим возникла ситуация когда для выполнения запроса потребовались чистые страницы и они уже есть в shared buffers. Постгресовый backend просто берет их и использует, ему не надо самому ничего чистить. Но если вдруг таких страниц нет, бэкенд приостанавливает работу и начинает поиск страниц чтобы сбросить их на диск и взять для своих нужд — что негативно сказывается на времени выполняющегося в данный момент запроса. Если вы видите, что у вас параметр maxwritten_clean большой, это значит, что background writer не справляется со своей работой и нужно увеличивать параметры bgwriter_lru_maxpages, чтобы он смог за один цикл сделать больше работы, больше очистить страничек.

И другой очень полезный показатель — это buffers_backend_fsync. Бэкенды не делают fsync, потому что это медленно. Они передают fsync выше по IO stack checkpointer«у. У checkpointer есть своя очередь, он периодически fsync обрабатывает и страницы в памяти синхронизирует с файлами на диске. Если очередь большая у checkpointer и заполнена, то бэкенд вынужден сам делать fsync и это замедляет работу бэкенда, т. е. клиент получит ответ позже, чем мог бы. Если вы видите, что у вас это значение больше нуля, то это уже проблема и нужно обратить внимание на настройки background writer’а и также оценить производительность дисковой подсистемы.

Внимание: _Следующий текст описывает статистические представления связанные с репликацией. Большая часть имен представлений и функций была переименована в Postgres 10. Суть переименований сводилась к замене xlog на wal и location на lsn в именах функций/представлений и т.п. Частный пример, функция pg_xlog_location_diff() была переименована в pg_wal_lsn_diff()._

Тут тоже у нас много всего. Но понадобятся нам всего лишь пункты, связанные с location.

Если мы видим, что все значения равны, то это идеальный вариант и реплика не отстает от мастера.

Вот эта шестнадцатеричная позиция — это позиция в журнале транзакций. Она постоянно увеличивается, если в базе есть какая-то активность: inserts, deletes и т. д.

сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

Если эти вещи отличаются, значит есть какой-то лаг. Лаг — это отставание мастера от реплики, т. е. данные отличаются между серверами.

Есть три причины отставания:

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

И вот три запроса, которые нам позволяют использовать статистику. Мы можем оценить, сколько записано у нас в журнале транзакции. Есть такая функция pg_xlog_location_diff и можем оценить лаг репликации в байтах и секундах. Мы тоже для этого используем значение из этого представления (VIEWs).

Примечание: _Вместо pg_xlog_locationdiff () функции можно использовать оператор вычитания и вычитать один location из другого. Удобно.

С лагом, который в секундах, есть один момент. Если на мастере не происходит никакой активности, транзакция там была где-то 15 минут назад и активности никакой нет, и если мы на реплике посмотрим этот лаг, то мы увидим лаг в 15 минут. Об этом стоит помнить. И это может вводить в ступор, когда вы посмотрели этот лаг.

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

select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;

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

Однако есть вторая метрика — seq_tup_read. Это количество строк, возвращенных в результате последовательного сканирования. Если усредненное число превышает 1 000, 10 000, 50 000, 100 000, то это уже показатель, что возможно вам нужно где-то построить индекс, чтобы обращения были по индексу, либо возможно оптимизировать запросы которые использую такие последовательные сканирования, чтобы такого не было.

Простой пример — допустим, запрос с большим OFFSET и LIMIT стоит. К примеру сканируется 100 000 строк в таблице и после этого берется 50 000 нужных строк, а предыдщие отсканированные строки отбрасываются. Это тоже плохой кейс. И такие запросы нужно оптимизировать. И здесь вот такой простой SQL-запрос, на котором можно это посмотреть и оценить полученные цифры.

select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;

Размеры таблиц также можно получить с помощью этой таблицы и с помощью дополнительных функций pg_total_relation_size(), pg_relation_size().

Вообще, есть метакоманды \dt и \di, которые можно использовать в PSQL и также посмотреть размеры таблиц и индексов.

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

Активность на запись. Что такое запись? Давайте рассмотрим операцию UPDATE — операцию обновления строк в таблице. По сути, update — это две операции (а то и ещё больше). Это вставка новой версии строки и пометка старой версии строки как устаревшей. В последствии придет автовакуум и вот эти устаревшие версии строк вычистит, пометит это место как доступное для повторного использования.

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

select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;

И за счет свего дизайна, UPDATE — это тяжеловесные операции. Но их можно облегчить. Есть hot updates. Они появились в PostgreSQL версии 8.3. И что это такое? Это легковесный update, который не вызывает перестроение индексов. Т. е. мы обновили запись, но при этом обновилась только запись в страничке (которая принадлежит таблице), а индексы по-прежнему указывают на ту же самую запись в странице. Там немного такая интересная логика работы, когда приходит вакуум, то он эти цепочки hot перестраивает и все продолжает работать без обновления индексов, и происходит все с меньшей тратой ресурсов.

И когда у вас n_tup_hot_upd большое, то это очень хорошо. Это значит, что легковесные updates преобладают и это по ресурсам выходит нам дешевле и все прекрасно.

ALTER TABLE table_name SET (fillfactor = 70);

Как увеличить объем hot updateов? Мы можем использовать fillfactor. Он определяет размер резервируемого свободного месте при заполнении страницы в таблице с помощью INSERT’ов. Когда в таблицу идут inserts, то они полностью заполняют страничку, не оставляют в ней пустого места. Потом выделяется новая страничка. Снова данные заполняются. И это поведение по умолчанию, fillfactor = 100%.

Мы можем сделать fillfactor в 70%. Т. е. при inserts выделилась новая страничка, но заполнилось всего лишь 70% странички. И 30% у нас осталось на резерв. Когда нужно будет сделать update, то он с высокой долей вероятности произойдет в той же самой страничке, и новая версия строки поместится в ту же страничку. И будет сделан hot_update. Таким образом облегчается запись на таблицах.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Очередь автовакуума. Автовакуум — это такая подсистема, по которой статистике в PostgreSQL очень мало. Мы можем в таблицах только в pg_stat_activity увидеть, сколько у нас вакуумов длятся в данный момент. Однако понять, сколько таблиц в очереди у него с ходу очень сложно.

Примечание: _Начиная с версии Postgres 10 ситуация с отслеживанием ватовакуума сильно улучшилась — появилось представление pg_stat_progressvacuum, которое существенно упрощает вопрос мониторинга автовакуума.

Мы можем использовать вот такой упрощенный запрос. И можем посмотреть, когда должен будет сделан вакуум. Но, как и когда должен запуститься вакуум? Вот эти устаревшие версии строк, о которых я говорил раньше. Update произошел, новая версия строки вставилась. Появилась устаревшая версия строки. В таблице pg_stat_user_tables есть такой параметр n_dead_tup. Он показывает количество «мертвых» строк. И как только количество мертвых строк стало больше, чем определенный порог, к таблице придет автовакуум.

И как рассчитывается этот порог? Это вполне конкретное процентное отношение от общего числа строк в таблице. Есть параметр autovacuum_vacuum_scale_factor. Он и определяет процентное отношение. Допустим, 10% + там дополнительный базовый порог в 50 строк. И что получается? Когда у нас мертвых строк стало больше чем »10% + 50» от всех строк в таблице, то мы ставим таблицу на автовауум.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Однако тут есть один момент. Базовые пороги у параметров av_base_thresh и av_scale_factor могут назначаться индивидуально. И, соответственно, порог будет не глобальный, а индивидуальный для таблицы. Поэтому чтобы рассчитать, там нужно использовать ухищрения и уловки. И если вам интересно, то вы можете посмотреть на опыт наших коллег из Avito (ссылка на слайде недействительно и обновлена в тексте).

Они написали для munin plugin, которые учитывает эти вещи. Там портянка на два листа. Но считает он корректно и довольно эффективно позволяет оценить, где у нас вакуума много требуется для таблиц, где мало.

Что мы можем с этим сделать? Если у нас очередь большая и автовакуум не справляется, то мы можем поднять количество воркеров вакуума, либо просто сделать вакуум агрессивнее, чтобы он триггерился раньше, обрабатывал таблицу маленькими кусочками. И тем самым очередь будет уменьшаться. — Главное здесь следить за нагрузкой на диски, т.к. вакуум штука небесплатная, хотя с появлением SSD/NVMe устройств проблема стала менее заметной.

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

Как я уже говорил, update — это не только обновление таблиц, это еще и обновление индексов. Соответственно, если у нас на таблице много индексов то при обновлении строк в таблице, индексы проиндексированных полей также нужно обновить, и если у нас есть неиспользуемые индексы, по которым нет индексовых сканирований, то они у нас висят балластом. И от них нужно избавляться. Для этого нам нужно поле idx_scan. Мы просто смотрим количество индексных сканирований. Если у индексов ноль сканирований за относительно длинный период хранения статистики (не менее чем 2–3 недели), то вероятней всего это плохие индексы, нам нужно от них избавиться.

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

Две ссылки:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Это более продвинутые примеры запросов для того, как искать неиспользуемые индексы.

Вторая ссылка — это довольно интересный запрос. Там очень нетривиальная логика заложена. Рекомендую его для ознакомления.

Что еще стоит подытожить по индексам?

Неиспользуемые индексы — это плохо.
Занимают место.
Замедляют операции обновления.
Лишняя работа для вакуума.

Если мы удалим неиспользуемые индексы, то мы сделаем базе только лучше.

Следующее представление — это pg_stat_activity. Это аналог утилиты ps, только в PostgreSQL. Если ps'ом вы смотрите процессы в операционной системе, то pg_stat_activity вам покажет активность внутри PostgreSQL.

Что мы можем оттуда полезного взять?

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

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

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

Мы можем выполнить вот такой запрос и посмотреть общий процент подключений относительно максимального лимита подключений и посмотреть, кто у нас занимает больше всего коннектов. И в данном приведенном случае мы видим, что user cron_role открыл 508 коннектов. И что-то с ним там произошло. Нужно с ним разбираться и смотреть. И вполне возможно, что это какое-то аномальное число подключений.

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

select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;

Обратите внимание: вот таким запросом мы