PostgreSQL performance diagnostics

Performance diagnostics is a feature for collecting and visualizing performance statistics for PostgreSQL clusters. It enables analyzing SQL query performance and session activity to identify bottlenecks and streamline database operations.

Getting startedGetting started

Make sure that:

• Managed PostgreSQL component is installed.
• Managed ClickHouse® component is installed and enabled.
• Monitoring component with Grafana is installed.
• You have permissions to modify the ManagedPostgresConfig configuration and create PostgresqlCluster clusters.

Enabling diagnostics at the platform levelEnabling diagnostics at the platform level

To enable performance diagnostics for all PostgreSQL clusters, run this command:

kubectl patch ManagedPostgresConfigs main --type='merge' -p='{"spec":{"settings": {"performance-diagnostics":{"enabled":true}}}}'

After performance diagnostics is enabled at the platform level, the system automatically deploys components for metric collection and storage (ClickHouse® and Grafana dashboards).

Enabling statistics collection for a clusterEnabling statistics collection for a cluster

After activating performance diagnostics at the platform level, enable statistics collection for each individual cluster.

To enable statistics collection for a PostgreSQL cluster:

1. Create or update the PostgresqlCluster specification:

   apiVersion: postgresql.stackland.yandex.cloud/v1
   kind: PostgresqlCluster
   metadata:
   name: my-postgres-cluster
   namespace: default
   spec:
   instances: 3
   enablePerfDiagStatsCollect: true

   storage:
   size: 10Gi

   postgresql:
   parameters:
   max_connections: "100"
   shared_buffers: "256MB"

   Where enablePerfDiagStatsCollect: true enables performance statistics collection for this cluster.

2. Apply the configuration:

   kubectl apply -f postgresql-cluster.yaml
   

3. Wait for the cluster pods to restart. To check their status, run this command:

   kubectl get pods -l postgresql=my-postgres-cluster
   

After the restart, a perfdiag sidecar container will run in each cluster pod, collecting statistics from PostgreSQL and sending them to ClickHouse®.

Viewing data in GrafanaViewing data in Grafana

To view collected performance metrics:

1. Open Grafana.

2. In the left-hand menu, select Dashboards.

3. Find and open the PostgreSQL Cluster Performance Diagnostics dashboard.

4. At the top of the dashboard, select:

   • Cluster: Cluster name
   • Time range: Time range for analysis

Dashboard panelsDashboard panels

The dashboard is organized into the following sections and panels:

Sessions activity statisticsSessions activity statistics

Sessions stats (for the moment of interval end) is a table displaying active PostgreSQL sessions at the end of the selected time interval. It contains the following data:

• Collection time (collect_time)
• Host and host role (host and host_role)
• Database and user (database and user)
• Session state (state)
• Active query (query)
• Process ID (pid)
• Application and client details (application_name, client_addr, client_hostname, and client_port)
• Wait events (wait_event_type and wait_event)
• Timestamps (backend_start, xact_start, query_start, and state_change)
• Transaction IDs (backend_xid and backend_xmin)
• Backend type (backend_type)
• Blocking processes (blocking_pids)
• Query ID (queryid)

Per-statement statisticsPer-statement statistics

This section contains time-series charts and a table with SQL query statistics.

Time-series charts:

• Top queries by average execution time, in milliseconds
• Top queries by consumed CPU user time, in CPU cores
• Top queries by consumed CPU system time, in CPU cores
• Top queries by avg rows scanned
• Top queries by reads from filesystem, in bytes
• Top queries by writes to filesystem, in bytes
• Top queries by average calls per second
• Shared blocks read
• Shared blocks written
• Query-local blocks read
• Query-local blocks written
• Temp blocks read
• Temp blocks written
• Top queries by generated WALs, in bytes

Table:

Per-query statistics for period (sorted by avg CPU consumption) is a pivot table of per-query statistics for the selected period, sorted by average CPU usage. It includes the following information:

• Host role (host_role)
• User and database (user and database)
• Query ID and text (queryid and query)
• Total number of calls (total_calls)
• Total number of scanned rows (total_rows_scanned)
• Total execution time (total_exec_time)
• Average execution time (average_exec_time)
• Total CPU time (total_cpu_time)
• Average CPU time (average_cpu_time)
• Total size of filesystem operations (total_fs_bytes)
• Average filesystem read size (average_fs_reads_bytes)
• Average filesystem write size (average_fs_writes_bytes)

Performance analysisPerformance analysis

Identifying slow queriesIdentifying slow queries

To find slow-running queries:

1. On the Per-Statement Statistics panel, sort queries by Average Execution Time or Total Execution Time.
2. Focus on queries with high execution times.
3. Analyze their execution plans using EXPLAIN ANALYZE.

Analyzing resource usageAnalyzing resource usage

To identify resource-intensive queries:

1. Check the CPU Usage panel for queries with high CPU consumption.
2. Review the I/O Operations panel for queries with heavy I/O activity.
3. Examine the WAL Generated panel for queries producing large volumes of WAL records.

Identifying blockersIdentifying blockers

To identify blocking processes:

1. In the Sessions Activity Statistics panel, find sessions with a non-empty blocking_pids field.
2. Identify which queries are causing the block.
3. Optimize transaction logic or add appropriate indexes.

Disabling diagnosticsDisabling diagnostics

To disable statistics collection for a cluster, run this command:

kubectl patch ManagedPostgresConfigs main --type='merge' -p='{"spec":{"settings": {"performance-diagnostics":{"enabled":false}}}}'

See alsoSee also

• Managed PostgreSQL
• Creating a PostgreSQL cluster
• Monitoring