Много idle это проблема с приложением. Зачем оно их держит?

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

ps -u postgres o pid,rss:8,cmd | awk 'NR>1 {A+=$2} {print} END{print "Total RSS: " A}'

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

ну тогда pgbouncer ставь

:) уже есть

может в приложениях забыли close дописать?

ps -u postgres o pid,rss:8,cmd | awk 'NR>1 {A+=$2} {print} END{print "Total RSS: " A}'

http://paste.org.ru/?sfz654

может в приложениях забыли close дописать?

я бы ожидал в таком случае idle in transaction

Кстати, у вас процессы кушают под 400-700Мб. Это нормально для того, что они делают?

может таймауты выставить для idle коннекшн?

http://paste.org.ru/?sfz654

посмотрите у тех, которые пользуют больше памяти что в smaps

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

[postgres@ conf]$ ps uxf | grep message1 | sort -nk6 | tail -10 | tee ? cat - >&2) | awk '{system("cat /proc/"$2"/smaps")}' | grep ^Private | awk '{A+=$2} END{print A/1024}' postgres 27508 2.0 2.7 2823712 455840 ? Ss 10:23 0:01 \_ postgres: message message1 172.20.3.166(47786) idle postgres 27410 3.1 2.7 2823716 456576 ? Ss 10:23 0:02 \_ postgres: message message1 172.20.3.166(47606) idle postgres 27401 8.3 4.4 3094168 727136 ? Ss 10:23 0:06 \_ postgres: message message1 172.20.3.166(47600) idle postgres 27509 10.8 4.4 3094164 727352 ? Ss 10:23 0:06 \_ postgres: message message1 172.20.3.166(47788) idle postgres 27511 13.1 4.4 3094164 727640 ? Ss 10:23 0:08 \_ postgres: message message1 172.20.3.166(47792) idle postgres 26902 12.8 4.4 3094236 730808 ? Ss 10:23 0:14 \_ postgres: message message1 172.20.3.166(47004) idle postgres 26915 6.0 4.5 3105144 746484 ? Ss 10:23 0:06 \_ postgres: message message1 172.20.3.166(47030) idle postgres 27071 6.2 4.5 3105144 747080 ? Ss 10:23 0:06 \_ postgres: message message1 172.20.3.166(47056) idle postgres 26917 8.4 4.5 3105120 748324 ? Ss 10:23 0:09 \_ postgres: message message1 172.20.3.166(47034) idle postgres 27192 8.0 4.5 3105164 748404 ? Ss 10:23 0:08 \_ postgres: message message1 172.20.3.166(47098) idle 6073.8 [postgres@ conf]$ psql -x -c "select * from pg_stat_activity where pid=27508"; Timing is on. -[ RECORD 1 ]----+------------------------------------------------------------------------------------------------------------------- datid | 16623 datname | message1 pid | 27508 usesysid | 16621 usename | message application_name | client_addr | 172.20.3.166 client_hostname | client_port | 47786 backend_start | 2017-05-11 10:24:00.667316+03 xact_start | query_start | 2017-05-11 10:24:42.819504+03 state_change | 2017-05-11 10:24:42.822031+03 wait_event_type | wait_event | state | idle backend_xid | backend_xmin | query | COMMIT PREPARED '131077_AAAAAAAAAAAAAP//rBQDGuJENnFZE/+wAAm5XjE=_AAAAAAAAAAAAAP//rBQDGuJENnFZE/+wAAm5YAAAAAMAAAAA'

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

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

планы конечно

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

и пр

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

ps uxf | grep message1 | sort -nk6 | tail -1 | tee ? cat - >&2) | awk '{system("cat /proc/"$2"/smaps")}' | grep ^Private | awk '{A+=$2} END{print A/1024}' postgres 26902 7.1 4.6 3118796 754684 ? Ss 10:23 0:18 \_ postgres: message message1 172.20.3.166(47004) idle 678.277 у меня на сессию 600 мегабайт ушло, хотя она в статусе в idle

ps uxf | grep message1 | sort -nk6 | tail -1 | tee ? cat - >&2) | awk '{system("cat /proc/"$2"/smaps")}' | grep ^Private | awk '{A+=$2} END{print A/1024}'

подскажите что не так, а то так и помру необразованным )

посмотрите в smaps что там если возможно, заведите задание, прибивающее долгопростаивющие сессии

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

воркмем ограничивает потребление на воркер на этапе планирования, дальше бекенд может сожрать, сколько угодно

Сколь я помню, work mem только для сортировки используется. И он относится к общим ресурсам процессов. А у вас каждое соединение жрет память. И именно с ними надо что-то делать.

Например, уменьшить количество соединений в пуле

Сколь я помню, work mem только для сортировки используется. И он относится к общим ресурсам процессов. А у вас каждое соединение жрет память. И именно с ними надо что-то делать.

work_mem это частная память каждого бекенда

Например, уменьшить количество соединений в пуле

сессий 60, 10 кушают 6 гб

уменьшить до 5ти ? :)

work_mem это частная память каждого бекенда

более того каждого подзапроса.

work_mem это частная память каждого бекенда

Ой

Сколь я помню, work mem только для сортировки используется. И он относится к общим ресурсам процессов. А у вас каждое соединение жрет память. И именно с ними надо что-то делать.

ещё на хеши в хешджойнах

уменьшить до 5ти ? :)

Ну они же у вас все равно idle)))

посмотрите в smaps что там если возможно, заведите задание, прибивающее долгопростаивющие сессии

Егор, можно подробнее, не понимаю что смотреть, а про smem я только сегодня загуглил

воркмем ограничивает потребление на воркер на этапе планирования, дальше бекенд может сожрать, сколько угодно

Как бы стоит задуматься о том сколько было страниц изменено с момента последнего analyze

Ну и да, посмотреть через pg_stat_statements общий топ. Скорее всего надо оптимизировать запросы

Как бы стоит задуматься о том сколько было страниц изменено с момента последнего analyze

возможно много, т.к основной профиль апдейты

99% дело в запросах

Егор, можно подробнее, не понимаю что смотреть, а про smem я только сегодня загуглил

там же все видно, какой объект, какой размер (обычно около 100 мб - объекты из шаренной памяти, остальное, то что может заинтересовать)

соберите статистику, заведите на будущее pg_stat_statements в заббикс или чем вы мониторите

Ну и да, посмотреть через pg_stat_statements общий топ. Скорее всего надо оптимизировать запросы

http://paste.org.ru/?5mttnm

SELECT d.datname, substring(query, 1, 150) AS short_query, round(total_time::numeric, 2) AS total_time, calls, round(mean_time::numeric, 2) AS mean, round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu FROM pg_stat_statements s join pg_database d on s.dbid=d.oid ORDER BY total_time DESC limit 10;

имхо никакой магии, индексы есть

message1=# select relname,seq_scan from pg_stat_user_tables where seq_scan>0; relname | seq_scan -------------------+---------- delivered_message | 1

А что за приложение убивается OOM киллером? И зачем ему в пике 60 конектов к базе выяснили уже?

А что за приложение убивается OOM киллером? И зачем ему в пике 60 конектов к базе выяснили уже?

May 11 09:37:49 hostname1 kernel: Out of memory: Kill process 17448 (postmaster) score 20 or sacrifice child May 11 09:38:39 hostname1 kernel: Out of memory: Kill process 17626 (postmaster) score 30 or sacrifice child May 11 09:41:13 hostname1 kernel: Out of memory: Kill process 17801 (postmaster) score 38 or sacrifice child May 11 09:41:46 hostname1 kernel: Out of memory: Kill process 18051 (postmaster) score 17 or sacrifice child May 11 09:46:04 hostname1 kernel: Out of memory: Kill process 18291 (postmaster) score 40 or sacrifice child May 11 09:51:30 hostname1 kernel: Out of memory: Kill process 19656 (postmaster) score 39 or sacrifice child May 11 10:05:11 hostname1 kernel: Out of memory: Kill process 23772 (postmaster) score 39 or sacrifice child May 11 10:11:51 hostname1 kernel: Out of memory: Kill process 24627 (postmaster) score 39 or sacrifice child May 11 10:20:24 hostname1 kernel: Out of memory: Kill process 25370 (postmaster) score 40 or sacrifice child May 11 10:22:55 hostname1 kernel: Out of memory: Kill process 26338 (postmaster) score 38 or sacrifice child May 11 10:28:32 hostname1 kernel: Out of memory: Kill process 26902 (postmaster) score 40 or sacrifice child May 11 10:31:26 hostname1 kernel: Out of memory: Kill process 29267 (postmaster) score 38 or sacrifice child May 11 10:38:10 hostname1 kernel: Out of memory: Kill process 29820 (postmaster) score 39 or sacrifice child May 11 10:46:06 hostname1 kernel: Out of memory: Kill process 30639 (postmaster) score 39 or sacrifice child May 11 10:46:39 hostname1 kernel: Out of memory: Kill process 31536 (postmaster) score 22 or sacrifice child May 11 10:47:13 hostname1 kernel: Out of memory: Kill process 31730 (postmaster) score 26 or sacrifice child May 11 10:50:06 hostname1 kernel: Out of memory: Kill process 31891 (postmaster) score 39 or sacrifice child May 11 10:52:10 hostname1 kernel: Out of memory: Kill process 32276 (postmaster) score 40 or sacrifice child May 11 10:52:54 hostname1 kernel: Out of memory: Kill process 703 (postmaster) score 26 or sacrifice child

само ядро решила взяться за оптимизацию ))))

да ахтунг какой-то )

Ааа попутал. Понял приложение, творящее это, на 172.20.3.166 развернуто. Побовали выяснить куда ему столько всего нужно?

нашли что идут инсерты в партицированную таблицу, партиций 1232 )

пошли к разработчикам узнавать зачем их столько завели)))

Кто-нибудь знает инструменты для анализа производительности индексов?

Кто-нибудь знает инструменты для анализа производительности индексов?

pg_stat_user_indexes

SELECT pg_stat_user_indexes.schemaname||'.'||pg_stat_user_indexes.relname, indexrelname, pg_stat_user_indexes.idx_scan, (coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)-coalesce(n_tup_hot_upd,0)+coalesce(n_tup_del,0)) as write_activity, pg_stat_user_tables.seq_scan, pg_stat_user_tables.n_live_tup, pg_size_pretty(pg_relation_size(pg_index.indexrelid::regclass)) as size from pg_stat_user_indexes join pg_stat_user_tables on pg_stat_user_indexes.relid=pg_stat_user_tables.relid join pg_index ON pg_index.indexrelid=pg_stat_user_indexes.indexrelid where pg_index.indisunique is false and pg_stat_user_indexes.idx_scan::float/(coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)-coalesce(n_tup_hot_upd,0)+coalesce(n_tup_del,0)+1)::float<0.01 and (coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)-coalesce(n_tup_hot_upd,0)+coalesce(n_tup_del,0))>10000 order by 4 desc,1,2;

показывает неэффективные индексы

Подскажите пожалуйста чат, где проконсультируют по OSM

Подскажите пожалуйста чат, где проконсультируют по OSM

начни с https://t.me/ruosm

спасибо

а потом сразу к @Komzpa конечно ?

есть следующая проблема есть сервер (30Gb Ram 80gb ssd) с одним инстансом постгреса - на нем крутятся примерно 80 баз - к каждой базе коннектятся сайты которые завязаны каждый на свою базу - в среднем весит 150 коннекшенов. Иногда возникает проблема с медленными запросами как итог остальные выстраиваются в очередь появляются тормоза и завершается все падением lighttpd. Может быть есть какие-то рекомендации как это технически правильно уладить? Спасибо.

закидать железом конечно же

Всем привет

есть следующая проблема есть сервер (30Gb Ram 80gb ssd) с одним инстансом постгреса - на нем крутятся примерно 80 баз - к каждой базе коннектятся сайты которые завязаны каждый на свою базу - в среднем весит 150 коннекшенов. Иногда возникает проблема с медленными запросами как итог остальные выстраиваются в очередь появляются тормоза и завершается все падением lighttpd. Может быть есть какие-то рекомендации как это технически правильно уладить? Спасибо.

set statement_timeout to 1;

Вот эту команду исполняю

CREATE INDEX IF NOT EXISTS userid_idx ON users (uid);

ERROR: S client not available

Ошибка вылазит

ERROR: syntax error at or near "NOT" (SQLSTATE 42601)

С чем может быть связано?

На своем компе все норм, на сервере - нет

на сервере 9.4, у себя 9.6?

или 9.5 у него

Как версию проверить?

select version()

У меня 9.5

На сервере 9.3

Спасибо

set statement_timeout to 1;

а можно поподробнее?

а можно поподробнее?

https://www.postgresql.org/docs/9.6/static/runtime-config-client.html#GUC-STATEMENT-TIMEOUT

https://www.postgresql.org/docs/9.6/static/runtime-config-client.html#GUC-STATEMENT-TIMEOUT

Я правильно понимаю, что долгие запросы в принципе не будут выполняться?

Я правильно понимаю, что долгие запросы в принципе не будут выполняться?

Да

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

Может лучше настроить пул соединений, так, чтобы каждой базе было гарантировано одно своё соединение, а остальные были в общем доступе? Как раз там 80 баз и 150 коннектов.

set statement_timeout to 1; и запросы перестают выполняться часами

и ведь не скажешь, что ты так шутишь

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

3 минуты это конечно за пределами. вам бы всю архитектуру пустить на оптимизацию

и ведь не скажешь, что ты так шутишь

Я не шучу.

тем не менее это очень радикально

3 минуты это конечно за пределами. вам бы всю архитектуру пустить на оптимизацию

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

Я не шучу.

ну конечно)) 1 мс

есть следующая проблема есть сервер (30Gb Ram 80gb ssd) с одним инстансом постгреса - на нем крутятся примерно 80 баз - к каждой базе коннектятся сайты которые завязаны каждый на свою базу - в среднем весит 150 коннекшенов. Иногда возникает проблема с медленными запросами как итог остальные выстраиваются в очередь появляются тормоза и завершается все падением lighttpd. Может быть есть какие-то рекомендации как это технически правильно уладить? Спасибо.

ресурсов?! серьёзно?..

ресурсов?! серьёзно?..

Скорее времени и людей

закидать железом конечно же

Модель vCPU Память (ГиБ) SSD-хранилище (ГБ) m3.2xlarge 8 30 2 x 80 Общий объем баз 44Гб. Самый большой клиент это 6Гб база.

блин, ну облако в амстердамах за 5 евро что-ли, 2 гига, 2 ядра, 50 гигов ссд. да на каждого клиента взять такой и не париться

для базы в 6Гб даже многовато выглядит

6Гб и запросы выполняются минутами? Самая лучшая рекомендация - рефакторинг схемы и запросов, остальное - временное и симптоматическое лечение

Вы че-то оч неправильное в базе делаете))

6 гб в памяти мясить оч быстро можно.. за секунды