Братцы, мозг сломал, неделю уже думаю: дать больше памяти постгессу (64Гб -> 256 Гб), при этом потеряв в скорости диска NVMe->SATA SSD и немного в скорости проца, или добавить дисков NVMe (сейчас 2 в страйпе, а можно поставить 4), при этом памяти останется 64 Гб?

База используется для аналитических запросов. Размер базы 670 гиг, прирастает примерно гиг в день.
Данные хранятся на ZFS со сжатием, коэффициент сжатия 3:1. Что происходит внутри базы, примерно понятно: много чтения с диска в случае full scan запросов. Смотрю на https://explain.tensor.ru/ , строю нужные индексы, слежу за неиспользуемыми индексами. Тем не менее, есть запросы, которые выполняются 3-5 минут. Если их сразу же повторить, время будет секунд 20-40, потому что будет hit в кеш.

Стою перед выбором:
- Ryzen 5 3600 на 64 Гб ОЗУ + 2 диска NVMe в страйпе (можно перейти на 4 диска)
- Intel E5-1650v3 на 256 Гб ОЗУ + 2 диска SATA SSD в страйпе

Думаю, у меня будет неделя на сравнить, но понять бы, как определить горлышко? Не хватает памяти или производительности дисковой подсистемы? А проц? Я же включаю компрессию файловой системы, а значит, часть производительности уходит в ОС?

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

Буду рад пояснить подробнее, если нужно.

Спасибо за наводку! okmeter для меня дороговат, поставил пока munin, понаблюдаю.


А что делать, у меня иначе база очень скоро не влезет. К тому же я решил, что сжатие повысит скорость чтения с диска, так как физически читать придётся в три раза меньше.

Я и подумал поэтому, что если сервер залить оперативной памятью, пусть и ценой скорости проца и диска, то ему полегчает...

Что-то munin меня озадачил. Если у кого-то есть время, посмотрите, буду благодарен комментариям:

https://xboxstat.ru/munin/www/

localhost.localdomain - та самая машина, на которой я хочу разогнать выполнение запросов. Это физический read-only слейв.
dignus.xboxstat.ru - мастер, где выполняются скрипты, наполняющие базу. Некоторые запросы он отправляет в слейв через dblink, чтобы не нагружать себя

Что вижу я: отсутствие проблем на интересующем меня хосте, зато вижу проблемы на мастере: занятость disk под сотню. При этом мастер меня как-то особо-то не беспокоил. Может, пора проактивно что-то предпринять?

А вот что не так в postgresql, не пойму. Может, надо за чем-то ещё следить?

Юниксберг,

спасибо за вопросы!

ssh меня не сильно раздражает: вход без пароля, только по ключу, плюс fail2ban. Но вы правы, оставить порт открытым только для некоторых ip несложно, а безопасность хоста повысит заметно. (тем более вспоминая ту чудовищную историю десятилетней давности с генерацией ключей на debian)

По пятницам народ обычно начинает активно играть, и мои скрипты начинают лить в базу данные. Если говорить про конкретно эту пятницу, то я, вместо того, чтобы присоединиться к играющим, подбивал статистику по Forza Horizon 5, нагружая запросами slave-сервер. А на какой машине скачок вызвал настороженность?

На query length я тоже смотрел, но не понимаю, что это за параметр. Как переводится каждое из слов, знаю, но "длина запроса"? Или это "длительность"?

Зомби-процессы берутся из-за того, что у Xbox Live API есть некий механизм, который "подвешивает" состояние в "Established" на сколь угодно долгое время. В результате мне пришлось обвешать конструкцию вотч-догами, которые периодически отстреливают таких клиентов. Их (зомби) количество не должно расти, так они раз в сутки должны очищаться.