|Pivotal Greenplum (GPDB)|
Query / 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