Сделал dashboard Postgresql overview для postgres_exporter.
Чем отличается от других дашбородов postgres_exporter?
Я объединил все другие дашборды postgres_exporter в один.
Этот дашборд показывает общую информацию по кластеру.
Скриншоты и краткая инструкция по установке: postgresql, postgres_exporter, prometheus, grafana под катом.
Почему бы не использовать pgwatch2 c influxdb?
Вот краткий и неполный список проблем на момент версии 1.7 (применимо и к более младшим и скорее всего к старшим, CORE team не поменялась):
- Стабильность. Периодически падает и теряет данные или ломает данные на диске. В последнем случае не может подняться или не может сделать компакшен, от чего количество открытых файлов улетает в космос. Лечится полной остановкой DB и выполнением команд в надежде, что хоть одна поможет.
- Скорость. Заявленное в маркетинговых бумажках касается не постоянного рейта, а спайков.
- Не работают внутренние лимиты на запросы вида
SHOW TAG KEYS FROM ALL
илиSHOW EXACT SERIES CARDINALITY
и на средней базе может положить все. - Потребление ресурсов. Сожрать 256ГБ RAM, закусить 320GB свопа и все равно упасть по OOMу — легко (в момент 6-ти часового запуска, который обусловлен тем, что при старте он читает с диска все индексы в память(InMem)).
- Платная кластеризация (была представлена как часть OSS в версии 0.9 (December 8, 2014) и исчезла в 1.0 (September 26, 2014), став привилегией Enterprise версии).
- Частые breaking changes. За 3 года сменили 5+ движков (закончили это делать на версии 0.9 (December 8, 2014)). Следующий Breaking Changes — это Influx 2.0, где они ушли от База Данных\Ретеншн полиси в сторону Buckets, поменяли язык запросов на Flux.
- Периодически выкатывают фичи непонятно зачем сделанные, например сделали ifql (Flux) или Continuous Queries (последние выпилили в пользу task, по факту те же яйца только с Flux-ом) или Chronograf(буква C в TICK), при живой то графане.
- Безалаберность при подготовке релизов.
- Не самосогласованные утилиты экспорта и импорта из базы — если вы что-то экспортировали через cli, то импортировать обратно файлик не прокатит. restore из backup полностью заменяет всю метаинформацию о базах. Селективности и merge не завезли.
- Телеграф как часть платформы TICK(буква T), например, они ломали поддержку Прометея в телеграфе 1.3.2 (замена символов не попадающих под
[a-z]
). Или, например, невозможность оверрайдить Retention Policy в (input,output).kafka, т.е. организовать полноценную связкуmetrics -> telegraf -> kafka -> telegraf -> influx
у вас не получится. - Капаситор(бука K в TICK), очень неадекватно себя ведет, подстать InfluxDB. Выжирает RAM как не в себя, может говорить, что всё "ок", когда данных нет. Требует нежного обращения и ухода.
PostgreSQL
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql96 postgresql96-server postgresql96-contrib
Инициализируем PostgreSQL.
/usr/pgsql-9.6/bin/postgresql96-setup initdb
В PostgreSQL добавляем расширение pg_stat_statements в postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
Стартуем PostgreSQL
systemctl start postgresql-9.6
После этого в БД, выполните следующую команду:
CREATE EXTENSION pg_stat_statements
Проверяем что pg_stat_statements выдает данные.
# SELECT * postgres-# FROM pg_stat_statements limit 1;
userid | dbid | queryid | query | calls | total_time | min_time | max_time | mean_time | stddev_time | rows
| shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | loca
l_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time
--------+-------+------------+---------------------------------------+-------+------------+----------+----------+-----------+-------------+-----
-+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+-----
---------------+----------------+-------------------+---------------+----------------
10 | 16541 | 1215596543 | SELECT pg_catalog.set_config(?, ?, ?) | 1 | 0.043 | 0.043 | 0.043 | 0.043 | 0 | 1
| 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0
(1 row)
Postgres_exporter и Prometheus
Ознакомьтесь с этим постом: https://mcs.mail.ru/help/monitoring-with-prometheus/postgresql-exporter
Конфиг экспортера от mail.ru заточен на k8s
А для большей части запросов в графиках надо подключать queries.yaml
Ссылка на файл в начале статьи есть, но вот в .service нет упоминания extend.query-path
Необходимые файлы вы можете взять здесь: https://github.com/lest/prometheus-rpm/tree/master/postgres_exporter
Postgres_exporter и Prometheus для Redhat систем устанавливаем из этого репозитория: https://github.com/lest/prometheus-rpm
Создаем файл /etc/yum.repos.d/prometheus.repo
со следующим содержимым:
[prometheus]
name=prometheus
baseurl=https://packagecloud.io/prometheus-rpm/release/el/$releasever/$basearch
repo_gpgcheck=1
enabled=1
gpgkey=https://packagecloud.io/prometheus-rpm/release/gpgkey
https://raw.githubusercontent.com/lest/prometheus-rpm/master/RPM-GPG-KEY-prometheus-rpm
gpgcheck=1
metadata_expire=300
Устанавливаем prometheus2 и postgres_exporter
yum install -y prometheus2 postgres_exporter
В файле prometheus.yml для работы с postgres_exporter в scrape_configs добавьте следующую секцию:
scrape_configs:
- job_name: postgresql
static_configs:
- targets: ['ip-адрес-postgres-exporter:9187']
labels:
alias: postgres
Запускаем prometheus2 и postgres_exporter
systemctl start prometheus
systemctl start postgres_exporter
Проверяем статус служб prometheus2 и postgres_exporter
systemctl status prometheus
systemctl status postgres_exporter
Проверяем статус job postgresql в web-интерфейсе prometheus
Если job postgresql в состоянии down, то либо у вас не работает служба postgres_exporter либо нет коннективности от prometheus до ip-адрес-postgres-exporter:9187
Если у вас ip-адрес-postgres-exporter:9187 открывается, но метрики такие как под спойлером, значит у postgres_exporter либо неправильный конфиг либо он не смог подключится к PostgreSQL:
# HELP go_gc_duration_seconds A summary of the GC invocation durations.
# TYPE go_gc_duration_seconds summary
go_gc_duration_seconds{quantile="0"} 9.6892e-05
go_gc_duration_seconds{quantile="0.25"} 0.000155543
go_gc_duration_seconds{quantile="0.5"} 0.000238513
go_gc_duration_seconds{quantile="0.75"} 0.000296884
go_gc_duration_seconds{quantile="1"} 0.000348549
go_gc_duration_seconds_sum 0.001311971
go_gc_duration_seconds_count 6
# HELP go_goroutines Number of goroutines that currently exist.
# TYPE go_goroutines gauge
go_goroutines 12
# HELP go_info Information about the Go environment.
# TYPE go_info gauge
go_info{version="go1.11"} 1
# HELP go_memstats_alloc_bytes Number of bytes allocated and still in use.
# TYPE go_memstats_alloc_bytes gauge
go_memstats_alloc_bytes 3.0422e+06
# HELP go_memstats_alloc_bytes_total Total number of bytes allocated, even if freed.
# TYPE go_memstats_alloc_bytes_total counter
go_memstats_alloc_bytes_total 1.8356112e+07
# HELP go_memstats_buck_hash_sys_bytes Number of bytes used by the profiling bucket hash table.
# TYPE go_memstats_buck_hash_sys_bytes gauge
go_memstats_buck_hash_sys_bytes 1.44586e+06
# HELP go_memstats_frees_total Total number of frees.
# TYPE go_memstats_frees_total counter
go_memstats_frees_total 42761
# HELP go_memstats_gc_cpu_fraction The fraction of this program's available CPU time used by the GC since the program started.
# TYPE go_memstats_gc_cpu_fraction gauge
go_memstats_gc_cpu_fraction 5.846483467559864e-06
# HELP go_memstats_gc_sys_bytes Number of bytes used for garbage collection system metadata.
# TYPE go_memstats_gc_sys_bytes gauge
go_memstats_gc_sys_bytes 2.371584e+06
# HELP go_memstats_heap_alloc_bytes Number of heap bytes allocated and still in use.
# TYPE go_memstats_heap_alloc_bytes gauge
go_memstats_heap_alloc_bytes 3.0422e+06
# HELP go_memstats_heap_idle_bytes Number of heap bytes waiting to be used.
# TYPE go_memstats_heap_idle_bytes gauge
go_memstats_heap_idle_bytes 6.1734912e+07
# HELP go_memstats_heap_inuse_bytes Number of heap bytes that are in use.
# TYPE go_memstats_heap_inuse_bytes gauge
go_memstats_heap_inuse_bytes 4.096e+06
# HELP go_memstats_heap_objects Number of allocated objects.
# TYPE go_memstats_heap_objects gauge
go_memstats_heap_objects 7956
# HELP go_memstats_heap_released_bytes Number of heap bytes released to OS.
# TYPE go_memstats_heap_released_bytes gauge
go_memstats_heap_released_bytes 6.070272e+07
# HELP go_memstats_heap_sys_bytes Number of heap bytes obtained from system.
# TYPE go_memstats_heap_sys_bytes gauge
go_memstats_heap_sys_bytes 6.5830912e+07
# HELP go_memstats_last_gc_time_seconds Number of seconds since 1970 of last garbage collection.
# TYPE go_memstats_last_gc_time_seconds gauge
go_memstats_last_gc_time_seconds 1.5943804951377325e+09
# HELP go_memstats_lookups_total Total number of pointer lookups.
# TYPE go_memstats_lookups_total counter
go_memstats_lookups_total 0
# HELP go_memstats_mallocs_total Total number of mallocs.
# TYPE go_memstats_mallocs_total counter
go_memstats_mallocs_total 50717
# HELP go_memstats_mcache_inuse_bytes Number of bytes in use by mcache structures.
# TYPE go_memstats_mcache_inuse_bytes gauge
go_memstats_mcache_inuse_bytes 27648
# HELP go_memstats_mcache_sys_bytes Number of bytes used for mcache structures obtained from system.
# TYPE go_memstats_mcache_sys_bytes gauge
go_memstats_mcache_sys_bytes 32768
# HELP go_memstats_mspan_inuse_bytes Number of bytes in use by mspan structures.
# TYPE go_memstats_mspan_inuse_bytes gauge
go_memstats_mspan_inuse_bytes 41496
# HELP go_memstats_mspan_sys_bytes Number of bytes used for mspan structures obtained from system.
# TYPE go_memstats_mspan_sys_bytes gauge
go_memstats_mspan_sys_bytes 49152
# HELP go_memstats_next_gc_bytes Number of heap bytes when next garbage collection will take place.
# TYPE go_memstats_next_gc_bytes gauge
go_memstats_next_gc_bytes 4.194304e+06
# HELP go_memstats_other_sys_bytes Number of bytes used for other system allocations.
# TYPE go_memstats_other_sys_bytes gauge
go_memstats_other_sys_bytes 1.538324e+06
# HELP go_memstats_stack_inuse_bytes Number of bytes in use by the stack allocator.
# TYPE go_memstats_stack_inuse_bytes gauge
go_memstats_stack_inuse_bytes 1.277952e+06
# HELP go_memstats_stack_sys_bytes Number of bytes obtained from system for stack allocator.
# TYPE go_memstats_stack_sys_bytes gauge
go_memstats_stack_sys_bytes 1.277952e+06
# HELP go_memstats_sys_bytes Number of bytes obtained from system.
# TYPE go_memstats_sys_bytes gauge
go_memstats_sys_bytes 7.2546552e+07
# HELP go_threads Number of OS threads created.
# TYPE go_threads gauge
go_threads 24
# HELP pg_exporter_last_scrape_duration_seconds Duration of the last scrape of metrics from PostgresSQL.
# TYPE pg_exporter_last_scrape_duration_seconds gauge
pg_exporter_last_scrape_duration_seconds 15.653763958
# HELP pg_exporter_last_scrape_error Whether the last scrape of metrics from PostgreSQL resulted in an error (1 for error, 0 for success).
# TYPE pg_exporter_last_scrape_error gauge
pg_exporter_last_scrape_error 1
# HELP pg_exporter_scrapes_total Total number of times PostgresSQL was scraped for metrics.
# TYPE pg_exporter_scrapes_total counter
pg_exporter_scrapes_total 30
# HELP pg_up Whether the last scrape of metrics from PostgreSQL was able to connect to the server (1 for yes, 0 for no).
# TYPE pg_up gauge
pg_up 0
# HELP postgres_exporter_build_info A metric with a constant '1' value labeled by version, revision, branch, and goversion from which postgres_exporter was built.
# TYPE postgres_exporter_build_info gauge
postgres_exporter_build_info{branch="",goversion="go1.11",revision="",version="0.0.1"} 1
# HELP process_cpu_seconds_total Total user and system CPU time spent in seconds.
# TYPE process_cpu_seconds_total counter
process_cpu_seconds_total 0.17
# HELP process_max_fds Maximum number of open file descriptors.
# TYPE process_max_fds gauge
process_max_fds 1024
# HELP process_open_fds Number of open file descriptors.
# TYPE process_open_fds gauge
process_open_fds 9
# HELP process_resident_memory_bytes Resident memory size in bytes.
# TYPE process_resident_memory_bytes gauge
process_resident_memory_bytes 8.781824e+06
# HELP process_start_time_seconds Start time of the process since unix epoch in seconds.
# TYPE process_start_time_seconds gauge
process_start_time_seconds 1.59438020205e+09
# HELP process_virtual_memory_bytes Virtual memory size in bytes.
# TYPE process_virtual_memory_bytes gauge
process_virtual_memory_bytes 1.1585536e+08
# HELP process_virtual_memory_max_bytes Maximum amount of virtual memory available in bytes.
# TYPE process_virtual_memory_max_bytes gauge
process_virtual_memory_max_bytes -1
# HELP promhttp_metric_handler_requests_in_flight Current number of scrapes being served.
# TYPE promhttp_metric_handler_requests_in_flight gauge
promhttp_metric_handler_requests_in_flight 2
# HELP promhttp_metric_handler_requests_total Total number of scrapes by HTTP status code.
# TYPE promhttp_metric_handler_requests_total counter
promhttp_metric_handler_requests_total{code="200"} 26
promhttp_metric_handler_requests_total{code="500"} 0
promhttp_metric_handler_requests_total{code="503"} 0
То нужно смотреть логи службы postgres_exporter:
journalctl --full --no-pager -u postgres_exporter
Grafana
Создаем файл /etc/yum.repos.d/grafana.repo
со следующим содержимым:
[grafana]
name=grafana
baseurl=https://packages.grafana.com/oss/rpm
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://packages.grafana.com/gpg.key
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
Устанавливаем grafana
yum -y install grafana initscripts urw-fonts wget
Запускаем grafana
systemctl start grafana-server
Берем dashboard здесь
https://grafana.com/grafana/dashboards/12273
Исходный код тут:
https://github.com/patsevanton/postgresql_overview_postgres_exporter
P.S. В этом дашборде мне не хватает знаний в promql и postgresql. Поэтому я надеюсь на то что вы мне поможете советом как улучшить дашборд или сделаете pull request.
P.S. Как руки дойдут, планирую сделать дашборд для информации по конкретной БД внутри PostgreSQL.
P.S. В телеграме пользователь MAdMAx прислал проверку на распухание индексов. Запрос показывает насколько общий размер индексов таблицы больше объема данных в таблице.
/* Схема, таблица, её размер в байтах */ WITH usertables AS
(SELECT nspname AS schemaname,
relname AS tablename,
pg_relation_size(C.oid) AS bytes
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE C.relkind = 'r' /* r - "обычная" таблица */
AND C.relhasindex = TRUE /* у которой есть индексы */
AND nspname NOT IN ('pg_catalog',
'information_schema')/* не системная */ ), /*Схема, таблица, название индекса, его размер в байтах */ userindexes AS
(SELECT schemaname,
tablename,
indexname,
pg_relation_size(C.oid) AS bytes
FROM pg_indexes I
INNER JOIN pg_class C ON I.indexname = C.relname
WHERE schemaname NOT IN ('pg_catalog',
'information_schema') )
SELECT schemaname,
tablename,
pg_size_pretty(S.bytes) AS table_size,
index_count,
pg_size_pretty(sum_bytes) AS sum_index_size,
round((sum_bytes / bytes)::numeric, 1) AS ratio /* коэффициент, на который общий объем всех индексов таблицы, отличается от её объема*/
FROM
(SELECT T.schemaname,
T.tablename,
T.bytes, /* размер таблицы */ count(I.indexname) AS index_count, /* кол-во индексов в таблице */ sum(I.bytes) AS sum_bytes /* их суммарный размер */
FROM usertables T
INNER JOIN userindexes I ON (T.schemaname = I.schemaname
AND T.tablename = I.tablename)
WHERE T.bytes > 0
AND I.bytes > 0 /* исключаем таблицы и индексы с размером 0 */
GROUP BY T.schemaname,
T.tablename,
T.bytes) S
WHERE (sum_bytes / bytes) > 2 /* желаемое значение коэффициента */
AND bytes > 10000000 /* желательный размер таблицы в байтах */
AND S.schemaname NOT IN ('partitions')/* исключение схем */
ORDER BY 6 DESC
Дальше нужно отельным запросом смотреть на индексы "подозрительной" таблицы
SELECT nspname || '.' || C.relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size",
I.indexrelname, I.idx_tup_read, I.idx_tup_fetch
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
left join pg_tablespace T on C.reltablespace = T.oid
left join pg_stat_user_indexes I on (I.schemaname = nspname and I.indexrelname = C.relname)
WHERE
relkind = 'i' /*только индексы*/
and I.relname = '%TABLE_NAME%'
ORDER BY 2 DESC