Killing hung process

Post date: Nov 29, 2012 5:57:52 PM

GPKILL UTILITY

It happens–sometimes there‟s an issue with Greenplum Database and it seems that your best option is to kill the Greenplum Database process. Using „kill -9‟ or „kill -11‟ can kill critical Greenplum processes and leave the database in an unstable state, even bringing your clusters down.

There is a better way. Release 4.2.1 of Greenplum Database provides the gpkill utility to protect you from accidentally killing key Greenplum Database processes. Depending on your specific Greenplum Database installation process, the kill utility can be aliased to the gpkill utility, which redirects the kill command‟s default path to gpkill. If you attempt to use the „kill‟ command to kill a critical process such as postmaster, gpkill displays an error message and asks you to contact EMC Support. So even if you accidentally run the kill command on a gpkill-enabled host, you can‟t hurt anything.

Other ways to kill the session

Killing Sessions – Easiest thing to do is kill queries in command center. You can also do the following:

Find the session you want to kill:

select usename, procpid, waiting, query_start from pg_catalog.pg_stat_activity;

Kill the session:

select pg_terminate_backend('<procpid>’);

Note: pg_cancel_backend and pg_terminate_backend, there was issues reported in the past(Ref Issue #17995 DDL and Utility Statements )

In rare circumstances, pg_cancel_backend and pg_terminate_backend did not terminate sessions as expected. Systems could not kill queries using pg_cancel_backend or

pg_terminate_backend while they were waiting on locks for unique indexes. So it may or may not work. This issue was resolved in Greenplum database version 4.2.2.4 but exist in the older version.

Killing a query/session that has been started with its username. Since regular Greenplum user is not permitted to run pg_cancel_backend so create a function like below from a privileged user or gpadmin and grant access to thr non-privileged user.

CREATE OR REPLACE FUNCTION kill_hungprocess(pid integer)

RETURNS boolean AS $body$

DECLARE

qry boolean;

BEGIN

qry := (SELECT pg_catalog.pg_cancel_backend(procpid)

FROM pg_stat_activity

WHERE usename=(select session_user)

AND procpid=pid);

RETURN qry;

END;

$body$

LANGUAGE plpgsql

SECURITY DEFINER

VOLATILE

RETURNS NULL ON NULL INPUT;

GRANT EXECUTE ON FUNCTION kill_hungprocess(pid integer) TO sachi;

The function below gives privilege to Non privileged user to kill any session. This function is created by gpadmin user.