Страницы

Сохранить статью у себя в соцсети:

понедельник, 8 октября 2012 г.

§ PostgreSQL monitoring via Zabbix

Настройка мониторинга PostgreSQL в Zabbix или Постгрес под наблюдением.

В PostgreSQL есть ряд системных таблицы в которых хранится статистика работы кластера БД. Выполняя обычные SQL запросы в эти таблицы можно получать разнообразные данные о базах, таблицах, индексах. 
ATTENTION: Эта статья, скрипты и конфиги устарели. Новая статья здесь.
ATTENTION: Эта статья, скрипты и конфиги устарели. Новая статья здесь.
Мониторинг будет строиться на основе bash-скриптов, которые играют роль оберток для SQL-запросов. В конфигурации агента будут определены ряд ключей с помощью которых сервер мониторинга будет опрашивать агента и получать необходимую информацию. Учитывая что статистические таблицы содержат информацию разного характера, поэтому скриптов будет несколько. В интернетах можно найти похожий вариант скриптов написанный на питоне, однако для его работы нужно ставить пару пакетов. Для bash-скриптов же, не нужно поддерживать избыточные зависимости, и если сравнивать с аналогом на питоне, то там нет некоторых возможностей, например возможность мониторинга потоковой репликации и детальной потабличной статистики вроде  blk_read/heap_read, seq_read/idx_scan и т.п. 
Дополнительно стоит отметить, что для сбора статистических данных необходимо включить следующие параметры в postgresql.conf:
  • track_activities - включает мониторинг выполнения команд всеми серверными процессами;
  • track_counts - включает сбор статистики по таблицам и индексам;
  • track_functions - включает сбор информации по пользовательским функциям;
  • track_io_counts - включает мониторинг блочного ввода-вывода (параметр появился в 9.2, включение может дать оверхед использования ресурсов, мониторинг с помощью информации предоставлямой параметром, здесь не рассматривается).
Статья будет строиться следующим образом: сначала будет описан процесс настройки, затем будет приведено описание всех ключей и затем в конце ссылки на скрипты и шаблон.
Итак, поехали:
Берем скрипты в репозитории на гитхабе или создаем их вручную и устанавливаем в каталог /var/lib/zabbix/scripts/ 
git clone https://github.com/lesovsky/zabbix-extensions/
# cp -a zabbix-extensions/files/postgresql/scripts/ /usr/libexec/zabbix-extensions/scripts/
# cp zabbix-extensions/files/postgresql/postgresql.conf /etc/zabbix/zabbix_agentd.d/
# chown zabbix: /usr/libexec/zabbix-extensions/scripts/pgsql.*
# chmod 750 /usr/libexec/zabbix-extensions/scripts/pgsql.*

Скрипты запускаются из под аккаунта zabbix и для подключения к PostgreSQL необходимо наличие файла с реквизитами .pgpass. Пропишите туда роль которая будет использоваться для подключения к PostgreSQL. В скриптах используется механизм который берет реквизиты из файла .pgpass. Тем не менее в ключах можно явно указать базу, тогда при выполнении скриптов, запросы будут брать данные относительно неё.
# echo 'localhost:5432:app_db:app_role:app_pass' > ~zabbix/.pgpass

Как видно ключи, для получения данных, используют скрипты и передают туда аргументы, в качестве аргументов как правило выступают имена таблиц, индексов, специальные параметры. Теперь нужно подгрузить этот файл в основную конфигурацию zabbix. В последних версиях появился специальный каталог откуда подгружаются все размещенные там конфиги, поэтому можно положить его туда и перезапустить агента.
# echo 'Include=/etc/zabbix/postgresql.conf' >> /etc/zabbix/zabbix_agentd.conf
# /etc/init.d/zabbix-agentd restart

Теперь все готово для мониторинга, осталось скачать шаблон и загрузить его в веб интерфейс и назначить на целевой хост. Скачать шаблон можно также из репозитория
Описание скриптов и ключей.
pgsql.ping.sh - отклик от БД.
  • pgsql.ping - время отклика от PostgreSQL. В psql посылается простой "SELECT 1".
pgsql.uptime.sh
  • pgsql.uptime - время работы PostgreSQL c момента запуска. 
pgsql.connections.sh - информация о соединениях.
  • pgsql.connections[idle_in_transaction] - количество соединений в состоянии простаиваемых транзакций;
  • pgsql.connections[idle] - количество проставиваемых соединений;
  • pgsql.connections[total] - общее количество соединений;
  • pgsql.connections[running] - количество активных соединений;
  • pgsql.connections[waiting] - количество ожидающих соединений (много = плохо).
pgsql.transactions.sh - информация о транзакциях (доступно до версии 9.2).
  • pgsql.transactions[idle] - самая долгая транзакция в состоянии 'idle'; (наличие таковых с большим временем = нежелательно);
  • pgsql.transactions[running] - самая долгая выполняющаяся транзакция;
  • pgsql.transactions[waiting] - самая долгая транзакция в состоянии ожидания (наличие таковых с большим временем = крайне нежелательно).
pgsql.db.size.sh
  • pgsql.db.size[имя_базы] - вычисляет размер указанной базы.
pgsql.dbstat.sh - общая статистика по базе вцелом, аргумент с именем базы является опциональным:
  • pgsql.dbstat[blks_hit,имя_базы] - количество страниц прочитанных из кэша;
  • pgsql.dbstat[blks_read,имя_базы] - количество страницы прочитанных с диска;
  • pgsql.dbstat[commits,имя_базы] - количество коммитов транзакций;
  • pgsql.dbstat[rollbacks,имя_базы] - количество откатов транзакций;
  • pgsql.dbstat[tup_deleted,имя_базы] - количество удаленных строк;
  • pgsql.dbstat[tup_inserted,имя_базы] - количество вставленных строк;
  • pgsql.dbstat[tup_fetched,имя_базы] - количество запрошенных строк;
  • pgsql.dbstat[tup_updated,имя_базы] - количество обновленных строк;
  • pgsql.dbstat[tup_returned,имя_базы] - количество строк отданных клиенту.
pgsql.relation.size.sh - размер отдельного индекса или таблицы без индексов, аргумент с именем базы является опциональным:
  • pgsql.relation.size[таблица/индекс,имя_базы] - получить размер индекса или размет таблицы без индексов.
pgsql.indexes.size.sh - размер всех индексов для указанной таблицы, аргумент с именем базы является опциональным:
  • pgsql.indexes.size[таблица,имя_базы] - получить размер всех индексов для указанной таблицы.
pgsql.relation.stat.sh - детальная статистика по таблицам, аргумент с именем базы является опциональным:
  • pgsql.relation.stat[таблица,heapread,имя_базы] - количество блоков таблицы прочитанных с диска (много = плохо);
  • pgsql.relation.stat[таблица,heaphits,имя_базы] - количество страниц таблицы прочитанных из кэша (много = хорошо);
  • pgsql.relation.stat[таблица,idxread,имя_базы] - количество блоков индекса прочитанных с диска (много = плохо);
  • pgsql.relation.stat[таблица,idxhits,имя_базы] - количество страниц индекса прочитанных из кэша (много = хорошо);
  • pgsql.relation.stat[таблица,toastread,имя_базы] - количество блоков TOAST-таблицы прочитанных с диска;
  • pgsql.relation.stat[таблица,toasthits,имя_базы] - количество страниц TOAST-таблицы прочитанных из кэша;
  • pgsql.relation.stat[таблица,seqscan,имя_базы] - количество выполненых последовательных сканирований таблицы (чем меньше, тем лучше);
  • pgsql.relation.stat[таблица,seqread,имя_базы] - количество последовательных чтений из таблицы;
  • pgsql.relation.stat[таблица,idxscan,имя_базы] - количество проходов по индексу (чем больше тем лучше);
  • pgsql.relation.stat[таблица,idxfetch,имя_базы] - количество строк отданных в результате прохода по индексу;
  • pgsql.relation.stat[таблица,inserted,имя_базы] - количество вставленных в таблицу строк;
  • pgsql.relation.stat[таблица,updated,имя_базы] - количество обновленных строк в таблице;
  • pgsql.relation.stat[таблица,deleted,имя_базы] - количество удаленных из таблицы строк;
  • pgsql.relation.stat[таблица,hotupdated,имя_базы] - количество HOT строк в таблице;
  • pgsql.relation.stat[таблица,live,имя_базы] - количество живых строк в таблице;
  • pgsql.relation.stat[таблица,dead,имя_базы] - количество строк помеченных для удаления (место занятое строкой освободится после вакуумации).
pgsql.buffercache.sh - статистика использования буферов. Тут используются тяжелые запросы и использование этой части мониторинга дает нагрузку на БД.
  • pgsql.buffercache[clear] - количество чистых буферов;
  • pgsql.buffercache[dirty] - количество буферов помеченных для очистки bgwriter'ом;
  • pgsql.buffercache[used] - количество используемых буферов;
  • pgsql.buffercache[total] - общее количество буферов.
pgsql.autovacuum.freeze.sh
  • pgsql.autovacuum.freeze - мониторинг состояния Transaction ID Wraparound (подробней здесь http://www.postgresql.org/docs/9.0/interactive/routine-vacuuming.html).
pgsql.wal.write.sh - сбор информации об интенсивности записи WAL-журналов (только для мастера). Тут стоит сказать, если в графике наблюдаются пики, следует увеличить значение checkpoint_segments:
  • pgsql.wal.write - получить текущую позицию записи WAL журнала.
pgsql.streaming.lag.sh - получение состояния потоковой репликации (только для слейва):
  • pgsql.streaming.lag[master] - получение лага репликации (чем меньше тем лучше, в идеале ноль).
Собственно, все. Также в шаблоне есть несколько параметров по мониторингу места, количества архивов и т.п. Эти параметры используют встроенные в zabbix ключи.

Скачать скрипты.
Скачать шаблон.
postgres

postgres

postgres

postgres

postgres

На главную "Virtualizing Linux"

24 комментария:

  1. Здравствуйте!
    Спасибо большое за know-how - все заработало отлично без всяких допиливаний напильником (Ubuntu 12.04). Единственное, пришлось заменить hasbanhg в скрипте pgsql.ping.sh на #!/bin/bash.

    Не планируете ли перенести свою работу на github? Думаю, многие с удовольствием пришлют push request :]

    Еще раз спасибо!

    ОтветитьУдалить
  2. Привет! спасибо за отзыв))
    Насчет гитхаба, идея хорошая и давно витает у меня в голове и близка к реализации. Туда я планирую выложить все наработки по мониторингам что у меня есть.

    ОтветитьУдалить
  3. Наш DBA подкинул еще пару запросов для мониторинга:

    root@puppet:/etc/puppet/modules/zabbix# git diff
    diff --git a/files/postgresql_scripts/pgsql.transactions.sh b/files/postgresql_scripts/pgsql.transactions.sh
    index d6fe438..cd9e0f8 100644
    --- a/files/postgresql_scripts/pgsql.transactions.sh
    +++ b/files/postgresql_scripts/pgsql.transactions.sh
    @@ -26,6 +26,12 @@ case "$PARAM" in
    'waiting' )
    query="SELECT COALESCE(EXTRACT (EPOCH FROM MAX(age(NOW(), query_start))), 0) as d FROM pg_stat_activity WHERE waiting = 't'"
    ;;
    +'pending_xa_count')
    + query="SELECT count(*) FROM pg_prepared_xacts where COALESCE(EXTRACT (EPOCH FROM age(NOW(), prepared)), 0) > 1000;"
    +;;
    +'pending_xa_max_time')
    + query="SELECT COALESCE(EXTRACT (EPOCH FROM max(age(NOW(), prepared))), 0) as d FROM pg_prepared_xacts;"
    +;;
    '*' ) echo "ZBX_NOTSUPPORTED"; exit 1;;
    esac

    ОтветитьУдалить
  4. Да это будет полезно тем у кого используются prepared транзакции. Спасибо!

    ОтветитьУдалить
    Ответы
    1. Добрый день.. огромная просьба, я уже который день ищу в инете не могу найти скрипт.. Есть zabbix 1.8 установлен на centos все работает нормально.. я хочу чтоб при щелчке мышкой на узел на карте zabbix в сплывающем окне кроме пинга и трасерта выходила еще и запуск телнета именно для того узла на которого щелкнул мышкой, как можно это реализовать? или если есть скрипт для запуска телнет или хотябы cmd открывал..

      Удалить
  5. А как насчет этого https://www.zabbix.com/wiki/non-english/ru/odbc_monitoring, скрипты не нужны.

    ОтветитьУдалить
    Ответы
    1. выглядит заманчиво)) спасибо за наводку!

      Удалить
    2. Я посмотрел... впечатления противоречивы))
      С одной стороны выглядит заманчиво и очень интересно. А вот с другой выплывает несколько проблем:
      1. начиная с тех что перечислены в разделе "Ограничения" по приведенной ссылке
      2. инструмент неподдерживается официально. Сам dotneft пишет "Скажем так не универсальное это решение и может быть очень много проблем в развертывании подобного мониторинга. Поэтому пока нет единого мнения как делать."
      3. из практических проблем, непонятно как обернуть пост-обработку результатов запросов (мне например надо сделать 3 запроса, полученные значения перевести из hex в dec и посчитать разницу, так я получаю лаг потоковой репликации).

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



      Удалить
  6. Добрый день Алексей!

    Спасибо за статью. Но к сожалению сам Zabbix server получает пустое значение посредством zabbix_get. Пока что интересует только pgsql.ping.sh. Агент считывает значение, а вот cервер нет.

    Centos 6.3
    Zabbix 2.0.3

    Спасибо заранее

    ОтветитьУдалить
    Ответы
    1. Привет! В каком смысле агент считывает значение? Вы пытаетесь выполнить скрипт из под аккаунта zabbix?
      Пустое значение отдается как правило, когда скрипт не может пройти аутентификацию в postgres.

      Удалить
    2. К примеру имеем сервер А (с zabbix агентом) и сервер Б (с самим zabbix сервером). То есть локально на сервере А сам скрипт отдельно срабатывает, а также если запускать посредством самого zabbix агента, вот так:
      zabbix_agentd -t "pgsql.ping"
      pgsql.ping[] [t|0.427]

      Но если сделать проверку от сервера Б, посредством zabbix_get, вот так:
      zabbix_get -s хх.хх.хх.хх -p 10050 -k "pgsql.ping"
      [здесь в консоли по идее пустота!!!]

      То данных нет, строка пустая.

      Аутентификация в базу проходит, т.к. скрипт отрабатывает нормально.

      Спасибо

      Удалить
    3. Вот вы, zabbix_agentd -t "pgsql.ping" от чъего имени запускаете? Агент работает из под аккаунта zabbix, и вы сделайте sudo -u zabbix pgsql.ping и посмотрите что будет.

      Удалить
    4. Сделал как вы сказали. Пишет следующее:

      [zabbix@cwcs04 scripts]$ bash pgsql.ping.sh
      WARNING: password file "/home/zabbix/.pgpass" has group or world access; permissions should be u=rw (0600) or less
      0.432

      Поменял разрешение, а также owner на zabbix. Warning ушёл, но zabbix_get на Zabbix сервере упорно показывает пустую строку.

      Забыл упомянуть, может это имеет значение. На сервере где установлен сам агент стоит Fedora 7.

      Спасибо.

      Удалить
    5. zabbix_get точно идет на тот сервер где запускается pgsql.ping? другие значения забираются нормально? например agent.version

      Удалить
    6. Fedora 7... вы не ошиблись с номером? )))

      Удалить
    7. 1.Да, zabbix_get идёт на тот сервер, где находится этот скрипт.

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

      [root@ip-xx-xx-xx-xx ~]# zabbix_get -s xx.xx.xx.xx -p 10050 -k "agent.version"
      2.0.3

      3. Вроде не ошибся

      [root@cwcs04 ~]# cat /etc/fedora-release
      Fedora release 7 (Moonshine)

      Удалить
  7. чтож остается включать подробный дебаг в сервере и ловить запросы))
    Fedora 7 это конечно сильно... сейчас уже Fedora 19 на дворе ))

    ОтветитьУдалить
    Ответы
    1. в дебаге pgsql.ping.nodata(180)
      freebsd 8.1 тоже пустая строка

      Удалить
  8. В версии zabbix 2.2 не добавляется темплейт

    http://pastebin.com/amCYzKEc

    ОтветитьУдалить
    Ответы
    1. вата... сяду разбираться как будет время. в ближайшее время не обещаю(((

      Удалить
    2. столкнулся с той же проблемой при добавлении шаблона. пришлось переписать, правда не уверен что сделал все правильно но дам совет, при возникновении ошибки "В выражении указана некорректная функция триггера "avg(0)". Ошибочный первый параметр." заменил avg на last и всё взлетело. данные от постргрескуэль сервера поступают не все, видимо не все скрипты отрабатываются, пока не смотрел, но основные данные видно на красивых графиках.

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

      Удалить
  9. http://pg-monz.github.io/pg_monz/index-en.html

    ОтветитьУдалить

Популярные сообщения

Профиль в Google+ Яндекс цитирования Яндекс.Метрика