Sachi's SQL collections for Greenplum database DBA's

Note: pg_stat_activity, pg_locks, pg_class, pg_namespace tables/views are most often used tables and views to monitor greenplum database.

create/generate DDL of each objects available in particular schema in separate SQL file

tbl_list=`psql -At -c "select tablename from pg_tables where schemaname ='${PGSCHEMA}' order by 1;"` # Fetch all table Name

for fname in $tbl_list

do

ddl=`PGPASSWORD='passwd' pg_dump -h hostname -U gpadmin testschema -s -t "${PGSCHEMA}.$fname" >${script_dir}/${output_dir}/$fname.sql` # Fetch ddl for all tables

#pg_dump -h hostname -U gpadmin testschema -s -t "${PGSCHEMA}.$fname" >${script_dir}/${output_dir}/$fname.sql

echo "Table DDL generated : "${PGSCHEMA}.$fname | tee -a ${log_file}

done

To see all the currently active queries for all resource queues,

SELECT rolname, rsqname, locktype, objid, transaction, pid, mode, granted

FROM pg_roles, pg_resqueue, pg_locks

WHERE pg_roles.rolresqueue=pg_locks.objid

AND pg_locks.objid=pg_resqueue.oid;

To see the roles assigned to a resource queue,

SELECT rolname, rsqname

FROM pg_roles, pg_resqueue

WHERE pg_roles.rolresqueue=pg_resqueue.oid;

Delleting all pg_temp schema.

cat /dev/null > execute_drop_on_all.sh | psql -d template1 -Atc "select datname from pg_database where datname != 'template0'" | while read a; do echo "Checking database ${a}"; psql -Atc "select 'drop schema if exists ' || nspname || ' cascade;' from (select nspname from pg_namespace where nspname like 'pg_temp%' union select nspname from gp_dist_random('pg_namespace') where nspname like 'pg_temp%' except select 'pg_temp_' || sess_id::varchar from pg_stat_activity) as foo" ${a} > drop_temp_schema_$a.ddl ; echo "psql -f drop_temp_schema_$a.ddl -d ${a}" >> execute_drop_on_all.sh ; done

- Detect all the orphan process through the unix utility

source /usr/local/greenplum-db/greenplum_path.sh ; ORPHAN="ps auwxx|grep postgres|grep con[0-9]|`echo "egrep -v \\\"wal|con0|gpsyncagent"`|`psql -R '|' -d template1 -Atc "select 'con'||sess_id::text from pg_stat_activity"`\""; gpssh `psql -R' ' -d template1 -Atc " select distinct '-h ' || hostname from gp_segment_configuration "` $ORPHAN

- Check from the database end.

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;

-- Check if the orphan process is blocking any session / query.

SELECT

w.relation::regclass AS "Table",

w.mode AS "Waiters Mode",

w.pid AS "Waiters PID",

w.mppsessionid AS "Waiters SessionID",

b.mode AS "Blockers Mode",

b.pid AS "Blockers PID",

b.mppsessionid AS "Blockers SessionID",

(select 'Hostname: ' || c.hostname ||' Content: '|| c.content || ' Port: ' || port from gp_segment_configuration c where c.content=b.gp_segment_id and role='p') AS "Blocking Segment"

FROM pg_catalog.pg_locks AS w,

pg_catalog.pg_locks AS b

Where ((w."database" = b."database" AND w.relation = b.relation)

OR w.transactionid = b.transaction)

AND w.granted='f'

AND b.granted='t'

AND w.mppsessionid <> b.mppsessionid

AND w.mppsessionid in (SELECT l.mppsessionid FROM pg_locks l WHERE l.granted = true AND relation in ( select relation from pg_locks where granted='f'))

AND w.gp_segment_id = b.gp_segment_id ORDER BY 1;

Orphan process is where there are process (related a gang) that is running on the segments and there is no corresponding process related to the same gang on the master or other segments.

This orphan process can arise when there is query abort , process crash , server / segment crash etc.

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 to its segments and issue COPY FROM/TO, so they are not orphan Process.

If the orphan process that is detected on master is IDLE and there is no corresponding process on any segment for that con<sessionID> , its prefectly fine , the segments IDLE process is terminated by the Pivotal Greenplum based on parameter gp_vmem_idle_resource_timeout