首页 > 数据库 >PostgreSQL如何查询IO消耗最高的SQL及优化,pg_stat_statements插件安装及使用

PostgreSQL如何查询IO消耗最高的SQL及优化,pg_stat_statements插件安装及使用

时间:2023-05-11 15:37:12浏览次数:69  
标签:插件 statements blks stat 索引 pg SQL PostgreSQL

PostgreSQL如何查询IO消耗最高的SQL及优化,pg_stat_statements

摘要:一、安装pg_stat_statements插件此插件用于统计数据库的资源消耗,分析SQL语句安装流程:

一、安装pg_stat_statements插件

此插件用于统计数据库的资源消耗,分析SQL语句

安装流程:

cd */postgresql-11.1/contrib/pg_stat_statements
make
make install

二、加载pg_stat_statements模块

–编辑postgresql.conf文件

vi $PGDATA/postgresql.conf

–修改配置

shared_preload_libraries='pg_stat_statements'
track_io_timing = on用于跟踪IO消耗的时间
track_activity_query_size = 1024设置单条SQL的最长长度,超过被截断显示(可选)

三、配置pg_stat_statements采样参数

–在postgresql.conf文件添加

pg_stat_statements.max = 10000 # 在pg_stat_statements中最多保留多少条统计信息
pg_stat_statements.track = all # all - (所有SQL包括函数内嵌套的SQL), top - 直接执行的SQL(函数内的sql不被跟踪), none - (不跟踪)
pg_stat_statements.track_utility = off # 是否跟踪非DML语句 (例如DDL,DCL), on表示跟踪, off表示不跟踪
pg_stat_statements.save = on # 重启后是否保留统计信息

 

# data/postgresql.conf中,进行配置:

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 1000

pg_stat_statements.track = all

shared_preload_libraries = 'pg_stat_statements',表示要在启动时导入pg_stat_statements 动态库。
pg_stat_statements.max = 1000,表示监控的语句最多为1000句。
pg_stat_statements.track = top,表示不监控嵌套的sql语句。
pg_stat_statements.track_utility = true,表示对 INSERT/UPDATE/DELETE/SELECT 之外的sql动作也作监控。
pg_stat_statements.save = true,表示当postgresql停止时,把信息存入磁盘文件以备下次启动时再使用。

 

修改完postgresql.conf文件后,重启数据库restart

pg_ctl —D $PGDATA restart

四、创建pg_stat_statements extension

postgres=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=#

五、分析SQL

可以通过查询pg_stat_statements视图,获取统计信息

postgres=# select * from pg_stat_statements;
userid | dbid | queryid | query | calls | total_time | min_time | max_time | mean_time | stddev_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time
--------+------+---------+-------+-------+------------+----------+----------+-----------+-------------+------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------
(0 rows)

查询哪些sql语句执行效率慢:

-- 查询哪些sql语句执行效率慢:

SELECT query, calls, total_time, (total_time/calls) as average ,rows,
100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY average DESC LIMIT 10;

 

重置统计信息

-- 统计结果一直都在,重启也不会清零,那么统计结果如何清零重新统计呢?
-- 执行下面SQL即可:
select pg_stat_statements_reset() ;

普通用户需执行授权(permission denied for function pg_stat_statements_reset)

permission denied for function pg_stat_statements_reset

-- 给数据库用户授权
GRANT EXECUTE ON FUNCTION pg_stat_statements_reset() TO pmusername;

 

 

2.如何获取不同情况的SQL

(1)最耗IO的SQL

单次消耗IO的前五条SQL,降序

select userid::regrole, dbid, query
from pg_stat_statements
order by (blk_read_time+blk_write_time)/calls desc limit 5;

总消耗IO的前五条SQL,降序

select userid::regrole, dbid, query
from pg_stat_statements
order by (blk_read_time+blk_write_time) desc limit 5;

(2)最耗时的SQL

单次调用耗时前五条SQL,降序

select userid::regrole, dbid, query
from pg_stat_statements
order by mean_time desc limit 5;

总耗时前五条SQL,降序(最关注的)

select userid::regrole, dbid, query
from pg_stat_statements
order by total_time desc limit 5;

(3)响应时间抖动最严重的SQL

select userid::regrole, dbid, query
from pg_stat_statements
order by stddev_time desc limit 5;

(4)最耗共享内存的SQL

select userid::regrole, dbid, query
from pg_stat_statements
order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;

(5)最耗临时空间的SQL

select userid::regrole, dbid, query
from pg_stat_statements
order by temp_blks_written desc limit 5;

六、重置统计信息

可以定期清理历史的统计信息,使用如下SQL

postgres=# select pg_stat_statements_reset();

pg_stat_statements_reset
--------------------------

(1 row)

七、优化方案

1.进行有针对的查询,避免使用select *

如果是要使用count(*)计算,要使用计算行数的子查询

select count(*) from
(
select
id
from users
where preferred_language = 'zh_CN'
and private_profile = True
) as temp;

2.避免使用NOT IN

避免使用IN或者NOT IN,因为这个操作会进行全表扫描。可以使用EXCEPT或NOT EXISTS来代替

3.模糊查询的优化可以参考《PostgreSQL模糊查询优化》这篇文章

4.添加适当的索引

对表中经常进行查询、排序、分组的字段添加索引

(1)b-tree索引

b-tree适合所有的数据类型,支持排序,支持大于、小于、等于、大于或等于、小于或等于的搜索。

postgres=# create index idx_t_btree_1 on t_btree using btree (id);

 

2)hash索引

hash索引存储的是被索引字段VALUE的哈希值,只支持等值查询。hash索引特别适用于字段VALUE非常长(不适合b-tree索引,因为b-tree一个PAGE至少要存储3个ENTRY,所以不支持特别长的VALUE)的情况。

如果只需要等值搜索,可以使用hash索引

postgres=# create index idx_t_hash_1 on t_hash using hash (info);

(3)gin倒排索引

当需要搜索多值类型内的VALUE时,适合多值类型,例如数组、全文检索、TOKEN。

–arr是数组类型
postgres=# create index idx_t_gin1_1 on t_gin1 using gin (arr);

(4)gist索引

它是一种平衡树结构的访问方法,在系统中作为一个基本模版,可以使用它实现任意索引模式。B-trees, R-trees和许多其它的索引模式都可以用GiST实现。但Gist索引创建耗时较长,占用空间也比较大。

postgres=# create index idx_t_gist_1 on t_gist using gist (pos);

(5)sp-gist索引

SP-GiST类似GiST,是一个通用的索引接口,但是SP-GIST使用了空间分区的方法,使得SP-GiST可以更好的支持非平衡数据结构,支持位置搜索。

postgres=# create index idx_t_spgist_1 on t_spgist using spgist (rg);

5.UNION ALL 比 UNION 快

UNION在进行表链接后会筛选掉重复的记录,UNION ALL不会去除重复记录UNION将会按照字段的顺序进行排序,UNION ALL只是简单的将两个结果合并后就返回

F.28.1. pg_stat_statements视图

该模块收集的统计信息通过一个名为 pg_stat_statements的系统视图提供。 这个视图为每个不同的数据库 ID、用户 ID 和查询 ID (最多到该模块可以追踪的不同语句的最大数量)包含一行。该视图的列显示在 表 F-20中。

表 F-20. pg_stat_statements

 

 

 

 

 

标签:插件,statements,blks,stat,索引,pg,SQL,PostgreSQL
From: https://www.cnblogs.com/chuangsi/p/17391158.html

相关文章

  • Postgresql集群搭建与PostGIS安装
    目录Postgresql集群搭建手册一、软件安装预先准备工作二、Postgresql安装(附带PostgreGIS安装)2.1potgresql安装2.2postgres常见错误说明三、PostgreGIS安装postGIS四.Postgresql数据库备份与恢复五.postgresSQL集群重启Postgresql集群搭建手册一、软件安装预先准备工作Linu......
  • OpenSeadragon 实战系列第三方插件
    序言在我们的项目中,一般不可能只是简单的显示图片,对应着还需要做一些图像标注、图像颜色过滤等操作,比如一些医学病理切片。所以openseadragon也为我们提供了一些插件,我们打开官网,找到plugins这些插件中有很多是中间件,各位根据自己的需求自行研究把,在我的项目中只使用......
  • STATA traj插件应用
    usehttps://www.andrew.cmu.edu/user/bjones/traj/data/panss.dta,cleargenoos=0replaceoos=rbinomial(1,.2)traj,var(p1-p6)indep(t1-t6)model(cnorm)min(-999)max(999)order(331)risk(risper)dropout(111)dcov(risperrisperrisp......
  • 未配置appkey或配置错误,uniapp原生安卓插件开发
    开发安卓原生插件一、官方文档1.https://nativesupport.dcloud.net.cn/NativePlugin/course/android2.下载对应的SDK、工程文件二、生成uniapp的appkey1.配置配置好Androidstudio的环境:SDK、JDK2.导入项目3.HbuilderX中创建新的项目“plugin_demo”,同时当前创建项目的APP......
  • Linux xfs文件系统stat命令Birth字段为空的原因探究
    在Linux平台找出某个目录下创建时间最早的文件,测试验证脚本结果是否准确的过程中发现一个很有意思的现象,stat命令在一些平台下Birth字段有值,而在一些平台则为空值,如下所示:RHEL8.7下,XFS文件系统[mysql@mysqlu02 ~]$ more /etc/redhat-release Red Hat Enterprise Linux......
  • netstat
    DisplaysprotocolstatisticsandcurrentTCP/IPnetworkconnections.NETSTAT[-a][-b][-e][-f][-n][-o][-pproto][-r][-s][-t][-x][-y][interval]-aDisplaysallconnectionsandlisteningports.-bDisplaystheexecutab......
  • 使用mybatisplus分页插件,设置了每页容量和页数但还是获取的全部数据(已解决)
    注意观察mybatisplus的查询语句,分页插件正确配置后,在查询时会使用limit限制查询的数量(如下图) 但是如果没配置好就是没有limit这个限制,只要加上配置文件或者确认自己的配置正确即可,配置参考如下博客springboot(二)整合mybatisplus+分页插件+代码生成springboot(二)整合mybat......
  • Jmeter - dubbo插件测试dubbo接口
    转载:https://blog.csdn.net/baidu_28340727/article/details/125570560一、背景对于如何使用Jmeter测试http接口,大家都已经比较熟悉,网上相关的知识也很多。但随着微服务越来越流行,仅仅会测试http接口已经远远不够。Dubbo作为当下非常流行的RPC接口,学习如何测试Dubbo接口已经成为......
  • git push出现冲突,用git status命令查看提示both modified: .idea/workspace.xml 问题
    问题:在github上管理项目,多次提交以后提交提示workspace.xml出现conflicted原因:Android项目在根目录的.gitignore文件中没有添加.idea文件夹忽略。解决办法:1.在.gitignore中添加下面内容:.gitignore.idea/.gitignore介绍:在工程实现过程中,会生成一些中间文件,或者在项目中的部分文......
  • Postgresql insert on conflict笔记
    描述针对数据写入时有主键冲突的情况,INSERTONCONFLICT语法可以将冲突主键的INSERT行为转换为UPDATE行为,从而实现冲突主键的覆盖写入。该特性又称UPSERT覆盖写,与MySQL的REPLACEINTO类似。[WITH[RECURSIVE]with_query[,...]]INSERTINTOtable_name[ASalias][......