Comments 51
Своп на сервере с Постгресом - последнее убежище неправильной настройки. Конечно, оно будет работать медленно.
Ну в нагрузочных тестах бывает одна из задач найти предел работы системы(сервера). А с вашим утверждением согласен на 100 , когда своп это плохо и деградация. Но только вот иногда настройки ну совсем не причем, могут запросы быть неоптимальными, памяти тупо мало - никакими настройками это не решите. Хотя если вы под настройками понимаете в том числе неоптимальные запросы - ну тогда наверное да.
Мне интересно мнение читателей: насколько это востребовано?
Востребовано. В рамках работ по теме "черного ящика" для СУБД были некоторые этюды по сохранению истории утилизации RAM процессами postgres. Но дальше эскизных тестов тема не пошла. Пока отложено в долгий ящик.
Я бы был готов даже инвестировать в исследования по этой теме, ну можно в партнерстве если есть наработки(может есть еще у кого). Меня пока, что текущее описание моделей поведения СУБД(потребления памяти), и главное предсказательная теория прям совсем не устраивают. Пишу провокационно, понимаю, ну так давайте устроим дебаты, кидайте ссылки и т.п. Только в споре рождается истина)
В настоящее время ведутся работы по теме анализа производительности СУБД.
Идея в следующем :
1) определяем что производительность снизилась
2) анализ причин - определение SQL запроса оказывающего максимальное влияние на деградацию производительности
3) анализ проблемного SQL запроса
4) исправление проблемы .
———
Методика, описанная в статье, начнется на шаге 3 и 4. Так, что , поживём увидим. Может и "можно в партнерстве если есть наработки".
Статья сохранена в закладках.
postgres ведь открытое по, есть код, можно скачать и подумать над ним.
Это большое заблуждение что в открытом ПО, не может быть закладок, что оно лучше описано чем коммерческое и т.п. Найдите мне спеца по С, который мог бы читать чужой код описывать его, знает СУБД и алгоритмы оптимизации. Кто умеет хотя бы проводить эксперименты правильные. В большинстве случаев эффективней не читать код а начинать с экспериментов и потом уже включать механизмы отладки и т.п.
Хотелось бы видеть полный план запроса, но если речь идет о свопе скорее нужно смотреть на "temp wtitten" и "temp read". Ведь "shared hit" - это данные которые запрос нашел в кэше.
В pg_stat_statements так же можно найти полезную информацию.
Со всем уважением, но мне кажется, автор не совсем понимает как работает posgtresql.
И начинает исследовать как ему кажется "черный ящик", и делать выводы.
Все давно расписано в документации раздел "Потребление ресурсов"
https://postgrespro.ru/docs/postgresql/17/runtime-config-resource
И настоятельно рекомендую для начала исследований
прочитать книгу "PostgreSQL 17 изнутри"
https://postgrespro.ru/education/books/internals
Все в свободном доступе и бесплатно.
После прочтения можно вернуться к теме мониторинга с совсем другим взглядом.
Ах да, совсем забыл упомянуть PGTUNE
https://pgtune.leopard.in.ua/
или какое-нибудь зеркало типа: https://pgtune.fariton.ru/
Но не забывайте, что pgtune это совсем не панацея.
И еще огорчу автора.
Боюсь что технологию, которую вы изобрели под названием "прокси, которая фиксирует запросы во временные таблицы" это вновь изобретенный велосипед на тему pg_stat_statements.
Читать здесь: https://postgrespro.ru/docs/postgresql/17/pgstatstatements
Ну а после того изучите pg_stat_statement вам откроется мир с десятком утилит и расширений которые анализируют pg_stat_statement и десятки других представлений.
Пример одной из утилит: https://habr.com/ru/articles/494162/
А ребята из postgrespro уже вплотную приблизились к базовому функционалу из Oracle Enterprise Manager 15-ти летней давности: https://postgrespro.ru/products/PPEM
Не стал я базовые вещи расписывать , дабы статью не уводить дальше от темы. Вы мне накидали ссылок, зачем? Все это не по теме. Давайте я вам приведу пример а потом задам вопрос и просьба ответьте на него конкретно а не про сферического коня в вакуме. https://postgrespro.ru/docs/postgresql/17/pgstatstatements и тому подобное могу получить интересующий нас запрос но воспроизвести в большинстве случаев его не получится. Приведу пример, 1С формирует запросы с использование временных таблиц. То есть заполняется временная таблица как правило на 1000-и записей и потом она или они используются в запросе. С помощью прокси я могу сохранить сам запрос и состояние временных таблиц и потом воспроизвести на тестовом сервере. Вопрос к вам конкретный ,приведите мне утилиту которая могла бы сохранить и затем воспроизвести полностью интересуемый запрос? Я таких не знаю, поэтому и предлагаю решение. И просьба сначала на этот вопрос ответить а потом я готов подискутировать как я не умею определять тяжелые запросы и все остальное)
Мне кажется что ЭТО ВЫ уходите от изначальной темы.
Вы просили инструменты мониторинга, я дал вам ссылки на инструменты и подходы которыми пользуются при мониторинге производительности postgresql.
При чем здесь временные таблицы?
В статье вы пишите "При анализе данных мониторинга производительности я не обнаружил параметров, которые бы явно коррелировали с объемом потребляемой памяти."
Я вам привел конкретный раздел настроек postgresql который влияет на объем оперативной памяти.
Вы пишите "Это побудило меня поднять вопрос о том, как можно улучшить идентификацию "тяжелых" и "опасных" запросов для системы на основе данных мониторинга."
Ну так pg_stat_statement и выдаст вам такую ифомрацию. Именно самые тяжелые запросы по CPU, можно по IO, можно по временным файлам, по времени выполнения и т.п.
В статье вы пишете, что делаете нагрузочное тестирование. В чем проблема повторить при нагрузочном тестировании запросы? Непонятно.
Если вы вы хотите подсмотреть запрос с параметрами, то log_statement = all in postgresql.conf
Если у вас меняются сильно планы в зависимости от параметров запроса, но есть расширение pg_stat_plans.
В статье у вас очень все поверхностно, и в комментариях вместо мониторинга нагрузки, вы уже пишете про захват и воспроизведение нагрузки (правда я не уверен что правильно вас понял).
Мне кажется вы статью вообще не читали. Хочется универсализма. Я предложил один из подходов. Предположим есть у вас данные мониторинга. Есть запрос потребляющий память. Есть сервер с памятью Х, Сможете ли вы расчитать на основании данных мониторинга плюс минус сколько таких запросов можно выполнить одновременно пока не начнется свопирование? Если сможете, просьба конкретные параметры и алгоритмы в студию.
pg_stat_statement и выдаст вам такую ифомрацию. Именно самые тяжелые запросы по CPU
pg_stat_statements не позволяет получить отчет по утилизации CPU. только по total_exec_time .
Хм, а если так?total_exec_time - blk_read_time - blk_write_time
И считать в процентах от общего числа...
Могу и запрос весь скинуть
Да я понимаю, что это не совсем точно CPU , но для оценки в % вполне подойдет.
with s AS
(SELECT
sum(total_exec_time) AS t,
sum(blk_read_time + blk_write_time) AS iot,
sum(total_exec_time - blk_read_time - blk_write_time) AS cput,
sum(calls) AS s,
sum(ROWS) AS r
FROM
pg_stat_statements
WHERE
TRUE), _pg_stat_statements AS (
SELECT
dbid,
regexp_replace(query, E'\\?(, ?\\?)+', '?') AS query,
sum(total_exec_time) AS total_exec_time,
sum(blk_read_time) AS blk_read_time,
sum(blk_write_time) AS blk_write_time,
sum(calls) AS calls,
sum(ROWS) AS rows
FROM
pg_stat_statements
WHERE
TRUE
GROUP BY
dbid,
query
) /* END WITH*/
--- запрос по основным показателям
SELECT
(100*total_exec_time/(SELECT t FROM s))::numeric(20, 2) AS time_percent,
(100*(blk_read_time+blk_write_time)/(SELECT iot FROM s))::numeric(20, 2) AS iotime_percent,
(100*(total_exec_time-blk_read_time-blk_write_time)/(SELECT cput FROM s))::numeric(20, 2) AS cputime_percent,
(total_exec_time/1000)*'1 second'::interval as total_time,
((total_exec_time-blk_read_time-blk_write_time)*1000/calls)::numeric(20, 2) AS avg_cpu_time_microsecond,
((blk_read_time+blk_write_time)*1000/calls)::numeric(20, 2) AS avg_io_time_microsecond,
calls,
(100*calls/(SELECT s FROM s))::numeric(20, 2) AS calls_percent,
rows,
(100*rows/(SELECT r from s))::numeric(20, 2) AS row_percent,
(select datname from pg_database where oid=dbid) as database,
query
FROM _pg_stat_statements
WHERE
(total_exec_time-blk_read_time-blk_write_time)/(SELECT cput FROM s)>= 0.05
UNION all
SELECT
(100*sum(total_exec_time)/(SELECT t FROM s))::numeric(20, 2) AS time_percent,
(100*sum(blk_read_time+blk_write_time)/(SELECT iot FROM s))::numeric(20, 2) AS iotime_percent,
(100*sum(total_exec_time-blk_read_time-blk_write_time)/(SELECT cput FROM s))::numeric(20, 2) AS cputime_percent,
(sum(total_exec_time)/1000)*'1 second'::interval,
(sum(total_exec_time-blk_read_time-blk_write_time)*1000/sum(calls))::numeric(10, 3) AS avg_cpu_time_microsecond,
(sum(blk_read_time+blk_write_time)*1000/sum(calls))::numeric(10, 3) AS avg_io_time_microsecond,
sum(calls),
(100*sum(calls)/(SELECT s FROM s))::numeric(20, 2) AS calls_percent,
sum(rows),
(100*sum(rows)/(SELECT r from s))::numeric(20, 2) AS row_percent,
'all' as database,
'other' AS query
FROM _pg_stat_statements
WHERE
(total_exec_time-blk_read_time-blk_write_time)/(SELECT cput FROM s)< 0.05
По желанию можно добавить остальные показатели, типа временных блоков, для себя написал утилитку, периодически борюсь с проблемными запросами в топах
Да хоть как. Вот эта формула точно к CPU вообще никакого отношения не имеет. Пальцем небо.
Если нужны точные данные для анализа, а не для отчетности юзерам то:
Тип pgpro_stats_rusage
exec_rusagepgpro_stats_rusage Статистика использования ресурсов при выполнении оператора.
Расширение pgpro_stats.
Может быть еще где-то есть, я использую этот инструмент.
Хотя в последний раз когда смотрел утилизацию CPU и не помню. Потому, что как правило производительность СУБД и утилизация CPU никак не связаны.
Вернее, более строго
если производительность не снижается, а CPU растет , то это не инцидент.
если производительность снижается , а CPU растет , то это инцидент.
Да хоть как. Вот эта формула точно к CPU вообще никакого отношения не имеет. Пальцем небо.
Ну прям таки никакого?
Если время уходит не работу с диском, то на что оно уходит?
Да я знаю, что есть еще блокировки которые наверное CPU не кушают, а только "total_exec_time". А какое еще значимое время может быть спрятано во total_exec_time?
Конечно можно этот показатель и переобозвать не "CPU" а назвать "other time" :)
Если нужны точные данные для анализа, а не для отчетности юзерам то:
Тип
pgpro_stats_rusage
На это я могу только облизываться. Бедны мои клиенты, не все могут позволить себе Postgres Pro Enterprise...
:(
Как видно, используется хэш-соединение, которое, безусловно, потребляет оперативную память (но есть вероятность, что необходимый план выполнения не попал в мониторинг). Однако значение в 1,4 ГБ не соответствовало моим вычислениям. Можно было бы предположить, что вся память, выделенная под запрос, не освобождается до окончания транзакции ...
Как было сказано в соседних комментариях, вы ловите не то и "не там".
shared hit - фактически, это "трафик" по страницам памяти, которые были прочитаны на всех итерациях по данному узлу - то есть если 100 раз прочитать одни и те же 10 страниц из памяти, то суммарно получится 1000 страниц. То есть "много читать нехорошо даже из памяти, но зато это быстро" (см. подробнее тут).
Ваша же проблема лежит ровно рядом на плане в temp written/read на узле HashAggregate - это именно внутренний "свап" PostgreSQL на диск, когда выделенной памяти не хватает на обработку данных конкретного узла. И если вы посмотрите весь план целиком, слева от этого узла будет рекомендация типа "попробуйте увеличить значение work_mem".
Повторюсь.... Хочется универсализма. Я предложил один из подходов. Предположим есть у вас данные мониторинга. Есть запрос потребляющий память. Есть сервер с памятью Х, Сможете ли вы рассчитать на основании данных мониторинга плюс минус сколько таких запросов можно выполнить одновременно пока не начнется свопирование? Если сможете, просьба конкретные параметры и алгоритмы в студию.
Проблема ровно в том, что у вас не "Есть запрос потребляющий память." Точнее, конкретно этот план не принадлежит такому запросу.
Но для решения задачи в поставленном виде "на основании данных мониторинга плюс минус сколько таких запросов можно выполнить одновременно" вообще не надо знать ничего про запросы. Достаточно знать, что один запрос в PG "базово" занимает все ресурсы конкретного процесса, обслуживающего соединение, а при использовании параллелизма - возможно, и несколько соседних.
То есть запускаем нагрузку в режиме 1x, снимаем пиковый занятый процессами объем памяти (proc), и дальше линейно масштабируем на кол-во клиентов.
О! многоуважаемый @Kilor подтянулся. Но боюсь автору даже он не сможет помочь :)
Пробовал , так не работает. Там даже вопросы связанные с временным выделением памяти в очень тяжелых запросах стоят особняком. Например когда она освобождается? Вопросы с разделяемой памятью, как ее считать? Но и заметьте вы предлагаете похоже ,что и я. Проведите аккуратно эксперимент - 1х, это важно(без воздействия среды, иначе как с повторяемостью экспериментов и результатов). Но я боюсь даже в этом случае замеры и дальнейшая аппроксимация могут привести к неправильным выводам. Я же предлагаю автоматизированный практический тест.
Сможете ли вы рассчитать на основании данных мониторинга плюс минус сколько таких запросов можно выполнить одновременно
Собственно, а каков смысл "предсказывания" результатов теста?
Если надо "убедиться, что система держит X пользователей" - тогда делаем тест эмулирующий профиль на X клиентах.
Если "проверить, какой максимум может выдержать система" - тогда делаем пошаговое увеличение X под нагрузкой, пока не начнем отлавливать деградацию любого вида.
Нагрузочные тесты дорогие и у нас их не ценят.(мое субьективное мнение) А еще они не отображают часто динамически изменяемую нагрузку в процессе эволюции ит системы. Часто в продакшн выпускается какие то критичные обновления мимо системы нагрузочного тестирования.
поэтому и предлагаю один из "дешевых" универсальных подходов. Смотря потом на средства мониторинга и зная что этот запрос приводил к свопирования в Х раз(одновременных запусков), а этот Х/100 - я буду четко понимать приоритеты оптимизации. Кому хватит времени и усердия проводить качественно такие эксперименты?
А зачем тогда вам все эти эксперименты, если они не могут ничего доказать или опровергнуть? Вам нужен нормальный мониторинг с адекватным анализом, типа такого, если это касается PG.
Эксперимент должен воспроизводится, это раз! Зная данные этих экспериментов выполненных системой на тестовой среде я могу сделать очень далеко идущие выводы. Доказать они могут - буквально выполненные одновременно(миниально) Х запросов (типа -Z) приводят к свопированию. Можно ли на основании этого делать вывод о потреблении памяти - ну это слишком просто(разделить и все, ну я писал), но выводы практические вполне.
Хочется иногда железобетонных гарантий)
Как-то ваши утверждения про воспроизводимость экспериментов и динамически изменяющуюся нагрузку и эволюцию системы "мимо тестов" не бьются между собой.
Вы сначала определитесь, что именно вам нужно и в каких условиях.
Если "тест косвенно соотносящийся с реальной системой" - это одно, если анализ проблем на реальной системе - другое. Почти всегда эти вещи связаны не очень сильно.
Банально, представьте, что на реальной базе вам не накатили критичный индекс, а в тестовом контуре он есть - и?..
Система меняется, нагрузочные тесты не успевают. Многопоточная, высоконагруженная система. Что в этих условиях мерять? Я предлагаю некоторые подходы которые помогают ввести какие то метрики, ничего больше. Мы с реальной системы собираем все(потенциально опасные) запросы. Воспроизводим автоматизированно на тестовой среде, без влияния других факторов. И получаем приоритезацию по их потенциальному влиянию на работу системы. Если на основании данных мониторинга это можно рассчитывать, то мои все рассуждения имеют сугубо теоретический характер.(но докажите мне, на простых примерах)
Система меняется, нагрузочные тесты не успевают. Многопоточная, высоконагруженная система. Что в этих условиях мерять?
Вроде ответ скрыт в вопросе - анализировать надо саму целевую систему, а не производные от нее.
Выше я приводил ссылку на демо нашей системы анализа нагрузки в PG - можно легко понять, кто из запросов "ест диск", как часто каждый стартует, heatmap времени выполнения, ...
Есть в математике простые вопросы типа большой теоремы ферма. Простые в формулировке, но ответить на них сложно.. Я предлагаю один из тестов по которому можно оценить практически систему мониторинга, ее оценку. Один из универсальных тестов. Не сердитесь, я в этой теме тоже разбираюсь, мое мнение отличается от мнения компании. Мне интересен вопрос с научной точки зрения. Поднимаю дискуссию, никому не хочу ничего доказывать.
Если вы можете на основании средств мониторинга решить эту практическую задачу(ок, пускай гипотетическую), простой универсальный эксперимент. Скажите как, я готов заплатить, ради истины даже если вы потерпите неудачу.
Если под задачей понимается "мы провели какие-то тесты в одном месте, предскажите, какая будет нагрузка в других условиях в другом месте", то вряд ли кто-то ее сможет решить, кроме крайне малого количества крайних случаев.
Ну в моем случае я уже расписывал, что неразумно делать тестовый стенд аналогичный рабочему серверу. Можно значительно уменьшить размер памяти, провести тесты и потом сделать аппроксимацию. Я надеюсь на это. Многие эксперименты я проводил на MSSQL, сознательно ограничивая память до минимума, гротеска , там было попроще. Здесь за счет виртуализации можно управлять.
Коллеги, не обессудьте. Мне хотелось бы поднять дебаты. Я рад что идет обсуждение. ЦПУ более менее понятно как считать. Не понятно как посчитать память используемую запросом? Философски даже, в каких попугаях мерять? Я предложил методику, критикуйте , предлагайте свои подходы. Но в целом вопрос стоит- как на высоконагруженных системах научится предугадывать когда настанут критические ситуации? С памятью мне не понятно, я ж писал, готов предложить написать статью и профинансировать , кто готов вложить свое время в полноценные исследования.
Если очень грубо, то на сервере у вас три лимитирующих ресурса: производительность CPU, объем RAM, пропускная способность носителя HDD/SSD (иногда еще пропускная способность сети, но очень уж нечасто).
Для предсказания нагрузки от линейного масштабирования одних и тех же "в среднем" запросов вам вообще не требуется знать, что там внутри у PG - просто измеряете %cpu, delta(proc), IOps.
К сожалению, проблемы начинаются (и сложности в предсказании) обычно из-за нелинейности масштабирования. Например:
CPU не на "железе", а на гипервизоре, который начал "душить" не в меру активного потребителя троттлингом
у диска при линейном росте IOps экспоненциально растет latency, и все ваши запросы тоже начинают дружно "тормозить"
Но в целом вопрос стоит- как на высоконагруженных системах научится предугадывать когда настанут критические ситуации?
Мое личное мнение на основе анализа и наблюдений за прошедшие 5 лет - предсказать невозможно.
1) СУБД по сути своей есть стохастическая система
2) СУБД в облачной инфраструктуре(сейчас самый распространенный вариант) - подвержена влиянию массы случайных и непредсказуемых факторов со стороны виртуализации
3)Современные разработчики вообще темой нагрузочного тестирования не заморачиваются, что в очередной раз вылезет со стороны приложения или backend - предсказать невозможно
4)Нагрузка и характер действий конечных пользователей непредсказуем в принципе
Поздравляю, вы придумали трейсинг )
БД с какого вида нагрузкой вообще? Олап или олтп? Процесс модет не отдать память после завершения работы запроса. Борьба за память на постгрес многогранная и одним анализом запросов не ограничивается. Предсказывать сколько памяти зохавает запрос сложно, но можно. Не очень понятно зачем понимание сколько запросов можно сунуть. Нагрузка так сильно однородна?
ОЛТП, система. Но с другой стороны в ОЛТП системах видел запросы Dynamic SQL по 4 GB текст запроса) Понятно что транзакционная модель с сериализацией обязывает "держать в уме" какое то количество памяти. Транзакции смотрел, закрываются. но в моменте может быть N открытых транзакций. Но идея в другом, хочется некоторых универсальных подходов. Про разделяемую память в постгри это отдельная история, специально не стал начинать с этого обсуждение иначе холивар был бы обеспечен.
Мысли вслух. Сколько потребляет памяти один запрос PostgreSQL?