How to address slow performance in Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article provides instructions on when and how to open a Service Request with Pivotal Support for performance issues on a Greenplum cluster.

Environment

Cause

A user alerts the Database Administrator (DBA) of a slower than normal query response time in the database. The DBA opens a ticket with Pivotal Support stating that users are experiencing a slow response. 

Resolution

The DBA must check the database and the cluster resources for any bugs, errors, or abnormal behavior.

Reasons for cluster slowness

• Greenplum Database version
• Active Queries (Running and Waiting):

  select waiting,waiting_reason,count(*) from pg_stat_activity where current_query <> '<IDLE>' group by waiting, waiting_reason;
  

• Resource Queues Activity:

  select * from gp_toolkit.gp_resq_activity_by_queue ;
  

• Queries that are spilling and the size of the spill files:

  SELECT g.datname "Database",
   g.procpid "Process",
   g.sess_id "Session",
   p.usename "User",
   sum(g.size)/1024/1024::float "Total Spill Size(MB)",
   sum(g.numfiles) "Total Spill Files"
  FROM gp_toolkit.gp_workfile_usage_per_query g
  JOIN pg_stat_activity p on g.sess_id = p.sess_id
  GROUP BY 1,2,3,4
  ORDER BY 4 DESC;
  
  

• Queries that are blocking and queries that are being blocked:

  SELECT
   d.datname AS "Database",
   a.usename AS "User",
   l.pid AS "Process ID",
   l.mppsessionid AS "Session ID",
   l.locktype AS "Lock Type",
   l.relation::regclass AS "Relation",
   l.mode AS "Lock Mode",
   now()-a.query_start AS "Blocked Duration",
   substring(a.current_query from 1 for 40) AS "Blocker Query"
  FROM
   pg_locks l,
   pg_stat_activity a,
   pg_database d
  WHERE l.mppsessionid=a.sess_id
  AND l.database=d.oid
  AND l.granted = true
  AND l.relation in ( select relation from pg_locks where granted='f')
  ORDER BY 4;
  
  

• OS performance:

  \! gpssh -f ~/gpconfigs/hostfile_segments "sar -u 1 3"
  \! gpssh -f ~/gpconfigs/hostfile_segments "ps -C postgres -o state,pid,command | grep con[0-9] | grep -v ^S"
  

 

Identify a few queries that are running longer than normal and follow the steps below: 
 

1. Check table statistics on the tables being used by these queries:

select * from gp_toolkit.gp_stats_missing;

2. Check table bloat on the tables being used by these queries:

select * from gp_toolkit.gp_bloat_diag;

3. Check for catalog bloat. Refer to this article to update statistics on pg_catalog tables. This should be done during a maintenance window.

If the DBA is still unable to identify the query/issue then open a Support Request and include the following information:
 

• Greenplum version
• A brief description of the problem
• Active Queries (Running and Waiting)
• Resource Queues Activity
• Queries that are spilling and the size of the spill files
• Queries that are blocking and queries that are being blocked
• OS performance
• Master log file
• /var/log/message file
• Include the DBA contact information at the end (Full Name, Phone, Email)

Additional Information