PostgreSQL 之慢 SQL 语句




  • 一 导读
    优化在硬件和非系统故障的情况下,对于提升数据库本身的性能非常重要。每一种类型的数据库都有自己不同的方式去跟踪优化数据库,这些方式中不仅仅包含了数据库系统本身参数层面的优化,而且也包括对 SQL 语句的优化。其中,对于 SQL 语句的优化是 DBA 经常需要接触的工作。因此需要经常关住慢 SQL 语句,以对其进行追踪优化。

    二 数据库参数
    PostgreSQL 中的慢 SQL 追踪是通过以记录日志的方式进行分析,追踪的,因此,需要优化 SQL 需要启动日志收集功能。以 RPM 方式安装的数据库日志收集功能默认是打开的,以源码编译的方式安装的数据库日志收集功能是关闭的。

    启动日志收集功能

     
    postgres=# show logging_collector ;
     logging_collector
     -------------------
     off
     (1 row)
     
    postgres=# alter system set logging_collector = on;
     ALTER SYSTEM

    重新启动数据库

    [postgres@developer ~]$ pg_ctl restart -D $PGDATA -l /tmp/logfile
    waiting for server to shut down.... done
    server stopped
    waiting for server to start.... done
    server started

    再次查看参数

    postgres=# show logging_collector ;
     logging_collector
     -------------------
     on
     (1 row)

    2.1 log_min_duration_statement
    该参数设置为对于单个 SQL 最小的执行时间,执行时间大于这个值的语句都将会被记录。该参数默认会记录所有的 SQL 语句。如果没有指定其单位,该单位默认以 ms 为单位。
    如设置记录的慢 SQL 语句为 2s,则日志中只记录执行大于 2s 的 SQL 语句。
    如下示例:

    postgres=# select count(*),p_date from tab_random_date group by p_date;
     count | p_date
     ---------+------------
     896 | 1993-01-12
     384 | 2014-12-21
     1152 | 1994-07-01
     256 | 2015-08-07
     512 | 2011-10-31
     512 | 2004-10-16
     512 | 2000-06-25
     640 | 2001-07-30
     384 | 2017-06-13
     256 | 2000-10-19
     640 | 2004-04-01
     768 | 2006-01-13
     768 | 2003-10-05
     512 | 2003-06-22
     896 | 2000-06-10
     384 | 1997-06-23
     512 | 2004-05-14
     768 | 2012-03-10
     384 | 2015-05-06
     256 | 2018-10-14
     128 | 2015-06-15
     128 | 2017-02-18
     1024 | 1993-05-05
     256 | 2018-07-20
     128 | 1998-05-08
     640 | 1996-08-02
     512 | 2014-11-19
     256 | 2019-05-05
     1536 | 1991-05-16
     128 | 2007-08-05
     640 | 1994-05-22
     256 | 2013-01-06
     1280 | 1996-05-12
     512 | 1998-09-10
     640 | 1999-02-08
     384 | 2008-01-31
     256 | 2016-08-28
     512 | 2002-10-03
     1792 | 1994-12-11
     Time: 5006.112 ms (00:05.006)

    上面 SQL 语句执行时间为 5s,那么该 SQL 语句将会被记录到日志中。查看日志内容

    [21777] LOG: duration: 5005.937 ms statement: select count(*),p_date from tab_random_date group by p_date;

    2.2 log_statement
    该参数用来设置记录语句的类型,允许的值为 none,ddl,mod 和 all。默认该参数值为 none,如果修改为 all,会导致数据库日志格式发生变化,如果没有特别配置可查询视图,可能会导致无法解析慢日志。通常,如果启用 log_min_duration_statement 参数,那么 log_min_duration_statement 参数需要结合 log_statement 参数一起使用。

    mod:记录所有的 DDL 和数据库修改的语句,如 INSERT、UPDATE、DELETE、TRUNCATE、COPY FROM 等。
    ddl:仅仅记录 DDL 语句。如 CREATE 、ALTER 、DROP。

    如下示例:

    LOG: duration: 7204.812 ms statement: insert into tab_random_date select * from tab_random_date;
     LOG: duration: 15749.567 ms statement: insert into tab_random_date select * from tab_random_date;
     LOG: duration: 2044.327 ms statement: select count(*) from tab_random_date;
     

    Speak Your Mind

    *