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
l.locktype AS "Blocker locktype",
l.relation::regclass AS "Blocking Table",
a.usename AS "Blocking user",
l.pid AS "Blocker pid",
l.mppsessionid AS "Blockers SessionID",
l.mode AS "Blockers lockmode",
now()-a.query_start AS "Blocked duration",
substring(a.current_query from 1 for 40) AS "Blocker Query"
FROM
pg_locks l,
pg_locks w,
pg_stat_activity a
WHERE l.pid=a.procpid
AND l.transaction=w.transactionid
AND l.granted = true
AND w.granted = false
AND l.transactionid is not NULL
ORDER BY 3;
  • 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