PostgreSQL 慢查询SQL跟踪操作

站长资源 2024/11/23 佚名

2 0 1

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的手段，这里不再深入说明。

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

PostgreSQL,慢查询,SQL跟踪

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

评论“PostgreSQL 慢查询SQL跟踪操作”

暂无评论...

www.wwsws.com 伏龙阁资源网

39,976影音资源

44,792技术资源

21,817软件资源

651,128站长资源

PostgreSQL 慢查询SQL跟踪操作

其他几个重要参数说明：

查看连接数变化

追踪慢SQL

处理慢SQL

postgresql 计算距离的实例(单位直接生成米)

postgresql 除法保留小数位的实例

评论“PostgreSQL 慢查询SQL跟踪操作”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

PostgreSQL 慢查询SQL跟踪操作

其他几个重要参数说明：

查看连接数变化

追踪慢SQL

处理慢SQL

postgresql 计算距离的实例(单位直接生成米)

postgresql 除法保留小数位的实例

评论“PostgreSQL 慢查询SQL跟踪操作”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存