极乐门资源网

PostgreSQL 慢查询SQL跟踪操作
数据库 2024/12/22 佚名
2 0
极乐门资源网 Design By www.ioogu.com

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跟踪
极乐门资源网 Design By www.ioogu.com
极乐门资源网 免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
极乐门资源网 Design By www.ioogu.com

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

暂无PostgreSQL 慢查询SQL跟踪操作的评论...
www.ioogu.com 极乐门资源网
139,976影音资源
144,792福利资源
21,817软件资源
631,128技术资源

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。

艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。

《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒 经典版》的玩家都可以获得奖励。

更新日志

友情链接

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