Troubleshooting DSM managed PostgreSQL/SQLServer clusters which experience performance, connectivity, or hanging query issues
search cancel

Troubleshooting DSM managed PostgreSQL/SQLServer clusters which experience performance, connectivity, or hanging query issues

book

Article ID: 410984

calendar_today

Updated On:

Products

VMware Data Services Manager

Issue/Introduction

DSM-managed PostgreSQL and SQL Server clusters may experience various performance or connectivity issues that require in-depth system-level troubleshooting with direct visibility into database processes and their network namespace. One or more of the following symptoms may indicate the need for node-level diagnostics:

  • Clients cannot establish connections to the database server, connection timeouts, or SSL handshake failures
  • Database connectivity and performance issues indicating a network interface experiencing high load from potentially few queries
  • Database client processes consuming excessive CPU resources or causing high disk I/O load
  • Queries hang indefinitely without apparent database-level deadlocks or lock waits
  • Network Traffic Analysis Required: Need to monitor specific TCP sockets or network traffic patterns
  • Need real-time monitoring of system resources to identify performance bottlenecks

Environment

VMware Data Services Manager (DSM)

      • PostgreSQL clusters: DSM 9.0.1 onwards
      • SQL Server clusters: DSM 9.1.0 onwards

Cause

DSM-managed database clusters may experience system-level issues that cannot be diagnosed through standard database monitoring tools. These issues require access to low-level system debugging utilities to analyze network traffic, process behavior, system calls, and resource utilization patterns.

Resolution

Overview

DSM provides a debugging tool (dsm-debug) that enables administrators to access a restricted set of command-line diagnostic utilities on PostgreSQL or SQL Server cluster nodes for in-depth troubleshooting. The tool creates ephemeral containers that share the same process and network namespace as the database nodes, providing full system-level visibility while maintaining security through command restrictions.

Prerequisites

  • Root SSH access to the DSM Appliance and DSM Admin privileges
  • Knowledge of cluster name, namespace, and resource kind (PostgresCluster or SQLServerCluster)

Important Notices

  • This is a feature intended for ad-hoc diagnostic purposes only
  • Not suitable for persistent monitoring and must not be used for automation
  • The CLI provides no backwards compatibility guarantees (CLI may change/be dropped/replaced)
  • The debug session shares the same resources as the database container — including memory, CPU, and network
  • The debug session commands that generate write disk or network I/O are restricted

Accessing Debug Tools

  1. SSH into the DSM Appliance VM.
  2. Type dsm-debug --help to see all options.
  3. Start a debugging session:
    • Connect to a PostgreSQL cluster — for example, cluster with name "my-pg" in namespace "my-namespace":
      dsm-debug --kind PostgresCluster --data-service-name my-pg --namespace my-namespace
       
    • Connect to a SQL Server cluster — for example, cluster with name "my-sqlserver" in namespace "dsm-system":
      dsm-debug --kind SQLServerCluster --data-service-name my-sqlserver --namespace dsm-system
       
    • For multi-node clusters, connect to a specific node by specifying --node-index (corresponds to the index as seen in the DSM UI under Database Nodes Topology):
      dsm-debug --kind PostgresCluster --data-service-name my-pg --namespace my-namespace --node-index 1
       
      • For PostgreSQL clusters: access the monitor node by specifying --node-index 2 for vSphere Cluster HA topology, or --node-index 1 for single-server topology.
      • For SQL Server clusters: access the config node by specifying --node-index 2 for vSphere Cluster HA topology.
  4. Run any of the available diagnostic commands.
  5. Type exit to terminate the debugging session.

Session Management

  • Session Limits: A maximum of 800 sessions can be started per database node. After that, the database node needs to be restarted to reset the count.
  • Audit Logging: All executed commands are automatically logged to /var/log/tdm/provider/audit/dsm-debug-audit.log
  • Isolation: Each session runs in an isolated and restricted environment which shares the process and network namespace of the database node.

Available Diagnostic Commands

The following commands are available in all debug sessions (both PostgreSQL and SQL Server):

Process Monitoring:

  • ps — Display running processes
  • top — Real-time process viewer
  • htop — Interactive process viewer
  • pgrep — Search for processes by name
  • pmap — Display memory map of processes

System Resource Monitoring:

  • free — Display memory usage
  • df — Display filesystem usage
  • du — Display directory space usage
  • vmstat — Display virtual memory statistics
  • iostat — Display I/O statistics
  • lscpu — Display CPU information
  • lsblk — Display block devices
  • lsmem — Display memory information

Network Analysis:

  • tcpdump — Capture network packets
  • iftop — Interactive network traffic monitor
  • nload — Display network usage
  • hostname — Display system hostname
  • ss — Display socket statistics

System Tracing and Analysis:

  • strace — Trace system calls and signals
  • lsof — List open files and network connections
  • iotop-c — Display I/O usage by process

File Operations and Text Processing:

  • echo, ls, find, stat, basename, dirname, pwd, readlink — File and directory operations and information
  • cat, head, tail, grep, egrep, fgrep, sort, uniq, cut, wc, tr — File content viewing and searching

System Information:

  • uname, uptime, date, printenv — System information

Utilities:

  • sleep, test, true, false, which, id, groups — General shell utilities

PostgreSQL-Specific Commands

The following additional commands are available when connecting to a PostgreSQL cluster:

  • pg_activity — Real-time PostgreSQL activity monitor (connect with: pg_activity -h localhost -U postgres)
  • pidof — Find process IDs by name (e.g., pidof postgres)

SQL Server-Specific Commands

The following additional commands are available when connecting to a SQL Server cluster. These support Kerberos/Active Directory (Windows Authentication) and network diagnostics:

Kerberos / Active Directory:

  • kinit — Obtain Kerberos ticket-granting ticket
  • klist — List cached Kerberos tickets
  • kdestroy — Destroy Kerberos tickets
  • ktutil — Kerberos keytab utility
  • kvno — Query Kerberos service ticket version numbers
  • ldapsearch — Query LDAP/Active Directory

Network Diagnostics:

  • nslookup — Query DNS name servers
  • dig — DNS lookup utility
  • nc (netcat) — Arbitrary TCP/UDP connections and listeners

Troubleshooting Examples

Connection Problems (PostgreSQL)


# Check network connectivity and listening ports
ss -tulpn | grep 5432
lsof -i :5432
 
# Monitor real-time network traffic
tcpdump -i any port 5432
 
# Analyze network interface load
nload
 

Connection Problems (SQL Server)


# Check network connectivity and listening ports
ss -tulpn | grep 1433
lsof -i :1433
 
# Monitor real-time network traffic
tcpdump -i any port 1433
 
# DNS resolution check
nslookup my-ad-domain.example.com
dig my-ad-domain.example.com
 

Process Analysis


# Find PostgreSQL processes
pgrep postgres
 
# Find SQL Server processes
pgrep sqlservr
 
# Examine process memory usage
pmap $(pgrep postgres)
 
# List open files and connections
lsof -p $(pgrep postgres)

Kerberos / Windows Authentication Diagnostics (SQL Server only)

 
# Test TCP connectivity to Key Distribution Center (KDC) port
nc -zv my-ad-domain.example.com 88

Powered by Wolken Software