Pivotal Knowledge Base

Follow

Script - Session level information

Goal

Is to provide a simple scripts to help administrator identify session level information in the database.

Note : Please verify the script on a test cluster , before running it on production .

Solution

User Activity

-- Current Active statements

select
    datname as "Db name",
    usename as "User name",
    procpid as "Pid",
sess_id as "SessionID", waiting as "Waiting", client_addr as "client Addr", application_name as "App Name", query_start as "Start time", substring(current_query from 1 for 40) as "Current Query" from pg_stat_activity
where current_query!='<IDLE>';

-- Longest running statements

select
    datname as "Db name",
    usename as "User name",
    procpid as "Pid",
sess_id as "SessionID", waiting as "Waiting", client_addr as "client Addr", application_name as "App Name", now() - query_start as "Duration", substring(current_query from 1 for 40) as "Current Query" from pg_stat_activity
where current_query!='<IDLE>' order by "Duration" desc;

-- All Users that is connected to the database and the connected duration

select
    datname as "Db name",
    usename as "User name",
    procpid as "Pid",
sess_id as "SessionID", waiting as "Waiting", client_addr as "client Addr", application_name as "App Name", now() - backend_start as "Connected Duration", substring(current_query from 1 for 40) as "Current Query" from pg_stat_activity order by "Connected Duration" desc;

The number of sessions that are currently connected to database by each user.

select usename as "User name",
       datname as "Db name",
       count(*) as "# of connections"
from pg_stat_activity
group by usename, datname;

The number of session waiting to attain resource or hanging.

select usename as "User name",
       datname as "Db name",
       count(*) as "# of connections"
from pg_stat_activity
where waiting='t'
group by usename, datname;

Hanging query details.

-- Check for any hanging query (master only)

select
    datname as "Db name",
    usename as "User name",
    procpid as "Pid",
    client_addr as "client Addr",
    application_name as "App Name",
    now() - query_start as "Duration",
    substring(current_query from 1 for 40) as "Current Query"
from pg_stat_activity
where waiting='t'
order by "Duration" desc;

-- Check for any hanging query (From all the segments and master), do refer to the known limitation on the "Orphan Process" section below as it also applies to this query.

SELECT datname                                AS "Db name", 
       usename                                AS "User name", 
       procpid                                AS "PID", 
       sess_id                                AS "Session ID", 
       granted                                AS "Lock Acquired", 
       total_segments                         AS "# segments waiting", 
       Now() - query_start                    AS "Duration",
       Substring(current_query FROM 1 FOR 40) AS "Current Query"
FROM   pg_stat_activity s, 
       (SELECT mppsessionid                     AS "session", 
               granted, 
               Count(DISTINCT(gp_segment_id)) AS "total_segments" 
        FROM   pg_locks 
        WHERE  granted = 'f' 
        GROUP  BY granted, 
                  session) l 
WHERE  l.session = s.sess_id;

For details about the hanging session (like locks) use queries mentioned here

Orphan Process Information.

NOTE:

  • From the below report total segments should be equal to total sessions, if not then the session is orphan or missing on one more segments
  • For more information on the orphan process use the scripts mentioned here

KNOWN ISSUE:

  • If there is a backup/restore (especially gp_dump/gp_restore) running, then to take of the parallel feature each segments connects independently and issue COPY FROM/TO , so they are not Orphan Process as well.
  • The query sometime leads to misleading message because when the query is run, this query might be in dispatch phase and not all segments could have started the transaction, so its always good to wait for 1 minutes and rerun the query to ensure if the process remains or changed.
select
   'con'|| a.mppsessionid AS "Session ID",
   b.total_seg as "Total Segments",
   count(a.*) AS "Total Sessions"
from
  (select distinct mppsessionid,gp_segment_id
   from pg_locks
   where mppsessionid not in (select sess_id from pg_stat_activity where procpid!=pg_backend_pid() OR current_query!='<IDLE>' OR waiting='t')
   and mppsessionid != 0
  ) a,
  (select count(*) as total_seg
   from gp_segment_configuration
   where role='p'
  ) b
group by 1,2
having count(a.*) < b.total_seg
order by 3;

Terminating / Cancelling a process from the database.

Kill certain individual process

select pg_cancel_backend(<process id>);
- OR- 
select pg_terminate_backend(<process id>);

<process id> can be received via the column procpid from pg_stat_activity view.

Kill all the session on the database

select pg_cancel_backend(procpid)
from pg_stat_activity;
- OR - 
select pg_terminate_backend(procpid)
from pg_stat_activity;

For more information on the pg_cancel/terminate_backend function refer to link here

Comments

  • Avatar
    Vijayakumar Ramdoss (Vijay Ram)

    Useful information.

Powered by Zendesk