pgcenter overview

pgCenter Overview

Alexey Lesovsky8/2015PostgreSQL-Consulting.com

Agenda● PostgreSQL statistics.● PostgreSQL ad-hoc management.● pgCenter statistics interface.● pgCenter actions.● PgCenter usage examples.

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.

http://www.postgresql.org/docs/9.4/static/monitoring-stats.html

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...)

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.

pgCenter: Goals● Viewing PostgreSQL statistics.● Fast access to management tasks:

● Show/Change Configuration;● Log viewing;● Cancel queries, Terminate backends.

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.

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

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.

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().

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.

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

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.

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.

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.


● 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.


● Determine biggest tables in your database● Table with indexes size● Table only size.● Indexes size.

● Size changes — check tables which grows now.


● 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.


● 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.

Thanks.

Alexey LesovskyPgCenter

https://github.com/lesovsky/pgcenter

PostgreSQL-Consulting.comalexey.lesovsky@postgresql-consulting.com

pgcenter overview

Engineering