GPDB6 관리 alias

Greenplum에서 자주 사용하는 쿼리를 alias로 정리하였습니다.
Greenplum 6 버전입니다.

1. 테스트 쿼리
  1) 시나리오 
      - 락으로 인하여, 어떤 쿼리에 문제가 있는지 확인하는 방법
      - 카티션 조인 발생 후 truncate 실행
  2) 쿼리
     세션 #1, gpadmin=# select count(*) from aaa, aaa, aaa;
     세션 #2, gpadmin=# truncate table aaa;
     
2. 수행 Alias

## qq: 현재 수행되고 있는 세션 확인, waiting에 t가 대기하고 있는 세션
[gpadmin@mdw ~]$ qq 
    ?column?     | usename | client_addr | waiting |  pid  | sess_id 
-----------------+---------+-------------+---------+-------+---------
 00:00:10.35229  | gpadmin |             | f       | 66659 |    1980
 00:00:00        | gpadmin |             | f       | 73344 |    2149
 00:00:02.341306 | gpadmin |             | t       | 66412 |    1977
(3 rows)

## qqit: 현재 수행되고 있는 세션 확인, waiting에 t가 대기하고 있는 세션이며, 쿼리 앞부분 확인
[gpadmin@mdw ~]$ qqit
    backend_tiem     |  duration_time  | usename | client_addr | waiting |  pid  | sess_id |      substring       
---------------------+-----------------+---------+-------------+---------+-------+---------+----------------------
 2019-11-20 14:22:00 | 00:00:12.251838 | gpadmin |             | f       | 66659 |    1980 | select count(*) from
 2019-11-20 14:31:42 | 00:00:00        | gpadmin |             | f       | 73372 |    2150 | SELECT substring(bac
 2019-11-20 14:21:30 | 00:00:04.240854 | gpadmin |             | t       | 66412 |    1977 | truncate table aaa;
(3 rows)

## lt: locked 된 테이블의 Lock 유형과 락 프로세스 찾기
   - running pid: 현재 수행되고 있는 프로세스 ID, 즉 카티션 조인되는 쿼리
   - waiting pid: 현재 locking된 프로세스 ID, truncate 구문
[gpadmin@mdw ~]$ lt
 locktype | relation |        mode         | waiting_pid | running_pid | gp_segment_id 
----------+----------+---------------------+-------------+-------------+---------------
 relation | aaa      | AccessExclusiveLock |       66412 |       66659 |            -1
(1 row)

## locks: 현재 Lock 확인, 현 시점에서는 필요 없으나, 어떤 락이 걸리는지 확인
[gpadmin@mdw ~]$ locks
  pid  | relname | locktype |        mode         | gp_segment_id 
-------+---------+----------+---------------------+---------------
 66659 | aaa     | relation | AccessShareLock     |            -1
 66412 | aaa     | relation | AccessExclusiveLock |            -1
 42560 | aaa     | relation | AccessShareLock     |             0
 42561 | aaa     | relation | AccessShareLock     |             1
 43080 | aaa     | relation | AccessShareLock     |             2
 43081 | aaa     | relation | AccessShareLock     |             3
(6 rows)

## cq: 현재 수행중인 쿼리 확인
[gpadmin@mdw ~]$ cq
    ?column?     |  pid  | usename | sess_id |                                                            query                                                            
-----------------+-------+---------+---------+-----------------------------------------------------------------------------------------------------------------------------
 00:00:57.979556 | 66659 | gpadmin |    1980 | select count(*) from aaa a, aaa b, aaa c;
 00:00:49.968572 | 66412 | gpadmin |    1977 | truncate table aaa;
 00:00:00        | 73958 | gpadmin |    2163 | select now()-query_start, pid, usename, sess_id, query from pg_stat_activity where state not like '%idle%' order by 1 desc;
(3 rows)


## 카티션 조인 쿼리 취소, 
[gpadmin@mdw ~]$ psql
psql (9.4.24)
Type "help" for help.

gpadmin=# select pg_terminate_backend(66659); -- 카티션 조인 쿼리 프로세스
 pg_terminate_backend 
----------------------
 t
(1 row)

gpadmin=# select pg_terminate_backend(66659);
WARNING:  PID 66659 is not a PostgreSQL server process
 pg_terminate_backend 
----------------------
 f  ==> 세션 없어짐을 확인
(1 row)

gpadmin=# \q

## cq: 현재 수행 쿼리
[gpadmin@mdw ~]$ cq
 ?column? |  pid  | usename | sess_id |                                                            query                                                            
----------+-------+---------+---------+-----------------------------------------------------------------------------------------------------------------------------
 00:00:00 | 74500 | gpadmin |    2189 | select now()-query_start, pid, usename, sess_id, query from pg_stat_activity where state not like '%idle%' order by 1 desc;
(1 row)

[gpadmin@mdw ~]$ lt
 locktype | relation | mode | waiting_pid | running_pid | gp_segment_id 
----------+----------+------+-------------+-------------+---------------
(0 rows)

[gpadmin@mdw ~]$ 


## alias 적용 방법
gpadmin 계정 홈디렉토리의 .bashrc에 적용

[gpadmin@mdw ~]$ vi .bashrc


## 현재 실행중인 쿼리 세션
alias qq='psql -c " select now()-query_start, usename, client_addr, waiting, pid, sess_id, pg_resgroup from pg_stat_activity  where state not like '\''%idle%'\'' order by 4, 1 desc;"'

## 현재 실행중인 쿼리 세션 및 일부 쿼리
alias qqit='psql  -c "SELECT substring(backend_start,1,19) as backend_tiem, now()-query_start as duration_time, usename, client_addr, waiting, pid, sess_id, pg_resgroup, substring(query,1,20) FROM pg_stat_activity AS query_string WHERE state <> '\''idle'\'' ORDER BY waiting, duration_time desc;"'

## lock 정보
alias lt='psql  -c "SELECT distinct w.locktype, w.relation::regclass AS relation, w.mode, w.pid as waiting_pid, other.pid as running_pid, w.gp_segment_id FROM pg_catalog.pg_locks AS w JOIN pg_catalog.pg_stat_activity AS w_stm ON (w_stm.pid = w.pid) JOIN pg_catalog.pg_locks AS other ON ((w.DATABASE = other.DATABASE AND w.relation = other.relation) OR w.transactionid = other.transactionid) JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.pid = other.pid) WHERE NOT w.granted and w.pid <> other.pid;"'
alias locks='psql -c " SELECT pid, relname, locktype, mode, a.gp_segment_id from pg_locks a, pg_class where relation=oid and relname not like '\''pg_%'\'' order by 3;"'

## 현재 실행 중인 쿼리
alias cq='psql -c "select now()-query_start, pid, usename, sess_id, query from pg_stat_activity where state not like '\''%idle%'\'' order by 1 desc;"'

## analyze 정보
alias na='psql -c "SELECT count(relname) from pg_class where reltuples=0 and relpages=0 and relkind='\''r'\'' and relname not like '\''t%'\'' and relname not like '\''err%'\'';" '
alias nan='psql -c "SELECT '\''analyze '\''||nspname||'\''.'\''||relname||'\'';'\'' from pg_class c, pg_namespace nc where nc.oid = c.relnamespace and c.reltuples=0 and c.relpages=0 and c.relkind='\''r'\'' and c.relname not like '\''t%'\'' and c.relname not like '\''err%'\'';"'

## Bloate 된 테이블 리스트 보기
alias bt='psql -c "select bdinspname schema_nm, bdirelname tb_nm, bdirelpages*32.0/1024.0 real_size_mb, bdiexppages*32.0/1024.0 exp_size_mb from gp_toolkit.gp_bloat_diag where bdirelpages*32.0/1024.0 > 100;" '

## Idle 세션 보기
alias is='psql -c " SELECT now()-query_start, usename, pid, sess_id, query from pg_stat_activity where state like '\''idle'\'' order by 1 desc;"'
alias it='psql  -c "SELECT now()-query_start, usename, pid, sess_id, query FROM pg_stat_activity where trim(query) like '\''%in transaction'\'' ORDER BY 1 DESC;"'

## analyze 안된 리스트
alias na='psql -c "SELECT count(relname) from pg_class where reltuples=0 and relpages=0 and relkind='\''r'\'' and relname not like '\''t%'\'' and relname not like '\''err%'\'';" '

## analyze 안된 리스트 쿼리 생성
alias nan='psql -c "SELECT '\''analyze '\''||nspname||'\''.'\''||relname||'\'';'\'' from pg_class c, pg_namespace nc where nc.oid = c.relnamespace and c.reltuples=0 and c.relpages=0 and c.relkind='\''r'\'' and c.relname not like '\''t%'\'' and c.relname not like '\''err%'\'';"'
alias pgoption='PGOPTIONS="-c gp_session_role=utility" psql -p 5432'

## 리소스 큐
alias rss='psql -c " select a.rsqname, a.rsqcountlimit as countlimit, a.rsqcountvalue as countvalue, a.rsqwaiters as waiters, a.rsqholders as running ,a.rsqcostlimit as costlimit, a.rsqcostvalue as costvalue, b.rsqignorecostlimit as ignorecostlimit, b.rsqovercommit as overcommit from pg_resqueue_status a, pg_resqueue b where a.rsqname =b.rsqname order by 1;"'
alias rs='psql  -c " select rsqname, rsqcountlimit cntlimit, rsqcountvalue cntval, rsqcostlimit costlimit, rsqcostvalue vostval, rsqmemorylimit memlimit, rsqmemoryvalue memval, rsqwaiters waiters, rsqholders holders from gp_toolkit.gp_resqueue_status;"'

## 임시 스키마 확인
alias ts='psql -c "select n.nspname from pg_namespace n where nspname not in (select '\''pg_temp_'\''||sess_id from pg_stat_activity) and nspname  like '\''pg_temp%'\'';"'

## Reorg 대상 테이블 추출 및 쿼리 생성
alias reorg='psql -AXtc "select '\''ALTER TABLE '\''||bdinspname||'\''.'\''||bdirelname||'\'' SET WITH (REORGANIZE=TRUE); '\'' qry from gp_toolkit.gp_bloat_diag where bdirelpages*32/1024 > 10 ;" '

# resource group
alias rga='psql -c "SELECT rolname, rsgname FROM pg_roles, pg_resgroup  WHERE pg_roles.rolresgroup=pg_resgroup.oid;"'
alias rg='psql -c "SELECT * FROM gp_toolkit.gp_resgroup_status_per_host;"'
alias rgs='psql -c "SELECT * FROM gp_toolkit.gp_resgroup_status_per_segment;"'
alias rgd='psql -c "SELECT * FROM gp_toolkit.gp_resgroup_status;"'

빠른 확인이 가능해서 생각보다 편리합니다.