gpdbguru

How to find the version of GPCC [Greenplum Command Center] gpcmdr -v  How to check GP version? gpssh --version psql admin -c "select version()"

psql -d dbname -c "select name as \"Postgres.conf GUC\", setting \"Current Setting\" from pg_settings where name='vacuum_freeze_min_age'" psql -d dbname -c "select c.oid::regclass as \"Table\", greatest(age(c.relfrozenxid), age(t.relfrozenxid)) as \"Age\" from pg_class c left join pg_class t on c.reltoastrelid = t.oid where c.relkind = 'r' order by 2 desc limit 10"

 To view  AO table bloat view  click here SCHEMANAME TABLENAME env PGOPTIONS="-c client_min_messages=WARNING" psql $PSQL_CONN_PARMS -c "select nspname as \"Schema\",  relname as \"Table\", count(*) as \"# files\", sum(case when compaction_possible='t' then 1 else 0 end) as \"# files compactible\", max(percent_hidden) as \"Max % of Hidden Rows\" from (select n.nspname, c.relname, (gp_toolkit.__gp_aovisimap_compaction_info(c.oid)).* from pg_class c join pg_namespace n on n.oid = c.relnamespace where nspname like lower('$SCHEMANAME') and relname like lower('$TABLENAME') and array_to_string(c.reloptions,',') like '%appendonly=true%' ) a group by nspname, relname order by nspname, 5 desc"

 fn_exists=$(psql -d <dbname> -At -c "select case when (exists( select * from pg_proc where proname = '__gp_aovisimap_compaction_info')) then 't' else 'f' end") if [[ $fn_exists == 'f' ]]; then psql -d -f $GPHOME/share/postgresql/compaction_info.sql fi

Note: This report's accuracy is based on up to date statistics. For best results run this report after analyze is performed. select bdinspname as "Schema", bdirelname as "Table", bdidiag "Bloat Diagnosis" from gp_toolkit.gp_bloat_diag order by 1,2 desc

SQL SELECT n.nspname||'.'||c.relname FROM pg_catalog.pg_class As c INNER JOIN pg_catalog.pg_namespace AS n ON c.relnamespace = n.oid AND n.nspname = '<schemaname>' AND  c.relkind IN ('r','v') AND c.relstorage != 'x' INNER JOIN pg_catalog.pg_roles AS r ON c.relowner = r.oid AND r.rolname = '<userid>' EXCEPT SELECT partitionschemaname||'.'||partitiontablename FROM pg_catalog.pg_partitions ORDER BY 1; Here <schemaname> and  <userid> can be supplied accordingly to fetch details.

SQL to report when vacuum was last run. select a.schemaname as "Schema", a.objname as "Table", to_char(statime, 'MM/DD/YYYY HH24:MI:SS') as "Last Vacuum" from pg_stat_operations a inner join information_schema.tables t on a.schemaname = t.table_schema and a.objname = t.table_name left outer join pg_catalog.pg_partitions p on t.table_schema = partitionschemaname and t.table_name = partitiontablename inner join (select n.nspname, r.relname, r.relstorage from pg_catalog.pg_class r inner join pg_catalog.pg_namespace n on r.relnamespace = n.oid ) c on t.table_schema = c.nspname and t.table_name = c.relname where and a.actionname = 'VACUUM' and t.table_type = 'BASE TABLE' and t.table_schema not in ('pg_catalog','gp_toolkit') and p.partitiontablename is null and c.relstorage != 'x' order by 1,3 desc

How do I check for tables which are bloated? select bdinspname as "Schema", bdirelname as "Table", bdidiag "Bloat Diagnosis" from gp_toolkit.gp_bloat_diag order by 1,2 desc

List of tables with missing statistics select  a.table_schema as \"Schema\", a.table_nameas \"Table\"  from (  select  t.table_schema,  coalesce(to_char(s.time,'MM/DD/YYYY'),'Never') as last_analyze,  t.table_name  from information_schema.tables t  left outer join (  select schemaname as schema_name,    objname as table_name,    statime as time  from pg_stat_operations  where actionname = 'ANALYZE'  ) s on s.schema_name = t.table_schema  and s.table_name = t.table_name  left outer join pg_catalog.pg_partitions p  on t.table_schema = partitionschemaname  and t.table_name = partitiontablename  inner join (select n.nspname, r.relname, r.relstorage from    pg_catalog.pg_class r    inner join pg_catalog.pg_namespace n    on r.relnamespace = n.oid    ) c  on t.table_schema = c.nspname     and t.table_na...

SQL to report when analyze was last run. select  t.table_schema as \"Schema\",  coalesce(to_char(s.time,'MM/DD/YYYY'),'Never') as \"Last Analyze\",  count(*)  from information_schema.tables t  left outer join (  select schemaname as schema_name,  objname as table_name,  statime as time  from pg_stat_operations  where actionname = 'ANALYZE'  ) s on s.schema_name = t.table_schema  and s.table_name = t.table_name  left outer join pg_catalog.pg_partitions p   on t.table_schema = partitionschemaname  and t.table_name = partitiontablename  inner join (select n.nspname, r.relname, r.relstorage from  pg_catalog.pg_class r  inner join pg_catalog.pg_namespace n  on r.relnamespace = n.oid  ) c   on t.table_schema = c.nspname and t.table_name = c.relname where t.table_type = 'BASE TABLE' and t.table_schema not in ('pg_catalog','gp_toolkit') and p.partitionta...

SQL to list out all locks that are waiting. select bl.pid as blocked_pid,  a.usename as blocked_user,  ka.current_query as blocking_statement,  now() - ka.query_start as blocking_duration,  kl.pid as blocking_pid,  ka.usename as blocking_user,  a.current_query as blocked_statement,  now() - a.query_start as blocked_duration  from pg_catalog.pg_locks bl  join pg_catalog.pg_stat_activity a  on bl.pid = a.procpid  join pg_catalog.pg_locks kl  join pg_catalog.pg_stat_activity ka  on kl.pid = ka.procpid  on bl.pid != kl.pid  where not bl.granted and kl.pid != pg_backend_pid()

SQL to check locks select lorlocktype as \"Type\", lorpid         as \"Pid\", lorrelname as \"Obj Name\", lormode        as \"Mode\" from gp_toolkit.gp_locks_on_relation where lorpid != pg_backend_pid() order by lorpid; List out all PIDs select distinct lorpid as \"Pid\" from gp_toolkit.gp_locks_on_relation where lorpid != pg_backend_pid() order by lorpid List out select PID(s) details: select lorlocktype as \"Type\", lorpid         as \"Pid\", lorrelname as \"Obj Name\", lormode        as \"Mode\", lorcurrentquery as \"Query\" from gp_toolkit.gp_locks_on_relation where lorpid in ( $(echo $PID | tr ' ' ',') )  order by lorpid Note: for above sql, get PID(s) as an Input

To list down all database level packages installed. echo $GPHOME $GPHOME/bin/gppkg --query --all | grep -v "[INFO]"

SQL to list out segment host configuration SELECT hostname AS \"Host\", sum(CASE WHEN role='p' AND content >= 0 THEN 1 ELSE 0 END) AS \"# of Primary Segs\", sum(CASE WHEN role='m' AND content >= 0 THEN 1 ELSE 0 END) AS \"# of Mirror Segs\", sum(CASE WHEN role=preferred_role THEN 1 ELSE 0 END) AS \"# in Preferred Role\" FROM gp_segment_configuration GROUP BY rollup(1);

To display database level configuration. Two options are given using an if condition. This will help to list down single or all configurations. $where_addtnl= "all" or $where_addtnl= "$configname" with seg_parms AS    ( SELECT paramname,         CASE WHEN array_upper(seg_vals,1) > 3         THEN (seg_vals[1] || ';' || seg_vals[2] || ';' || seg_vals[3] || '....')         ELSE array_to_string(seg_vals,';')         END AS seg_vals         FROM         (            SELECT paramname,            array_agg(distinct paramvalue) AS seg_vals            FROM gp_toolkit.gp_param_settings()            GROUP BY 1         ) a     ) SELECT name AS \"Name\", setting || coalesce(unit,'') AS \"Master Setting\", seg_vals AS \"Di...

SQL=   SELECT COUNT(*) FROM gp_toolkit.gp_pgdatabase_invalid; SQL=  SELECT * FROM gp_toolkit.gp_pgdatabase_invalid; SQL=  SELECT time as \"Timestamp\", dbid as \"DB Id\", \"desc\" as \"Description of change\"  FROM gp_configuration_history WHERE dbid IN (SELECT dbid FROM gp_segment_configuration WHERE status='d')  ORDER BY time desc;

Distribution keys play an important role in selecting data from table. SELECT pgn.nspname as table_owner ,pgc.relname as table_name ,COALESCE(pga.attname,'DISTRIBUTED RANDOMLY') as distribution_keys FROM ( SELECT gdp.localoid, CASE WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN Unnest(gdp.attrnums) ELSE NULL END AS attnum FROM gp_distribution_policy gdp ORDER BY gdp.localoid ) AS distrokey INNER JOIN pg_class AS pgc ON distrokey.localoid = pgc.oid INNER JOIN pg_namespace pgn ON pgc.relnamespace = pgn.oid LEFT OUTER JOIN pg_attribute pga ON distrokey.attnum = pga.attnum and distrokey.localoid = pga.attrelid ORDER BY pgn.nspname, pgc.relname;

Generating the report of locks if its related to orphan process 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_lo...

Generating the report of resource queue usage or if locktype = resource queue SELECT rsqname AS "RQname", rsqcountlimit AS "RQActivestmt-Limit", rsqcountvalue AS "RQActivestmt-Current", rsqcostlimit AS "RQCost-Limit", rsqcostvalue AS "RQCost-Current", rsqmemorylimit::integer AS "RQMemory-Limit", rsqmemoryvalue::integer AS "RQMemory-Current", rsqholders AS "RQHolders", rsqwaiters AS "RQWaiters" FROM gp_toolkit.gp_resqueue_status;

TOP 10 - Relation size in the database - Index inclusive SELECT nspname ||'.'|| relname AS "Relation Name", nspname AS "Schema Name", case relkind when 'r' then 'Table' when 'i' then 'Index' when 'S' then 'Sequence' when 't' then 'Toast Table' when 'v' then 'View' when 'c' then 'Composite Type' when 'o' then 'Append-only Tables' when 's' then 'Special' end AS "Object Type", pg_size_pretty(pg_total_relation_size(a.oid)) AS "size" FROM pg_class a , pg_namespace b WHERE b.oid = a.relnamespace AND nspname NOT IN ('pg_catalog', 'information_schema') AND a.relkind!='i' AND b.nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(a.oid) DESC LIMIT 10;