华山资源网

PostgreSQL 慢查询SQL跟踪操作
数据库 2024/12/28 佚名
3 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的手段，这里不再深入说明。

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

华山资源网 Design By www.eoogi.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
华山资源网 Design By www.eoogi.com
www.eoogi.com 华山资源网
120,135影音资源
344,641技术资源
22,817软件资源
435,032站长资源
一句话新闻
苹果官宣WWDC 2024！预计会有大批AI功能 - 2024/12/28

3月27日消息，苹果宣布2024年全球开发者大会（WWDC）将于6月10日至6月14日举行，巧合的是，这次大会与端午假期重合。

苹果官方表示：

在线参加 Apple 每年规模最大的开发者盛会。亲眼见证 Apple 最新平台、技术和工具的发布。了解如何创建和改进你的 App 和游戏。与 Apple 设计师和工程师互动交流，与全球开发者社区建立联系。以上活动均免费在线举行。

探索各种新的工具、框架和功能，助力你打造出理想的 App 和游戏。通过视频讲座学习新技能，与 Apple 专家进行一对一会面，以推进你的项目，完善你的构思。

Swift Student Challenge 旨在支持和鼓舞下一代开发者、创作者和企业家。太平洋时间 3 月 28 日，我们将公布今年的获奖者名单。获奖者将有资格参加在 Apple Park 举办的特别活动。我们还会选出 50 名杰出获胜者，他们将受邀前往库比提诺，获得为期三天的非凡体验，包括参加 Apple Park 的特别活动。

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

三星在GTC上展示了专为下一代游戏GPU设计的GDDR7内存。

首次推出的GDDR7内存模块密度为16GB，每个模块容量为2GB。其速度预设为32 Gbps（PAM3），但也可以降至28 Gbps，以提高产量和初始阶段的整体性能和成本效益。

据三星表示，GDDR7内存的能效将提高20%，同时工作电压仅为1.1V，低于标准的1.2V。通过采用更新的封装材料和优化的电路设计，使得在高速运行时的发热量降低，GDDR7的热阻比GDDR6降低了70%。

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
华山资源网 Design By www.eoogi.com