Pivotal Knowledge Base

Follow

Query / statement appears to be hanging in Pivotal Greenplum

Environment

Product Version
Pivotal Greenplum (GPDB)  

Symptom

Query / statements seem to be hanging, or queries are not progressing, or it is slow.

Resolution

There is no technical term 'hanging'. There are two things a process can do:

1. Waiting

Waiting for locks

-- database locks (look at pg_stat_activity/pg_locks)
-- find which transaction blocks the current one
-- resource queue (same as database locks)
-- find whether "active_statements" has been exceeded
-- process locks (look at process stack)
-- check for orphan processes on segments

Waiting for kernel calls

It can also wait for a resource or kernel call or it can run. This can be discovered by using pstack/strace/gdb.

-- pstack/strace/gdb show read() or write() which never returns
-- check the OS log file and the disk subsystem for issues
-- pstack/strace/gdb show recv() or send() never returning or failing
-- possible networking issue (kernel, drivers, firewall, switch) or the other end of the socket has disappeared (check process on receiving end)

2. Running

-- doing something humongous which takes more than expected time - hash/sort spill (check pgsql_tmp/), some big memory processing, swapping, etc.
-- best way to examine these is - find out all you can about the process - pg_stat_activity, pg_lock, ps output on master and segments, is the process spilling, is the OS swapping
-- if nothing comes up - check stacks, on master and segments and figure out from the stacks

Comments

Powered by Zendesk