Postgresql 의 설정 및 Trace Log

** postgres 의 설정(postgresql.conf)을 잘보면 다음의 part 들로 구성되어 있다.
설정 변경 시에는 postgres 를 재기동한다.

1. FILE LOCATIONS
: postgres 에서 필요한 hba.conf 파일이나 data directory, pid file 의 directory 등 파일 관련 설정

2. CONNECTIONS AND AUTHENTICATION
: postmaster 의 listen port 및 max_connection 등 접속 및 인증 관련 설정

3. RESOURCE USAGE (except WAL)
: shared buffer 및 temp buffer 등의 크기 지정, shared preload library 추가 등의 설정 등
: 기타 Resource 사용과 관련된 몇가지 설정

4. WRITE AHEAD LOG
: wal_level, commit_delay 등 WAL 관련 설정
: checkpoint 및 archive 관련 설정

5. REPLICATION
: 이중화 관련 설정

6. QUERY TUNING
: 각종 Query Optimization 관련 Factor 들 설정

7. ERROR REPORTING AND LOGGING
: Trace Log 관련 설정.
: 어느 파일에 언제, 어떤 내용을, 어떤 방식으로 남길 것인지에 대한 다양한 설정이 있음.

8. RUNTIME STATISTICS
: stat collector 가 수집할 내용들을 설정하거나,log_statement_stats 와 같이 query 수행과 함께 통계데이터를 모니터링할 수 있는 기능 설정

9. AUTOVACUUM PARAMETERS 등 기타 설정

위의 설정들 중 7 번 Part 에서 logging_collector 설정을 on 으로 하면 파일로 trace 로그 및 통계 모니터링 로그를 남기고 off 로 하면 stdout 으로 보여준다.
무엇을 남길지를 아래와 같이 설정하니…

debug_print_parse = on 
debug_print_rewritten = on 
debug_print_plan = off 
debug_pretty_print = on 
log_checkpoints = off 
log_connections = on 
log_disconnections = on 
log_duration = on 
#log_error_verbosity = default      # terse, default, or verbose messages 
#log_hostname = off 
log_line_prefix = '%h %u %a %x'

아래와 같이 사용자가 수행한 query 에 대해 connect 부터 disconnection 까지 그리고 중간의 parse/plan tree 까지 상세히 보여준다.
파일로 또는 stadout 으로 Postgressql 의 수행 과정을 trace log 를 통해 확인해보자.

[local] bitmyer [unknown] 0LOG:  connection authorized: user=bitmyer database=mydb 
[local] bitmyer psql 0LOG:  PARSER STATISTICS 
[local] bitmyer psql 0DETAIL:  ! system usage stats: 
        !       0.000062 elapsed 0.000000 user 0.000000 system sec 
        !       [0.000999 user 0.001999 sys total] 
        !       0/0 [0/256] filesystem blocks in/out 
        !       0/61 [0/852] page faults/reclaims, 0 [0] swaps 
        !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent 
        !       0/0 [2/0] voluntary/involuntary context switches 
[local] bitmyer psql 0STATEMENT:  select count(*) from dplee; 
[local] bitmyer psql 0LOG:  statement: select count(*) from dplee; 
[local] bitmyer psql 0LOG:  PARSE ANALYSIS STATISTICS 
[local] bitmyer psql 0DETAIL:  ! system usage stats: 
        !       0.000309 elapsed 0.000000 user 0.000000 system sec 
        !       [0.000999 user 0.001999 sys total] 
        !       0/0 [0/256] filesystem blocks in/out 
        !       0/74 [0/931] page faults/reclaims, 0 [0] swaps 
        !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent 
        !       0/0 [2/0] voluntary/involuntary context switches 
[local] bitmyer psql 0STATEMENT:  select count(*) from dplee; 
[local] bitmyer psql 0LOG:  parse tree: 
[local] bitmyer psql 0DETAIL:     {QUERY 
           :commandType 1 
           :querySource 0 
           :canSetTag true 
           :utilityStmt <> 
           :resultRelation 0 
           :hasAggs true 
           :hasWindowFuncs false 
           :hasSubLinks false 
           :hasDistinctOn false 
           :hasRecursive false 
           :hasModifyingCTE false 
           :hasForUpdate false 
           :cteList <> 
           :rtable ( 
              {RTE 
              :alias <> 
              :eref 
                 {ALIAS 
                 :aliasname dplee 
                 :colnames ("c1" "c2") 
                 } 
              :rtekind 0 
              :relid 16385 
              :relkind r 
              :inh true 
              :inFromCl true 
              :requiredPerms 2 
              :checkAsUser 0 
              :selectedCols (b) 
              :modifiedCols (b) 
              } 
           ) 
           :jointree 
              {FROMEXPR 
              :fromlist ( 
                 {RANGETBLREF 
                 :rtindex 1 
                 } 
              ) 
              :quals <> 
              } 
           :targetList ( 
              {TARGETENTRY 
              :expr 
                 {AGGREF 
                 :aggfnoid 2803 
                 :aggtype 20 
                 :aggcollid 0 
                 :inputcollid 0 
                 :args <> 
                 :aggorder <> 
                 :aggdistinct <> 
                 :aggstar true 
                 :agglevelsup 0 
                 :location 7 
                 } 
              :resno 1 
              :resname count 
              :ressortgroupref 0 
              :resorigtbl 0 
              :resorigcol 0 
              :resjunk false 
              } 
           ) 
           :returningList <> 
           :groupClause <> 
           :havingQual <> 
           :windowClause <> 
           :distinctClause <> 
           :sortClause <> 
           :limitOffset <> 
           :limitCount <> 
           :rowMarks <> 
           :setOperations <> 
           :constraintDeps <> 
           } 

[local] bitmyer psql 0STATEMENT:  select count(*) from dplee; 
[local] bitmyer psql 0LOG:  REWRITER STATISTICS 
[local] bitmyer psql 0DETAIL:  ! system usage stats: 
        !       0.000005 elapsed 0.000000 user 0.000000 system sec 
        !       [0.000999 user 0.001999 sys total] 
        !       0/0 [0/256] filesystem blocks in/out 
        !       0/2 [0/946] page faults/reclaims, 0 [0] swaps 
        !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent 
        !       0/0 [2/0] voluntary/involuntary context switches 
[local] bitmyer psql 0STATEMENT:  select count(*) from dplee; 
[local] bitmyer psql 0LOG:  rewritten parse tree: 
[local] bitmyer psql 0DETAIL:  ( 
           {QUERY 
           :commandType 1 
           :querySource 0 
           :canSetTag true 
           :utilityStmt <> 
           :resultRelation 0 
           :hasAggs true 
           :hasWindowFuncs false 
           :hasSubLinks false 
           :hasDistinctOn false 
           :hasRecursive false 
           :hasModifyingCTE false 
           :hasForUpdate false 
           :cteList <> 
           :rtable ( 
              {RTE 
              :alias <> 
              :eref 
                 {ALIAS 
                 :aliasname dplee 
                 :colnames ("c1" "c2") 
                 } 
              :rtekind 0 
              :relid 16385 
              :relkind r 
              :inh true 
              :inFromCl true 
              :requiredPerms 2 
              :checkAsUser 0 
              :selectedCols (b) 
              :modifiedCols (b) 
              } 
           ) 
           :jointree 
              {FROMEXPR 
              :fromlist ( 
                 {RANGETBLREF 
                 :rtindex 1 
                 } 
              ) 
              :quals <> 
              } 
           :targetList ( 
              {TARGETENTRY 
              :expr 
                 {AGGREF 
                 :aggfnoid 2803 
                 :aggtype 20 
                 :aggcollid 0 
                 :inputcollid 0 
                 :args <> 
                 :aggorder <> 
                 :aggdistinct <> 
                 :aggstar true 
                 :agglevelsup 0 
                 :location 7 
                 } 
              :resno 1 
              :resname count 
              :ressortgroupref 0 
              :resorigtbl 0 
              :resorigcol 0 
              :resjunk false 
              } 
           ) 
           :returningList <> 
           :groupClause <> 
           :havingQual <> 
           :windowClause <> 
           :distinctClause <> 
           :sortClause <> 
           :limitOffset <> 
           :limitCount <> 
           :rowMarks <> 
           :setOperations <> 
           :constraintDeps <> 
           } 
        ) 

[local] bitmyer psql 0STATEMENT:  select count(*) from dplee; 
[local] bitmyer psql 0LOG:  PLANNER STATISTICS 
[local] bitmyer psql 0DETAIL:  ! system usage stats: 
        !       0.000226 elapsed 0.000000 user 0.000000 system sec 
        !       [0.000999 user 0.001999 sys total] 
        !       0/0 [0/256] filesystem blocks in/out 
        !       0/62 [0/1008] page faults/reclaims, 0 [0] swaps 
        !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent 
        !       0/0 [2/0] voluntary/involuntary context switches

You may also like...