Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

参数——日志、慢查询排查

分类

以 Pgsql 10 为例，存在三种日志:

  • 重做(WAL)日志

    • 一般不可读
    • 对应目录为 $PGDATA/pg_xlog

  • 事务提交日志

    • 记录事务的元数据
    • 对应目录为 $PGDATA/pg_clog

  • 运行日志

    • 重点掌握
    • 对应目录为 $PGDATA/pg_log

整体策略

  • logging_collector

    • 是否将日志重定向到文件
      • 一般要设置为on，才能生成日志文件

  • log_truncate_on_rotation

    • 存在同名日志时，是否覆盖
      • 一般设置为on，直接覆盖
      • 如果设置为off，则追加

  • log_rotation_age

    • 单个日志的记录时长限制
      • 默认1d(1天)

  • log_rotation_size

    • 单个日志的大小限制
      • 默认为0，表示无限制
      • 可设置为10M，在时长限制内，超过此数字，会生成新的日志文件

  • log_line_prefix

    • 定义日志内容的输出项
      • 可选为%m %p %u %d %r
      • %m 表示 毫秒时间戳
        • 另有%t表示无毫秒，%n表示unix毫秒
      • %p 表示 进程ID
      • %u 表示 用户名
      • %d 表示 数据库名
      • %r 表示 远程主机和端口
        • 另有%h表示只显示远程主机

  • log_destination

    • 定义日志文件的格式
      • 可选为 csvlog
        • 表示输出csv和log两种文件格式

  • log_timezone

    • 定义日志的时区
      • 可选为PRC(中华人民共和国)或者Asia/Shanghai，和服务器一致

记录内容

  • log_statement

    • 设置日志记录的内容级别，可选如下:
      • none 默认，不记录
      • ddl 记录所有数据定义命令(CREATE，ALTER，DROP)
      • mod ddl基础上，再加上数据修改语句(INSERT，UPDATE)
      • all 记录所有执行的语句，极其影响性能
    • 一般要设置为ddl，只记录定义命令，但不记录修改语句
      • 需要依赖下面3秒的执行时长限制来记录所有超时语句
    • 注意: 就算设置为none，3秒的执行时长限制记录也会生效

  • log_duration

    • 是否记录每条SQL语句的执行时长
      • 一般要设置为off，不用单独记录

  • log_min_duration_statement

    • 单个语句的执行时长限制
      • 可设置为3s，执行时长超过此数字，会生成一条记录
    • 这个比log_duration更好用，因为语句和耗时都记录在同一行
      • 在语句前，会生成duration: xxxx ms

  • log_lock_waits

    • 是否记录等待时间超过deadlock_timeout而被锁的日志
      • 一般要设置为on，需要记录，用来监控死锁情况
      • 这里deadlock_timeout可设置为1s

  • log_checkpoints

    • 是否记录每日的checkpoint情况
      • 一般要设置为on，需要记录

管理规则

  • log_directory 日志存放目录

  • log_filename 日志命名规则

    • postgresql-%I.log
      • 最多保存12小时的日志,每小时一个文件
    • postgresql-%H.log
      • 最多保存24小时的日志,每小时一个文件
    • postgresql-%w.log
      • 最多保存一周的日志,每天一个文件
    • postgresql-%d.log
      • 最多保存一个月的日志,每天一个文件
    • postgresql-%j.log
      • 最多保存一年的日志,每天一个文件

慢查询排查

  • 编辑配置文件postgresql.conf

    • 找到如下一行 
      #log_min_duration_statement = -1
    • 把-1改成1000，表示1000ms以上的都记录

  • 重启服务器，重载配置

    /path/to/pg_ctl reload -D /path/to/pgdata

  • 查看：切超级用户，打开日志文件

    sudo su - postgres
less /path/to/pg_log/postgresql.log

    Shift + G跳到文件末尾，查询关键字duration，找到慢语句

  • 慢语句分析：连接数据库的命令行

    • 命令行中执行\timing打开计时
    • EXPLAIN(xxx)语句分析
      • 看有无Seq Scan顺序扫描拖慢语句
      • 并不会真正执行，更新语句也可以放进去
      • 但不要跟EXPLAIN ANALYZE混淆，这个是真的会执行

  • 也可使用扩展 pg_stat_statements，参照“语句统计视图”一节

    • 导入到numbers表格中，右键筛选，过滤掉调用次数少的(或者直接在数据库语句中筛选)
    • 按照调用次数的多少，找到xxx语句，用EXPLAIN(xxx)分析