The Sad State of PostgreSQL Monitoring

Slide 1

Slide 1 text

Tomas Barton (@barton_tomas) The Sad State of PostgreSQL Monitoring © gawrifort

Slide 2

Slide 2 text

PostgreSQL monitoring • PostgreSQL is an awesome database! We try to focus on premise open-source solutions.

Slide 3

Slide 3 text

PostgreSQL monitoring • PostgreSQL is an awesome database! • PostgreSQL monitoring tools are not so awesome We try to focus on premise open-source solutions.

Slide 4

Slide 4 text

PostgreSQL monitoring • PostgreSQL is an awesome database! • PostgreSQL monitoring tools are not so awesome • Database developers are usually not good with UX (or developing frontend) We try to focus on premise open-source solutions.

Slide 5

Slide 5 text

Slide 6

Slide 6 text

From PostgreSQL wiki: monitoring solutions • EnterpriseDB Postgres Enterprise Manager • pganalyze • pgwatch2 • pg_statsinfo & pg_stats_reporter • PGObserver • pgCluu • PoWA • OPM: Open PostgreSQL Monitoring Source: https://wiki.postgresql.org/wiki/Monitoring

Slide 7

Slide 7 text

One monitoring solution to rule them all?

Slide 8

Slide 8 text

Probably not. Depends on requirements.

Slide 9

Slide 9 text

1. Basic OS metrics • CPU • RAM • Disk I/O 2. PostgreSQL statistics • Database stats • Table stats • Index stats • Query stats 3. Conﬁgurable dashboards • Simple UI 4. Conﬁguration hints • Query optimization • Index/Table bloat 5. Alerting Requirements

Slide 10

Slide 10 text

No content

Slide 11

Slide 11 text

Common metrics • most interesting metrics can be collected from build-in tables: • pg_stat_database • pg_stat_bgwriter

Slide 12

Slide 12 text

Supported functions function/version 9.2+ 9.1 8.3-9.0 8.1-8.2 7.4-8.0 datid x x x x datname x x x x numbackends x x x x x xact_commit x x x x x xact_rollback x x x x x blks_read x x x x x blks_hit x x x x x tup_returned x x x tup_fetched x x x

Slide 13

Slide 13 text

Supported functions (continue) function/version 9.2+ 9.1 8.3-9.0 8.1-8.2 7.4-8.0 tup_returned x x x tup_fetched x x x tup_updated x x x tup_deleted x x x conflicts x x temp_files x temp_bytes x deadlocks x blk_read_time x

Slide 14

Slide 14 text

PGObserver • From Zalando • Components: • Gatherer (Java) • Frontend (Python + JavaScript) • Backend DB (PostgreSQL) • Optional: • Aggregation crons • Blocking monitor • InﬂuxDB exporter • Timeline analysis

Slide 15

Slide 15 text

No content

Slide 16

Slide 16 text

No content

Slide 17

Slide 17 text

No content

Slide 18

Slide 18 text

Interesting features • Unused indexes • Duplicate indexes • Bloated tables Disadvantages • Not actively developed • Old-school interface • Complicated installation PGOBserver - summary

Slide 19

Slide 19 text

OPM (Open PostgreSQL Monitoring) • Components: • opm-core (JavaScript/Perl) • check_pgactivity • wh_nagios (PostgreSQL extension) • PostgreSQL (data storage) • nagios / icinga (check execution)

Slide 20

Slide 20 text

No content

Slide 21

Slide 21 text

No content

Slide 22

Slide 22 text

No content

Slide 23

Slide 23 text

• connections • bgwriter • btree_bloat • commit_ratio • database_size • hit_ratio • invalid_indexes • last_analyze • last_vacuum • longest_query • max_freeze_age • streaming_delta • temp_files • wal_files OPM - many checks few examples (not a complete list):

Slide 24

Slide 24 text

OPM - example • check_streaming_delta • reﬂects changes in PostgreSQL versions q{SELECT application_name, client_addr, pid, sent_lsn, write_lsn, flush_lsn, replay_lsn, CASE pg_is_in_recovery() WHEN true THEN pg_last_wal_receive_lsn() ELSE pg_current_wal_lsn() END FROM pg_stat_replication WHERE state NOT IN ('startup', 'backup')}, 01. 02. 03. 04. 05. 06.

Slide 25

Slide 25 text

Interesting features • Well maintained nagios check script • No need to install agent (if you're using Nagios) Disadvantages • Clumsy UI • Can't compare multiple databases OPM - summary

Slide 26

Slide 26 text

PostreSQL Workload Analyzer (PoWA) • Components: • powa-archivist (PostgreSQL extension) • powa-web (Python/JavaScript) • Optional: • pg_stat_statements - providing data about queries being executed • pg_qualstats - providing data about predicates, or where clauses • pg_stat_kcache - providing data about operating-system level cache • HypoPG - allowing you to create hypothetical indexes

Slide 27

Slide 27 text

No content

Slide 28

Slide 28 text

No content

Slide 29

Slide 29 text

Interesting features • Query hints • Resources used per query • Package for Debian/Redhat Disadvantages • Clumsy UI • Can't compare multiple databases • PoWA is storing data in DB we're monitoring PoWA - summary

Slide 30

Slide 30 text

pgCluu • Statically generated reports • Components: • pgcluu_collectd (Perl) • pgcluu.service - generate reports (Perl/JavaScript)

Slide 31

Slide 31 text

No content

Slide 32

Slide 32 text

No content

Slide 33

Slide 33 text

Interesting features • System/database statistics in one place Disadvantages • Clumsy UI • Can't compare multiple databases • Can't change date range pgCluu - summary

Slide 34

Slide 34 text

pganalyze • Components: • collector (go) • web interface (hosted)

Slide 35

Slide 35 text

No content

Slide 36

Slide 36 text

Interesting features • Query hints • Table stats • Package for Debian/Redhat • Easy installation Disadvantages • SaaS • No free trial (with full features) • No open-source backend pganalyze - summary

Slide 37

Slide 37 text

openclipart.org

Slide 38

Slide 38 text

What we're getting?

Slide 39

Slide 39 text

What we're getting? • Various attempts on frontend UI

Slide 40

Slide 40 text

What we're getting? • Various attempts on frontend UI • Different techniques of collecting metrics

Slide 41

Slide 41 text

What we're getting? • Various attempts on frontend UI • Different techniques of collecting metrics • Incompatible agents/storage

Slide 42

Slide 42 text

What we're getting? • Various attempts on frontend UI • Different techniques of collecting metrics • Incompatible agents/storage • Hardly customizable solutions

Slide 43

Slide 43 text

What we're getting? • Various attempts on frontend UI • Different techniques of collecting metrics • Incompatible agents/storage • Hardly customizable solutions • Similar functionality reimplemented many times

Slide 44

Slide 44 text

What do we need?

Slide 45

Slide 45 text

Unobtrusive monitoring Kitchen Stories (2003) - Bent Hamer

Slide 46

Slide 46 text

What do we need? • Unobtrusive monitoring

Slide 47

Slide 47 text

What do we need? • Unobtrusive monitoring • Common protocols

Slide 48

Slide 48 text

What do we need? • Unobtrusive monitoring • Common protocols • Modular components / customizable features

Slide 49

Slide 49 text

What do we need? • Unobtrusive monitoring • Common protocols • Modular components / customizable features • Usable UI

Slide 50

Slide 50 text

pgwatch2 • Components: • Metrics collector (go) • Web UI (Grafana) • Timeseries DB (InﬂuxDB) • Conﬁg DB (PostgreSQL)

Slide 51

Slide 51 text

No content

Slide 52

Slide 52 text

No content

Slide 53

Slide 53 text

No content

Slide 54

Slide 54 text

Interesting features • Great UI (thx to Grafana!) • Timeseries storage • Default data retention 90 days ( PW2_IRETENTIONDAYS ) Disadvantages • High coupling (single container) • Persistence? • Scalability? pgwatch2 - summary

Slide 55

Slide 55 text

Collectd • Metric collector (written in C) • Includes predeﬁned queries for PostgreSQL • Customizable queries • Supports OS metrics (CPU, RAM, disk plugins) • Multiple supported outputs: • Graphite • Prometheus • Riemann • OpenTSDB

Slide 56

Slide 56 text

Collectd example Statement "SELECT sum(n_live_tup) AS live,\ sum(n_dead_tup) AS dead FROM pg_stat_user_tables;" Type "pg_n_tup_g" InstancePrefix "live" ValuesFrom "live" MinVersion 80300 01. 02. 03. 04. 05. 06. 07. 08. 09. 10.

Slide 57

Slide 57 text

Telegraf • Metric collector (go) • Reads data from pg_stat_database and pg_stat_bgwriter • Part of TICK stack • Supports OS metrics (CPU, RAM, disk plugins) • Customizable queries [[inputs.postgresql]] address = "postgres://telegraf@localhost/someDB" databases = ["app_production"] 01. 02. 03.

Slide 58

Slide 58 text

Monitoring system we'd like to have

Slide 59

Slide 59 text

Datadog • Basic metrics description • Collecting metrics from pg_stat* Open-source • Similar dashboards can be displayed in Grafana • Most problematic is storing high- cardinality data in timeseries DB Examples of SaaS solutions

Slide 60

Slide 60 text

Datadog

Slide 61

Slide 61 text

No content

Slide 62

Slide 62 text

Sysdig sysdig -s 2000 -A -c echo_fds proc.name=postgres \ and evt.buffer contains SELECT 01. 02.

Slide 63

Slide 63 text

Conclusion • DB clusters might require different approaches to monitoring • Metric collection daemons ( collectd , telegraf ) works well for common metrics • Grafana is currently best (open-source) UI for monitoring dashboards • Expensive monitoring queries should be executed in less frequent intervals

Slide 64

Slide 64 text

Future? • Monitoring PostgreSQL from container (no DB restart needed) • Unix-like components (single purpose) rather than complex monitoring systems • Advanced query analysis and hints

Slide 65

Slide 65 text

#grafanaporn

Slide 66

Slide 66 text

@barton_tomas Thank you for attention!

Slide 67

Slide 67 text

Created in Shower

Slide 68

Slide 68 text

• Sad Elephant by gawrifort • You're being monitored • Kitchen Stories (2003) • Business vector created by Dooder - Freepik.com • Icons made by Webalys Freebies from www.ﬂaticon.com • Building software by Manu