pgCenter Overview

Quick overview of pgCenter - admin tool for PostgreSQL

Transcript

1. pgCenter Overview Alexey Lesovsky 8/2015 PostgreSQL-Consulting.com
2. Agenda • PostgreSQL statistics. • PostgreSQL ad-hoc management. • pgCenter statistics interface. • pgCenter actions. • PgCenter usage examples.
3. PostgreSQL Statistics • http://www.postgresql.org/docs/9.4/static/monitoring-stats.html • Predefined views show the results of statistics collection. • Build your own custom views using statistics functions.
4. PostgreSQL Statistics • pg_stat_activity, • pg_stat_database, • pg_stat_all_tables (pg_stat_user_tables, pg_stat_sys_tables), • pg_stat_all_indexes (pg_stat_user_indexes, pg_stat_sys_indexes), • pg_statio_all_tables (pg_statio_user_tables, pg_statio_sys_tables), • pg_statio_all_indexes (pg_statio_user_indexes, pg_statio_sys_indexes), • pg_stat_user_functions, • pg_stat_replication, • pg_stat_bgwriter, pg_stat_archiver, pg_stat_database_conflicts, etc... • contrib modules (pg_stat_statements, pgstattuple, etc...)
5. PostgreSQL Ad-Hoc Management • Show configuration. • Edit configuration (postgresql.conf, pg_hba.conf, etc...). • Reload PostgreSQL. • Logs: view, tailing, search. • Watch long running queries. • Cancel queries. • Terminate backends. • Custom tasks with psql.
6. pgCenter: Goals • Viewing PostgreSQL statistics. • Fast access to management tasks: • Show/Change Configuration; • Log viewing; • Cancel queries, Terminate backends.
7. pgCenter Statistics • Display statistics in top-like manner. • Overall system and PostgreSQL state. • Databases, tables, indexes, functions statistics. • Long queries based on pg_stat_activity. • Statatements statistics based on pg_stat_statements. • Tables and indexes sizes. • Replication statistics based on pg_stat_replication.
8. pgCenter Statistics • Overview screen • Time, Load Average, CPU Usage, Current connection state. • Postgres activity grouped by backends states. • Autovacuum activity: workers, current longest autovacuum. • Statements: per second, average duration, current longest transaction
9. pgCenter Statistics • Statistics screen • Hotkeys for switching between various stats. • Change sort column and order (desc, asc). • Additional actions for stats. • Change age threshold for long queries. • Cancel queries, Terminate backends.
10. pgCenter Actions: Configuration • Show config with $PAGER or less by default. • Edit configuration file with $EDITOR or use vi by default • Available when pgCenter and PostgreSQL runs on the same host • Edit postgresql.conf, pg_hba.conf, pg_ident.conf, recovery.conf. • Reload PostgreSQL with pg_reload_conf().
11. pgCenter Actions: Logs • Log tail in sub-window. • Open log file in $PAGER or using less by default. • Available when pgCenter and PostgreSQL runs on the same host.
12. pgCenter Actions: Long queries • Cancel queries, Terminate backends. • Use pg_cancel_query(), pg_terminate_backends() functions. • Cancel or Terminate using backend pid. • Cancel or Terminate group of backends using state mask. • active | idle | idle_in_xact | waiting | others
13. pgCenter Actions: Connections • If I have 2, 4, 8, 16... PostgreSQL hosts? • Create connections to other hosts (limited by 8). • Switch between connections. • Close current connection. • Write connection options into ~/.pgcenterrc (or another file). • Open connections with --file=FILENAME option.
14. pgCenter Actions: Others • Show system tables and indexes statistics (default: off). • Reset PostgreSQL statistics counters. • Change long queries age threshold (default: 10 seconds). • Start psql session to current PostgreSQL.
15. pgCenter Usage: Examples • Permanent non-zero rollbacks or deadlocks — poor application code quality. • Reads > Hits — Insufficient memory or shared buffers prewarm. • Tmp files and bytes > 0 — Insufficient work_mem value.
16. pgCenter Usage: Examples • Seq scan > 0 and very high Seq read — index required. • hot_updates = 0 and very high updates — set fillfactor on table. • dead > 0 (with high updates or deletes) — bad autovacuum setup and possible bloat.
17. pgCenter Usage: Examples • Determine biggest tables in your database • Table with indexes size • Table only size. • Indexes size. • Size changes — check tables which grows now.
18. pgCenter Usage: Examples • Queries with high query or transaction age (> hours) — source of tables/indexes bloat. • Idle in transaction state — application code not close transaction, that also cause table/index bloat and may block other transactions. • Waiting state — one query block others, growing response time. • If you see here anything — probably it's bad and cause problems.
19. pgCenter Usage: Examples • pg_stat_statements contrib module • http://www.postgresql.org/docs/9.4/static/pgstatstatements.html • Should be installed into your database (see CREATE EXTENSION). • Statements statistics • General stats: calls, calls/s, returned rows, rows/s. • Resource usage stats: CPU time, Read/Write time. • Periodically reset statistics for accurate results, eg. every day.
20. Thanks. Alexey Lesovsky PgCenter https://github.com/lesovsky/pgcenter PostgreSQL-Consulting.com alexey.lesovsky@postgresql-consulting.com