乘风原创程序

  • PostgreSQL 慢查询SQL跟踪操作
  • 2021/1/26 11:12:39
  • postgresql 开启慢sql捕获在排查问题时是个很有效的手段。根据慢sql让我在工作中真正解决了实际问题,很有帮助。

    postgresql 日志支持的输出格式有 stderr(默认)、csvlog 、syslog

    一般的错误跟踪,只需在配置文件 【postgresql.conf】简单设置几个参数,当然还有错误级别等要设置。

    logging_collector = on
    log_destination = 'stderr'
    log_directory = 'log'
    log_filename = 'postgresql-%y-%m-%d_%h%m%s.log'
    select 
       name,
       setting,
       vartype,
       boot_val,
       reset_val 
    from pg_settings 
    where name in('logging_collector','log_destination','log_directory','log_filename');
    

    默认的跟踪日志记录在 pgdate/log 中,如 /usr/local/pgsql/data/log 。

    其他几个重要参数说明:

    log_rotation_age = 1440  #minute,多长时间创建新的文件记录日志。0 表示禁扩展。
    log_rotation_size = 10240  #kb,文件多大后创建新的文件记录日志。0 表示禁扩展。
    log_truncate_on_rotation = on #可重用同名日志文件

    当需要跟踪sql语句或者慢语句,得需要设置以下参数:

    log_statement = all  #需设置跟踪所有语句,否则只能跟踪出错信息
    log_min_duration_statement = 5000  #milliseconds,记录执行5秒及以上的语句
    

    log_statement:

    设置跟踪的语句类型,有4种类型:none(默认), ddl, mod, all。跟踪所有语句时可设置为 "all"。

    log_min_duration_statement:

    跟踪慢查询语句,单位为毫秒。如设置 5000,表示日志将记录执行5秒以上的sql语句。

    当 log_statement=all 和 log_min_duration_statement 同时设置时,将跟踪所有语句,忽略log_min_duration_statement 设置。所以需按情况设置其中一个或两个值。

    加载配置

    select pg_reload_conf();
    show log_min_duration_statement;
    

    针对某个用户或者某数据库进行设置

    alter database test set log_min_duration_statement=5000;

    捕获正在查询的慢sql

    select * from pg_stat_activity where state<>'idle' and now()-query_start > interval '5 s' order by query_start ; 

    补充:postgresql cpu占用100%性能分析及慢sql优化

    查看连接数变化

    cpu利用率到达100%,首先怀疑,是不是业务高峰活跃连接陡增,而数据库预留的资源不足造成的结果。我们需要查看下,问题发生时,活跃的连接数是否比平时多很多。

    对于rds for pg,数据库上的连接数变化,可以从控制台的监控信息中看到。而当前活跃的连接数>可以直接连接数据库,使用下列查询语句得到:

    select count( * ) from pg_stat_activity where state not like '%idle';

    追踪慢sql

    如果活跃连接数的变化处于正常范围,则很大概率可能是当时有性能很差的sql被大量执行导致。由于rds有慢sql日志,我们可以通过这个日志,定位到当时比较耗时的sql来进一步做分析。但通常问题发生时,整个系统都处于停滞状态,所有sql都慢下来,当时记录的>慢sql可能非常多,并不容易排查罪魁祸首。这里我们介绍几种在问题发生时,即介入追查慢sql的方法。

    1、第一种方法是使用pg_stat_statements插件定位慢sql,步骤如下。

    1.1 如果没有创建这个插件,需要手动创建。我们要利用插件和数据库系统里面的计数信息(如sql执行时间累积等),而这些信息是不断累积的,包含了历史信息。为了更方便的排查当前的cpu满问题,我们要先重置计数器。

    create extension pg_stat_statements;
    select pg_stat_reset();
    select pg_stat_statements_reset();

    1.2 等待一段时间(例如1分钟),使计数器积累足够的信息。

    1.3 查询最耗时的sql(一般就是导致问题的直接原因)。

    select * from pg_stat_statements order by total_time desc limit 5;

    1.4 查询读取buffer次数最多的sql,这些sql可能由于所查询的数据没有索引,而导致了过多的buffer读,也同时大量消耗了cpu。

    select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;

    2、第二种方法是,直接通过pg_stat_activity视图,利用下面的查询,查看当前长时间执行,一直不结束的sql。这些sql对应造成cpu满,也有直接嫌疑。

    select datname, usename, client_addr, application_name, state, backend_start, xact_start, xact_stay, query_start, query_stay, replace(query, chr(10), ' ') as query from (select pgsa.datname as datname, pgsa.usename as usename, pgsa.client_addr client_addr, pgsa.application_name as application_name, pgsa.state as state, pgsa.backend_start as backend_start, pgsa.xact_start as xact_start, extract(epoch from (now() - pgsa.xact_start)) as xact_stay, pgsa.query_start as query_start, extract(epoch from (now() - pgsa.query_start)) as query_stay , pgsa.query as query from pg_stat_activity as pgsa where pgsa.state != 'idle' and pgsa.state != 'idle in transaction' and pgsa.state != 'idle in transaction (aborted)') idleconnections order by query_stay desc limit 5;
    datname |  usename  | client_addr |   application_name   | state |     backend_start     |     xact_start      |  xact_stay  |     query_start     | query_stay  |                                                                                         
                                 query                                                                                                                      
    ---------+-------------+---------------+--------------------------+--------+-------------------------------+-------------------------------+---------------+-------------------------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     denali | denaliadmin | 10.222.16.45 | pgadmin iii - query tool | active | 2018-02-26 22:36:05.603781+00 | 2018-02-26 22:36:13.054396+00 | 187614.245395 | 2018-02-26 22:36:13.054396+00 | 187614.245395 | select * from gen3_search_eu_17q2_20171115_epl.place_name \r where place_id not in (select place_id from gen3_search_eu_17q1_20170308_epl.place_name ) \r and name not in (select name from gen3_search_eu_17q1_20170308_epl.place_name)\r and lang = 'eng'\r limit 50
     denali | denaliadmin | 10.222.16.45 | pgadmin iii - query tool | active | 2018-02-26 23:46:24.442846+00 | 2018-02-26 23:46:34.920261+00 | 183392.37953 | 2018-02-26 23:46:34.920261+00 | 183392.37953 | select * from gen3_search_eu_17q2_20171115_epl.place_name \r where place_id not in (select place_id from gen3_search_eu_17q1_20170308_epl.place_name ) \r and name not in (select name from gen3_search_eu_17q1_20170308_epl.place_name)\r and lang = 'eng'\r limit 50\r 
     denali | denaliadmin | 10.222.16.45 | pgadmin iii - query tool | active | 2018-02-27 01:19:53.83589+00 | 2018-02-27 01:20:01.519778+00 | 177785.780013 | 2018-02-27 01:20:01.519778+00 | 177785.780013 | select * from gen3_search_eu_17q2_20171115_epl.place_name \r where place_id not in (select place_id from gen3_search_eu_17q1_20170308_epl.place_name ) \r and name not in (select name from gen3_search_eu_17q1_20170308_epl.place_name)\r limit 50
     denali | denaliadmin | 10.222.16.45 | pgadmin iii - query tool | active | 2018-02-27 01:46:05.207888+00 | 2018-02-27 01:47:52.039779+00 | 176115.260012 | 2018-02-27 01:47:52.039779+00 | 176115.260012 | select a.place_id, a.metadata_dictionary_id,a.value, a.lang, b.place_id, b.metadata_dictionary_id, b.value, b.lang\r from gen3_search_eu_17q1_20170308_epl.place_address a \r inner join gen3_search_eu_17q2_20171115_epl.place_address b\r on a.place_id = b.place_id \r where a.metadata_dictionary_id = b.metadata_dictionary_id and a.lang = b.lang and a.value!=b.value and b.place_id not in (select poi_id from gen3_search_eu_17q2_20171115_epl.place_embeded_ids)\r limit 100\r 
     denali | denaliadmin | 10.224.14.148 | pgadmin iii - query tool | active | 2018-02-27 05:05:39.903885+00 | 2018-02-27 05:05:48.827779+00 | 164238.472012 | 2018-02-27 05:05:48.827779+00 | 164238.472012 | select a.place_id, a.metadata_dictionary_id,a.value, a.lang, b.place_id, b.metadata_dictionary_id, b.value, b.lang\r from gen3_search_eu_17q1_20170308_epl.place_address a \r inner join gen3_search_eu_17q2_20171115_epl.place_address b\r on a.place_id = b.place_id \r where a.metadata_dictionary_id = b.metadata_dictionary_id and a.lang = b.lang and a.value!=b.value and b.place_id not in (select poi_id from gen3_search_eu_17q2_20171115_epl.place_embeded_ids)\r limit 100\r 
    (5 rows)

    3、第3种方法,是从数据表上表扫描(table scan)的信息开始查起,查找缺失索引的表。数据表如果缺失索引,大部分热数据又都在内存时(例如内存8g,热数据6g),此时数据库只能使用表扫描,并需要处理已在内存中的大量的无关记录,而耗费大量cpu。特别是对于表记录数超100的表,一次表扫描占用大量cpu(基本把一个cpu占满),多个连接并发(例如上百连接),把所有cpu占满。

    3.1 通过下面的查询,查出使用表扫描最多的表:

    select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;

    3.2 查询当前正在运行的访问到上述表的慢查询:

    select * from pg_stat_activity where query ilike '%<table name>%' and query_start - now() > interval '10 seconds';

    3.3 也可以通过pg_stat_statements插件定位涉及到这些表的查询:

    select * from pg_stat_statements where query ilike '%<table>%'order by shared_blks_hit+shared_blks_read desc limit 3;

    处理慢sql

    对于上面的方法查出来的慢sql,首先需要做的可能是cancel或kill掉他们,使业务先恢复:

    select pg_cancel_backend(pid) from pg_stat_activity where query like '%<query text>%' and pid != pg_backend_pid();
    select pg_terminate_backend(pid) from pg_stat_activity where query like '%<query text>%' and pid != pg_backend_pid();

    如果这些sql确实是业务上必需的,则需要对他们做优化。这方面有“三板斧”:

    1、对查询涉及的表,执行analyze <table>或vacuum anzlyze <table>,更新表的统计信息,使查询计划更准确。注意,为避免对业务影响,最好在业务低峰执行。

    2、执行explain (query text)或explain (buffers true, analyze true, verbose true) (query text)命令,查看sql的执行计划(注意,前者不会实际执行sql,后者会实际执行而且能得到详细的执行信息),对其中的table scan涉及的表,建立索引。

    3、重新编写sql,去除掉不必要的子查询、改写union all、使用join clause固定连接顺序等到,都是进一步深度优化sql的手段,这里不再深入说明。

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持本教程网。如有错误或未考虑完全的地方,望不吝赐教。