Pivotal Knowledge Base

Follow

Greenplum: How To Address Slow Performance

Environment

 Product  Version
 Pivotal Greenplum  4.3.x
 OS  RHEL 6.x

Purpose

This article provides step by step instructions regarding WHEN and HOW to open a Service Request with Pivotal Support for performance issues on a Greenplum cluster.

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 slowness and request a WebEx to address the issue.

Resolution

The DBA should check the database and the cluster resources for any evidence of abnormality.  

  • 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 query or a few queries which are running longer than normal.

NOTE: Offending queries are usually the ones that are blocking and/or spilling to disk.

  • Check table statistics on the tables being used by these queries
select * from gp_toolkit.gp_stats_missing;
  • Check table bloat on the tables being used by these queries
select * from gp_toolkit.gp_bloat_diag;
  • 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)

 

Comments

Powered by Zendesk