主页 / dba / 问题 / 56872
Accepted
David S
Asked: 2014-01-16 21:46:15 +0800 CST

Postgresql 日志记录问题

  • 772

我正在寻求一些帮助来理解我的 PostgreSQL 9.3 日志文件。

（注意：也许这应该是多个问题，它们都涉及相同的一般主题——理解我的日志文件）。在我的 postgresql.conf 文件中，我有以下日志相关设置：

log_min_duration_statement = 0
log_line_prefix = '%m session_id: %c; trans_id: %x '
log_statement = 'all'

如果我的日志中有以下内容：

2014-01-15 21:39:22.217 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: BEGIN
2014-01-15 21:39:22.217 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.132 ms
2014-01-15 21:39:22.218 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT t.oid, typarray
    FROM pg_type t JOIN pg_namespace ns
        ON typnamespace = ns.oid
    WHERE typname = 'hstore';

2014-01-15 21:39:22.219 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 1.651 ms
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: ROLLBACK
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.043 ms
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: BEGIN
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.028 ms
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: select version()
2014-01-15 21:39:22.221 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.204 ms
2014-01-15 21:39:22.222 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: select current_schema()
2014-01-15 21:39:22.222 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.136 ms
2014-01-15 21:39:22.222 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: show transaction isolation level
2014-01-15 21:39:22.222 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.065 ms
2014-01-15 21:39:22.223 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-01-15 21:39:22.223 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.281 ms
2014-01-15 21:39:22.223 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-01-15 21:39:22.223 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.063 ms
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT 'x' AS some_label
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.053 ms
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: ROLLBACK
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.032 ms
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: BEGIN
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.023 ms
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: show standard_conforming_strings
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.035 ms
2014-01-15 21:39:22.225 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT api_log_entry.id AS api_log_entry_id 
    FROM api_log_entry
2014-01-15 21:39:22.227 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 1.319 ms

问题：

在此截取的日志中，每个日志条目（所有具有持续时间的内容）是否代表来自客户端应用程序的调用（并且是往返）？

鉴于我的 log_line_prefix，为什么 trans_id 显示为零？这让我不敢说，但我想我会为每个记录的“BEGIN”看到一个新的 trans_id。

postgresql postgresql-9.3

1 个回答

  1. Best Answer

    那里的一切都可能是客户端往返，但您会发现多语句显示每个组件语句的单独持续时间。时间是在服务器上执行它所花费的时间，而不是包括网络访问的时间。

    出现没有事务 ID 的条目是因为 PostgreSQL 使用“虚拟事务 ID”来降低事务 ID 的消耗率，只有在有写入或锁定操作时才分配真实的 txid。

    • 2

相关问题