PostgreSQL中高耗SQL的获取可以使用pg_stat_statements模块来获取，pg_stat_statements模块提供执行SQL语句的执行统计信息。
该模块必须在postgresql.conf的shared_preload_libraries中增加pg_stat_statements来载入，因为它需要额外的共享内存。增加或移除该模块需要将数据库重启。
当pg_stat_statements被载入时，它会跟踪该服务器的所有数据库的统计信息。该模块提供了视图pg_stat_statements以及函数pg_stat_statements_reset用于访问和操纵这些统计信息。这些视图和函数不是全局可用的，但是可以在指定数据库创建该扩展。
创建extension模块
postgres=# CREATE EXTENSION pg_stat_statements; CREATE EXTENSION |
修改数据库PG_HOME下的postgresql.conf文件
shared_preload_libraries= pg_stat_statements
pg_stat_statements.max= 10000 #pg_stat_statements中记录的最大的SQL条目数，默认为5000
pg_stat_statements.track= all#记录pg_stat_statements中的
pg_stat_satements.saveon #用来控制数据库在关闭的时候，是否将SQL信息保存到文件中。默认打开
pg_stat_satements.track_utilityon #追踪SQL命令：DQLDDL 以及DQL,DDL以外的其他SQL命令(off只记录DQLDDL)
如果没有配置postgresql.conf文件中的shared_preload_libraries,那么将会提示如下报错：
ERROR:pg_stat_statements must be loaded via shared_preload_libraries
使用pg_ctl重新启动数据库，使扩展生效。
pg_ctl start -D $PGDATA -l /tmp/pg_rotate_logfile() |
进入数据库，查看pg_stat_statements视图，有数据则安装成功。
psql -U postgres -d pgtestdb select * from pg_stat_statements; |
该视图的结构信息如下：
由于安全性原因，只有超级用户和pg_read_all_stats角色的成员被允许看到其他用户执行的查询的SQL文本或者queryid。
log_min_duration_statement这个参数可以控制阈值的时间，如果查询花费的时间长于此阈值时间，则会记录该SQL。默认为1s。可以使用
ALTER SYSTEM SETlog_min_duration_statement = 1000;
更改阈值记录，单位为ms。
我们可以在数据库中看到平均运行时间最高的查询，如下所示：
SELECT total_time, min_time,(total_time/calls) as avg_time, max_time, mean_time, calls, rows,query FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10; |
返回结果如下：
其中各项的涵义：
total_time：返回查询的总运行时间（以毫秒为单位）。
min_time、avg_time和max_time：返回查询的最小、平均和最大运行时间。
mean_time：使用total_time/调用返回查询的平均运行时间（以毫秒为单位）。
Calls (调用)：返回查询运行的总数。
Rows(行数)：返回由于查询而返回或受影响的行总数。
Query(查询)：返回正在运行的查询。默认情况下，最多显示1024个查询字节。可以使用track_activity_query_size参数更改此值。
pg_stat_statements所获得的统计数据一直累积到重置。
可以使用以下脚本进行按天备份。
备份完成后可以通过具有超级用户权限的用户连接到数据库以重置统计数据来运行重置：
SELECTpg_stat_statements_reset();
#!/bin/bash # this script is aimed to delete the expired data; # and use the vacummdb command to clean up databases. # Copyright(c) 2016--2016 yuxiangli All Copyright reserved. echo "-----------------------------------------------------" echo `date +%Y%m%d%H%M%S` dates= `date +%Y%m%d` psql -U hbdx_xxx -h 133.0.xxx.xx -d testdb -p xxx << EOF create table public.pg_stat_statements_$dates as select * from public.pg_stat_statements; SELECT pg_stat_statements_reset(); q EOF echo "-----------------------------------------------------" echo `date +%Y%m%d%H%M%S` echo "-----------------------------------------------------" |
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/130094.html
摘要：作者谭峰张文升出版日期年月页数页定价元本书特色中国开源软件推进联盟分会特聘专家撰写，国内多位开源数据库专家鼎力推荐。张文升中国开源软件推进联盟分会核心成员之一。 很高兴《PostgreSQL实战》一书终于出版，本书大体上系统总结了笔者 PostgreSQL DBA 职业生涯的经验总结，本书的另一位作者张文升拥有丰富的PostgreSQL运维经验，目前就职于探探科技任首席PostgreS...
摘要：近日，正式发布了版本，该版本进行了大量的修复和功能改进。事实上，开发者获得的所有标准会使它更加强大一个最好的例子是来自近年来的索引，它给提供了极大的性能提升。在最新发布的版本中，在中的输出也更具可读性。 【编者按】作为一款开源的对象—关系数据库，Postgres 一直得到许多开发者喜爱。近日，Postgres 正式发布了9.5版本，该版本进行了大量的修复和功能改进。而本文将分享10个 ...
摘要：旨在记录自己的学习过程，方便日后遇到问题是及时查阅复习，另一方面也希望能帮助像笔者一样从来没使用过的人快速熟悉。 这篇文章主要记录的是本人学习使用IntelliJ IDEA的笔记，可能不是特别的详细。旨在记录自己的学习过程，方便日后遇到问题是及时查阅复习，另一方面也希望能帮助像笔者一样从来没使用过IDEA的人快速熟悉IDEA。文章错误之处还请各位大佬批评指正。（文末有本人的微信公众号，...
摘要：上有主节点和从节点两部分，两者主要的功能是生成查询计划并派发，以及协调并行计算，同时在上保存着，这个全局目录存着一组数据库系统本身所具有的元数据的系统表。 前言：近年来，互联网的快速发展积累了海量大数据，而在这些大数据的处理上，不同技术栈所具备的性能也有所不同，如何快速有效地处理这些庞大的数据仓，成为很多运营者为之苦恼的问题！随着Greenplum的异军突起，以往大数据仓库所面临的很多...
阅读 1346·2023-01-11 13:20
阅读 1684·2023-01-11 13:20
阅读 1132·2023-01-11 13:20
阅读 1858·2023-01-11 13:20
阅读 4100·2023-01-11 13:20
阅读 2704·2023-01-11 13:20
阅读 1385·2023-01-11 13:20
阅读 3597·2023-01-11 13:20
