ืคืืจืืข ืืืคืจืืื ืื pgsentinel ืคืึทืจืืขื ืืขืจืื ื ืคืื ืื ืืขืืืข ื ืึธืืขื (), ืืืึธืก ืืืกืืฃ ืื pg_active_session_history ืืืื ืื ื ืฆื PostgreSQL - ืื ืืขืฉืืืืข ืคืื โโืึทืงืืืื ืกืขืฉืึทื ื (ืขื ืืขื ืฆื Oracle's v$active_session_history).
ืืกืขื ืฉืึทืื, ืืึธืก ืืขื ืขื ืคืฉืื ืจืืข-ืืืจื-ืกืขืงืื ืืข ืกื ืึทืคึผืฉืึทืฅ ืคืื pg_stat_activity, ืึธืืขืจ ืขืก ืืขื ืขื ืืืืืืืง ืคืื ืงืื:
- ืึทืืข ืึทืงืืืืืึทืืืืืื ืืื ืคึฟืึธืจืืึทืฆืืข ืืื ืกืืึธืจื ืืืืื ืืื ืืึทืจืึทื, ืืื ืื ืงืึทื ืกืืื ืกืืืข ืคืื โโืืึผืจืื ืืื ืจืขืืืึทืืืืืึทื ืืืจื ืื ื ืืืขืจ ืคืื ืืขืฆืืข ืกืืึธืจื ืจืขืงืึธืจืืก.
- ืื queryid ืคืขืื ืืื ืฆืืืขืืขืื - ืืขืจ ืืขืืืืงืขืจ queryid ืคึฟืื ืื pg_stat_statements ืคืึทืจืืขื ืืขืจืื ื (ืคืึทืจ-ืื ืกืืึทืืืจืื ื ืคืืจืืื ืื).
- ืื top_level_query ืคืขืื ืืื ืฆืืืขืืขืื - ืืขืจ ืืขืงืกื ืคืื ืื ืึธื ืคึฟืจืขื ืคึฟืื ืืืึธืก ืื ืงืจืึทื ื ืึธื ืคึฟืจืขื ืืื ืืขืจืืคื (ืืืื ื ืืฆื pl/pgsql)
ืืึทื ืฅ ืจืฉืืื ืคืื pg_active_session_history ืคืขืืืขืจ:
ืืืึทื | ืืืคึผ ------------------+------------------------- ืึทืฉ_ืืืื | ืฆืืื ืฉืืขืืคึผื ืืื ืฆืืื ืืึธื ืข ืืึทืืื | oid ืืึทืืขื ืึทืืข | ืืขืงืกื ืคึผืื | ืื ืืึทืืืฉืขืจ ื ืืฆื | oid usename | ืืขืงืกื ืึทืคึผืืึทืงืืืฉืึทื_ื ืึธืืขื | ืืขืงืกื client_addr | ืืขืงืกื ืงืืืขื ื_ืืึธืกืื ืึทืืข | ืืขืงืกื ืงืืืขื ื_ืคึผืึธืจื | ืื ืืึทืืืฉืขืจ ืืึทืงืขื ื_ืกืืึทืจื | ืฆืืื ืกืืึทืืคึผ ืืื ืฆืืื ืืึธื ืข xact_start | ืฆืืื ืกืืึทืืคึผ ืืื ืฆืืื ืืึธื ืข query_start | ืฆืืื ืกืืึทืืคึผ ืืื ืฆืืื ืืึธื ืข ืฉืืึทื_ืืฉืึทื ืืข | ืฆืืื ืกืืึทืืคึผ ืืื ืฆืืื ืืึธื ืข wait_event_type | ืืขืงืกื ืืืึทืื_ืขืืืขื ื | ืืขืงืกื ืฉืืึทื | ืืขืงืกื ืืึทืงืขื ื_ืงืกืื | xid backend_xmin | xid top_level_query | ืืขืงืกื ืึธื ืคึฟืจืขื | ืืขืงืกื queryid | ืืืืื ื ืืึทืงืขื ื_ืืืคึผ | ืืขืงืกื
ืขืก ืืื ื ืึธื ืงืืื ืคืึทืจืืืง ืคึผืขืงื ืคึฟืึทืจ ืื ืกืืึทืืืจืื ื. ืขืก ืืื ืกืึทืืืืฉืขืกืืื ืฆื ืึธืคึผืืึธืืืจื ืื ืืงืืจืื ืืื ืึทืกืขืืืึทื ืื ืืืืืืึธืืขืง ืืื. ืืืจ ืขืจืฉืืขืจ ืืึทืจืคึฟื ืฆื ืื ืกืืึทืืืจื ืื "ืืขืืืขื" ืคึผืขืงื ืคึฟืึทืจ ืืืื ืกืขืจืืืขืจ ืืื ืฉืืขืื ืืขื ืืจื ืฆื pg_config ืืื ืื PATH ืืืึทืืขืืืืืง. ืืืจ ืงืืืึทืื:
cd pgsentinel/src
ืืึทืื
ืืึทืื ืื ืกืืึทืืืจื
ืืืื ืคึผืึทืจืึทืืขืืขืจืก ืฆื postgres.conf:
shared_preload_libraries = 'pg_stat_statements, pgsentinel'
track_activity_query_size = 2048
pg_stat_statements.track = ืึทืืข# ื ืืืขืจ ืคืื ืืขืฆืืข ืจืขืงืึธืจืืก ืืขืืืืื ืืื ืืึผืจืื
pgsentinel_ash.max_entries = 10000
ืจืืกืืึทืจื PostgreSQL ืืื ืฉืึทืคึฟื ืึท ืคืึทืจืืขื ืืขืจืื ื:
ืฉืึทืคึฟื ืืขืฉืคึผืจืืื ืคึผืืกื ืืื ืขื;
ืื ืึทืงืืืืืึทืืืืืื ืืื ืคึฟืึธืจืืึทืฆืืข ืึทืืึทืื ืืื ืื ืฆื ืขื ืืคึฟืขืจื ืคึฟืจืืื ืึทืืึท ืืื:
- ืืืืฃ ืืืึธืก ืืืืจืื ืกืขืฉืึทื ื ืืึธื ืืืจ ืคืึทืจืืจืขื ืืขื ืื ืืขืจืกื ืฆืืื?
- ืืืึธืก ืกืขืฉืึทื ื ืืขื ืขื ืื ืืขืจืกื ืึทืงืืืื?
- ืืืึธืก ืจืืงืืืขืก ืืขื ืขื ืื ืืขืจืกื ืึทืงืืืื?
ืืืจ ืงืขื ื, ืคืื ืงืืจืก, ืืึทืงืืืขื ืขื ืืคึฟืขืจืก ืฆื ืื ืคึฟืจืืื ืืื SQL ืงืืืืจืื, ืึธืืขืจ ืขืก ืืื ืืขืจ ืืึทืงืืืขื ืฆื ืืขื ืืขื ืืืืืฉืืืึทืื ืืืืฃ ืึท ืืจืึทืคืืง ืืืจื ืืืืืืืืื ื ืื ืื ืืขืจืืืึทืื ืคืื ืฆืืื ืืื ืื ืืืื. ืืืจ ืงืขื ืขื ืืึธื ืืึธืก ืืื ืึท ืคืจืื ืคึผืจืึธืืจืึทื (ืืืจ ืงืขื ืขื ืืจืืคืงืืคืืข ืื ืืขืืืืื ืืื ืึทืจืืขืก ืืื ืื ืึธืคึผืืืืืื ื ).
ืืืขื ืกืืึทืจืืื ื, PASH-Viewer (ืกืืึทืจืืื ื ืคืื ืืืขืจืกืืข 0.4.0) ืืฉืขืงืก ืคึฟืึทืจ ืื ืืืึทืืืึทื ืคืื ืื pg_active_session_history ืืืื ืื ื ืืื ืืืื ืขืก ืืืืืกืฅ, ืขืก ืืึธืืื ืื ืืื ืฆืข ืึทืงืืืืืึทืืืืืื ืืขืฉืืืืข ืคึฟืื ืขืก ืืื ืืืื ืฆื ืืืืขื ืขื ื ืืึท ืื ืงืึทืืื ื ืืึทืื, ืึทืคึผืืืืืื ื ืื ืืจืึทืคืืง ืืขืืขืจ 15 ืกืขืงืื ืืขืก.
ืืงืืจ: www.habr.com