Pivotal Greenplum Database (GPDB) all versions
Query or statements seem to be hanging, or queries are not progressing, or it is slow.
There is no technical term 'hanging'. There are two things a process can do:
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)
-- 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