Postgresql / PPAS
-
[PPAS / Postgresql]해당 Instance 내의 INDEX 확인.Postgresql / PPAS/운영 2014. 11. 18. 17:33
SELECT i.relname AS indname, i.relowner AS indowner, idx.indrelid::regclass, am.amname AS indam, idx.indkey, ARRAY( SELECT pg_get_indexdef(idx.indexrelid, k + 1, TRUE) FROM generate_subscripts(idx.indkey, 1) AS k ORDER BY k ) AS indkey_names, idx.indexprs IS NOT NULL AS indexprs, idx.indpred IS NOT NULL AS indpredFROM pg_index AS idxJOIN pg_class AS iON i.oid = idx.indexrelidJOIN pg_am AS amON i..
-
[PPAS / Postgresql] 테이블 사이즈 확인Postgresql / PPAS/운영 2014. 11. 18. 17:31
- 테이블 사이즈 확인SELECT relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"FROM pg_class CLEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind 'i'AND nspname !~ '^pg_toast'ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20;
-
[PPAS / Postgresql] pg_locks 테이블을 참고하여 Transaction 사용하는 테이블 확인Postgresql / PPAS/Lock 2014. 11. 18. 17:30
blocking_statement 필드와 blocking_pid 를 확인하여어떤 테이블이 트랜잭션을 쓰고있는지 확인.focus=# select relation::regclass from pg_locks where pid=2782; relation -----------------------focus.pk_tb_samplefocus.ix_tb_sample_01 focus.tb_sample DUALfocus.ix_tb_sample_02focus.ix_tb_sample_03focus.ix_tb_sample_04
-
[PPAS / Postgresql] SQL 경합 현상 분석 쿼리Postgresql / PPAS/Lock 2014. 11. 18. 17:26
- SQL 경합 현상 분석 쿼리 SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, ka.current_query AS blocking_statement,TO_CHAR(NOW() - ka.query_start, 'HH24:MI:SS') AS blocking_duration,kl.pid AS blocking_pid, ka.usename AS blocking_user,a.current_query AS blocked_statement,TO_CHAR(NOW() - a.query_start, 'HH24:MI:SS') AS blocked_durationFROM pg_catalog.pg_locks blJOIN pg_catalog.pg_stat_activity a ON..