PostgreSQL Instances Overview¶

!image

Connected¶

Reports whether PMM Server can connect to the PostgreSQL instance.

Version¶

The version of the PostgreSQL instance.

Shared Buffers¶

Defines the amount of memory the database server uses for shared memory buffers. Default is 128MB. Guidance on tuning is 25% of RAM, but generally doesn’t exceed 40%.

Disk-Page Buffers¶

The setting wal_buffers defines how much memory is used for caching the write-ahead log entries. Generally this value is small (3% of shared_buffers value), but it may need to be modified for heavily loaded servers.

Memory Size for each Sort¶

The parameter work_mem defines the amount of memory assigned for internal sort operations and hash tables before writing to temporary disk files. The default is 4MB.

Disk Cache Size¶

PostgreSQL’s effective_cache_size variable tunes how much RAM you expect to be available for disk caching. Generally adding Linux free+cached will give you a good idea. This value is used by the query planner whether plans will fit in memory, and when defined too low, can lead to some plans rejecting certain indexes.

Autovacuum¶

Whether autovacuum process is enabled or not. Generally the solution is to vacuum more often, not less.

PostgreSQL Connections¶

Max Connections: The maximum number of client connections allowed. Change this value with care as there are some memory resources that are allocated on a per-client basis, so setting max_connections higher will generally increase overall PostgreSQL memory usage.
Connections: The number of connection attempts (successful or not) to the PostgreSQL server.
Active Connections: The number of open connections to the PostgreSQL server.

PostgreSQL Tuples¶

Tuples: The total number of rows processed by PostgreSQL server: fetched, returned, inserted, updated, and deleted.
Read Tuple Activity: The number of rows read from the database: as returned so fetched ones.
Tuples Changed per 5 min: The number of rows changed in the last 5 minutes: inserted, updated, and deleted ones.

PostgreSQL Transactions¶

Transactions: The total number of transactions that have been either been committed or rolled back.
Duration of Transactions: Maximum duration in seconds any active transaction has been running.

Temp Files¶

Number of Temp Files: The number of temporary files created by queries.
Size of Temp files: The total amount of data written to temporary files by queries in bytes.

All temporary files are taken into account by these two gauges, regardless of why the temporary file was created (e.g., sorting or hashing), and regardless of the log_temp_files setting.

Conflicts and Locks¶

Conflicts/Deadlocks: The number of queries canceled due to conflicts with recovery in the database (due to dropped tablespaces, lock timeouts, old snapshots, pinned buffers, or deadlocks).
Number of Locks: The number of deadlocks detected by PostgreSQL.

Buffers and Blocks Operations¶

Operations with Blocks: The time spent reading and writing data file blocks by back ends, in milliseconds.

Tip

Capturing read and write time statistics is possible only if track_io_timing setting is enabled. This can be done either in configuration file or with the following query executed on the running system:

ALTER SYSTEM SET track_io_timing=ON;
SELECT pg_reload_conf();

Buffers: The number of buffers allocated by PostgreSQL.

Canceled Queries¶

The number of queries that have been canceled due to dropped tablespaces, lock timeouts, old snapshots, pinned buffers, and deadlocks.

Data shown by this gauge are based on the pg_stat_database_conflicts view.

Cache Hit Ratio¶

The number of times disk blocks were found already in the buffer cache, so that a read was not necessary.

This only includes hits in the PostgreSQL buffer cache, not the operating system’s file system cache.

Checkpoint Stats¶

The total amount of time that has been spent in the portion of checkpoint processing where files are either written or synchronized to disk, in milliseconds.

PostgreSQL Settings¶

The list of all settings of the PostgreSQL server.

System Summary¶

This section contains the following system parameters of the PostgreSQL server: CPU Usage, CPU Saturation and Max Core Usage, Disk I/O Activity, and Network Traffic.

Get expert help¶

If you need assistance, you can find comprehensive and free database knowledge on our community forum or blog posts. For professional support and services, contact our Percona Database Experts.

Community Forum Get a Percona Expert