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.
  1. SQL to list all parameter settings in Greenplum
  2. DCA Information SQL
  3. SQL to list Databases, Schemas, Users and Roles in Greenplum
  4. SQL to list Grants and Privileges in Greenplum
  5. SQL to list Schema Objects in Greenplum
  6. SQL to monitor partition information in Greenplum
  7. SQL to display information about Resource queues in Greenplum
  8. SQL to check database activities in Greenplum
  9. SQL to display Database Locks and Resource Queues in Greenplum
  10. SQL to display Database Size,Schema Size, Table and Index Size in Greenplum
  11. SQL to monitor health check of Greenplum database
  12. Finding distribution keys of all tables in the greenplum database
  13. SQL to display table and index bloat in Greenplum
  14. SQL to check user details and its activities
  15. Schema Object Relation level Administration

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
ċ
Check_processinfo_with_highest_spill.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_bloats_on_table.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_connection.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_contention.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_cpu_memory_utilizations.sql
(1k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_db_activity.sql
(1k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_disk_rw.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_dynamic_mem.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_fatal_and_error_log.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_idle.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_io.sql
(1k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_load_and_swap.sql
(1k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_locks.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_locks_not_granted.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_locks_on_table.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_log.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_log_30min.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_network_rw.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_pgstat.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_pgstat_gt10min.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_pgstat_gt15min.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_pgstat_gt20min.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_pgstat_gt25min.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_pgstat_gt30min.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_pgstat_gt5min.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_pgstat_not_idle.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_pgstat_waiting.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:55 AM
ċ
check_queries_in_db.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_query_spill.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_rq.sql
(5k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_rq_activity_by_rq.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_rq_activity_by_users.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_rq_status_wrt_curr_activity.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_schema_size_all.sql
(1k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_space.sql
(1k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_sql_running_gt10min.sql
(2k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_sql_running_gt20min.sql
(2k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_sql_running_gt30min.sql
(2k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_sql_running_gt5min.sql
(2k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_table_not_analyzed_3days.sql
(1k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_todays_log.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
check_waiting.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
checkdistributions
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
checklastanalyze
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
connected_user_and_duration.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
copylogs.sh
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
cpprdlog3days.sh
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
current_active_statement.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
gentblcntfromafile.sh
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
getcntpgclass
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
getcnttable
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
hanging_query_details.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
list_schema.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
list_users.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
longest_running_statements.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:56 AM
ċ
orphan_process.sql
(1k)
Sachchida Ojha,
Feb 6, 2016, 9:57 AM
ċ
rq.sql
(1k)
Sachchida Ojha,
Feb 6, 2016, 9:57 AM
ċ
sendingemail
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:57 AM
ċ
session_per_connected_user.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:57 AM
ċ
session_waiting_or_hanging.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:57 AM
ċ
set_search_path.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:57 AM
ċ
top20insert.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:57 AM
ċ
top20insertsummary.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:57 AM
ċ
top20select.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:57 AM
ċ
top20selectsummary.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:57 AM
ċ
user_info.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:57 AM
ċ
user_rq_map.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:57 AM
ċ
waiting_query_details.sql
(0k)
Sachchida Ojha,
Feb 6, 2016, 9:57 AM
Comments