Здравствуйте. Есть БД. версия 9.6. Круглосуточно мониторинг показывает нагрузку

на дисковую систему 1 Гигабайт в секунду. Постоянно что-то там читает. Причем график ровный.
Мы сисадмины, прогеры отвечающие за БД, пытаются переложить ответственность на нас из-за большого латенси. Но у нас по железу все в порядке. Могли бы что-то посоветовать? Как помочь найти (девочке, её котёнка) причину этой нагрузки?

>прогеры отвечающие за БД, пытаются переложить ответственность на нас из-за большого латенси. Вообще, нагрузка, которую создаёт СУБД -- определяется по сути только средствами СУБД, потому пинать надо как раз этих DBA. Ну, вы можэте посмотреть iotop, да, чтобы попытаться убедиться, что нагрузка идёт в основном от СУБД (а не от каких-нибудь ещё непонятночего). А потом -- к ним. Выяснять, сколько там занимает autovacuum, сколько bgwriter, сколько запросы (последнее -- в основном через модуль auto_explain, остальные методы в цэлом труднее соотнести с реальностью).

А, да, в линуксах очень вероятно, что чуть-чуть поможэт смена io scheduler на noop. Но это всё... Ну, такоэ.... То есть если у вас 100 потоков, каждый в основном висит на чтении -- то в дисковой системе висят в среднем 100 реквестов, что даёт очередь в 8 мегабайт, и, при скорости дисковой подсистемы в гигабайт/секунду -- latency около 8мс минимум. Учитывая, что каких-то потерь в дисковой подсистеме линукса непредусмотрено -- вы, как сисадмины с этим сделать ничего не можэте. Да, база хочет читать, да, из такой нагрузки у неё создаётся такая очередь и такая задержка обработки.

Кстати, судя по скорости -- у вас там RAID-1 из двух SATA SSD?

латенси там нормальный. там WA вечно 20-40 процентов. латенси в районе 3-8 держится, выше не прыгает

А кто такой WA?

3-8 -- микро или милли секунды?

Параметр top wa — (%wa percent I/O wait) — показывает процент операций, готовых быть выполненных процессором, но находящихся в состоянии ожидания от диска. Это может быть работа с базой данных.

а что по записи? странно такое иметь совсем без записи и да, если запись в ноль пренебрежимо мала и конечно нет других читателей кроме пг, то походу разработчики сами себе буратины иинадо смотреть планы

ms не такая крутая СХД и сеть, что бы такие показатели были )

Переложили бы Вы на кого-нибудь ответственность за использование давно не поддерживаемой версии PostgreSQL (я даже уж забыл, какие там были средства диагностики). ;(

Ах, iowait. Ну, ничего такого. Ну да, io-bound нагрузка -- логично, что у него будет большой io wait. Более того, дажэ до 50% обычно не дотягивает -- что намекает, что система, возможно, вполне вытягивает потребную нагрузку (но это не точно).

по записи БД не очень активная. в среднем по палате от 30 до 100 мб в секунду .

Ну, тогда так себе. Но, опять жэ -- это всё вопросы или к тем, кто нагрузку даёт, или к тем, кто даёт жэлезо под эту нагрузку. Вы, как сисадмины, что-то сильно изменить тут не можэте.

такова жизнь, не всё в наших силах.

тут вопрос вообще стоит в том, что мы не можем понять сколько надо ресурсов. уперлись во что то, но во что уперлись- не понятно. вот и пытаемся понять в чем дело. p.s. по планам запросов- все более менее четко(если так можно сказать для супер старой версии)97 процентов запросов отрабатывают примерно за пол секунды, остальные в районе 30 секунд(там если сложные вещи).

Ну, то есть я хочу сказать, что есть вот вещи, которые определяются СХД, сетью, физикой и прочими сисдаминскими вещами. Это, по сути -- минимум скорости прохождения пакетов и обработки во всяких там свитчах/контроллерах. Это сисадминское, да. Но это обычно микросекунды-сотни микросекунд. В плохих случаях миллисекунды, да. А есть -- размер очереди (который определяется почти исключительно приложэнием) и физические ограничения на скорость её обработки. Делоим второе на первое -- получаем задержку, определяемую исключительно приложэнием. И если это приложэнием не вашэ -- то это вопросы к тому, что им управляет.

Ой, Да это ничего страшного — возможно, уже нашлись люди, в силах которых майнить криптовалюту на вашем сервере (и да, если Вам кажется, что это ерунда — именно такие случаи систематически "всплывают" в IRC-канале по PostgreSQL, в среднем по несколько штук в год). ;(

Я как-то не понял -- так вы упёрлись или у вас всё более-менее чётко?

тогда можно оценить баланс чтения по запросам и что имеем на io системы смотреть кто ещё пожрал io может у вас вакуума никогда не было, таблички разнесло, может что угодно быть

Кстатеда, если воркеров оключить от базы -- то как, нагрузка перестанет или продолжытся, хе-хе?

система работает, но не на той скорости на которой нам надо. мы ищем причину.

пробовали-нагрузка падает и WA соответственно тоже )

тогда смотрите кто что читает может и не в базу совсем а может и в базу, поймёте по файлам в каком месте

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

раз виртуалка попробуйте погонять на в разы увеличенном буферном кеше

То есть ничего чёткого нет, скорости нехватает. Так берите планы запросов, берите explain (analyze, verbose, buffers) -- и смотрите, что там тупит. io медленное (это по отношэнию buffers read ко времени обычно можно определить -- оно будет очень схожым во многих случаях, когда видно, что ничего практически большэ запрос не делал), или в CPU упирается или что-то ещё мешает.

Это утешает!

делали- все тяжелые запросы затюнены. мы работаем над улучшением запросов. но это не быстро.

Но, опять жэ, это работа DBA. (И определять потребное количество воркеров для максимальной скорости либо для устраивающей задержки -- тожэ работа DBA).

в субботу в 4 раза увеличивать собираемся )

Причём тут "затюнены"? Они у вас работает не с той скоростью, с которой вам надо. И первое, что вам нужно -- определить, чем они там занимаются.

а вообще какие характерные размеры таблиц в строках / байтах?

Ну и вообще, что значит "затюнены"? Они достигли минимального потребления ресурсов для вашых потребностях в данных? Ой ли? У вас тогда были бы спец.чипы, разработанные с привлечением хорошых математиков и лучшых программистов и заказанные по 17nm технологии. Пока спец.чипов у вас не стоит -- а стоит постгрес с его конскими накладными расходами на чтение строк -- производительность вашых запросов очень далека от максимальной. Уверен, кстати, что и в рамках возможностей ванильного постгреса -- тожэ далека. Ну, учитывая общий уровень, понимания, да. Всегда можно что-то улучшыть. Вопрос только -- готовы ли вы за это платить (или лучшэ вам заплатить за что-то ещё, или забить просто).

Вообще, как-то выбешывает этот подход. Не только (и не столько) в постгресе/DBAстве. "С сердцэм у меня всё в порядке, только вот астма и одышка замучали". В каком порядке -- ты ведь понятия не имеешь, что там работает и на каком уровне.

pg_activity покажет какие запросы жрут сколько IO, не подскажу как это посмотреть без этого

то, что было на момент когда я увидел эту базу, было просто адом, и я не говорю что у нас все идеально по запросам. я говорю-нет каких то определенных запросов, которые отжирают все ресурсы. на системе большое количество мелких запросов.(очень большое) мы не беремся утверждать что у нас есть такие крутые инженереы-математики, и уж тем более специально разработанные чипы. вопрос в том, что сейчас мы много читаем с этой базы, есть приложение (монолит) в котором нет возможности разом переписать все(план на это есть, но до него пока не дошли) так вот идея в том, что мы добились не высокого латенси(3-8 мс) на дисковой подсистеме(раньше БД крутилась на hdd(и она больше не работала, чем работала). но меня смущает высокий WA. как только ты что то селектишь, к примеру 50-70 мб в секунд( не запросами с 20+джойнами) WA на подсистеме поднимается до 8-10 %. что это может быть за очередь, когда никакой нагрузки нет, но проц ждет IO ? и да, есть ещё системы которые крутятся на этой СХД-они ведут себя нормально.

Высокий wa - ожидание от диска (чаще всего). Выкиньте бд с схд тупой, на локальные, хотя бы sata ssd, не говоря уже о nvme.

нет такой возможности (

Я ещё раз повторюсь -- чтобы понять, чем занимается сервер, выполняющий запросы -- надо смотреть analyze buffers этих запросов. Ну, хорошо. Не надо. Есть другие способы. Просто они все гораздо сложнее -- потому ОЧЕНЬ ПОЛЕЗНО смотреть analyze buffers. Чтобы было проще это доставать -- лучшэ через auto_explain и сэмплинг. Когда посмотрите -- начнёте понимать, это чтение или сортировки, или seq scan, или что там можэт быть.

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

уже проверяли. это не сортировка. это именно чтения. я бы сразу написал, что пролбема с сортировками.

нет такой возможности. только если load balanser ставить который SQL может на разные базы балансировать(типа pgpool2

И что вам тогда неясно? Почему только 1 гигабайт в секунду? Ну, наверное, или сетевуха десятка всего или вы выдали два SSD в RAID 1. Или почему гигабайт в секунду чтения даёт такую скорость работы приложэния? Так для этого и нужэн explain запросов, с определением количества buffers read и прочим -- чтобы поделить гигабайт на объёмы чтения, и понять, где мы что теряем.

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

Так, я что-то не понял. Вы DBA или сисадмин таки? Вначале вроде было наоборот про сисадминов?

не, я то ДБА. и сижу в этой группе и читаю посты часто. а писал о проблеме сис админ:)

Сисадмины, это мы. Есть отдельное подразделение, которое по железу.

А, понял. Обе стороны тут.

Именно.

В общем, план выглядит простой. Вы устраиваете sampling с auto_explain -- и смотрите, сколько базе требуется чтения под вашу нагрузку. В смысле -- сколько байт читает один <средний> запрос или там один <средний> клиент. Потом смотрите реальный сегодняшний максимум (клиентов/запросов в секунду) -- и определяете, сколько должно бы быть на сегодня. Гигабайт в секунду чтения, допустим. В процэссе -- прикидываете, насколько это оправдано. Возможно, есть алгоритмические способы получать гораздо большэ результата за заметно мЕньшые затраты. Потом -- спрашываете, сколько есть максимум скорости чтения с вашэй СХД. Прикидываете, сходятся ли результаты (вашэго максимума скорости и потребления IO сегодняшнего и максимума, который должна бы выдавать СХД. А заодно -- скорости, которая она выдаёт при максимуме нагрузки по результатам мониторинга). Когда все рузльтаты более-менее устаканятся и станут логично друг с другом соотноситься -- определяете, можэт ли ваша конфигурацыя потянуть нагрузку, которую вы хотите. (Потом или добиваете жэлеза по потребности или уменьшаете хотелки по максимальной скорости или закрываете проект как нерентабельный).

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

Кстати, к сисадминам -- в линуксе на SATA какой-то невменяемый prefetch по дефолту, типа 128 или 256. Потсгресу тащемта нужэн 16 (т.е. 8 килобайт). Иногда, если SSD с дурацким размером сектора -- типа 16к или 32к -- вроде бывает полезно выставить в размер сектора (т.е. 32 или 64) -- но сейчас ужэ редко (поскольку SATA SSD обычно упираются в интэрфейс). Так что поставьте hdparmом prefetch у реальных девайсов, если это вообще сработает (от HBA зависит, понятно). (а у lvm тогда лучшэ вообще 0 поставить).